我采用了以下数据样本。
trip_id,vehicle_id,customer_id,fleet,trip_start,distance_miles,journey_duration
1,d3550e496af4,442342ac078e,Salt Lake City,2020-06-02 16:12:22,2.30266927956152,0 days 00:13:12.549351
2,2afc10228a2b,4d3ea6d8bb4b,Provo,2020-06-02 16:17:21,0.495335235709548,0 days 00:02:48.407770
3,442342ac078e,442342ac078e,Salt Lake City,2020-06-02 16:43:05,0.7933172567617909,0 days 00:15:33.417755
4,8701da8e6582,567c93d144ed,Provo,2020-06-02 19:34:40,0.9158009891104686,0 days 00:07:04.912849
5,b70fa4bc1486,391526cd2b71,Provo,2020-06-02 20:02:37,1.6248457639858709,0 days 00:11:51.821411
6,f6f0a689fc3a,2b9d754d1c4f,Provo,2020-06-02 20:57:27,0.8310125874177197,0 days 00:07:37.959237我使用以下命令将此数据读入df:
df = pd.read_clipboard(sep=',')

我正在努力解决的是如何使用这些信息创建一个汇总表。我想要的输出df如下:

在这里,我希望按城市分组,同时能够计算该城市中独特车辆、独特客户、行程的总数以及每次行程的总距离和持续时间(以分钟为单位)。
例如,我们可以看到,对于第0行和第2行,有两个唯一的车辆,但它们来自同一个客户。
我尝试过使用groupby/summing/unique方法,但在处理我想要获取的某些值时遇到了问题。你知道下一步该去哪里吗?干杯
发布于 2021-10-14 11:43:51
您需要转换一些列,然后就可以进行分组和汇总
df['trip_start'] = pd.to_datetime(df['trip_start'], format='%Y-%d-%m %H:%M:%S')
df['journey_duration'] = pd.to_timedelta(df['journey_duration'])
df['Date'] = df['trip_start'].dt.strftime('%b %Y')
df.groupby(['Date', 'fleet']).agg(
Total_Customers = ('customer_id', 'nunique'),
Total_Vehicles = ('vehicle_id', 'nunique'),
Total_Trips = ('trip_id', 'nunique'),
Total_Distance = ('distance_miles', 'sum'),
Total_Duration = ('journey_duration', 'sum'),
)https://stackoverflow.com/questions/69569383
复制相似问题