Pandas笔记2

##pandas笔记2

#聚合

#在DataFrame的多列上应用多个函数
r = df.rolling(window=3,min_periods=1)
r[[‘A’,‘B’]].aggregate([np.sum,np.mean])

#将不同的函数应用于DataFrame的不同列
r = df.rolling(window=3,min_periods=1)
r.aggregate({‘A’ : np.sum,‘B’ : np.mean})

#缺失数据处理

#检查缺失值
df[‘one’].isnull()

#填充缺失值
#用0填充
df.fillna(0)
#用前值填充method=‘pad’/‘fill’，用后值填充method=‘bfill’/‘backfill’
#df.fillna(method=‘pad’)

#丢弃值
df.dropna()

#分组

ipl_data = {‘Team’: [‘Riders’, ‘Riders’, ‘Devils’, ‘Devils’, ‘Kings’,
‘kings’, ‘Kings’, ‘Kings’, ‘Riders’, ‘Royals’, ‘Royals’, ‘Riders’],
‘Rank’: [1, 2, 2, 3, 3,4 ,1 ,1,2 , 4,1,2],
‘Year’: [2014,2015,2014,2015,2014,2015,2016,2017,2016,2014,2015,2017],
‘Points’:[876,789,863,673,741,812,756,788,694,701,804,690]}
df = pd.DataFrame(ipl_data)

print (df)

#输出
Points Rank Team Year
0 876 1 Riders 2014
1 789 2 Riders 2015
2 863 2 Devils 2014
3 673 3 Devils 2015
4 741 3 Kings 2014
5 812 4 kings 2015
6 756 1 Kings 2016
7 788 1 Kings 2017
8 694 2 Riders 2016
9 701 4 Royals 2014
10 804 1 Royals 2015
11 690 2 Riders 2017

df.groupby(‘Team’)
print(df.groupby(‘Team’).groups)

#输出
{
‘Devils’: Int64Index([2, 3], dtype=‘int64’),
‘Kings’: Int64Index([4, 6, 7], dtype=‘int64’),
‘Riders’: Int64Index([0, 1, 8, 11], dtype=‘int64’),
‘Royals’: Int64Index([9, 10], dtype=‘int64’),
‘kings’: Int64Index([5], dtype=‘int64’)
}

#按多列分组
print (df.groupby([‘Team’,‘Year’]).groups)

{
(‘Devils’, 2014): Int64Index([2], dtype=‘int64’),
(‘Devils’, 2015): Int64Index([3], dtype=‘int64’),
(‘Kings’, 2014): Int64Index([4], dtype=‘int64’),
(‘Kings’, 2016): Int64Index([6], dtype=‘int64’),
(‘Kings’, 2017): Int64Index([7], dtype=‘int64’),
(‘Riders’, 2014): Int64Index([0], dtype=‘int64’),
(‘Riders’, 2015): Int64Index([1], dtype=‘int64’),
(‘Riders’, 2016): Int64Index([8], dtype=‘int64’),
(‘Riders’, 2017): Int64Index([11], dtype=‘int64’),
(‘Royals’, 2014): Int64Index([9], dtype=‘int64’),
(‘Royals’, 2015): Int64Index([10], dtype=‘int64’),
(‘kings’, 2015): Int64Index([5], dtype=‘int64’)
}

#迭代遍历分组

grouped = df.groupby(‘Year’)

for name,group in grouped:
print (name)
print (group)

#输出
2014
Points Rank Team Year
0 876 1 Riders 2014
2 863 2 Devils 2014
4 741 3 Kings 2014
9 701 4 Royals 2014
2015
Points Rank Team Year
1 789 2 Riders 2015
3 673 3 Devils 2015
5 812 4 kings 2015
10 804 1 Royals 2015
2016
Points Rank Team Year
6 756 1 Kings 2016
8 694 2 Riders 2016
2017
Points Rank Team Year
7 788 1 Kings 2017
11 690 2 Riders 2017

#选择一个分组
get_group()

grouped = df.groupby(‘Year’)
print (grouped.get_group(2014))

#输出
Points Rank Team Year
0 876 1 Riders 2014
2 863 2 Devils 2014
4 741 3 Kings 2014
9 701 4 Royals 2014

#聚合
grouped = df.groupby(‘Year’)
print (grouped[‘Points’].agg(np.mean))

#输出
Year
2014 795.25
2015 769.50
2016 725.00
2017 739.00
Name: Points, dtype: float64

#一次应用多个聚合函数
grouped = df.groupby(‘Team’)
agg = grouped[‘Points’].agg([np.sum, np.mean, np.std])
print (agg)

#输出
sum mean std
Team
Devils 1536 768.000000 134.350288
Kings 2285 761.666667 24.006943
Riders 3049 762.250000 88.567771
Royals 1505 752.500000 72.831998
kings 812 812.000000 NaN

#查看每个分组的大小
size()

grouped = df.groupby(‘Team’)
print (grouped.agg(np.size))

#输出
Team
Devils 2 2 2
Kings 3 3 3
Riders 4 4 4
Royals 2 2 2
kings 1 1 1

#转换
#分组或列上的转换返回索引大小与被分组的索引相同的对象
grouped = df.groupby(‘Team’)
score = lambda x: (x - x.mean()) / x.std()*10
print (grouped.transform(score))

#过滤

filter = df.groupby(‘Team’).filter(lambda x: len(x) >= 3)
print (filter)

#输出
Points Rank Team Year
0 876 1 Riders 2014
1 789 2 Riders 2015
4 741 3 Kings 2014
6 756 1 Kings 2016
7 788 1 Kings 2017
8 694 2 Riders 2016
11 690 2 Riders 2017