python计数求和_python – 为总计添加值和列的计数

我想你可以使用aggregate总和和大小：

df = (df.B + df.C).groupby([df.A,df.S]).agg(['sum','size']).unstack(fill_value=0)

print (df)

sum size

S 2012 2013 2014 2015 2016 2012 2013 2014 2015 2016

A

d 6 6 7 0 0 1 1 1 0 0

f 7 0 0 8 6 1 0 0 1 1

g 0 7 8 8 0 0 1 1 1 0

h 8 8 0 0 7 1 1 0 0 1

然后按第一级列组合并得到总和,为MultiIndex的列添加级别总计：

df1 = df.groupby(level=0,axis=1).sum()

new_cols= list(zip(df1.columns.get_level_values(0),['total'] * len(df.columns)))

df1.columns = pd.MultiIndex.from_tuples(new_cols)

print (df1)

sum size

total total

A

d 19 3

f 21 3

g 23 3

h 23 3

df2 = pd.concat([df,df1],axis=1).sort_index(axis=1)

df2.loc['total'] = df2.sum()

print (df2)

size sum

S 2012 2013 2014 2015 2016 total 2012 2013 2014 2015 2016 total

A

d 1 1 1 0 0 3 6 6 7 0 0 19

f 1 0 0 1 1 3 7 0 0 8 6 21

g 0 1 1 1 0 3 0 7 8 8 0 23

h 1 1 0 0 1 3 8 8 0 0 7 23

total 3 3 2 2 2 12 21 21 15 16 13 86

另一个可行的解决方案是pivot_table：

df['D'] = df.B + df.C

print (df.pivot_table(index='A',columns='S',values='D',aggfunc=[np.sum,len],fill_value=0,margins=True,margins_name='Total'))

sum len

S 2012 2013 2014 2015 2016 Total 2012 2013 2014 2015 2016 Total

A

d 6.0 6.0 7.0 0.0 0.0 19.0 1.0 1.0 1.0 0.0 0.0 3.0

f 7.0 0.0 0.0 8.0 6.0 21.0 1.0 0.0 0.0 1.0 1.0 3.0

g 0.0 7.0 8.0 8.0 0.0 23.0 0.0 1.0 1.0 1.0 0.0 3.0

h 8.0 8.0 0.0 0.0 7.0 23.0 1.0 1.0 0.0 0.0 1.0 3.0

Total 21.0 21.0 15.0 16.0 13.0 86.0 3.0 3.0 2.0 2.0 2.0 12.0

如果需要将值转换为int：

print (df.pivot_table(index='A',margins_name='Total')

.astype(int))

sum len

S 2012 2013 2014 2015 2016 Total 2012 2013 2014 2015 2016 Total

A

d 6 6 7 0 0 19 1 1 1 0 0 3

f 7 0 0 8 6 21 1 0 0 1 1 3

g 0 7 8 8 0 23 0 1 1 1 0 3

h 8 8 0 0 7 23 1 1 0 0 1 3

Total 21 21 15 16 13 86 3 3 2 2 2 12

df2 = pd.concat([df,axis=1).sort_index(axis=1).sort_index(axis=1,level=1)

print (df2)

size sum size sum size sum size sum size sum size sum

S 2012 2012 2013 2013 2014 2014 2015 2015 2016 2016 total total

A

d 1 6 1 6 1 7 0 0 0 0 3 19

f 1 7 0 0 0 0 1 8 1 6 3 21

g 0 0 1 7 1 8 1 8 0 0 3 23

h 1 8 1 8 0 0 0 0 1 7 3 23

df2.columns = df2.columns.droplevel(0)

print (df2)

S 2012 2012 2013 2013 2014 2014 2015 2015 2016 2016 total total

A

d 1 6 1 6 1 7 0 0 0 0 3 19

f 1 7 0 0 0 0 1 8 1 6 3 21

g 0 0 1 7 1 8 1 8 0 0 3 23

h 1 8 1 8 0 0 0 0 1 7 3 23