python - Pandas 合并并保留在右边并删除左边

Question

我有一份周五所有证券的价格表。一些证券在周六和周五保持相同的价格。我想为周六以外的证券价格复制周五到周六的价格。

我尝试使用 pandas merge 来完成这个任务，如下所示。

我将 Indicator 设置为 True，如下所示对两个数据帧进行 outer Join。

# df_friday has 10 securities
# df _saturday has 3 securities
merge_df=pd.merge(df_friday,df_saturday,on='security',how="outer",indicator=True)

merge_df = merge_df[merge_df['_merge']=='left_only']
merge_df =merge_df.drop(['price_y','_merge'],axis=1)
merge_df = merge_df.rename(columns = {'price_x':'price'})

df_saturday = pd.concat([df_saturday,merge_df],ignore_index=True)

我的两个数据框的列是一样的，

Columns: [security, price]

我的做法是否正确？或者我可以用一种简单的方式做到这一点吗？

例如，

# df_friday
  security price
1 apple    35.25
2 reliance  25.5
3 samsung   12.5
4 tata      28.5
5 sony      30.2

# df_saturday
  security price
1 reliance  26.8
2 samsung   11.2

# df_saturday_result should be as follows,
  security price
1 reliance  26.8
2 samsung   11.2
3 apple    35.25
4 tata      28.5
5 sony      30.2

Answer 1

对其他答案中提到的 3 种方法进行了一些时间检查。

fri =pd.DataFrame (columns =['security', 'price'], index = range(3), data =[['a',2],['b',4],['c',6]] )
sat =pd.DataFrame (columns =['security', 'price'], index = range(2), data =[['a',3],['c',5]] )

In [90]: %timeit out = sat.merge(fri, how='outer', on=['security', 'price']).drop_duplicates()
5.19 ms ± 150 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [91]: %timeit result_1 = pd.concat([sat,fri],ignore_index=True).drop_duplicates(subset=['security'], keep='first')
1.82 ms ± 26.8 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

In [92]: %timeit result_2 = pd.concat([sat, fri[~fri.security.isin(sat.security)]], ignore_index=True)
1.19 ms ± 113 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

In [93]: %timeit out = sat.merge(fri, how='outer', on=['security', 'price']).drop_duplicates()
5.02 ms ± 181 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

看起来过滤然后连接是最快的，而连接然后去重并不算太糟糕。相比之下，合并非常慢。