D6 分组运算+聚合运算+数据导入导出

1、分组运算
拆分-应用-合并
拆分：根据什么进行分组？
应用：把每个分组进行什么样的计算？
合并：把每个分组的计算结果合并起来

import pandas as pd
import numpy as np

df = pd.DataFrame({'key1':['a','a','b','b','a'],
                   'key2':['one','two','one','two','one'],
                   'data1':np.random.randint(1,10,5),
                   'data2':np.random.randint(1,10,5)})
df
df['data1'].groupby(df['key1']).mean()
key= [1,2,1,1,2]
df['data1'].groupby(key).mean()
df['data1'].groupby([df['key1'],df['key2']]).sum()
df['data1'].groupby([df['key1'],df['key2']]).size()
df.groupby('key1').sum()['data1']
-- 多层索引的分组
mean=df.groupby(['key1','key2']).sum()['data1']
mean
mean.unstack()
-- 用for进行迭代
for name,group in df.groupby('key1'):
    print name
    print group
-- 转换成字典
dict(list(df.groupby('key1')))
-- 按列分组
df.dtypes
df.groupby(df.dtypes,axis=1).sum()

df = pd.DataFrame(np.random.randint(1,10,(5,5)),
                  columns=['a','b','c','d','e'],
                  index=['Alice','Bob','Candy','Dark','Emily'])
df.ix[1,1:3] = np.NaN
df
mapping={'a':'red','b':'red','c':'blue','d':'orange','e':'blue'}
grouped=df.groupby(mapping,axis=1)
grouped.sum()
grouped.size()
grouped.count()
       
-- 对函数进行分组
df = pd.DataFrame(np.random.randint(1,10,(5,5)),
                  columns=['a','b','c','d','e'],
                  index=['Alice','Bob','Candy','Dark','Emily'])
df
def _group_key(idx):
    print idx
    return idx
df.groupby(_group_key).size()

def _group_key(idx):
    print idx
    return len(idx)
df.groupby(_group_key).size()     
df.groupby(len).sum()

--多级索引分组
columns=pd.MultiIndex.from_arrays([['China','USA','China','USA','China'],
                                   ['A','A','B','C','B']],names=['country','index'])
df = pd.DataFrame(np.random.randint(1,10,(5,5)),columns=columns)
df  
-- 根据索引级别分组(默认用行分组)
df.groupby(level='country',axis=1).sum()
df.groupby(level='index',axis=1).sum()         

2、聚合运算
– 内置聚合函数

df = pd.DataFrame({'key1':['a','a','b','b','a'],
                   'key2':['one','two','one','two','one'],
                   'data1':np.random.randint(1,10,5),
                   'data2':np.random.randint(1,10,5)})
df
df.groupby('key1').sum()
df.groupby('key1').mean()
df.groupby('key1').min()
df.groupby('key1').max()
df.groupby('key1').describe()

-- 自定义聚合函数agg
-- peak_range波动幅度
grouped = df.groupby('key1')
def peak_range(s):
    print type(s)
    return s.max()-s.min()
grouped.agg(peak_range) 

-- 多个聚合函数
grouped.agg(['std','mean','sum',peak_range])  
grouped.agg(['std','mean','sum',('range',peak_range)]) 
d = {'data1':['mean',('range',peak_range)],
     'data2':'sum'}
grouped.agg(d)
grouped.agg(d),reset_index()
df.groupby('key1',as_index=False).agg(d)

-- 分组运算
df = pd.DataFrame({'key1':['a','a','b','b','a'],
                   'key2':['one','two','one','two','one'],
                   'data1':np.random.randint(1,10,5),
                   'data2':np.random.randint(1,10,5)})
df  
k1_mean = df.groupby('key1').mean().add_prefix('mean_')
k1_mean
-- 合并两个表
pd.merge(df,k1_mean,left_on='key1',right_index=True)
-- 转换
k1_mean =  df.groupby('key1').transform(np.mean).add_prefix('mean_')
k1_mean
df[k1_mean.columns] = k1_mean
df
-- 自定义函数➕转换
def demean(s):
    return s-s.mean()
key=['one','two','one','two','one']
demeaned = df.groupby(key).transform(demean)
demeaned
demeaned.groupby(key).mean()


