7.5 예제 : 미국 농무부 음식 데이터베이스¶

{ "id": 21411,

"description" : "KENTUCKY FRIED CHICKEN, Fried Chicken, EXTRA CRISPY, Wing, meat and skin with breading", 

"tags" : ["KFC"], 

"manufacturer" : "Kentucky Fried Chicken", 

"group" : "Fast Foods", 

"portions": [

    { 

        "amount":1,             

        "unit": "wing, with skin",

        "grams": 68.0 

    }, 

        ... 

], 

"nutrients" : [ 

    { 

        "value": 20.8", 

        "units": "g",

        "description": "Protein", 

        "group": "Composition"        

    },      

        ... 

]

}

step 1. 데이터 가져오기
step 2. 음식의 이름과 그룹, id 그리고 제조사 추출
step 3. 음식 그룹의 분포 확인
step 4. 영양소 정보 분석
step 5. 시각화
- 음식 그룹과 영양소의 종류별 중간 값 그래프

step 1. 데이터 가져오기¶

In [1]:

import json
import urllib
from pandas import DataFrame, Series
import pandas as pd

url = 'http://www.toplmo.net/~moodern/study/python/plfda/pydata-book/ch07/foods-2011-10-03.json'
r = urllib.urlopen(url)
db = json.load(r)
#db = json.load(open('/home/moodern/study/python/plfda/pydata-book/ch07/foods-2011-10-03.json'))

len(db)

Out[1]:

In [33]:

db[0].keys()

Out[33]:

[u'portions',
 u'description',
 u'tags',
 u'nutrients',
 u'group',
 u'id',
 u'manufacturer']

In [34]:

db[0]['nutrients'][0]

Out[34]:

{u'description': u'Protein',
 u'group': u'Composition',
 u'units': u'g',
 u'value': 25.18}

In [35]:

nutrients = DataFrame(db[0]['nutrients'])

In [42]:

nutrients[:7]

Out[42]:

	description	group	units	value
0	Protein	Composition	g	25.18
1	Total lipid (fat)	Composition	g	29.20
2	Carbohydrate, by difference	Composition	g	3.06
3	Ash	Other	g	3.28
4	Energy	Energy	kcal	376.00
5	Water	Composition	g	39.28
6	Energy	Energy	kJ	1573.00

7 rows × 4 columns

step 2. 음식의 이름과 그룹, id 그리고 제조사 추출¶

In [2]:

info_keys = ['description', 'group', 'id', 'manufacturer']
info = DataFrame(db, columns=info_keys)

In [3]:

info[:5]

Out[3]:

	description	group	id
0	Cheese, caraway	Dairy and Egg Products	1008
1	Cheese, cheddar	Dairy and Egg Products	1009
2	Cheese, edam	Dairy and Egg Products	1018
3	Cheese, feta	Dairy and Egg Products	1019
4	Cheese, mozzarella, part skim milk	Dairy and Egg Products	1028

5 rows × 4 columns

In [48]:

info

Out[48]:

	description	group	id	manufacturer
