python - 按 Pandas 数据框的相同部分字符串分组

我一直在使用熊猫将JSON数据导出到csv文件。现在，我被要求对这些数据进行分组，并获取按system分组的每个日期的总和。以下是我的DataFrame的示例。

数据框：

system,totalCapacity,totalLocatedCapacity,availableCapacity,date
aadata02,96155472,99924183,39116616,20170728
aadata02,41943174,41614541,15946266,20170728
aadata03,52764600,50966017,13839882,20170728
aadata03,52764600,44043720,15503376,20170728
aadata03,37373700,35654440,7073598,20170728
...
bbdata01,38473680,25168248,24006696,20170728
bbdata01,17585400,14681478,11711826,20170728
bbdata01,22015224,6907992,20668746,20170728

我从以下代码开始：

import pandas as pd

csvin = "test.csv"
csvout = "test2.csv"

df = pd.read_csv(csvin)
col = ['site', 'totalPoolCapacity', 'totalLocatedCapacity',
       'availableVolumeCapacity', 'date']
df = df.groupby('date', as_index=False).sum()
df['site'] = pd.Series('aa', index=df.index)
with open(csvout, 'w+') as f:
    df.to_csv(f, index=False, header=True, columns=col)

这只会按日期对所有内容进行求和，并将所有内容置于站点aa下。如何修改我的代码，使其以以下方式输出：

site,totalCapacity,totalLocatedCapacity,availableCapacity,date
aa,903240114,735713005,421348788,20170728
bb,78074304,46757718,56387268,20170728

最佳答案

你在找吗

df.groupby([df.system.str[:2], 'date']).sum().reset_index()

    system  date    totalCapacity   totalLocatedCapacity    availableCapacity
0   aa      20170728    281001546   272202901               91479738
1   bb      20170728    78074304    46757718                56387268