如何识别数据帧合并期间内部联接中没有的内容 [英] how to identify whats NOT in the inner join during dataframe merge

问题描述

我有一帧看起来像这样(df):

I have one frame that looks like this (df):

                                 2000q1         2000q2         2000q3  
State        RegionName                                                  
New York     New York                NaN            NaN            NaN   
California   Los Angeles   207066.666667  214466.666667  220966.666667   
Illinois     Chicago       138400.000000  143633.333333  147866.666667

(请注意，State，RegionName这里是MultiIndex)

(notice that State,RegionName here is a MultiIndex)

和一幅看起来像这样(ut)的帧:

and one frame that looks like this (ut):

     State    RegionName
0  Alabama        Auburn
1  Alabama      Florence
2  Alabama  Jacksonville
3  Alabama    Livingston
4  Alabama    Montevallo

因此，要获取两个数据帧中State，RegionName都位于的所有行，请执行以下操作:

So to get all rows where State,RegionName are in both dataframes, I do this:

dfut = pd.merge(df, ut, how='inner', left_index=True, right_on=['State', 'RegionName'])

那行得通.我现在想要一个行列表，其中df框架中的行不在ut框架中-就像"NOT内部联接"一样.我很确定我需要做一个LEFT联接，这会给我整个df，但是我不确定如何从中减去ut相交的行.希望它清除.谢谢

That works. I now want a list of rows where rows from df frame are NOT in ut frame -- like a "NOT inner join". I am pretty sure that I need to do a LEFT join which will give me the entire df, but I am not sure how to subtract ut intersecting rows out of it. Hope its clear. Thank you

推荐答案

在您的merge和query('_merge != "both"')

dfut = pd.merge(df, ut, how='outer',
                left_index=True, right_on=['State', 'RegionName'],
                indicator=True)

dfut.query('_merge != "both"')

这篇关于如何识别数据帧合并期间内部联接中没有的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！