0	Cheese, caraway	Dairy and Egg Products	1008
1	Cheese, cheddar	Dairy and Egg Products	1009
2	Cheese, edam	Dairy and Egg Products	1018
3	Cheese, feta	Dairy and Egg Products	1019
4	Cheese, mozzarella, part skim milk	Dairy and Egg Products	1028
5	Cheese, mozzarella, part skim milk, low moisture	Dairy and Egg Products	1029
6	Cheese, romano	Dairy and Egg Products	1038
7	Cheese, roquefort	Dairy and Egg Products	1039
8	Cheese spread, pasteurized process, american, ...	Dairy and Egg Products	1048
9	Cream, fluid, half and half	Dairy and Egg Products	1049
10	Sour dressing, non-butterfat, cultured, filled...	Dairy and Egg Products	1058
11	Milk, filled, fluid, with blend of hydrogenate...	Dairy and Egg Products	1059
12	Cream substitute, liquid, with lauric acid oil...	Dairy and Egg Products	1068
13	Cream substitute, powdered	Dairy and Egg Products	1069
14	Milk, producer, fluid, 3.7% milkfat	Dairy and Egg Products	1078
15	Milk, reduced fat, fluid, 2% milkfat, with add...	Dairy and Egg Products	1079	None
16	Milk, reduced fat, fluid, 2% milkfat, with add...	Dairy and Egg Products	1080
17	Milk, reduced fat, fluid, 2% milkfat, protein ...	Dairy and Egg Products	1081
18	Milk, lowfat, fluid, 1% milkfat, with added vi...	Dairy and Egg Products	1082
19	Milk, lowfat, fluid, 1% milkfat, with added no...	Dairy and Egg Products	1083
20	Milk, lowfat, fluid, 1% milkfat, protein forti...	Dairy and Egg Products	1084
21	Milk, nonfat, fluid, with added vitamin A and ...	Dairy and Egg Products	1085
22	Milk, nonfat, fluid, with added nonfat milk so...	Dairy and Egg Products	1086
23	Milk, nonfat, fluid, protein fortified, with a...	Dairy and Egg Products	1087
24	Milk, buttermilk, fluid, cultured, lowfat	Dairy and Egg Products	1088
25	Milk, low sodium, fluid	Dairy and Egg Products	1089
26	Milk, dry, whole, with added vitamin D	Dairy and Egg Products	1090
27	Milk, dry, nonfat, regular, without added vita...	Dairy and Egg Products	1091
28	Milk, dry, nonfat, instant, with added vitamin...	Dairy and Egg Products	1092
29	Milk, dry, nonfat, calcium reduced	Dairy and Egg Products	1093
30	Milk, buttermilk, dried	Dairy and Egg Products	1094
31	Milk, canned, condensed, sweetened	Dairy and Egg Products	1095
32	Milk, canned, evaporated, with added vitamin D...	Dairy and Egg Products	1096
33	Milk, canned, evaporated, nonfat, with added v...	Dairy and Egg Products	1097
34	Milk, indian buffalo, fluid	Dairy and Egg Products	1108
35	Milk, sheep, fluid	Dairy and Egg Products	1109
36	Yogurt, plain, skim milk, 13 grams protein per...	Dairy and Egg Products	1118
37	Yogurt, vanilla, low fat, 11 grams protein per...	Dairy and Egg Products	1119
38	Egg, whole, cooked, fried	Dairy and Egg Products	1128
39	Egg, whole, cooked, hard-boiled	Dairy and Egg Products	1129
40	Egg, duck, whole, fresh, raw	Dairy and Egg Products	1138
41	Egg, goose, whole, fresh, raw	Dairy and Egg Products	1139
42	Cheese, pasteurized process, swiss, without di...	Dairy and Egg Products	1148
43	Cheese food, pasteurized process, american, wi...	Dairy and Egg Products	1149
44	Cheese, goat, soft type	Dairy and Egg Products	1159
45	Cheese, low fat, cheddar or colby	Dairy and Egg Products	1168
46	Cheese, low-sodium, cheddar or colby	Dairy and Egg Products	1169
47	Sour cream, reduced fat	Dairy and Egg Products	1178	None
48	Sour cream, light	Dairy and Egg Products	1179	None
49	Sour cream, fat free	Dairy and Egg Products	1180	None
50	USDA Commodity, cheese, cheddar, reduced fat	Dairy and Egg Products	1182	None
51	Yogurt, vanilla or lemon flavor, nonfat milk, ...	Dairy and Egg Products	1184	None
52	Parmesan cheese topping, fat free	Dairy and Egg Products	1185
53	Cheese, cream, fat free	Dairy and Egg Products	1186
54	Yogurt, chocolate, nonfat milk	Dairy and Egg Products	1187	None
55	KRAFT CHEEZ WHIZ Pasteurized Process Cheese Sauce	Dairy and Egg Products	1188	None
56	KRAFT CHEEZ WHIZ LIGHT Pasteurized Process Che...	Dairy and Egg Products	1189	None
57	KRAFT FREE Singles American Nonfat Pasteurized...	Dairy and Egg Products	1190	None
58	KRAFT VELVEETA Pasteurized Process Cheese Spread	Dairy and Egg Products	1191	None
59	KRAFT VELVEETA LIGHT Reduced Fat Pasteurized P...	Dairy and Egg Products	1192	None
	...	...	...	...

6636 rows × 4 columns

step 3. 음식 그룹의 분포 확인¶

In [49]:

pd.value_counts(info.group)[:10]

Out[49]:

Vegetables and Vegetable Products    812
Beef Products                        618
Baked Products                       496
Breakfast Cereals                    403
Legumes and Legume Products          365
Fast Foods                           365
Lamb, Veal, and Game Products        345
Sweets                               341
Fruits and Fruit Juices              328
Pork Products                        328
dtype: int64

step 4. 영양소 정보 분석¶

각 음식의 영양소 정보를 거대한 테이블에 담아보자
- a) 음식의 영양소 리스트를 DataFrame에 반환 후, 음식 id를 추가
- b) 이 DataFrame을 리스트에 추가
- c) concat 메서드를 이용해서 하나로 합치자
1)에서 만들어진 테이플에서 중복데이터를 제거한다
step3에서 만든 음식 정보를 병합한다

In [4]:

nutrients = []

for rec in db : 
    fnuts = DataFrame(rec['nutrients'])
    fnuts['id'] = rec['id']
    nutrients.append(fnuts)   

In [5]:

nutrients[:2]

Out[5]:

