将列转换为行以创建事件日志数据集

Question

您能帮我将列转换成行以创建事件日志系列吗

我想用以下列创建一个事件日志数据集

我的表格如下所示：

ID1     ID2      Event1             Event1_activity     Event2              Event2_activity     Event3              Event3_activity
10001A  6456    05.09.2019 12:32    Event1_Description  09.09.2019 12:40    Event2_Description  10.09.2019 12:40    Event3_Description
10001A  6456    05.09.2019 12:32    Event1_Description  09.09.2019 12:40    Event2_Description  10.09.2019 12:40    Event3_Description
20001B  8793    03.09.2019 09:45    Event1_Description  10.09.2019 12:25    Event2_Description  11.09.2019 12:25    Event3_Description
20001B  9017    03.09.2019 09:49    Event1_Description  10.09.2019 12:25    Event2_Description  11.09.2019 12:25    Event3_Description
20001B  5454    04.09.2019 12:42    Event1_Description  10.09.2019 12:25    Event2_Description  11.09.2019 12:25    Event3_Description

根据ID1和ID2，我想基于包含各自事件和活动的列创建一系列事件日志

基本上，我的事件日志表应该如下所示：

ID          Event               Activity
6456-10001A 05.09.2019 12:32    Event1_Description
6456-10001A 09.09.2019 12:40    Event2_Description
6456-10001A 10.09.2019 12:40    Event3_Description
6456-10001A 05.09.2019 12:32    Event1_Description
6456-10001A 09.09.2019 12:40    Event2_Description
6456-10001A 10.09.2019 12:40    Event3_Description
8793-20001B 03.09.2019 09:45    Event1_Description
8793-20001B 10.09.2019 12:25    Event2_Description
8793-20001B 04.09.2019 09:45    Event3_Description
9017-20001B 03.09.2019 09:49    Event1_Description
9017-20001B 10.09.2019 12:25    Event2_Description
9017-20001B 04.09.2019 09:49    Event3_Description
5454-20001B 04.09.2019 12:42    Event1_Description
5454-20001B 10.09.2019 12:25    Event2_Description
5454-20001B 05.09.2019 12:42    Event3_Description

任何建议都将不胜感激

Answer 1

df

您可以创建新的ID，然后连接数据帧子集并按ID排序

df['ID'] = df['ID2'].astype(str) + '-' + df['ID1']
n_events = 3
pd.concat([df[['ID', f'Event{i}', f'Event{i}_activity']].rename(columns={f'Event{i}': 'Event', f'Event{i}_activity': 'Activity'}) 
           for i in range(1, n_events+1)]
         ).sort_values(by='ID').reset_index(drop=True)

        ID           Event             Activity
0   5454-20001B 04.09.2019 12:42    Event1_Description
1   5454-20001B 10.09.2019 12:25    Event2_Description
2   5454-20001B 11.09.2019 12:25    Event3_Description
3   6456-10001A 05.09.2019 12:32    Event1_Description
4   6456-10001A 05.09.2019 12:32    Event1_Description
5   6456-10001A 09.09.2019 12:40    Event2_Description
6   6456-10001A 09.09.2019 12:40    Event2_Description
7   6456-10001A 10.09.2019 12:40    Event3_Description
8   6456-10001A 10.09.2019 12:40    Event3_Description
9   8793-20001B 03.09.2019 09:45    Event1_Description
10  8793-20001B 10.09.2019 12:25    Event2_Description
11  8793-20001B 11.09.2019 12:25    Event3_Description
12  9017-20001B 03.09.2019 09:49    Event1_Description
13  9017-20001B 10.09.2019 12:25    Event2_Description
14  9017-20001B 11.09.2019 12:25    Event3_Description

如果必须保留ID的原始顺序，则必须采取不同的方法