《利用python进行数据分析》读书笔记--第七章 数据规整化：清理、转换、合并、重塑（三）

5、示例：usda食品数据库

下面是一个具体的例子，书中最重要的就是例子。

#-*- encoding: utf-8 -*-
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from pandas import Series,DataFrame
import re
import json

#加载下面30M+的数据
db = json.load(open('E:\\foods-2011-10-03.json'))
#print len(db)
#print type(db)  #得到的db是个list,每个条目都是含有某种食物全部数据的字典
#print db[0]  #这一条非常长
#print db[0].keys()
#nutrients 是keys中的一个key，它对应的值是有关食物营养成分的一个字典列表，很长……
#print db[0]['nutrients'][0]
#下面将营养成分做成DataFrame
nutrients = DataFrame(db[0]['nutrients'])  #将字典列表直接做成DataFrame
#print nutrients.head()
#print type(db[0]['nutrients'])
info_keys = ['description','group','id','manufacturer']
info = DataFrame(db,columns = info_keys)
#print info
#查看分类分布情况
#print pd.value_counts(info.group)
#现在，为了将所有的营养数据进行分析，需要将所有营养成分整合到一个大表中，下面分几个步骤来完成
nutrients = []

for rec in db:
    fnuts = DataFrame(rec['nutrients'])
    fnuts['id'] = rec['id']  #广播
    nutrients.append(fnuts)
nutrients = pd.concat(nutrients,ignore_index = True) #将列表连接起来，相当于rbind，把行对其连接在一起

#去重,这是数据处理的重要步骤
print nutrients.duplicated().sum()
nutrients = nutrients.drop_duplicates()
#由于nutrients与info有重复的名字，所以需要重命名一下info
#注意下面这样的命名方式
col_mapping = {'description':'food',
'group':'fgroup'}
#rename函数返回的是副本，需要copy = False
info = info.rename(columns = col_mapping,copy = False)
#print info.columns #查看一下列名
col_mapping = {'description':'nutrient','group':'nutgroup'}
nutrients = nutrients.rename(columns = col_mapping,copy = False)
#print nutrients.columns 
#做完上面这些，显然我们需要将两个DataFrame合并起来
print nutrients.ix[:10,:]
#print info.id
ndata = pd.merge(nutrients,info,on = 'id',how = 'outer')
print ndata
print ndata.ix[3000]
#注意下面的处理方式很nice
result = ndata.groupby(['nutrient','fgroup'])['value'].quantile(0.5)
print result
result['Zinc, Zn'].order().plot(kind = 'barh')
plt.show()
#只要稍微动动脑子（作者不止一次说过了……额），就可以发现各营养成分最为丰富的食物是什么了
by_nuttriend = ndata.groupby(['nutgroup','nutrient'])
print by_nuttriend.head()
#注意下面取出最大值的方式
get_maximum = lambda x:x.xs(x.value.idxmax())
get_minimum = lambda x:x.xs(x.value.idxmin())
max_foods = by_nuttriend.apply(get_maximum)[['value','food']]
#让food小一点
max_foods.food = max_foods.food.str[:50]
print max_foods.head()
print max_foods.ix['Amino Acids']['food']

>>>

14179
                       nutrient     nutgroup units    value    id
0                       Protein  Composition     g    25.18  1008
1             Total lipid (fat)  Composition     g    29.20  1008
2   Carbohydrate, by difference  Composition     g     3.06  1008
3                           Ash        Other     g     3.28  1008
4                        Energy       Energy  kcal   376.00  1008
5                         Water  Composition     g    39.28  1008
6                        Energy       Energy    kJ  1573.00  1008
7          Fiber, total dietary  Composition     g     0.00  1008
8                   Calcium, Ca     Elements    mg   673.00  1008
9                      Iron, Fe     Elements    mg     0.64  1008
10                Magnesium, Mg     Elements    mg    22.00  1008
<class 'pandas.core.frame.DataFrame'>
Int64Index: 375176 entries, 0 to 375175
Data columns:
nutrient        375176  non-null values
nutgroup        375176  non-null values
units           375176  non-null values
value           375176  non-null values
id              375176  non-null values
food            375176  non-null values
fgroup          375176  non-null values
manufacturer    293054  non-null values
dtypes: float64(1), int64(1), object(6)
nutrient                 Glycine
nutgroup             Amino Acids
units                          g
value                      0.073
id                          1077
food            Spearmint, fresh
fgroup          Spices and Herbs
manufacturer                    
Name: 3000
nutrient          fgroup                           
Adjusted Protein  Sweets                               12.900
                  Vegetables and Vegetable Products     2.180
Alanine           Baby Foods                            0.085
                  Baked Products                        0.248
                  Beef Products                         1.550
                  Beverages                             0.003
                  Breakfast Cereals                     0.311
                  Cereal Grains and Pasta               0.373
                  Dairy and Egg Products                0.271
                  Ethnic Foods                          1.290
                  Fast Foods                            0.514
                  Fats and Oils                         0.000
                  Finfish and Shellfish Products        1.218
                  Fruits and Fruit Juices               0.027
                  Lamb, Veal, and Game Products         1.408
...
Zinc, Zn  Finfish and Shellfish Products       0.67
          Fruits and Fruit Juices              0.10
          Lamb, Veal, and Game Products        3.94
          Legumes and Legume Products          1.14
          Meals, Entrees, and Sidedishes       0.63
          Nut and Seed Products                3.29
          Pork Products                        2.32
          Poultry Products                     2.50
          Restaurant Foods                     0.80
          Sausages and Luncheon Meats          2.13
          Snacks                               1.47
          Soups, Sauces, and Gravies           0.20
          Spices and Herbs                     2.75
          Sweets                               0.36
          Vegetables and Vegetable Products    0.33
Length: 2246
<class 'pandas.core.frame.DataFrame'>
MultiIndex: 467 entries, (u'Amino Acids', u'Alanine', 48) to (u'Vitamins', u'Vitamin K (phylloquinone)', 395)
Data columns:
nutrient        467  non-null values
nutgroup        467  non-null values
units           467  non-null values
value           467  non-null values
id              467  non-null values
food            467  non-null values
fgroup          467  non-null values
manufacturer    444  non-null values
dtypes: float64(1), int64(1), object(6)
                            value                                          food
nutgroup    nutrient                                                           
Amino Acids Alanine         8.009             Gelatins, dry powder, unsweetened
            Arginine        7.436                  Seeds, sesame flour, low-fat
            Aspartic acid  10.203                           Soy protein isolate
            Cystine         1.307  Seeds, cottonseed flour, low fat (glandless)
            Glutamic acid  17.452                           Soy protein isolate
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
[Finished in 14.1s]