[                           description        group    units     value    id
0                              Protein  Composition        g    25.180  1008
1                    Total lipid (fat)  Composition        g    29.200  1008
2          Carbohydrate, by difference  Composition        g     3.060  1008
3                                  Ash        Other        g     3.280  1008
4                               Energy       Energy     kcal   376.000  1008
5                                Water  Composition        g    39.280  1008
6                               Energy       Energy       kJ  1573.000  1008
7                 Fiber, total dietary  Composition        g     0.000  1008
8                          Calcium, Ca     Elements       mg   673.000  1008
9                             Iron, Fe     Elements       mg     0.640  1008
10                       Magnesium, Mg     Elements       mg    22.000  1008
11                       Phosphorus, P     Elements       mg   490.000  1008
12                        Potassium, K     Elements       mg    93.000  1008
13                          Sodium, Na     Elements       mg   690.000  1008
14                            Zinc, Zn     Elements       mg     2.940  1008
15                          Copper, Cu     Elements       mg     0.024  1008
16                       Manganese, Mn     Elements       mg     0.021  1008
17                        Selenium, Se     Elements      mcg    14.500  1008
18                       Vitamin A, IU     Vitamins       IU  1054.000  1008
19                             Retinol     Vitamins      mcg   262.000  1008
20                      Vitamin A, RAE     Vitamins  mcg_RAE   271.000  1008
21      Vitamin C, total ascorbic acid     Vitamins       mg     0.000  1008
22                             Thiamin     Vitamins       mg     0.031  1008
23                          Riboflavin     Vitamins       mg     0.450  1008
24                              Niacin     Vitamins       mg     0.180  1008
25                    Pantothenic acid     Vitamins       mg     0.190  1008
26                         Vitamin B-6     Vitamins       mg     0.074  1008
27                       Folate, total     Vitamins      mcg    18.000  1008
28                        Vitamin B-12     Vitamins      mcg     0.270  1008
29                          Folic acid     Vitamins      mcg     0.000  1008
30                        Folate, food     Vitamins      mcg    18.000  1008
31                         Folate, DFE     Vitamins  mcg_DFE    18.000  1008
32                         Cholesterol        Other       mg    93.000  1008
33        Fatty acids, total saturated        Other        g    18.584  1008
34  Fatty acids, total monounsaturated        Other        g     8.275  1008
35  Fatty acids, total polyunsaturated        Other        g     0.830  1008
36                          Tryptophan  Amino Acids        g     0.324  1008
37                           Threonine  Amino Acids        g     0.896  1008
38                          Isoleucine  Amino Acids        g     1.563  1008
39                             Leucine  Amino Acids        g     2.412  1008
40                              Lysine  Amino Acids        g     2.095  1008
41                          Methionine  Amino Acids        g     0.659  1008
42                             Cystine  Amino Acids        g     0.126  1008
43                       Phenylalanine  Amino Acids        g     1.326  1008
44                            Tyrosine  Amino Acids        g     1.216  1008
45                              Valine  Amino Acids        g     1.682  1008
46                            Arginine  Amino Acids        g     0.952  1008
47                           Histidine  Amino Acids        g     0.884  1008
48                             Alanine  Amino Acids        g     0.711  1008
49                       Aspartic acid  Amino Acids        g     1.618  1008
50                       Glutamic acid  Amino Acids        g     6.160  1008
51                             Glycine  Amino Acids        g     0.439  1008
52                             Proline  Amino Acids        g     2.838  1008
53                              Serine  Amino Acids        g     1.472  1008
54                             Protein  Composition        g    25.180  1008
55                   Total lipid (fat)  Composition        g    29.200  1008
56         Carbohydrate, by difference  Composition        g     3.060  1008
57                                 Ash        Other        g     3.280  1008
58                              Energy       Energy     kcal   376.000  1008
59                               Water  Composition        g    39.280  1008
                                   ...          ...      ...       ...   ...

[162 rows x 5 columns],
                            description        group    units     value    id
0                              Protein  Composition        g    24.900  1009
1                    Total lipid (fat)  Composition        g    33.140  1009
2          Carbohydrate, by difference  Composition        g     1.280  1009
3                                  Ash        Other        g     3.930  1009
4                               Energy       Energy     kcal   403.000  1009
5                              Sucrose       Sugars        g     0.240  1009
6                              Lactose       Sugars        g     0.230  1009
7                              Maltose       Sugars        g     0.150  1009
8                       Alcohol, ethyl        Other        g     0.000  1009
9                                Water  Composition        g    36.750  1009
10                            Caffeine        Other       mg     0.000  1009
11                         Theobromine        Other       mg     0.000  1009
12                              Energy       Energy       kJ  1684.000  1009
13                       Sugars, total  Composition        g     0.520  1009
14                Fiber, total dietary  Composition        g     0.000  1009
15                         Calcium, Ca     Elements       mg   721.000  1009
16                            Iron, Fe     Elements       mg     0.680  1009
17                       Magnesium, Mg     Elements       mg    28.000  1009
18                       Phosphorus, P     Elements       mg   512.000  1009
19                        Potassium, K     Elements       mg    98.000  1009
20                          Sodium, Na     Elements       mg   621.000  1009
21                            Zinc, Zn     Elements       mg     3.110  1009
22                          Copper, Cu     Elements       mg     0.031  1009
23                         Fluoride, F     Elements      mcg    34.900  1009
24                       Manganese, Mn     Elements       mg     0.010  1009
25                        Selenium, Se     Elements      mcg    13.900  1009
26                       Vitamin A, IU     Vitamins       IU  1002.000  1009
27                             Retinol     Vitamins      mcg   258.000  1009
28                      Vitamin A, RAE     Vitamins  mcg_RAE   265.000  1009
29                      Carotene, beta     Vitamins      mcg    85.000  1009
30                     Carotene, alpha     Vitamins      mcg     0.000  1009
31        Vitamin E (alpha-tocopherol)     Vitamins       mg     0.290  1009
32                           Vitamin D     Vitamins       IU    24.000  1009
33        Vitamin D3 (cholecalciferol)     Vitamins      mcg     0.600  1009
34                 Vitamin D (D2 + D3)     Vitamins      mcg     0.600  1009
35                 Cryptoxanthin, beta     Vitamins      mcg     0.000  1009
36                            Lycopene     Vitamins      mcg     0.000  1009
37                 Lutein + zeaxanthin     Vitamins      mcg     0.000  1009
38                   Tocopherol, gamma     Vitamins       mg     0.000  1009
39                   Tocopherol, delta     Vitamins       mg     0.000  1009
40      Vitamin C, total ascorbic acid     Vitamins       mg     0.000  1009
41                             Thiamin     Vitamins       mg     0.027  1009
42                          Riboflavin     Vitamins       mg     0.375  1009
43                              Niacin     Vitamins       mg     0.080  1009
44                    Pantothenic acid     Vitamins       mg     0.413  1009
45                         Vitamin B-6     Vitamins       mg     0.074  1009
46                       Folate, total     Vitamins      mcg    18.000  1009
47                        Vitamin B-12     Vitamins      mcg     0.830  1009
48                      Choline, total     Vitamins       mg    16.500  1009
49           Vitamin K (phylloquinone)     Vitamins      mcg     2.800  1009
50                          Folic acid     Vitamins      mcg     0.000  1009
51                        Folate, food     Vitamins      mcg    18.000  1009
52                         Folate, DFE     Vitamins  mcg_DFE    18.000  1009
53                             Betaine     Vitamins       mg     0.700  1009
54                    Vitamin E, added     Vitamins       mg     0.000  1009
55                 Vitamin B-12, added     Vitamins      mcg     0.000  1009
56                         Cholesterol        Other       mg   105.000  1009
57        Fatty acids, total saturated        Other        g    21.092  1009
58  Fatty acids, total monounsaturated        Other        g     9.391  1009
59  Fatty acids, total polyunsaturated        Other        g     0.942  1009
                                   ...          ...      ...       ...   ...

[237 rows x 5 columns]]

