Python 数据框中的计算

Question

我有一个数据框，

Check In Date   Check Out Date  Number  stage

2020/5/22 16:23 2020/5/22 18:39 1         a

2020/5/22 22:41 2020/5/23 2:03  1         b

2020/5/23 2:04  2020/5/23 2:04  1         c

2020/5/23 2:04  2020/5/23 2:56  1         d

2020/5/23 2:56  2020/5/23 2:56  2         a

2020/5/24 8:39  2020/5/24 8:39  2         b

2020/5/24 8:40  2020/5/24 10:58 2         c

2020/5/24 10:59 2020/5/24 10:59 2         d

df = pd.DataFrame({'Check In Date': ['2020/5/22 16:23', '2020/5/22 22:41', '2020/5/23 2:04', '2020/5/23 2:04', '2020/5/23 2:56', '2020/5/24 8:39', '2020/5/24 8:40', '2020/5/24 10:59'],

                   'Check Out Date': ['2020/5/22 18:39', '2020/5/23 2:03', '2020/5/23 2:04', '2020/5/23 2:56', '2020/5/23 2:56', '2020/5/24 8:39', '2020/5/24 10:58', '2020/5/24 10:59'],

                   'Number': [1, 1, 1, 1, 2, 2, 2, 2],

                   'stage': ['a', 'b', 'c', 'd', 'a', 'b', 'c', 'd']})

我正在尝试在数据框中进行一些计算，如下所示：

          1       2

a -> b  4:02:00 5:43:00

b -> c  0:01:00 0:01:00

c -> d  0:00:00 0:01:00

等于

                         1                                       2

a -> b  b: ckeck in date - a: check out date    b: ckeck in date - a: check out date

b -> c  c: ckeck in date - b: check out date    c: ckeck in date - b: check out date

c -> d  d: ckeck in date - c: check out date    d: ckeck in date - c: check out date

我检查了与熊猫和数据框相关的示例，但我仍然不知道如何实现这一点。任何想法？

Answer 1

用于DataFrameGroupBy.shift移动列stage和Check Out Date，通过 重塑形状DataFrame.unstack，因此在最后一步中可以通过移动列减去DataFrame.sub：df['Check In Date'] = pd.to_datetime(df['Check In Date'])df['Check Out Date'] = pd.to_datetime(df['Check Out Date'])g = df.groupby('Number')df = (df.assign(shitfted = g['Check Out Date'].shift(),                stage = g['stage'].shift() + ' -> ' + df['stage'])        .set_index(['stage','Number'])[['Check In Date','shitfted']]        .unstack()        .dropna()      )df = df['Check In Date'].sub(df['shitfted'])print (df)Number        1               2stage                          a -> b 04:02:00 1 days 05:43:00b -> c 00:01:00 0 days 00:01:00c -> d 00:00:00 0 days 00:01:00编辑：对于所有组合，使用交叉连接并按所有组合进行过滤：df['Check In Date'] = pd.to_datetime(df['Check In Date'])df['Check Out Date'] = pd.to_datetime(df['Check Out Date'])from  itertools import combinationsc = [f'{a} -> {b}' for a, b in (combinations(df['stage'].unique(), 2))]print (c)['a -> b', 'a -> c', 'a -> d', 'b -> c', 'b -> d', 'c -> d']df = (df.merge(df, on='Number')       .assign(stage = lambda x: x.pop('stage_x') + ' -> ' + x.pop('stage_y'))       .query('stage in @c')# df = df[df['stage'].isin(c)]        .set_index(['stage','Number'])[['Check In Date_y','Check Out Date_x']]        .unstack())df = df['Check In Date_y'].sub(df['Check Out Date_x'])print (df)Number        1               2stage                          a -> b 04:02:00 1 days 05:43:00a -> c 07:25:00 1 days 05:44:00a -> d 07:25:00 1 days 08:03:00b -> c 00:01:00 0 days 00:01:00b -> d 00:01:00 0 days 02:20:00c -> d 00:00:00 0 days 00:01:00