Preparing the food data collected in HIES for analysis

The steps and considerations involved in processing in-house food quantities and meals consumed data collected in HIES for the analysis, focusing on the distribution of household average dietary energy consumption per capita. It highlights the importance of this distribution in estimating poverty and undernourishment prevalence, as well as informing food systems in the Pacific.

• food data
• HIES
• dietary energy consumption
• poverty estimation
• undernourishment prevalence
• food systems
• Pacific
• data processing

jebediah Follow

Uploaded on Dec 08, 2023 | 8 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. Preparing the food data collected in HIES for analysis Presenter name: Nathalie Troubat Presenter title: Harmonization advisor (SPC/PACTSTAT) Pacific Regional Conference on Poverty Analysis Methods 2 to 6 April 2023 Nadi, Fiji PACSTAT project (P169122): Statistical Innovation and Capacity Building in the Pacific Island Countries

2. This presentation is about The steps to go from in house food quantities and meals consumed AFH collected in HIES into a distribution of household average dietary energy consumption per capita

3. Why do we need the distribution of dietary energy consumption? To derive cost of food basket to estimate poverty (SDG 1) Inform on the distribution of household per capita dietary energy consumption To estimate the prevalence of undernourishment (SDG 2.1.1) Inform food systems in the pacific (factsheets, reports, etc)

6. Food data processing of HIES conducted in the region Some considerations The use of the standard module based on the 2018 WB/FAO recommendations allows for the development of a standard food data processing: Develop a set of routines that can be imported from one country to the other after light adaptation Decrease time of processing Compare results between and within countries when same design and process apply The development of a regional food composition database (PNDB) based on COICOP food product classification: Allows to speed up food matching and between and within comparison as same nutrient factors are used Allows for the analysis of more than 12 essential nutrients Since 2018, all HIES have been processed as joint WB/FAOSAP/SPC collaboration involving NSOS as much as possible to avoid the release of potential conflicting results The approach adopted for the PICS served as basis for the development of the guidelines on food data processing as a joint UN/STATNORWAY/SPC/WB initiative

7. Guidelines on food data processing - What is it? Content Introduction Concepts and principles Step by step: Principles and step-by-step guide 1. Gathering all input and auxiliary data 2. Domain editing, obvious and systematic errors (DOS) 3. Adjust and merge data files 4. Detecting outliers at unit of measure level 5. Monetary value imputation 6. Convert food quantities to grams 7. Edit quantities in gram 8. Calculate dietary energy 9. Impute dietary energy for remaining items 10. Aggregation and macro editing Steps are in short: Gather data Convert quantities to a standard measure Estimate monetary value when it is not already there Estimate kilocalories Checking and cleaning in all the steps 11. Documentation

8. Guidelines on food data processing process so far Meeting of the UN-CEAG (54th Session of the UN Statistical Commission) The drafting team (Ellen Cathrine Ki sterud - coordinator (SSB), Elisabeth Foster (WB), Astrid Mathiassen (SSB), Ana Moltedo (FAO), Nathalie Troubat (SPC)) Open meetings in the Food security and consumption data task team of UN-CEAG First draft circulated in September 2022 Workshop in Rome, October 2022 (COMESA, SPC, WFP, FAO, World Bank, NSO Malawi, NSO Zimbabwe, NSO Zambia) A new version of the guidelines circulated to UN- CEAG ++ with feedback in December 2022 New version is drafted April 2023

9. Main steps of the food data processing Check and correct inconsistencies between quantities and monetary values for each combination product/unit of measurement Code editing, harmonization and merging of all food datasets Assessment and preparation of the information from market survey Conversion of quantities into gram Identify and correct potential outliers in the distribution of quantities in grams per capita and/or food monetary values Convert into nutrient values the expenditures on meals consumed away from home Aggregate and derive the per capita dietary energy consumption and food monetary values distributions Convert into nutrient values all quantities corresponding to in house consumption

10. Code editing, harmonization and merging of the food datasets This task is performed by SPC/SDD All the sections in the questionnaire reporting information on food are merged Household 7 day recall module on in house consumption (one dataset per food product) Individual 7 day recall module on meals prepared and consumed away from home (one dataset per meal event) Individual 3 months recall module on expenditures to extract information on amount spent on alcoholic beverages and tobacco Individual 12 months recall module on expenditures to extract information on amount spent during ceremonies and at school canteen This information is considered only to look at possible double counting in total expenditures and when possible need to be reconciled with in house and AFH modules All codes of food items are edited (check if code associated to product reported are corresponding) All variables are renamed and harmonized following same standards adopted in other PIC surveys

