如何添加新行，复制某些列，但在其他列中指定新值

Question

我有一个如下所示的数据帧：

df = pd.DataFrame({'VisitorID': [1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000],
                   'EpochTime': [1554888560, 1554888560, 1554888560, 1554888560, 1554888560, 1521333510, 1521333510, 1521333510],
                   'HitTime': [1400, 5340, 7034, 11034, 13059, 990, 4149, 6450],
                   'HitNumber':[23, 54, 55, 65, 110, 14, 29, 54],
                   'PagePath':['orders/details', 'orders/payment', 'orders/afterpayment', 'orders/myorders', 'customercare', 'orders/details', 'orders/payment', 'orders/myorders']})

print(df)
   VisitorID   EpochTime  HitTime  HitNumber             PagePath
0       1000  1554888560     1400         23       orders/details
1       1000  1554888560     5340         54       orders/payment
2       1000  1554888560     7034         55  orders/afterpayment
3       1000  1554888560    11034         65      orders/myorders
4       1000  1554888560    13059        110         customercare
5       1000  1521333510      990         14       orders/details
6       1000  1521333510     4149         29       orders/payment
7       1000  1521333510     6450         54      orders/myorders

实际上，我的数据帧是+-1000万行。并且有两个列。 数据由显示客户行为的网站数据组成

我想做什么
为了分析客户在到达跟踪的第一个页面之前在网站上停留的时间，我想在每个组上方添加一行，从列中复制最上面一行的值：

• 探视者
• 时代

但为列提供了新值：

• 命中时间=0
• HitNumber=0
• 页面路径=Home

信息：组合VisitorID+EpochTime使组唯一

我通过以下代码实现了这一点，但运行需要+-5分钟，我认为应该有一种更快的方法：

lst = []
for x, y in df.groupby(['VisitorID', 'EpochTime']):
    lst.append(y.iloc[:1])

df_first = pd.concat(lst, ignore_index=True)

df_first['HitTime'] = 0.0
df_first['HitNumber'] = 0.0
df_first['PagePath'] = 'Home'

print(df_first)
   VisitorID   EpochTime  HitTime  HitNumber PagePath
0       1000  1521333510      0.0        0.0     Home
1       1000  1554888560      0.0        0.0     Home

df_final = pd.concat([df, df_first], ignore_index=True).sort_values(['VisitorID', 'EpochTime', 'HitNumber']).reset_index(drop=True)

print(df_final)
   VisitorID   EpochTime  HitTime  HitNumber             PagePath
0       1000  1521333510      0.0        0.0                 Home
1       1000  1521333510    990.0       14.0       orders/details
2       1000  1521333510   4149.0       29.0       orders/payment
3       1000  1521333510   6450.0       54.0      orders/myorders
4       1000  1554888560      0.0        0.0                 Home
5       1000  1554888560   1400.0       23.0       orders/details
6       1000  1554888560   5340.0       54.0       orders/payment
7       1000  1554888560   7034.0       55.0  orders/afterpayment
8       1000  1554888560  11034.0       65.0      orders/myorders
9       1000  1554888560  13059.0      110.0         customercare

df_final的输出是我的预期输出

所以问题是，我能以一种更有效的方式做到这一点吗

Answer 1

可以使用^{}稍微提高性能：

d = {'HitTime':0,'HitNumber':0,'PagePath':'Home'}
df_first = df.drop_duplicates(['VisitorID', 'EpochTime']).assign(**d)

df_final = (pd.concat([df, df_first], ignore_index=True)
             .sort_values(['VisitorID', 'EpochTime', 'HitNumber'])
             .reset_index(drop=True))

---

print(df_final)

   VisitorID   EpochTime  HitTime  HitNumber             PagePath
0       1000  1521333510        0          0                 Home
1       1000  1521333510      990         14       orders/details
2       1000  1521333510     4149         29       orders/payment
3       1000  1521333510     6450         54      orders/myorders
4       1000  1554888560        0          0                 Home
5       1000  1554888560     1400         23       orders/details
6       1000  1554888560     5340         54       orders/payment
7       1000  1554888560     7034         55  orders/afterpayment
8       1000  1554888560    11034         65      orders/myorders
9       1000  1554888560    13059        110         customercare

另一个想法是通过减法和按索引最后排序来更改df_first中的索引值：

d = {'HitTime':0,'HitNumber':0,'PagePath':'Home'}
df_first = df.drop_duplicates(['VisitorID', 'EpochTime']).assign(**d)
df_first.index -= .5

df_final = pd.concat([df, df_first]).sort_index().reset_index(drop=True)
print(df_final)
   VisitorID   EpochTime  HitTime  HitNumber             PagePath
0       1000  1554888560        0          0                 Home
1       1000  1554888560     1400         23       orders/details
2       1000  1554888560     5340         54       orders/payment
3       1000  1554888560     7034         55  orders/afterpayment
4       1000  1554888560    11034         65      orders/myorders
5       1000  1554888560    13059        110         customercare
6       1000  1521333510        0          0                 Home
7       1000  1521333510      990         14       orders/details
8       1000  1521333510     4149         29       orders/payment
9       1000  1521333510     6450         54      orders/myorders