pandas 从一列开始合并，完全在其他列上? [英] pandas merge as of on one column, exactly on other columns?

问题描述

我正在尝试合并 2 个数据帧，在某些列上完全匹配，在其他列(通常是日期)上匹配 as_of.这篇文章很好地描述了这个意图(我将复制粘贴下面的主要内容):

I am trying to merge 2 dataframes, with exact matching on some columns and as_of matching on some other column (typically a date). The intention is very well described in this post (I'll copy paste the main content below):

Pandas:对一列进行近似连接, 其他列完全匹配

上面的帖子已经回答了；只有它可以追溯到 2016 年，在引入 pandas.merge_asof 之前.我相信现在已经发布了一个更简单的答案.残酷的方法是将每组行的 as_of 合并为我想要完全合并的列的相同值.但是有没有更优雅的版本?

The post above was answered ; only it dates back from 2016, before the introduction of pandas.merge_asof. I believe there can be an easier answer now that it's been released. Brutal approach would be to merge as_of for each group of rows with the same values for the cols on which I want to merge exactly on. But is there a more elegant version?

所需输入和输出的精确描述:

Precise description of desired input and outputs:

输入

df1 = pd.DataFrame({'index': ['a1','a2','a3','a4'], 'col1': ['1232','432','432','123'], 'col2': ['asd','dsa12','dsa12','asd2'], 'col3': ['1','2','2','3'], 'date': ['2010-01-23','2016-05-20','2010-06-20','2008-10-21'],}).set_index('index')

df1
Out[430]: 
       col1   col2 col3        date
index                              
a1     1232    asd    1  2010-01-23
a2      432  dsa12    2  2016-05-20
a3      432  dsa12    2  2010-06-20
a4      123   asd2    3  2008-10-21

df2 = pd.DataFrame({'index': ['b1','b2','b3','b4'], 'col1': ['132','432','432','123'], 'col2': ['asd','dsa12','dsa12','sd2'], 'col3': ['1','2','2','3'], 'date': ['2010-01-23','2016-05-23','2010-06-10','2008-10-21'],}).set_index('index')

df2
Out[434]: 
      col1   col2 col3        date    b_col
index                             
b1     132    asd    1  2010-01-23        1
b2     432  dsa12    2  2016-05-23        2
b3     432  dsa12    2  2010-06-10        3
b4     123    sd2    3  2008-10-21        4

输出:

       col1   col2 col3        date b_col
index                                                     
a2      432  dsa12    2  2016-05-20     2
a3      432  dsa12    2  2010-06-20     3

注意 1:我需要这样做的原因是我需要类似 groupby(...)[...].rolling(...).transform(...) 的延迟似乎还不存在，除非我遗漏了什么?

NOTE 1: the reason why I need to do this is that I need something like groupby(...)[...].rolling(...).transform(...) with latency which doesn't seem to exist yet, unless I am missing something?

注意 2:我想避免计算所有对，然后过滤，因为数据框可能会变得太大.

NOTE 2: I want to avoid computing all couples and then filtering as the dataframe may get too big.

推荐答案

我试图更接近您的问题.但是，我没有尝试 merge_asof 而是合并.我希望这种方法可以帮助您:

I have tried to get closer to your problem. However, I did not try merge_asof but merge. I hope this approach can help you:

import numpy as np
import pandas as pd


df1 = pd.DataFrame({'index': ['a1', 'a2', 'a3', 'a4'], 'col1': ['1232', '432', '432', '123'],
                'col2': ['asd', 'dsa12', 'dsa12', 'asd2'], 'col3': ['1', '2', '2', '3'],
                'date': ['2010-01-23', '2016-05-20', '2010-06-20', '2008-10-21'],
                }).set_index('index')

df2 = pd.DataFrame({'index': ['b1', 'b2', 'b3', 'b4'], 'col1': ['132', '432', '432', '123'],
                'col2': ['asd', 'dsa12', 'dsa12', 'sd2'], 'col3': ['1', '2', '2', '3'],
                'date': ['2010-01-23', '2016-05-23', '2010-06-10', '2008-10-21'],
                }).set_index('index')


columns = ['col1', 'col2', 'col3']


                                                                                                                                 
new_dic = pd.merge(df1, df2, on=columns, right_index=True).drop_duplicates(subset=['date_x']).drop(labels='date_y', axis=1)          
                                                                                                                             
                                                                                                                  

print(new_dic)

这篇关于 pandas 从一列开始合并，完全在其他列上?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！