In [7]:

nutrients = pd.concat(nutrients, ignore_index=True)

In [8]:

nutrients

Out[8]:

	description	group	units	value	id
0	Protein	Composition	g	25.180	1008
1	Total lipid (fat)	Composition	g	29.200	1008
2	Carbohydrate, by difference	Composition	g	3.060	1008
3	Ash	Other	g	3.280	1008
4	Energy	Energy	kcal	376.000	1008
5	Water	Composition	g	39.280	1008
6	Energy	Energy	kJ	1573.000	1008
7	Fiber, total dietary	Composition	g	0.000	1008
8	Calcium, Ca	Elements	mg	673.000	1008
9	Iron, Fe	Elements	mg	0.640	1008
10	Magnesium, Mg	Elements	mg	22.000	1008
11	Phosphorus, P	Elements	mg	490.000	1008
12	Potassium, K	Elements	mg	93.000	1008
13	Sodium, Na	Elements	mg	690.000	1008
14	Zinc, Zn	Elements	mg	2.940	1008
15	Copper, Cu	Elements	mg	0.024	1008
16	Manganese, Mn	Elements	mg	0.021	1008
17	Selenium, Se	Elements	mcg	14.500	1008
18	Vitamin A, IU	Vitamins	IU	1054.000	1008
19	Retinol	Vitamins	mcg	262.000	1008
20	Vitamin A, RAE	Vitamins	mcg_RAE	271.000	1008
21	Vitamin C, total ascorbic acid	Vitamins	mg	0.000	1008
22	Thiamin	Vitamins	mg	0.031	1008
23	Riboflavin	Vitamins	mg	0.450	1008
24	Niacin	Vitamins	mg	0.180	1008
25	Pantothenic acid	Vitamins	mg	0.190	1008
26	Vitamin B-6	Vitamins	mg	0.074	1008
27	Folate, total	Vitamins	mcg	18.000	1008
28	Vitamin B-12	Vitamins	mcg	0.270	1008
29	Folic acid	Vitamins	mcg	0.000	1008
30	Folate, food	Vitamins	mcg	18.000	1008
31	Folate, DFE	Vitamins	mcg_DFE	18.000	1008
32	Cholesterol	Other	mg	93.000	1008
33	Fatty acids, total saturated	Other	g	18.584	1008
34	Fatty acids, total monounsaturated	Other	g	8.275	1008
35	Fatty acids, total polyunsaturated	Other	g	0.830	1008
36	Tryptophan	Amino Acids	g	0.324	1008
37	Threonine	Amino Acids	g	0.896	1008
38	Isoleucine	Amino Acids	g	1.563	1008
39	Leucine	Amino Acids	g	2.412	1008
40	Lysine	Amino Acids	g	2.095	1008
41	Methionine	Amino Acids	g	0.659	1008
42	Cystine	Amino Acids	g	0.126	1008
43	Phenylalanine	Amino Acids	g	1.326	1008
44	Tyrosine	Amino Acids	g	1.216	1008
45	Valine	Amino Acids	g	1.682	1008
46	Arginine	Amino Acids	g	0.952	1008
47	Histidine	Amino Acids	g	0.884	1008
48	Alanine	Amino Acids	g	0.711	1008
49	Aspartic acid	Amino Acids	g	1.618	1008
50	Glutamic acid	Amino Acids	g	6.160	1008
51	Glycine	Amino Acids	g	0.439	1008
52	Proline	Amino Acids	g	2.838	1008
53	Serine	Amino Acids	g	1.472	1008
54	Protein	Composition	g	25.180	1008
55	Total lipid (fat)	Composition	g	29.200	1008
56	Carbohydrate, by difference	Composition	g	3.060	1008
57	Ash	Other	g	3.280	1008
58	Energy	Energy	kcal	376.000	1008
59	Water	Composition	g	39.280	1008
	...	...	...	...	...