df = pd.DataFrame({'key1':['a','a','b','b','a','a','a','b','b','a'],
                   'key2':['one','two','one','two','one','one','two','one','two','one'],
                   'data1':np.random.randint(1,10,10),
                   'data2':np.random.randint(1,10,10)})
df 
-- data1排序后最大的前2行
def top(g,n=2,column='data1'):
    return g.sort_values(by-column,ascending-False)[:n]
top(df)
df.groupby('key1').apply(top)
df.groupby('key1').apply(top,n=3,column='data2')

states=['Ohio','New York','Vermont','Florida',
        'Oregon','Nevada','California','Idaho']
group_key = ['East']*4+['West']*4
data = pd.Series(np.random.randn(8),index=states)
data['Vermont','Nevada','Idaho']]=np.nan
data
data.groupby(group_key).mean()
data.groupby(group_key).apply(fill_mean)
data.groupby(group_key).apply(lambda g: g.fillna(g.mean()))   

3、数据导入导出
数据IO：
● 索引：将一个列或多个列读取出来构成DataFrame，其中涉及是否从文件中读取索引及列名
● 类型推断和数据转换：包括用户自定义的转换及缺失值标记
● 日期解析
● 迭代：针对大文件进行逐块迭代。这个是pandas和python原生的csv库的最大区别
● 不规则数据问题L跳过一些行，或注释
索引及列名
缺失值处理
逐块读取数据
保存数据到磁盘
二进制数据pickle
其他格式简介：HDF5–C语言实现的库，可高效读取磁盘上的二进制存储的科学数据
EXCEL-- pd.read_excel/pd.ExcelFile/pd.ExcelWriter
SQL-- 通过pd.io.sql模块来从数据库读取数据
JSON-- 通过json模块转换为字典，再转换为DataFrame
NoSQL(MongoDB数据库）-- 需要结合相应的数据库模块，如pumongo。再通过游标把数据读出来，转换为DataFrame。

— csv文件：
-- ex1:
a,b,c,d,message
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo
-- ex2:
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo    

-- 读取文件
import pandas as pd
import numpy as np
pd.read_csv('cho4/ex1.csv')
%more ch04/ex1.csv
pd.read_table('ch04/ex1.csv',sep=',')
-- ex2没有列名称
%more ch04/ex2.csv
pd.read_csv('ch04/ex2.csv')   
pd.read_csv('ch04/ex2.csv',header=None)   --随机列名称
pd.read_csv('ch04/ex2.csv',header=None,names=['a','b','c','d','msg'])  --指定列名称
pd.read_csv('ch04/ex2.csv',header=None,names=['a','b','c','d','msg'],index_col='msg')  -- 按msg为索引
pd.read_csv('ch04/ex2.csv',header=None,names=['a','b','c','d','msg'],index_col=['msg','b']) --多级索引


-- ex3:
     A         B         C
aaa  -0.264438 -1.026059  -0.619500
bbb  0.927272  0.302904   -0.032399
ccc  -0.264273 -0.386314  -0.217601
ddd  -0.871858 -0.348283  1.100491

pd.read_table('ch04/ex3.csv',sep='\s+')


--ex5
something,a,b,c,d,message
one,1,2,3,4,NA
two,5,6,,8,world
three,9,10,11,12,foo
-- 处理缺失值
pd.read_csv('ch04/ex5.csv')
pd.read_csv('ch04/ex5.csv',na_values=['NA','NULL','foo'])
pd.read_csv('ch04/ex5.csv',na_values=['message':'NA','NULL','foo'],'something':['two']])

-- ex6是一个大数据
-- 读取前10大
pd.read_csv('cho4/ex6.csv', nrows=10)
-- 迭代
tr =  pd.read_csv('ch04/ex6.csv',chunksize=1000)
tr
-- key列中每个字母出现个数
result=pd.Series([])
for chunk in tr:
  result=result.add(chunk['key'].value_counts(),fill_value=0)
-- 出现最多的前10个
result-result.sort_values(ascending=False)
result[:10]  

df = pd.read_csv('ch04/ex5.csv')
df
df.to_csv('ch04/ex5_out.csv')
df.to_csv('ch04/ex5_out.csv',index=False)
df.to_csv('ch04/ex5_out.csv',index=False,header=None,columns=['b','c','message'],sep='|')