Disclaimer
The opinions expressed in the article are those of the author(s) only,
and may not necessarily represent the views of EFSA or of any other EU
Institution or body.
EFSA’s food consumption data
The European Food Safety Authority collects food consumption data from all EU countries and and integrates them in their database. The data is based on country specific surveys, in which individuals get interviewed about their food consumption habits. This data is used for EFSA’s risk assessments as part of the exposure assessment.
Accessing the data
The data is available from EFSA’s website http://www.efsa.europa.eu/en/food-consumption/comprehensive-database in form of Excel files.
I selected for this tutorial the file of the “Chronic food consumption statistics” - “grams per day” - “all subjects”.
The following code reads the data from the EFSA website and writes it to a local file.
The file contains 4 sheets and we select the last one, which contains data on the lowest foodex level 4.
First download and cache it,
if (!file.exists(&quot;chronicgdaytotpop.xlsx&quot;))
download.file(&quot;http://www.efsa.europa.eu/sites/default/files/chronicgdaytotpop.xlsx&quot;,&quot;chronicgdaytotpop.xlsx&quot;)and the read it into R. The I fix a different spelling in the data versus the shape files to be used later for the map.
data % filter(row_number()==1) %__% glimpse## Observations: 1
## Variables: 17
## $ Country &quot;Austria&quot;
## $ Survey &quot;Austrian Study on Nutritional Status 2010-1...
## $ Pop_Class &quot;Adults&quot;
## $ Foodex_L3 &quot;Wheat milling products&quot;
## $ Foodex_L4 &quot;Couscous&quot;
## $ Metrics &quot;A.01.000051&quot;
## $ Nr_Subjects 308
## $ Nr_Consumers 7
## $ Mean 1.280844
## $ STD 9.331736
## $ P5 0
## $ P10 0
## $ Median 0
## $ P95 0
## $ P97_5 0
## $ P99 56
## $ Comment NAin the following way:
There was a food consumption survey with name ‘Austrian Study on Nutritional Status 2010-12 - Adults’ run in ‘Austria’.
One group of ‘308’ ‘Adults’ was surveyed and the ‘Mean’ food consumption of food ‘Couscous’ in that group was ‘1.28’ g intake per day. There are some more variables for the distribution of the daily intake. Note the large standard deviation, which means that the eating habits of ‘Couscous’ various a lot.
Analysis of standart deviation
One interesting question on this data is, which food are distributed evenly, so most individuals eat them in similar proportions.
One potential interpretation of those, is to say that these are the food which are ‘eaten in all of Europe, in all ages’ in the same quantities.
Lets find those with dplyr, like this:
stds % group_by(Foodex_L4) %__% filter(Mean __ 5) %__% summarise(STD=mean(STD),mean=mean(Mean)) %__% arrange(STD) %__% head(20)
knitr::kable(stds)
Foodex_L4
STD
mean
Salt, iodised
3.092542
6.313222
Salt
4.000443
8.159481
Rice starch
5.130434
6.681824
Fructose
5.279714
6.063511
Oil, frying, blend
6.041108
6.292762
Cocoa powder
6.304501
6.495044
Jelly candies
6.926092
5.243410
Margarine and similar products
7.385982
7.290324
Cream 40 % fat
7.488790
6.402034
Parsley root (Petroselinum crispum)
7.644194
11.049928
Duck meat (Anas spp.)
7.887929
5.294000
Tomato ketchup
7.911607
5.703227
Coffee beans, roasted and ground
8.036786
7.561675
Cooked sausage
8.102821
5.397307
Cheese, Parmigiano Reggiano
8.367041
7.220518
Salad dressing, 25 - 50% oil
8.407296
5.696910
Breadcrumbs
8.685303
6.797546
Spring onions, bulb (Allium cepa)
8.739136
5.635335
Plaice (Pleuronectes)
8.758317
5.157403
Jam, Raspberries (Rubus idaeus)
8.949646
8.119948
So it seems that all Europeans agree on eating similar portions of :
salt
rice starch
fructose
Oil
Cream
tomato ketchup
coffee
cooked sausage
and others.
Prepare data for mapping
For mapping purposes we can now decide which food group we want to use, ‘Jam’ in this case, and need to decide, how to aggregate the data of the different surveys and population groups. In this case I take the most simple approach, which is to average over all surveys and population groups.
This is good enough for illustrative purposes, but a exposure assessment based on this data needed to find a more sophisticated strategy in order to consider methodological differences between the studies.
A more detailed explained on how to use this data, see EFSA’s website.2
jam % filter(Foodex_L4=='Jam') %__% group_by(Country) %__% summarise(food_mean=mean(Mean,na.rm = T))The data is now in a format, ready to be presented in a pan European map, having a single value per country.
knitr::kable(jam)
Country
food_mean
Austria
5.2262848
Belgium
7.3638807
Bulgaria
0.0318451
Cyprus
0.9350935
Czech Rep.
3.6384642
Denmark
0.0000000
Finland
1.3505814
France
6.2648030
Germany
5.7111286
Greece
0.6074363
Hungary
0.0000000
Ireland
0.1185676
Italy
1.7422288
Latvia
1.7141950
Netherlands
4.5594901
Romania
3.9689273
Spain
0.4005222
Sweden
3.0928232
United Kingdom
1.1951599
For this tutorial we will use the tmap package.
It comes already with a shape file of Europe.
First we will restrict it to EU countries:
Europe.eu %
mutate(Country=ifelse(Country=='Czech Republic','Czech Rep.',Country))
Europe.jam % group_by(Country,Foodex_L4) %__% summarise(food_mean=mean(Mean,na.rm = T))
food_data % spread(&quot;Foodex_L4&quot;,'food_mean') %__%
ungroup() %__%
mutate(Country=ifelse(Country=='Czech Republic','Czech Rep.',Country))This results in a table, which has one column per food:
knitr::kable(food_data)
Country
Couscous
Dried fruits
Jam
Wheat grain
Austria
0.2876738
0.1139407
5.2262848
0.0000000
Belgium
0.5355902
0.0032250
7.3638807
0.0000000
Bulgaria
0.0000000
0.0000000
0.0318451
0.0826588
Cyprus
0.0000000
0.0000000
0.9350935
0.0000000
Czech Rep.
0.0000000
0.2356088
3.6384642
0.0000000
Denmark
0.0000000
0.0000000
0.0000000
0.0001695
Finland
0.0303171
0.0333622
1.3505814
0.0000000
France
4.6042196
0.0000000
6.2648030
1.2603470
Germany
0.0124737
0.1177921
5.7111286
0.0356976
Greece
0.0000000
0.0000000
0.6074363
0.0000000
Hungary
0.0000000
0.0000000
0.0000000
0.0000000
Ireland
0.1457767
0.2361331
0.1185676
0.0000000
Italy
0.0589026
0.0006176
1.7422288
0.0000000
Latvia
0.0000000
0.5121008
1.7141950
0.0000000
Netherlands
0.0629168
0.0725512
4.5594901
0.0069556
Romania
0.0355434
0.0000000
3.9689273
0.0018987
Spain
0.0000000
0.0129597
0.4005222
0.0000000
Sweden
0.2039704
0.2110512
3.0928232
0.0230651
United Kingdom
0.5319579
0.1647893
1.1951599
0.0044839
This new data frame will be merged with the shape file
Europe.4foods % filter(row_number()==1) %__% select(Country,Survey,Nr_Subjects,Pop_Class) %__% group_by(Country) %__% summarise(numSubjects=sum(Nr_Subjects))
kable(peopleSurveyed)
Country
numSubjects
Austria
765
Belgium
3744
Bulgaria
1720
Cyprus
303
Czech Rep.
2353
Denmark
8563
Finland
7482
France
4079
Germany
16875
Greece
903
Hungary
1360
Ireland
2458
Italy
3323
Latvia
2913
Netherlands
6587
Romania
1382
Spain
2909
Sweden
5498
United Kingdom
7480
This can be plotted as a bar chart, to compare easily the number of individuals surveyed.
As we can see, there are large differences between them. Some Nordic countries have each surveyed more then 5000 individuals, while others have below 1000.
ggplot(peopleSurveyed) +
geom_bar(aes(x = reorder(Country,numSubjects),
y = numSubjects), stat = 'identity'
) + coord_flip()
The same data shown as map shows a rather clear difference between north and southern Europe.
Does this mean that the (richer) Nordic countries invest more money in food consumption surveys ?
Or is it related to population (only) ?
A first hint to this question is to look at number of individuals together with GDP and population of a country.
Europe.surveyed %
fixCountries
data.veg % filter(!`Foodex L1` %in% c(&quot;Meat and meat products (including edible offal)&quot;,
&quot;Fish and other seafood (including amphibians, rept&quot;,
&quot;Milk and dairy products&quot;,&quot;Eggs and egg products&quot;,
&quot;Animal and vegetable fats and oils&quot;))
veg.country % group_by(Country) %__% summarize(mean=mean(Mean)) %__% arrange(mean)Europe.eu.veg