英文:
Iterating over rows and groups in dataframe
问题
假设我有以下数据框：
d = {'Date': ['2020-1-1', '2020-1-2', '2020-1-3', '2020-1-1', '2020-1-2', '2020-1-3', '2020-1-1', '2020-1-2', '2020-1-3'],'col2': ['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C'],'col3': [0.01, 0.02, 0.03, 0.02, 0.03, 0.04, 0.05, 0.1, 0.01]}d = pd.DataFrame(data=d)d['Date'] = pd.to_datetime(d['Date'])
并且得到了以下数据框：
Date col2 col30 2020-01-01 A 0.011 2020-01-02 A 0.022 2020-01-03 A 0.033 2020-01-01 B 0.024 2020-01-02 B 0.035 2020-01-03 B 0.046 2020-01-01 C 0.057 2020-01-02 C 0.108 2020-01-03 C 0.01
你想要遍历行，以便对于每个Date获取col3的两个最高值，并按col2分组。例如，你应该得到以下结果：
Date col2 col33 2020-01-01 B 0.026 2020-01-01 C 0.054 2020-01-02 B 0.037 2020-01-02 C 0.102 2020-01-03 A 0.035 2020-01-03 B 0.04
最后，对于每一天，你想要计算col3的总和，如下所示：
Date sum0 2020-01-01 0.071 2020-01-02 0.132 2020-01-03 0.07
请注意，实际示例中可能有更多的组（col2）和更多的日期。
英文:
Suppose I have following data frame:
d = {'Date': ['2020-1-1', '2020-1-2', '2020-1-3', '2020-1-1', '2020-1-2','2020-1-3','2020-1-1', '2020-1-2', '2020-1-3'], 'col2': ['A','A','A', 'B','B','B', 'C','C','C'],'col3':[0.01,0.02,0.03,0.02,0.03,0.04,0.05,0.1,0.01]}d = pd.DataFrame(data=d)d['Date'] = pd.to_datetime(d['Date'])d
and get:
Date col2 col30 2020-01-01 A 0.011 2020-01-02 A 0.022 2020-01-03 A 0.033 2020-01-01 B 0.024 2020-01-02 B 0.035 2020-01-03 B 0.046 2020-01-01 C 0.057 2020-01-02 C 0.108 2020-01-03 C 0.01
How could I iterate over rows, so that I get for each Date the 2 highest values of col3 and group from col2?
So for example I should get:
2020-01-01 B 0.022020-01-01 C 0.052020-01-02 B 0.032020-01-02 C 0.102020-01-03 A 0.032020-01-03 B 0.04
And at the end to sum for each day col 3:
Date sum2020-01-01 0.072020-01-02 0.132020-01-03 0.07
But of course real example has much more groups (col2) and more dates.
答案1
得分: 1
我认为这样：d[['日期', 'col3']].groupby('日期')['col3'].nlargest(2).groupby('日期').sum()
英文:
I think this:
d[['Date', 'col3']].groupby('Date')['col3'].nlargest(2).groupby('Date').sum()
答案2
得分: 1
grouped_df = d.groupby("日期")results = []for name, group in grouped_df:group.sort_values(by="col3", ascending=False, inplace=True)top_2 = group.nlargest(2, "col3")top_2_sum = top_2["col3"].sum()results.append((name, top_2_sum))sum_df = pd.DataFrame(results, columns=["日期", "总和"])print(sum_df)
Output:
日期 总和0 2020-01-01 0.071 2020-01-02 0.132 2020-01-03 0.07
英文:
grouped_df = d.groupby("Date")results = []for name, group in grouped_df:group.sort_values(by="col3", ascending=False, inplace=True)top_2 = group.nlargest(2, "col3")top_2_sum = top_2["col3"].sum()results.append((name, top_2_sum))sum_df = pd.DataFrame(results, columns=["Date", "Sum"])print(sum_df)
Output:
Date Sum0 2020-01-01 0.071 2020-01-02 0.132 2020-01-03 0.07
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论