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='|')