pandas 集团的意思 - 进入数据框？ [英] Pandas groupby mean - into a dataframe?

问题描述

说我的数据如下所示：

date,name,id,dept,sale1,sale2,sale3,total_sale
1/1/17,John,50,Sales,50.0,60.0,70.0,180.0
1/1/17,Mike,21,Engg,43.0,55.0,2.0,100.0
1/1/17,Jane,99,Tech,90.0,80.0,70.0,240.0
1/2/17,John,50,Sales,60.0,70.0,80.0,210.0
1/2/17,Mike,21,Engg,53.0,65.0,12.0,130.0
1/2/17,Jane,99,Tech,100.0,90.0,80.0,270.0
1/3/17,John,50,Sales,40.0,50.0,60.0,150.0
1/3/17,Mike,21,Engg,53.0,55.0,12.0,120.0
1/3/17,Jane,99,Tech,80.0,70.0,60.0,210.0

我想要一个新列 average ，这是 total_sale 对于每个 name，id，dept 元组的平均值

I want a new column average, which is the average of total_sale for each name,id,dept tuple

我试过

I tried

df.groupby(['name', 'id', 'dept'])['total_sale'].mean()

意思是：

And this does return a series with the mean:

name  id  dept 
Jane  99  Tech     240.000000
John  50  Sales    180.000000
Mike  21  Engg     116.666667
Name: total_sale, dtype: float64

但是我将如何参考数据？该系列是一种形状（3，）的一维。理想情况下，我希望将这些数据放回到具有适当列的数据框中，以便我可以通过 name / id / dept 正确引用。

but how would I reference the data? The series is a one dimensional one of shape (3,). Ideally I would like this put back into a dataframe with proper columns so I can reference properly by name/id/dept.

推荐答案

如果您在系列中调用 .reset_index（），它会为您提供您想要的数据框每个级别的索引都将转换为一列）：

If you call .reset_index() on the series that you have, it will get you a dataframe like you want (each level of the index will be converted into a column):

df.groupby(['name', 'id', 'dept'])['total_sale'].mean().reset_index()

编辑：to回应OP的评论，将这个列添加回原始数据框有点棘手。您没有与原始数据框相同的行数，因此您无法将其分配为新列。但是，如果您将索引设置为相同， pandas 很聪明，并且会为您正确填写值。试试这个：

to respond to the OP's comment, adding this column back to your original dataframe is a little trickier. You don't have the same number of rows as in the original dataframe, so you can't assign it as a new column yet. However, if you set the index the same, pandas is smart and will fill in the values properly for you. Try this:

cols = ['date','name','id','dept','sale1','sale2','sale3','total_sale']
data = [
['1/1/17', 'John', 50, 'Sales', 50.0, 60.0, 70.0, 180.0],
['1/1/17', 'Mike', 21, 'Engg', 43.0, 55.0, 2.0, 100.0],
['1/1/17', 'Jane', 99, 'Tech', 90.0, 80.0, 70.0, 240.0],
['1/2/17', 'John', 50, 'Sales', 60.0, 70.0, 80.0, 210.0],
['1/2/17', 'Mike', 21, 'Engg', 53.0, 65.0, 12.0, 130.0],
['1/2/17', 'Jane', 99, 'Tech', 100.0, 90.0, 80.0, 270.0],
['1/3/17', 'John', 50, 'Sales', 40.0, 50.0, 60.0, 150.0],
['1/3/17', 'Mike', 21, 'Engg', 53.0, 55.0, 12.0, 120.0],
['1/3/17', 'Jane', 99, 'Tech', 80.0, 70.0, 60.0, 210.0]
]
df = pd.DataFrame(data, columns=cols)

mean_col = df.groupby(['name', 'id', 'dept'])['total_sale'].mean() # don't reset the index!
df = df.set_index(['name', 'id', 'dept']) # make the same index here
df['mean_col'] = mean_col
df = df.reset_index() # to take the hierarchical index off again

这篇关于 pandas 集团的意思 - 进入数据框？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！