python - 各个月份的列值总和

Question

我有一个像这样的数据框:

Timestamp   Consumption
4/1/2017 20:00  257
4/1/2017 21:00  262
4/1/2017 22:00  256
4/1/2017 23:00  256
4/2/2017 0:00   263
4/2/2017 1:00   256
4/2/2017 2:00   265
4/2/2017 3:00   259
4/2/2017 4:00   256
4/2/2017 5:00   260
4/2/2017 6:00   265
4/2/2017 7:00   265

我想计算各个月份的消费总和并将其放入列表中。喜欢:

[1031,2089]

并根据时间和日期进行求和。例如 23:00 至 06:00:

[2080]

我怎样才能实现这个目标？请帮忙。

Answer 1

第一个转换列 to_datetime :

df.Timestamp = pd.to_datetime(df.Timestamp, dayfirst=True)

然后resample按月计算 sum:

a = df.resample('m', on='Timestamp')['Consumption'].sum().dropna().tolist()
print (a)
[1031, 2089]

另一个类似的解决方案 - 添加了 set_index :

a = df.set_index('Timestamp').resample('m')['Consumption'].sum().dropna().tolist()
print (a)
[1031, 2089]

使用groupby解决方案，Grouper和总和:

a = df.set_index('Timestamp')
      .groupby(pd.Grouper(freq='m'))['Consumption']
      .sum()
      .dropna()
      .tolist()
print (a)
[1031, 2089]

编辑:

如果在 Timestamp 列中过滤日期之间，请使用 DatetimeIndex Partial String Indexing :

df.Timestamp = pd.to_datetime(df.Timestamp, dayfirst=True)
date1 = '2017-01-04 23:00'
date2 ='2018-02-04 06:00'
df1 = df.set_index('Timestamp')['Consumption']
a = df1.loc[date1:date2].sum()
print (a)
2080 

编辑:

如果需要DataFrame.between_time :

print (df)
         Timestamp  Consumption
0   4/1/2017 20:00          257
1   4/1/2017 21:00          262
2   4/1/2017 22:00          256
3   4/1/2017 23:00          256
4    4/2/2017 0:00          263
5    4/2/2017 1:00          256
6    4/2/2017 2:00          265
7    4/2/2017 3:00          259
8    4/2/2017 4:00          256
9    4/2/2017 5:00          260
10   4/2/2018 6:00          265
11   4/2/2018 7:00          265
12  4/3/2017 20:00          256
13  4/3/2017 21:00          263
14   4/3/2017 1:00          256
15   4/4/2017 2:00          265
16   4/4/2017 3:00          259
17   4/4/2017 8:00          256

<小时/>

df.Timestamp = pd.to_datetime(df.Timestamp, dayfirst=True)
df1 = df.set_index('Timestamp')['Consumption'].between_time('23:00','6:00')
print (df1)
Timestamp
2017-01-04 23:00:00    256
2017-02-04 00:00:00    263
2017-02-04 01:00:00    256
2017-02-04 02:00:00    265
2017-02-04 03:00:00    259
2017-02-04 04:00:00    256
2017-02-04 05:00:00    260
2018-02-04 06:00:00    265
2017-03-04 01:00:00    256
2017-04-04 02:00:00    265
2017-04-04 03:00:00    259
Name: Consumption, dtype: int64

print (df1.sum())
2860