来自多个csv的一个字段（列）的数据总和

Question

我有多个像这样的CSV文件

“A.csv”

day,pid,wscore,lscore,wk,wd,lk,ld
1,"A",1,0,1,0,0,1
4,"A",2,0,1,0,1,1
5,"A",1,1,2,0,0,2
6,"A",1,0,2,1,1,2
...

“B.csv文件”

day,pid,wscore,lscore,wk,wd,lk,ld
1,"B",1,0,2,1,1,2
2,"B",2,2,2,1,0,1
4,"B",2,2,2,2,2,1
5,"B",2,1,1,1,1,1
...

“C.csv文件”

day,pid,wscore,lscore,wk,wd,lk,ld
2,"C",2,1,2,1,2,2
3,"C",1,2,2,1,2,2
5,"C",2,2,2,1,1,1
...

我希望将天数和pid一起“滑入”，然后将wscore、lscore、wk、wd、lk和ld的平均值一起“滑入”

示例输出.csv你知道吗

day,pid,wscore,lscore,wk,wd,lk,ld
1,"A,B",1,0,1.5,0.5,0.5,1.5
2,"B,C",2,1.5,2, ...
3,"C",1,2,2, ...
4,"A,B",2,1, ...
5,"A,B,C",2.5, ...
...

我怎样才能做到这一点？ 非常感谢。你知道吗

Answer 1

df=pd.concat([A,B,C],axis=0)#A=pd.read_csv('A.csv')

如果你不需要'，'在pid

 pd.concat([df.groupby(['day'])['pid'].sum(),df.groupby(['day']).mean()],axis=1)
Out[297]: 
     pid    wscore    lscore        wk        wd        lk        ld
day                                                                 
1     AB  1.000000  0.000000  1.500000  0.500000  0.500000  1.500000
2     BC  2.000000  1.500000  2.000000  1.000000  1.000000  1.500000
3      C  1.000000  2.000000  2.000000  1.000000  2.000000  2.000000
4     AB  2.000000  1.000000  1.500000  1.000000  1.500000  1.000000
5    ABC  1.666667  1.333333  1.666667  0.666667  0.666667  1.333333
6      A  1.000000  0.000000  2.000000  1.000000  1.000000  2.000000

如果你需要'，'

pd.concat([df.groupby(['day'])['pid'].apply(lambda x: "%s" % ','.join(x)),df.groupby(['day']).mean()],axis=1)

Out[300]: 
       pid    wscore    lscore        wk        wd        lk        ld
day                                                                   
1      A,B  1.000000  0.000000  1.500000  0.500000  0.500000  1.500000
2      B,C  2.000000  1.500000  2.000000  1.000000  1.000000  1.500000
3        C  1.000000  2.000000  2.000000  1.000000  2.000000  2.000000
4      A,B  2.000000  1.000000  1.500000  1.000000  1.500000  1.000000
5    A,B,C  1.666667  1.333333  1.666667  0.666667  0.666667  1.333333
6        A  1.000000  0.000000  2.000000  1.000000  1.000000  2.000000