389355 rows × 5 columns

In [9]:

nutrients.duplicated().sum()

Out[9]:

In [10]:

nutrients = nutrients.drop_duplicates()

In [11]:

col_mapping = {'description' : 'food', 
                       'group' : 'fgroup'}

info = info.rename(columns = col_mapping, copy=False)
info

Out[11]:

	food	fgroup	id	manufacturer
0	Cheese, caraway	Dairy and Egg Products	1008
1	Cheese, cheddar	Dairy and Egg Products	1009
2	Cheese, edam	Dairy and Egg Products	1018
3	Cheese, feta	Dairy and Egg Products	1019
4	Cheese, mozzarella, part skim milk	Dairy and Egg Products	1028
5	Cheese, mozzarella, part skim milk, low moisture	Dairy and Egg Products	1029
6	Cheese, romano	Dairy and Egg Products	1038
7	Cheese, roquefort	Dairy and Egg Products	1039
8	Cheese spread, pasteurized process, american, ...	Dairy and Egg Products	1048
9	Cream, fluid, half and half	Dairy and Egg Products	1049
10	Sour dressing, non-butterfat, cultured, filled...	Dairy and Egg Products	1058
11	Milk, filled, fluid, with blend of hydrogenate...	Dairy and Egg Products	1059
12	Cream substitute, liquid, with lauric acid oil...	Dairy and Egg Products	1068
13	Cream substitute, powdered	Dairy and Egg Products	1069
14	Milk, producer, fluid, 3.7% milkfat	Dairy and Egg Products	1078
15	Milk, reduced fat, fluid, 2% milkfat, with add...	Dairy and Egg Products	1079	None
16	Milk, reduced fat, fluid, 2% milkfat, with add...	Dairy and Egg Products	1080
17	Milk, reduced fat, fluid, 2% milkfat, protein ...	Dairy and Egg Products	1081
18	Milk, lowfat, fluid, 1% milkfat, with added vi...	Dairy and Egg Products	1082
19	Milk, lowfat, fluid, 1% milkfat, with added no...	Dairy and Egg Products	1083
20	Milk, lowfat, fluid, 1% milkfat, protein forti...	Dairy and Egg Products	1084
21	Milk, nonfat, fluid, with added vitamin A and ...	Dairy and Egg Products	1085
22	Milk, nonfat, fluid, with added nonfat milk so...	Dairy and Egg Products	1086
23	Milk, nonfat, fluid, protein fortified, with a...	Dairy and Egg Products	1087
24	Milk, buttermilk, fluid, cultured, lowfat	Dairy and Egg Products	1088
25	Milk, low sodium, fluid	Dairy and Egg Products	1089
26	Milk, dry, whole, with added vitamin D	Dairy and Egg Products	1090
27	Milk, dry, nonfat, regular, without added vita...	Dairy and Egg Products	1091
28	Milk, dry, nonfat, instant, with added vitamin...	Dairy and Egg Products	1092
29	Milk, dry, nonfat, calcium reduced	Dairy and Egg Products	1093
30	Milk, buttermilk, dried	Dairy and Egg Products	1094
31	Milk, canned, condensed, sweetened	Dairy and Egg Products	1095
32	Milk, canned, evaporated, with added vitamin D...	Dairy and Egg Products	1096
33	Milk, canned, evaporated, nonfat, with added v...	Dairy and Egg Products	1097
34	Milk, indian buffalo, fluid	Dairy and Egg Products	1108
35	Milk, sheep, fluid	Dairy and Egg Products	1109
36	Yogurt, plain, skim milk, 13 grams protein per...	Dairy and Egg Products	1118
37	Yogurt, vanilla, low fat, 11 grams protein per...	Dairy and Egg Products	1119
38	Egg, whole, cooked, fried	Dairy and Egg Products	1128
39	Egg, whole, cooked, hard-boiled	Dairy and Egg Products	1129
40	Egg, duck, whole, fresh, raw	Dairy and Egg Products	1138
41	Egg, goose, whole, fresh, raw	Dairy and Egg Products	1139
42	Cheese, pasteurized process, swiss, without di...	Dairy and Egg Products	1148
43	Cheese food, pasteurized process, american, wi...	Dairy and Egg Products	1149
44	Cheese, goat, soft type	Dairy and Egg Products	1159
45	Cheese, low fat, cheddar or colby	Dairy and Egg Products	1168
46	Cheese, low-sodium, cheddar or colby	Dairy and Egg Products	1169
47	Sour cream, reduced fat	Dairy and Egg Products	1178	None
48	Sour cream, light	Dairy and Egg Products	1179	None
49	Sour cream, fat free	Dairy and Egg Products	1180	None
50	USDA Commodity, cheese, cheddar, reduced fat	Dairy and Egg Products	1182	None
51	Yogurt, vanilla or lemon flavor, nonfat milk, ...	Dairy and Egg Products	1184	None
52	Parmesan cheese topping, fat free	Dairy and Egg Products	1185
53	Cheese, cream, fat free	Dairy and Egg Products	1186
54	Yogurt, chocolate, nonfat milk	Dairy and Egg Products	1187	None
55	KRAFT CHEEZ WHIZ Pasteurized Process Cheese Sauce	Dairy and Egg Products	1188	None
56	KRAFT CHEEZ WHIZ LIGHT Pasteurized Process Che...	Dairy and Egg Products	1189	None
57	KRAFT FREE Singles American Nonfat Pasteurized...	Dairy and Egg Products	1190	None
58	KRAFT VELVEETA Pasteurized Process Cheese Spread	Dairy and Egg Products	1191	None
59	KRAFT VELVEETA LIGHT Reduced Fat Pasteurized P...	Dairy and Egg Products	1192	None
	...	...	...	...