11. Example of structure of the food consumption file

12. Main steps of the food data processing Check and correct inconsistencies between quantities and monetary values for each combination product/unit of measurement Code editing, harmonization and merging of all food datasets Assessment and preparation of the information from market survey Conversion of quantities into gram Identify and correct potential outliers in the distribution of quantities in grams per capita and/or food monetary values Convert into nutrient values the expenditures on meals consumed away from home Aggregate and derive the per capita dietary energy consumption and food monetary values distributions Convert into nutrient values all quantities corresponding to in house consumption

13. Preparation and assessment of the information collected in the market survey Market survey collects information on the price and weight in gram of one unit of product For each food group one dataset all datasets are merged Derive the weight in gram of each combination product/unit of measurement weight in gram of one unit Price of one gram PNDBCode item_desc fooddescriptioninthehies unit N 01.1.1.1.2_99 01.1.1.1.2_99 01.1.1.1.2_99 01.1.1.2.9_99 01.1.1.2.9_99 01.1.1.2.9_99 01.1.1.3.1_97 01.1.1.3.1_97 01.1.1.3.1_99 01.1.1.3.1_99 01.1.1.3.1_99 01.1.1.3.1_99 01.1.1.3.1_99 01.1.1.3.9_03 01.1.1.3.9_03 01.1.1.3.9_03 01.1.1.3.9_03 01.1.1.3.9_96 Rice Rice Rice Flour, wheat, maize Flour, wheat, maize Flour, wheat, maize Bread and local bread Bread and local bread Cracker Biscuits Cracker Biscuits Cracker Biscuits Cracker Biscuits Cracker Biscuits Sweet Biscuits Sweet Biscuits Sweet Biscuits Sweet Biscuits Cakes Rice, not further specified Rice, not further specified Rice, not further specified Flour, not further specified Flour, not further specified Flour, not further specified Bread, loaf, not further specified Bread, loaf, not further specified Crackers, not further specified Crackers, not further specified Crackers, not further specified Crackers, not further specified Crackers, not further specified Biscuits, sweet, all others Biscuits, sweet, all others Biscuits, sweet, all others Biscuits, sweet, all others Cake, not further specified (kg) Kilo-grams Bag Bundle / Bunch / Pack (kg) Kilo-grams Bag Bundle / Bunch / Pack Each / Piece Tray (kg) Kilo-grams (g) Grams Bucket Bundle / Bunch / Pack Each / Piece (g) Grams Box / Carton Bundle / Bunch / Pack Each / Piece Tray 12 43 1000 10000 10000 1000 25000 4000 200 413 1000 1 5000 250 5000 1 2400 250 200 1000 0.0165 0.0020 0.0015 0.0420 0.0018 0.0025 0.0235 0.0388 0.0210 2.7500 0.0056 0.0120 0.0040 4.5000 0.0140 0.0150 0.0283 0.0350 unit may refer to bag instead of kg 1 5 seems there is a problem with the price of one kg 36 1 7 1 3 2 unit seems to be wrong 29 1 1 3 5 6 9 3 unit seems to be wrong unit seems to be wrong Assess the quality of the prices (look at differences in prices by region, look at coefficient of variation, look at differences in prices per one gram of product and unit of measurement etc)

14. Preparation and assessment of the information collected in the market survey Market survey collects information on the price and weight in gram of one unit of product For each food group one dataset all datasets are merged Derive the weight in gram of each combination product/unit of measurement Assess the quality of the prices (look at differences in prices by region, look at coefficient of variation, look at differences in prices per one gram of product and unit of measurement etc)

15. Main steps of the food data processing Check and correct inconsistencies between quantities and monetary values for each combination product/unit of measurement Code editing, harmonization and merging of all food datasets Assessment and preparation of the information from market survey Conversion of quantities into gram Identify and correct potential outliers in the distribution of quantities in grams per capita and/or food monetary values Convert into nutrient values the expenditures on meals consumed away from home Aggregate and derive the per capita dietary energy consumption and food monetary values distributions Convert into nutrient values all quantities corresponding to in house consumption

16. Check and correct inconsistencies between quantities and monetary values reported for each combination product/unit Why it is important to do it? oExample How? oWe looked simultaneously at the distribution of per capita quantities, food expenditures and unit value for each combination product/unit of measurement to detect odd values oOdd values are detected using the Tukey method applying the interquartile range test for outlier detection oDecision to correct or not a quantity or a monetary value adopts a multivariate approach depending on whether or not quantities, food monetary values and unit values were detected as outlier amounts are corrected using the original quantity and the median unit price of that product for that unit

