按日期和其他列值筛选

Question

数据框如下所示：

df.columns = ['ReportDate', 'ClientId', 'ClientRevenue']

我想得到所有客户的名单报告收入较高的b/w 2日期。下面是一些未经测试的大纲代码，但不知道是否有更直接的python方法：

enddatedf = df.loc[df['ReportDate'] == endDate]
startdatedf = df.loc[df['ReportDate'] == startDate]

endclients = enddatedf['ClientId'].unique()
startclients = startdatedf['ClientId'].unique()
commonclients = list(set(startclients).intersect(set(endclients)) #because clients might have dropped off in b/w

risingclients = []
for client in commonclients:
    startrevenue = startdatedf.loc[startdatedf['ClientId'] == client, 'ClientRevenue'].values[0]
    endrevenue = enddatedf.loc[enddatedf['ClientId'] == client, 'ClientRevenue'].values[0]
    if endrevenue > startrevenue:
    risingclients.append(client)

谢谢

Answer 1

正在创建数据。请在问题中提供数据。：）

startdate = pd.datetime(2019, 1, 1)
enddate = pd.datetime(2019, 3, 31)

df = pd.DataFrame(
    data={
        "ReportDate": [startdate, enddate, startdate, enddate, startdate, enddate],
        "ClientId": [2, 1, 3, 3, 1, 2],
        "ClientRevenue": [1432, 8493, 2316, 2145, 3211, 8763],
    }
)

print(df)

  ReportDate  ClientId  ClientRevenue
0 2019-01-01         2           1432
1 2019-03-31         1           8493
2 2019-01-01         3           2316
3 2019-03-31         3           2145
4 2019-01-01         1           3211
5 2019-03-31         2           8763

第一步是为startdate和enddate过滤df。你知道吗

df = df.loc[((df['ReportDate']==startdate) | (df['ReportDate']==enddate)),:]

接下来，对数据帧进行排序，以便按日期顺序将客户机放在一起。你知道吗

df = df.sort_values(['ClientId','ReportDate'])

ReportDate  ClientId  ClientRevenue
4 2019-01-01         1           3211
1 2019-03-31         1           8493
0 2019-01-01         2           1432
5 2019-03-31         2           8763
2 2019-01-01         3           2316
3 2019-03-31         3           2145

接下来，从enddate ClientRevenue中减去startdate ClientRevenue。如果值为正，则客户在这两个日期之间有增长。你知道吗

result = df.groupby('ClientId').last() - df.groupby('ClientId').first()
print(result)

         ReportDate  ClientRevenue
ClientId                          
1           89 days           5282
2           89 days           7331
3           89 days           -171

最后，过滤结果数据帧中的正'ClientRevenue'，并将索引（'ClientId'）放到列表中。你知道吗

print("ClientId with positive return: ", result[result['ClientRevenue']>0].index.tolist())
ClientId with positive return:  [1, 2]

编辑 我错过了关于客户下车的部分，但我回去测试了，它仍然有效。你知道吗

正在添加ClientId=0，但只有startdate。你知道吗

  ReportDate  ClientId  ClientRevenue
0 2019-01-01         0           1324
1 2019-01-01         2           1432
2 2019-03-31         1           8493
3 2019-01-01         3           2316
4 2019-03-31         3           2145
5 2019-01-01         1           3211
6 2019-03-31         2           8763

计算结果为：

         ReportDate  ClientRevenue
ClientId                          
0            0 days              0
1           89 days           5282
2           89 days           7331
3           89 days           -171

ClientId with positive return:  [1, 2]

Answer 2

df = df.sort_values(['ReportDate'], ascending=[True]) #Ensure your ReportDate is datetime column
df = df[(df['ReportDate'] > startDate) & (df['date'] <= endDate)] #You can have startDate, endDate as variables at top of your code section
del df['ReportDate']
df = df.groupby(['ClientId'],as_index=False).sum()
df = df.sort_values(['ClientRevenue'], ascending=[False])
top5 = df.head(5)   #Selecting the top 5 clients