python-3.x - 如何为 pandas 中的不同列值添加新的值行

Question

我有像这样的数据框

 ORDER    STATUS   DATE
23412      200      7-2-2020
23412      300      8-2-2020
23412      400      10-2-2020
91234      300      8-2-2020
91234      400      9-2-2020
671234     200      10-3-2020

我想为每个不同的订单添加静态行，状态=600且日期=31-12-9999

预期输出

ORDER    STATUS   DATE
23412      200      7-2-2020
23412      300      8-2-2020
23412      400      10-2-2020
23412      600      31-12-9999
91234      300      8-2-2020
91234      400      9-2-2020
91234      600      31-12-9999
671234     200      10-3-2020
671234    600      31-12-9999

如何在 pandas 中完成此操作？

Answer 1

使用DataFrame.drop_duplicates与 DataFrame.assign对于新的 DataFrame，添加到原始数据 concat ，按DataFrame.sort_index对索引值进行排序仅使用稳定的算法 mergesort 并最后将索引转换为默认 RangeIndex by DataFrame.reset_index与drop=True:

df1 = df.drop_duplicates('ORDER', keep='last').assign(STATUS=600, DATE='31-12-9999')
df = pd.concat([df, df1]).sort_index(kind='mergesort').reset_index(drop=True)
print (df)
    ORDER  STATUS        DATE
0   23412     200    7-2-2020
1   23412     300    8-2-2020
2   23412     400   10-2-2020
3   23412     600  31-12-9999
4   91234     300    8-2-2020
5   91234     400    9-2-2020
6   91234     600  31-12-9999
7  671234     200   10-3-2020
8  671234     600  31-12-9999

还有更多解决方案，每个都不同 - @Quang Hoang 排序数据(也许有问题，也许没有)，@sammywemmy 和我的解决方案不排序数据。另外 groupby 显然很慢，因此如果性能很重要，最好避免(如果可能):

#some sample data, 100krows, 10k groups
np.random.seed(123)
N = 100000
L = ['7-2-2020', '8-2-2020', '10-2-2020', '8-2-2020', '9-2-2020', '10-3-2020']

df = pd.DataFrame({'ORDER': np.random.randint(10000, size=N),
                   'STATUS': np.random.randint(500, size=N),
                   'DATE':np.random.choice(L, N)}).sort_values('ORDER').reset_index(drop=True)
print (df)

In [391]: %timeit pd.concat([df, pd.DataFrame({'ORDER':df.ORDER.unique(), 'STATUS':600,'DATE':'31-12-9999'})],ignore_index=True).sort_values(['ORDER','STATUS'])
47.9 ms ± 1.27 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [392]: %timeit pd.concat([df, df.drop_duplicates('ORDER', keep='last').assign(STATUS=600, DATE='31-12-9999')]).sort_index(kind='mergesort').reset_index(drop=True)
34.1 ms ± 543 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [393]: %timeit pd.concat([group.append({'ORDER':name,'STATUS':600, 'DATE':'31-12-9999'}, ignore_index=True)  for name,group in df.groupby('ORDER')],ignore_index=True )
24 s ± 455 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

<小时/>

#some sample data, 100krows, 100 groups
np.random.seed(123)
N = 100000
L = ['7-2-2020', '8-2-2020', '10-2-2020', '8-2-2020', '9-2-2020', '10-3-2020']

df = pd.DataFrame({'ORDER': np.random.randint(100, size=N),
                   'STATUS': np.random.randint(500, size=N),
                   'DATE':np.random.choice(L, N)}).sort_values('ORDER').reset_index(drop=True)
print (df)

In [398]: %timeit pd.concat([df, pd.DataFrame({'ORDER':df.ORDER.unique(), 'STATUS':600,'DATE':'31-12-9999'})],ignore_index=True).sort_values(['ORDER','STATUS'])
31 ms ± 1.41 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [399]: %timeit pd.concat([df, df.drop_duplicates('ORDER', keep='last').assign(STATUS=600, DATE='31-12-9999')]).sort_index(kind='mergesort').reset_index(drop=True)
28 ms ± 354 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [400]: %timeit pd.concat([group.append({'ORDER':name,'STATUS':600, 'DATE':'31-12-9999'}, ignore_index=True)  for name,group in df.groupby('ORDER')],ignore_index=True )
290 ms ± 46.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)