从数据框中提取上周数据并找到最大值

Question

我有一个下面的数据框，我想提取最近一周的数据并找到该周的最大值，我该怎么做？

Date        Value
2020-10-4   2768
2020-10-3   3032
2020-10-2   2264
2020-10-1   2723
2020-10-0   3407
2020-10-1   11660
2020-10-2   13551
2020-10-3   8031
2020-10-4   8183
2020-10-5   6186
2020-10-6   9394
2020-10-7   11498
2020-10-8   43214
2020-10-9   5538
2020-10-10  21590
2020-10-11  5356
2020-10-12  8371
2020-10-13  21999
2020-10-14  11729
2020-10-15  11983
2020-10-16  6056
2020-10-17  13360
2020-10-18  12258
2020-10-19  11437
2020-10-20  10506
2020-10-21  39793
2020-10-22  1131
2020-10-23  1356
2020-10-24  6891
2020-10-25  57736
2020-10-26  14274
2020-10-27  3884
2020-10-28  12164
2020-10-29  6936
2020-10-30  11705

Answer 1

在max中使用Series.last

df['Date'] = pd.to_datetime(df['Date'], errors='coerce')

m = df.set_index('Date')['Value'].last('W').max()
print (m)
14274

编辑：

df['Date'] = pd.to_datetime(df['Date'], errors='coerce')

s = df.set_index('Date')['Value'].last('W')
print (s)
Date
2020-10-26    14274
2020-10-27     3884
2020-10-28    12164
2020-10-29     6936
2020-10-30    11705
Name: Value, dtype: int64

df = s[s == s.max()].reset_index()
print (df)
        Date  Value
0 2020-10-26  14274

编辑:如果需要持续7天：

s = df.set_index('Date')['Value'].last('7d')
print (s)
Date
2020-10-24     6891
2020-10-25    57736
2020-10-26    14274
2020-10-27     3884
2020-10-28    12164
2020-10-29     6936
2020-10-30    11705
Name: Value, dtype: int64

df = s[s == s.max()].reset_index()
print (df)
        Date  Value
0 2020-10-25  57736

如果需要从今天起持续7天：

df['Date'] = pd.to_datetime(df['Date'], errors='coerce')

df = df[(pd.Timestamp('now').floor('d') - df['Date']).dt.days < 7]

df = df[df['Value'] == df['Value'].max()]
print (df)
         Date  Value
32 2020-10-28  12164    

Answer 2

首先要做的是创建一组要选择的日期，然后使用它过滤数据帧。这应该可以做到这一点(也就是说，如果您的日期是datetime格式的话……)

import pandas as pd
import datetime as dt
df = pd.DataFrame(...)
today = dt.date.today()
last_week = {today-dt.timedelta(x) for x in range(7)}
df = df[df.Date.isin(last_week)]
max = df[df.Date==df.Date.max()]
print(df)
print(max)