6636 rows × 4 columns

In [12]:

col_mapping = {'description' : 'nutrient',
                       'group' : 'nutgroup'}
nutrients = nutrients.rename(columns=col_mapping, copy=False)

nutrients

Out[12]:

	nutrient	nutgroup	units	value	id
0	Protein	Composition	g	25.180	1008
1	Total lipid (fat)	Composition	g	29.200	1008
2	Carbohydrate, by difference	Composition	g	3.060	1008
3	Ash	Other	g	3.280	1008
4	Energy	Energy	kcal	376.000	1008
5	Water	Composition	g	39.280	1008
6	Energy	Energy	kJ	1573.000	1008
7	Fiber, total dietary	Composition	g	0.000	1008
8	Calcium, Ca	Elements	mg	673.000	1008
9	Iron, Fe	Elements	mg	0.640	1008
10	Magnesium, Mg	Elements	mg	22.000	1008
11	Phosphorus, P	Elements	mg	490.000	1008
12	Potassium, K	Elements	mg	93.000	1008
13	Sodium, Na	Elements	mg	690.000	1008
14	Zinc, Zn	Elements	mg	2.940	1008
15	Copper, Cu	Elements	mg	0.024	1008
16	Manganese, Mn	Elements	mg	0.021	1008
17	Selenium, Se	Elements	mcg	14.500	1008
18	Vitamin A, IU	Vitamins	IU	1054.000	1008
19	Retinol	Vitamins	mcg	262.000	1008
20	Vitamin A, RAE	Vitamins	mcg_RAE	271.000	1008
21	Vitamin C, total ascorbic acid	Vitamins	mg	0.000	1008
22	Thiamin	Vitamins	mg	0.031	1008
23	Riboflavin	Vitamins	mg	0.450	1008
24	Niacin	Vitamins	mg	0.180	1008
25	Pantothenic acid	Vitamins	mg	0.190	1008
26	Vitamin B-6	Vitamins	mg	0.074	1008
27	Folate, total	Vitamins	mcg	18.000	1008
28	Vitamin B-12	Vitamins	mcg	0.270	1008
29	Folic acid	Vitamins	mcg	0.000	1008
30	Folate, food	Vitamins	mcg	18.000	1008
31	Folate, DFE	Vitamins	mcg_DFE	18.000	1008
32	Cholesterol	Other	mg	93.000	1008
33	Fatty acids, total saturated	Other	g	18.584	1008
34	Fatty acids, total monounsaturated	Other	g	8.275	1008
35	Fatty acids, total polyunsaturated	Other	g	0.830	1008
36	Tryptophan	Amino Acids	g	0.324	1008
37	Threonine	Amino Acids	g	0.896	1008
38	Isoleucine	Amino Acids	g	1.563	1008
39	Leucine	Amino Acids	g	2.412	1008
40	Lysine	Amino Acids	g	2.095	1008
41	Methionine	Amino Acids	g	0.659	1008
42	Cystine	Amino Acids	g	0.126	1008
43	Phenylalanine	Amino Acids	g	1.326	1008
44	Tyrosine	Amino Acids	g	1.216	1008
45	Valine	Amino Acids	g	1.682	1008
46	Arginine	Amino Acids	g	0.952	1008
47	Histidine	Amino Acids	g	0.884	1008
48	Alanine	Amino Acids	g	0.711	1008
49	Aspartic acid	Amino Acids	g	1.618	1008
50	Glutamic acid	Amino Acids	g	6.160	1008
51	Glycine	Amino Acids	g	0.439	1008
52	Proline	Amino Acids	g	2.838	1008
53	Serine	Amino Acids	g	1.472	1008
162	Protein	Composition	g	24.900	1009
163	Total lipid (fat)	Composition	g	33.140	1009
164	Carbohydrate, by difference	Composition	g	1.280	1009
165	Ash	Other	g	3.930	1009
166	Energy	Energy	kcal	403.000	1009
167	Sucrose	Sugars	g	0.240	1009
	...	...	...	...	...

