我有一個數據框，Check In Date Check Out Date Number stage2020/5/22 16:23 2020/5/22 18:39 1 a2020/5/22 22:41 2020/5/23 2:03 1 b2020/5/23 2:04 2020/5/23 2:04 1 c2020/5/23 2:04 2020/5/23 2:56 1 d2020/5/23 2:56 2020/5/23 2:56 2 a2020/5/24 8:39 2020/5/24 8:39 2 b2020/5/24 8:40 2020/5/24 10:58 2 c2020/5/24 10:59 2020/5/24 10:59 2 ddf = 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 2a -> b 4:02:00 5:43:00b -> c 0:01:00 0:01:00c -> d 0:00:00 0:01:00等于 1 2a -> b b: ckeck in date - a: check out date b: ckeck in date - a: check out dateb -> c c: ckeck in date - b: check out date c: ckeck in date - b: check out datec -> d d: ckeck in date - c: check out date d: ckeck in date - c: check out date我檢查了與熊貓和數據框相關的示例，但我仍然不知道如何實現這一點。任何想法？
1 回答
天涯盡頭無女友
TA貢獻1831條經驗 獲得超9個贊
用于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? ? ? ? ? ? ? ?2
stage? ? ? ? ? ? ? ? ? ? ? ? ??
a -> b 04:02:00 1 days 05:43:00
b -> c 00:01:00 0 days 00:01:00
c -> 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 combinations
c = [f'{a} -> ' 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? ? ? ? ? ? ? ?2
stage? ? ? ? ? ? ? ? ? ? ? ? ??
a -> b 04:02:00 1 days 05:43:00
a -> c 07:25:00 1 days 05:44:00
a -> d 07:25:00 1 days 08:03:00
b -> c 00:01:00 0 days 00:01:00
b -> d 00:01:00 0 days 02:20:00
c -> d 00:00:00 0 days 00:01:00
添加回答
舉報
0/150
提交
取消