时间戳

首页 > 解决方案 > 按 Inspector_ID 分组总和，以及熊猫中的 Day

pandas - 按 Inspector_ID 分组总和，以及熊猫中的 Day

问题描述

我有一个如下所示的数据框。这是两个检查员在不同的日子里走过的距离。

Inspector_ID      Timestamp                  Distance
    1              2018-07-24 7:31:00          0
    1              2018-07-24 7:31:01          2.3
    1              2018-07-24 7:33:01          2.8
    1              2018-07-25 7:32:04          0
    1              2018-07-25 7:33:06          3.6
    2              2018-07-20 8:35:08          0
    2              2018-07-20 8:36:10          5.6
    2              2018-07-20 8:37:09          2.1
    2              2018-07-27 8:38:00          0
    2              2018-07-27 8:39:00          3
    2              2018-07-27 8:40:00          2.6

从上面，我想准备下面的数据框

预期产出

Inspector_ID   Day_of_the_month   Total_distance   No.of_entries_in that_day  week_of_the_month
1              24                 5.1              3                          4
1              25                 3.6              2                          4
2              20                 7.7              3                          3
2              27                 5.6              3                          4

标签： pandasdatetimepandas-groupby

解决方案

    import pandas as pd

    from datetime import datetime as dt
    df={"Inspector_ID":[1,1,1,1,1,2,2,2,2,2,2]     ,"Timestamp" :["2018-07-24 7:31:00" ,"2018-07-24 7:31:01" ,"2018-07-24 7:33:01" ,"2018-07-25 7:32:04" ,"2018-07-25 7:33:06","2018-07-20 7:31:00" ,"2018-07-20 8:36:10" ,"2018-07-20 8:37:09","2018-07-27 8:38:00" ,"2018-07-27 8:39:00","2018-07-27 8:38:00"],"Distance":[0,2.3,2.8,0,3.6,0,5.6,2.1,0,3,2.6]}

    df["Day"]=[]
    df["week_of_month"]=[]

    for entry in df["Timestamp"]:  
        df["Day"].append(dt.strptime(entry,'%Y-%m-%d %H:%M:%S').day)
        df["week_of_month"].append((dt.strptime(entry,'%Y-%m-%d %H:%M:%S').day - 1) // 7 + 1)

    df=pd.DataFrame(df)

    result=df.groupby(['Inspector_ID','Day','week_of_month'])['Distance'].agg({"totdist":"sum","NoEntries":"count"})
    print(result)

推荐阅读