17. How does it work? Ratio price to median price 2.032 1.081 0.100 1.000 0.171 1.000 1.200 8.000 0.100 Price of one unit Median price of one unit Original quantity Median quantity Unit of measurement Orignal expenditure Median expenditure Outlier quantity Outlier expenditure Outlier price Quantity corrected Expenditure corrected Food description Chicken, not further specified Rice, not further specified 2 Bread, loaf, all others Rice, not further specified 2 Rice, not further specified14 Bread, loaf, not further specified Rice, not further specified28 Rice, not further specified28 Bread, loaf, all others 10 2 1 3 16 21 2 21 21 3 (kg) K Bag Each / (c) Cu (c) Cu Bundle (lbs) (lbs) Each / 95 40 24 1.2 1.2 14 16.8 112 1.5 9.8 18.5 3 12.6 10.5 4 10.5 10.5 3 9.5 20.0 0.1 0.6 0.1 2.0 0.6 4.0 0.1 4.7 18.5 1 0.6 0.5 2 0.5 0.5 1 1 1 1 1 0 0 0 0 0 1 1 0 0 1 1 0 0 0 1 0 1 0 1 0 1 1 1 2 9.8 No action 240 24 24 No action 14 7 7 No action No action 28 1.5 14 1.5 15

18. Main steps of the food data processing Check and correct inconsistencies between quantities and monetary values for each combination product/unit of measurement Code editing, harmonization and merging of all food datasets Assessment and preparation of the information from market survey Conversion of quantities into gram Identify and correct potential outliers in the distribution of quantities in grams per capita and/or food monetary values Convert into nutrient values the expenditures on meals consumed away from home Aggregate and derive the per capita dietary energy consumption and food monetary values distributions Convert into nutrient values all quantities corresponding to in house consumption