375176 rows × 5 columns

In [13]:

ndata = pd.merge(nutrients, info, on='id', how='outer')

In [14]:

ndata

Out[14]:

	nutrient	nutgroup	units	value	id	food	fgroup	manufacturer
0	Protein	Composition	g	25.180	1008	Cheese, caraway	Dairy and Egg Products
1	Total lipid (fat)	Composition	g	29.200	1008	Cheese, caraway	Dairy and Egg Products
2	Carbohydrate, by difference	Composition	g	3.060	1008	Cheese, caraway	Dairy and Egg Products
3	Ash	Other	g	3.280	1008	Cheese, caraway	Dairy and Egg Products
4	Energy	Energy	kcal	376.000	1008	Cheese, caraway	Dairy and Egg Products
5	Water	Composition	g	39.280	1008	Cheese, caraway	Dairy and Egg Products
6	Energy	Energy	kJ	1573.000	1008	Cheese, caraway	Dairy and Egg Products
7	Fiber, total dietary	Composition	g	0.000	1008	Cheese, caraway	Dairy and Egg Products
8	Calcium, Ca	Elements	mg	673.000	1008	Cheese, caraway	Dairy and Egg Products
9	Iron, Fe	Elements	mg	0.640	1008	Cheese, caraway	Dairy and Egg Products
10	Magnesium, Mg	Elements	mg	22.000	1008	Cheese, caraway	Dairy and Egg Products
11	Phosphorus, P	Elements	mg	490.000	1008	Cheese, caraway	Dairy and Egg Products
12	Potassium, K	Elements	mg	93.000	1008	Cheese, caraway	Dairy and Egg Products
13	Sodium, Na	Elements	mg	690.000	1008	Cheese, caraway	Dairy and Egg Products
14	Zinc, Zn	Elements	mg	2.940	1008	Cheese, caraway	Dairy and Egg Products
15	Copper, Cu	Elements	mg	0.024	1008	Cheese, caraway	Dairy and Egg Products
16	Manganese, Mn	Elements	mg	0.021	1008	Cheese, caraway	Dairy and Egg Products
17	Selenium, Se	Elements	mcg	14.500	1008	Cheese, caraway	Dairy and Egg Products
18	Vitamin A, IU	Vitamins	IU	1054.000	1008	Cheese, caraway	Dairy and Egg Products
19	Retinol	Vitamins	mcg	262.000	1008	Cheese, caraway	Dairy and Egg Products
20	Vitamin A, RAE	Vitamins	mcg_RAE	271.000	1008	Cheese, caraway	Dairy and Egg Products
21	Vitamin C, total ascorbic acid	Vitamins	mg	0.000	1008	Cheese, caraway	Dairy and Egg Products
22	Thiamin	Vitamins	mg	0.031	1008	Cheese, caraway	Dairy and Egg Products
23	Riboflavin	Vitamins	mg	0.450	1008	Cheese, caraway	Dairy and Egg Products
24	Niacin	Vitamins	mg	0.180	1008	Cheese, caraway	Dairy and Egg Products
25	Pantothenic acid	Vitamins	mg	0.190	1008	Cheese, caraway	Dairy and Egg Products
26	Vitamin B-6	Vitamins	mg	0.074	1008	Cheese, caraway	Dairy and Egg Products
27	Folate, total	Vitamins	mcg	18.000	1008	Cheese, caraway	Dairy and Egg Products
28	Vitamin B-12	Vitamins	mcg	0.270	1008	Cheese, caraway	Dairy and Egg Products
29	Folic acid	Vitamins	mcg	0.000	1008	Cheese, caraway	Dairy and Egg Products
30	Folate, food	Vitamins	mcg	18.000	1008	Cheese, caraway	Dairy and Egg Products
31	Folate, DFE	Vitamins	mcg_DFE	18.000	1008	Cheese, caraway	Dairy and Egg Products
32	Cholesterol	Other	mg	93.000	1008	Cheese, caraway	Dairy and Egg Products
33	Fatty acids, total saturated	Other	g	18.584	1008	Cheese, caraway	Dairy and Egg Products
34	Fatty acids, total monounsaturated	Other	g	8.275	1008	Cheese, caraway	Dairy and Egg Products
35	Fatty acids, total polyunsaturated	Other	g	0.830	1008	Cheese, caraway	Dairy and Egg Products
36	Tryptophan	Amino Acids	g	0.324	1008	Cheese, caraway	Dairy and Egg Products
37	Threonine	Amino Acids	g	0.896	1008	Cheese, caraway	Dairy and Egg Products
38	Isoleucine	Amino Acids	g	1.563	1008	Cheese, caraway	Dairy and Egg Products
39	Leucine	Amino Acids	g	2.412	1008	Cheese, caraway	Dairy and Egg Products
40	Lysine	Amino Acids	g	2.095	1008	Cheese, caraway	Dairy and Egg Products
41	Methionine	Amino Acids	g	0.659	1008	Cheese, caraway	Dairy and Egg Products
42	Cystine	Amino Acids	g	0.126	1008	Cheese, caraway	Dairy and Egg Products
43	Phenylalanine	Amino Acids	g	1.326	1008	Cheese, caraway	Dairy and Egg Products
44	Tyrosine	Amino Acids	g	1.216	1008	Cheese, caraway	Dairy and Egg Products
45	Valine	Amino Acids	g	1.682	1008	Cheese, caraway	Dairy and Egg Products
46	Arginine	Amino Acids	g	0.952	1008	Cheese, caraway	Dairy and Egg Products
47	Histidine	Amino Acids	g	0.884	1008	Cheese, caraway	Dairy and Egg Products
48	Alanine	Amino Acids	g	0.711	1008	Cheese, caraway	Dairy and Egg Products
49	Aspartic acid	Amino Acids	g	1.618	1008	Cheese, caraway	Dairy and Egg Products
50	Glutamic acid	Amino Acids	g	6.160	1008	Cheese, caraway	Dairy and Egg Products
51	Glycine	Amino Acids	g	0.439	1008	Cheese, caraway	Dairy and Egg Products
52	Proline	Amino Acids	g	2.838	1008	Cheese, caraway	Dairy and Egg Products
53	Serine	Amino Acids	g	1.472	1008	Cheese, caraway	Dairy and Egg Products
54	Protein	Composition	g	24.900	1009	Cheese, cheddar	Dairy and Egg Products
55	Total lipid (fat)	Composition	g	33.140	1009	Cheese, cheddar	Dairy and Egg Products
56	Carbohydrate, by difference	Composition	g	1.280	1009	Cheese, cheddar	Dairy and Egg Products
57	Ash	Other	g	3.930	1009	Cheese, cheddar	Dairy and Egg Products
58	Energy	Energy	kcal	403.000	1009	Cheese, cheddar	Dairy and Egg Products
59	Sucrose	Sugars	g	0.240	1009	Cheese, cheddar	Dairy and Egg Products
	...	...	...	...	...	...	...	...

