import pandas as pd
import numpy as np
df = pd.DataFrame( {
'A': ['d','d','d','f','f','f','g','g','g','h','h','h'],
'B': [5,5,6,7,5,6,6,7,7,6,7,7],
'C': [1,1,1,1,1,1,1,1,1,1,1,1],
'S': [2012,2013,2014,2015,2016,2012,2013,2014,2015,2016,2012,2013]
} );
df = (df.B + df.C).groupby([df.A, df.S]).sum().unstack(fill_value=0)
print (df)
S 2012 2013 2014 2015 2016
A
d 6 6 7 0 0
f 7 0 0 8 6
g 0 7 8 8 0
h 8 8 0 0 7
我想添加每年在数据框中求和的值的计数以及另外两个列[年总数]和[总计数]
编辑;
Dataframe should look something like this;
S 2012 2012 2013 2013 2014 2014 2015 2015 Tot(sum) Tot(#)
A
d 6 x 6 x 7 x 0 x 19 x
f 7 x 0 x 0 x 8 x 15 x
g 0 x 7 x 8 x 8 x 23 x
h 8 x 8 x 0 x 0 x 16 x
编辑2;
@Jezrael,如果我只选择我需要的行(如另一个问题所述),我会遇到列名相同的问题。我们该如何解决?
编辑3;
顺便说一句,是否有可能在2012年专栏文章中使用通用引用,所以我以后不必更改代码了吗?类似于数据框的第一列; df_without_first列= df.drop(第一列,轴= 1)
最佳答案
我认为您可以使用 aggregate
sum
和size
:
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
然后按第一列级别的
groupby
并获取sum
,将total
级别添加到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
最后
concat
包括DataFrames
和按 sort_index
排序的列: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',
columns='S',
values='D',
aggfunc=[np.sum, len],
fill_value=0,
margins=True,
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,df1], 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
关于python - 将总计的值和列数相加,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/40735688/