19. Conversion of quantities into gram Process 3. Convert quantities in local unit into grams using weight in grams per one unit of product from the market survey (regional first and national second) One liter of oil weighs 920 grams 1. Convert into gram all the quantities reported in standard unit (pounds, onces, gram, kg 2. Convert all volumetric unit in gram using density (liter, ml, gallon) One liter of honey weighs 1420g One liter of water weighs 1000 g 6- Estimate remaining quantities in gram dividing the food expenditure by the median price of one gram estimated from the survey 5- Estimate quantities in gram dividing the food expenditure by the median price of one gram from the market survey 4. Convert quantities in gram using ad-hoc conversions

20. Main steps of the food data processing Check and correct inconsistencies between quantities and monetary values for each combination product/unit of measurement Code editing, harmonization and merging of all food datasets Assessment and preparation of the information from market survey Conversion of quantities into gram Identify and correct potential outliers in the distribution of quantities in grams per capita and/or food monetary values Convert into nutrient values the expenditures on meals consumed away from home Aggregate and derive the per capita dietary energy consumption and food monetary values distributions Convert into nutrient values all quantities corresponding to in house consumption

21. Identify and correct extreme quantities or food monetary vaues For each product and area: look at the distribution of the logarithm of the quantities in gram per capita of each product in the region (or area) Estimation of partakers Identification using Tukey interquartile method with a multiplier of 2 or 1.5 quantities detected as outstandingly low or high are corrected using the median quantity in gram of the product in the region or area Quantity of sugar in gram per capita per day 1,500 1,000 Distribution is unchanged but high quantities are detected and replaced with median quantities 500 0 Before correction After correction The corresponding monetary value is corrected multiplying the corrected quantities with the median price of one gram in the region or area (if food monetary value was not detected as an outlier it can be used to correct the quantity)

22. Main steps of the food data processing Check and correct inconsistencies between quantities and monetary values for each combination product/unit of measurement Code editing, harmonization and merging of all food datasets Assessment and preparation of the information from market survey Conversion of quantities into gram Identify and correct potential outliers in the distribution of quantities in grams per capita and/or food monetary values Convert into nutrient values the expenditures on meals consumed away from home Aggregate and derive the per capita dietary energy consumption and food monetary values distributions Convert into nutrient values all quantities corresponding to in house consumption

23. Conversion of quantity into nutrient value 1. Merge the file with the file containing information of the nutrient value per 100 grams of product The refuse factor corresponds to the non edible part of the food : 100 % of butter is edible while only 20% of a crab is edible PNDB database 2. Estimate the dietary energy consumed: Kcal=quantity in gram*(1-refuse/100)*energy_kcal 3. As only number of meals consumed away from home were collected the process to estimate the dietary energy consumed away from home is different

24. Estimation of the dietary energy consumed away from home Process: As information on quantities, composition of the meals or restaurant surveys is not available, dietary energy consumed away from home is estimated applying the average cost of one calorie consumed in the house to the amount spent to consume food away from home. This approach assumes that the composition and the price of a food basket consumed in and outside the house are very similar. WB is estimating the amount of kcal consumed away from home ONLY for reference population and is using a flat cost with an adjustment factor or 1.25 to account for marketing margins and costs of running a food business FAO is using the median cost of one calorie consumed in the house by area and income quintile For most of the surveys using the 7 day recall AFH module, SPC has been using the median cost by region or area and income group together with an adjustment factor between 1.1 and 1.25 (depends on countries)

25. Differences in the various approaches to estimate cost of one kcal Cost of one kcal estimated for the reference population WB (11 to 40th percentile)

26. Impact on the difference in the cost used to estimate kcal consumed away from home The FAO approach systematically over estimate the amount of kcal consumed away from home Using the kcal cost of most consumed foods by reference population disregarding area leads to an overestimation of the kcal consumed by poor people in urban areas and an underestimation of the kcal consumed away from home by poor people in rural area

27. Estimation of the dietary energy consumed away from home one step further Process: When the information on meals consumed away from home is collected by meal event the per calorie cost approach can be further disaggregated based on the type of meals consumed (to account for differences in the cots of meal by type of meal): - For breakfast, lunch and diner we use the median cost of one calorie consumed in the house aggregating on all in house food consumed. - For snacks and non alcoholic beverages we use the cost of snack or non alcoholic beverages consumed in the house (we select only foods corresponding to a snack consumed in the house or to non alcoholic beverages) - For bottle of water we replace amount of calories with 0 - For hot beverages we take the average calories of 100 grams of hot beverage and we assmue one hot beverage weight around 250 grams (one cup) adopt same principle as above bottle of water we only take 0

28. Main steps of the food data processing Check and correct inconsistencies between quantities and monetary values for each combination product/unit of measurement Code editing, harmonization and merging of all food datasets Assessment and preparation of the information from market survey Conversion of quantities into gram Identify and correct potential outliers in the distribution of quantities in grams per capita and/or food monetary values Convert into nutrient values the expenditures on meals consumed away from home Aggregate and derive the per capita dietary energy consumption and food monetary values distributions Convert into nutrient values all quantities corresponding to in house consumption

29. Estimation of the average dietary energy consumption (DEC) of the household (kcal/capita(day) Total calories divided by the number of partakers and the number of days of the reference period (7)

30. Aggregation and final editing of dietary energy and food monetary value distributions Estimate the average dietary energy consumption per capita per day and average food expenditure for each household Take the logarithm of that distribution and use Tukey IQR method or use thresholds (less than 600kcal/capita/day and 4500 kcal/capita/day) Distribution of per capita dietary energy consumption Distribution of per capita food expenditure 5.0e-04 .3 4.0e-04 3.0e-04 .2 Density Density 2.0e-04 .1 1.0e-04 0 0 5000 10000 15000 0 Household daily dietary energy consumption per capita (kcal/capita/day) 0 10 20 30 Household daily food expenditure per capita

31. What to do after unacceptable values have been identified? Many reasons for which we may observe too high or low DEC (disregarding issues related to survey design and field work): Systematic error in the data (wrong coding, wrong unit of measurement, wrong weights in gram of one unit) A product is reported only once and the quantity reported is extreme In case of a diary, effect of fatigue can be observed and some households do not fill the second diary (in such case it may be recommended to consider only one diary for these households) Some households may report as consumed quantities that are not meant to be consumed by the household (for instance a proportion of the quantities of coconuts reported is meant for pig feeding) An important quantity of food is wasted during storage or cooking ! once extreme values identified we should NOT drop them but go back to the data. Process is iterative BUT

32. it can happen we have to drop households! interview__key 12-21-58-87 12-21-58-87 12-21-58-87 12-21-58-87 12-21-58-87 12-21-58-87 96-74-40-76 95-83-06-72 27-07-52-62 27-07-52-62 27-07-52-62 96-88-01-71 82-66-93-57 82-66-93-57 82-66-93-57 82-66-93-57 13-76-94-15 85-63-06-01 description kava kava kava kava tobacco tobacco Salt Island cabbage Powder milk Salt kava Island cabbage Green coconut Pamplemousse, grapefruit Pork meat (fresh or frozen) Salt Rice Tinned mackerel coicop qty unit section 5b - alcohol, tobacco kava 5b - alcohol, tobacco kava 5b - alcohol, tobacco kava 5b - alcohol, tobacco kava 5b - alcohol, tobacco kava 5b - alcohol, tobacco kava amount region PNDBdescription kava kava kava kava tobacco tobacco Salt, not further specified Cabbage, slippery bush 11.32143 Milk, powdered, not further specified 19.11905 Salt, not further specified 19.11905 kava 19.11905 Cabbage, slippery bush 30.19048 Coconut, green 95.23035 Grapefruit 95.23035 Pork, not further specified 95.23035 Salt, not further specified 95.23035 Rice, not further specified 120.8214 Mackerel, canned, not further specified 143.7434 cabbage ppd_cal 231087099 231087099 231087099 231087099 221082002 221082002 119060099 117045006 114030099 119060099 231087099 117045006 116042012 116042043 112012099 119060099 111001099 113025016 1000 Torba 300 Torba 5000 Torba 1000 Torba 1000 Torba 2000 Torba 30 Torba 0 0 0 0 0 0 0 One HH for which no entry in food recall 250 g (grams) 15-foodrecall 1 Bundle / Bunch /packet 2 g (grams) 15-foodrecall 250 Can / Bottle 15-foodrecall 30000 Torba 5b - alcohol, tobacco kava 2 Bundle / Bunch /packet 15-foodrecall 246.1538 Torba 4 Each / Piece 15-foodrecall 126.7026 Sanma 1 Each / Piece 15-foodrecall31.8726 Sanma 15 kg 15-foodrecall 7 Spoon 15-foodrecall 1 kg 15-foodrecall 165.3333 Torba 1 Can / Bottle 15-foodrecall 15-foodrecall 123.0769 Torba 4 Torba One HH that reported only salt 2500 Torba 4875 Sanma 77 Sanma One HH that reported only 200 Torba Weights need to be adjusted

33. Example of high consumption Average calories consumed by the HH (kcal/capita(day) calories from the product consumed Amount original Quantity gram Amount final interview__key Food_description_HIES source Quantity original unit hhsize 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 15-88-00-97 Rice, not further specified Flour, not further specified Sugar, not further specified Chicken, not further specified Coconut, brown Oil, cooking Breadfruit Coffee, mix (e.g. 3in1) Fish, reef, not further specified Home produced Cream, coconut, canned/UHT Home produced Banana, cooking, raw Pandanus Coconut, water only Banana, common e.g. cavendish Home produced Coconut, green Mackerel, canned, not further specified Noodles, not further specified Cash Powdered drink/flavouring, e.g. kool aid/Tang Luncheon meat, chicken Milk, long life, shelf stable (UHT), not further specified Fish, canned in oil, not further specified Spices, not further specified Sauce, soy/shoyu Cash Cash Cash Cash Home produced Cash Home produced Cash 2 1 5 44 10 3 10 10 20 10 10 3 50 10 50 5 6 1 3 3 7 2 4 Bag Bag Bag (lbs) Pounds Each / Piece Can / Bottle Each / Piece Bundle / Bunch / Pack 2.5 (lbs) Pounds (c) Cups (lbs) Pounds Bundle / Bunch / Pack 15 Each / Piece (lbs) Pounds Each / Piece Can / Bottle Each / Piece Can / Bottle Can / Bottle Can / Bottle Can / Bottle Can / Bottle Can / Bottle 26 18 13.5 36 2.5 8.85 20 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 18160 12500 10000 19976 14000 2190.81 16030 2500 9080 2365.88 4540 20430 17500 4540 32050 1800 26 18 4388 3150 2814 2138 1942 1408 973 839 505 278 266 255 241 221 220 194 169 150 114 112 109 100 55 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 20732.5 13.5 36 2.5 8.85 20 2.5 50 2.5 50 2.5 5 Home produced Home produced Home produced 5 15 25 25 5 25 12.5 6 5 13.5 8.55 17.5 4 6 5 Home produced Cash 25 12.5 600 538 1020 3090 992.25 400 2364 6 5 Cash Cash Cash Cash Cash Cash 13.5 8.55 17.5 4 6

34. Final file ready for analysis with distribution of DEC (kcal/capita/day) and food expenditure ($/capita/day) Household per capita average dietary energy consumption Household per capita average food expenditures

35. Thank you Nathalie Troubat: ntroubat68@gmail.com Address/post: Pacific Community (SPC), 95 Promenade Roger Laroque, BP D5 98848 Noumea, New Caledonia Phone: +687 262000 | Fax: +687 263818 | Email: complaints@spc.int | Web: https://spc.int/accountability