375176 rows × 8 columns

In [15]:

ndata.ix[30000]

Out[15]:

nutrient                                       Glycine
nutgroup                                   Amino Acids
units                                                g
value                                             0.04
id                                                6158
food            Soup, tomato bisque, canned, condensed
fgroup                      Soups, Sauces, and Gravies
manufacturer                                          
Name: 30000, dtype: object

step 5. 시각화¶

In [16]:

result = ndata.groupby(['nutrient', 'fgroup'])['value'].quantile(0.5)

In [17]:

result['Zinc, Zn'].order().plot(kind='barh')

Out[17]:

<matplotlib.axes.AxesSubplot at 0x1b6ff690>

In [22]:

by_nutrient = ndata.groupby(['nutgroup', 'nutrient'])

get_maximum = lambda x: x.xs(x.value.idxmax())
get_minimum = lambda x: x.xs(x.value.idxmin())

max_foods = by_nutrient.apply(get_maximum)[['value', 'food']]

max_foods.food = max_foods.food.str[:50]

In [23]:

max_foods.ix['Amino Acids']['food']

Out[23]:

nutrient
Alanine                           Gelatins, dry powder, unsweetened
Arginine                               Seeds, sesame flour, low-fat
Aspartic acid                                   Soy protein isolate
Cystine                Seeds, cottonseed flour, low fat (glandless)
Glutamic acid                                   Soy protein isolate
Glycine                           Gelatins, dry powder, unsweetened
Histidine                Whale, beluga, meat, dried (Alaska Native)
Hydroxyproline    KENTUCKY FRIED CHICKEN, Fried Chicken, ORIGINA...
Isoleucine        Soy protein isolate, PROTEIN TECHNOLOGIES INTE...
Leucine           Soy protein isolate, PROTEIN TECHNOLOGIES INTE...
Lysine            Seal, bearded (Oogruk), meat, dried (Alaska Na...
Methionine                    Fish, cod, Atlantic, dried and salted
Phenylalanine     Soy protein isolate, PROTEIN TECHNOLOGIES INTE...
Proline                           Gelatins, dry powder, unsweetened
Serine            Soy protein isolate, PROTEIN TECHNOLOGIES INTE...
Threonine         Soy protein isolate, PROTEIN TECHNOLOGIES INTE...
Tryptophan         Sea lion, Steller, meat with fat (Alaska Native)
Tyrosine          Soy protein isolate, PROTEIN TECHNOLOGIES INTE...
Valine            Soy protein isolate, PROTEIN TECHNOLOGIES INTE...
Name: food, dtype: object