Aggregate Data For a List of Data Frames Pandas

Question

I have a list of data frames like this,

   Name  Product    Quantity
0  A     1010       10
1  A     2010       12
2  B     4145       18
3  B     5225       14
4  B     6223       16
5  C     7222       18

   Name  Product    Quantity
0  A     1010       14
1  A     2010       12
2  B     4145       21
3  B     5225       18
4  B     7565       19
5  C     7222       11

   Name  Product    Quantity
0  A     1010       15
1  A     2010       14
2  B     4145       13
3  B     5225       15
4  B     7565       17

From above list of I would like to get the average quantity (rounded) for each product that is under the same name.

Output needed is,

   Name  Product    Avg Quantity
0  A     1010       13
1  A     2010       13
2  B     4145       17
3  B     5225       16
4  B     6223       5
5  B     7565       12
6  C     7222       10

For now, I do not know how to go about this in pandas unless I save each data frame into an excel file/sheet and combine them in excel. I do not want that route, I like to do it in pandas.

The closest I could find an earlier post is this. However, that did not help. Any suggestion would be great to achieve my output.

score 1 · Answer 1 · answered Jan 25 '19 at 15:38

So concat your all dfs first , then using groupby with mean

yourdf=pd.concat([df1,df2,df3]).groupby(['Name','Product'])['Quantity'].mean().reset_index()
yourdf
Out[410]: 
  Name  Product   Quantity
0    A     1010  13.000000
1    A     2010  12.666667
2    B     4145  17.333333
3    B     5225  15.666667
4    B     6223  16.000000
5    B     7565  18.000000
6    C     7222  14.500000

Aggregate Data For a List of Data Frames Pandas

1 Answers1