英文:
Pandas DataFrame.groupby().agg() issue
问题
我尝试使用pandas的groupby()和agg()方法，但遇到了一些问题。
我需要对某些列进行求和（使用agg({'column': sum}）），对其他列进行加权平均，其中权重在一列中，还需要对剩余的列进行平均值计算。
我想在列C上进行加权平均，权重是列B中的值。
然后，对于在agg()中未指定的列（F、G等等，我有很多这些列），我想应用.mean()方法，最终保留所有列。
您能帮助我吗？
谢谢
我尝试过以下代码：
df = df.groupby(['Date', 'Hour']).agg({'A': 'sum','B': 'sum','C': 加权平均?,'D': 'sum','E': 'mean'}).reset_index()
但我不知道如何正确编写它。
英文:
I'm trying to use pandas groupby().agg() but I have some issues.
Date Year Month Week Hour A B C D E F G ..mercoledì 5 aprile 2023 4 14 5 6 6 144,79 0 868,74 6 36mercoledì 5 aprile 2023 4 14 6 214 214 144,79 0 30985,0 6 214mercoledì 5 aprile 2023 4 14 6 6 6 144,79 0 868,74 6 36mercoledì 5 aprile 2023 4 14 7 220 220 180,26 0 39657,2 220 48mercoledì 5 aprile 2023 4 14 7 100 100 180,26 146 18026 100 10mercoledì 5 aprile 2023 4 14 8 220 220 225,2 0 49544 220 48mercoledì 5 aprile 2023 4 14 8 57 57 2,2 146 129,38 6 57
I have to sum some columns (and that goes with agg({'column':sum}), do a weighted average on others, with the weights being in a column, and have to mean() the remaining columns.
df = df.groupby(['Date','Hour']).agg({'A':'sum','B':'sum','C': weighted average?,'D':'sum','E':'mean'}).reset_index()
I wanna do the weighted average on C, and the weights are the values in column B.
Then, for the non-indicated columns in .agg() (F,G, and so on, I have many of them) I wanna apply the method .mean(), keeping all the columns in the end.
Can you help me?
Thank you
Tried this:
df = df.groupby(['Date','Hour']).agg({'A':'sum','B':'sum','C': weighted average?,'D':'sum','E':'mean'}).reset_index()
But I don't know how to properly code it
答案1
得分: 1
以下是翻译好的部分：
import pandas as pddata = {"Date": ["mercoledì 5 aprile 2023", "mercoledì 5 aprile 2023", "mercoledì 5 aprile 2023", "mercoledì 5 aprile 2023", "mercoledì 5 aprile 2023", "mercoledì 5 aprile 2023"],"Year": [2023, 2023, 2023, 2023, 2023, 2023],"Month": [4, 4, 4, 4, 4, 4],"Week": [14, 14, 14, 14, 14, 14],"Hour": [5, 6, 6, 7, 7, 8],"A": [6, 214, 6, 220, 100, 220],"B": [6, 214, 6, 220, 100, 57],"C": [144.79, 144.79, 144.79, 180.26, 180.26, 2.2],"D": [0, 0, 0, 0, 146, 146],"E": [868.74, 30985.0, 868.74, 39657.2, 18026.0, 129.38],"F": [6, 214, 36, 48, 10, 57],"G": [36, 214, 36, 48, 10, 57],}df = pd.DataFrame(data)print(df)# Calculate sum of A, mean of B, and weighted mean of C using B as weightsresult = df.groupby(['Date','Hour']).agg({'A': 'sum','B': 'sum','C': lambda x: (df['B'] * df['C']).sum() / df['B'].sum(),'D': 'sum','E': 'mean'})print(result.reset_index())
请注意，代码中的HTML实体（如"和'）没有被翻译，因为它们是代码的一部分，不需要翻译。
英文:
Something like this:
import pandas as pddata = {"Date": ["mercoledì 5 aprile 2023", "mercoledì 5 aprile 2023", "mercoledì 5 aprile 2023", "mercoledì 5 aprile 2023", "mercoledì 5 aprile 2023", "mercoledì 5 aprile 2023"],"Year": [2023, 2023, 2023, 2023, 2023, 2023],"Month": [4, 4, 4, 4, 4, 4],"Week": [14, 14, 14, 14, 14, 14],"Hour": [5, 6, 6, 7, 7, 8],"A": [6, 214, 6, 220, 100, 220],"B": [6, 214, 6, 220, 100, 57],"C": [144.79, 144.79, 144.79, 180.26, 180.26, 2.2],"D": [0, 0, 0, 0, 146, 146],"E": [868.74, 30985.0, 868.74, 39657.2, 18026.0, 129.38],"F": [6, 214, 36, 48, 10, 57],"G": [36, 214, 36, 48, 10, 57],}df = pd.DataFrame(data)print(df)# Calculate sum of A, mean of B, and weighted mean of C using B as weightsresult = df.groupby(['Date','Hour']).agg({'A': 'sum','B': 'sum','C': lambda x: (df['B'] * df['C']).sum() / df['B'].sum(),'D': 'sum','E': 'mean'})print(result.reset_index())Date Hour A B C D E0 mercoledì 5 aprile 2023 5 6 6 150.134561 0 868.741 mercoledì 5 aprile 2023 6 220 220 150.134561 0 15926.872 mercoledì 5 aprile 2023 7 320 320 150.134561 146 28841.603 mercoledì 5 aprile 2023 8 220 57 150.134561 146 129.38
答案2
得分: 0
不能直接使用agg来计算加权平均值，因为这需要两列*。
一种方法是在计算前/后进行预处理。加权平均值等于sum(C*B)/sum(B)：
out = (df.eval('C = C*B').groupby(['Date', 'Hour']).agg({'A': 'sum','B': 'sum','C': 'sum','D': 'sum','E': 'mean'}).eval('C = C/B').reset_index())
*注意：如果您已经使用B/C计算不同的聚合值，您需要使用它们的副本。
要处理所有列，您可以使用一个字典：
d = {c: 'mean' for c in df.columns.difference(['Date', 'Hour'])}for c in ['A', 'B', 'C', 'D']:d[c] = 'sum'out = (df.eval('C = C*B').groupby(['Date', 'Hour'], as_index=False).agg(d).eval('C = C/B'))
*您可以使用groupby.apply来计算加权平均值，但这应该作为单独的操作进行。
英文:
You cannot compute a weighted average with agg directly as this requires two columns*.
One way would be to pre-/post-process the computation. The weighted average is equal to sum(C*B)/sum(B):
out = (df.eval('C = C*B').groupby(['Date', 'Hour']).agg({'A': 'sum','B': 'sum','C': 'sum','D': 'sum','E': 'mean'}).eval('C = C/B').reset_index())
NB. If you were already computing a different aggregation with B/C you would need to use copies of them.
To handle all columns you can use a dictionary:
d = {c: 'mean' for c in df.columns.difference(['Date', 'Hour'])}for c in ['A', 'B', 'C', 'D']:d[c] = 'sum'out = (df.eval('C = C*B').groupby(['Date', 'Hour'], as_index=False).agg(d).eval('C = C/B'))
* you can however compute the weighted average with groupby.apply, but this should be done as a separate operation.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论