熊猫：即使没有共同值

Question

我有3个数据范围。我合并df1和df2通过公共列。但是，我需要使用df3来查找在创建的GroupBy中看到的对的值。我也可以使用两列合并通过内部加入来完成这一部分，但是我还需要了解没有任何共同元素的条目。到目前为止，我能做的是在这里有一个模型问题：

ch = {'country':['India','India','India','USA','USA','Italy','Italy'],'hotel':['Taj','Oberoi','Hilton','Taj','Hilton','Oberoi','Marriott']}
ch_df = pd.DataFrame.from_dict(ch)

hm = {'hotel':['Taj','Taj','Taj','Oberoi','Oberoi','Marriott','Marriott','Marriott','Hilton','Hilton'],'menu':['ildi','dosa','soup','soup','ildi','soup','pasta','pizza','pizza','burger']}
hm_df = pd.DataFrame.from_dict(hm)

cm = {'country':['India','India','India','USA','USA','USA','Italy','Italy'],'menu':['ildi','dosa','soup','dosa','burger','pizza','pizza','pasta']}
cm_df = pd.DataFrame.from_dict(cm)

chm_df = pd.merge(ch_df, hm_df, left_on='hotel', right_on='hotel')

pd.merge(left=chm_df, right=cm_df, on=['country','menu'], how='inner').groupby(['country','hotel'])['menu'].apply(list).reset_index(name='menu items')
  country     hotel          menu items
0   India    Oberoi        [ildi, soup]
1   India       Taj  [ildi, dosa, soup]
2   Italy  Marriott      [pasta, pizza]
3     USA    Hilton     [pizza, burger]
4     USA       Taj              [dosa]

我需要的条目是：

5   Italy  Oberoi                    []
...

一种低效的方法是在hm_df中添加到每个对的条目，一个允许的菜单项并在GroupBy之后将其删除。但是看起来很丑。什么是更优雅的方法？

Answer 1

如果需要，所有可能的组合都是可能的，可以使用 dataframe.unstack with dataframe.stack ，对于替换为空名单的不存在的值fill_value = [] parameter：

df = pd.merge(chm_df, cm_df, on=['country','menu']).groupby(['country','hotel'])['menu'].apply(list).unstack(fill_value=[]).stack().reset_index(name='menu items')
    
print (df)
   country     hotel          menu items
0    India    Hilton                  []
1    India  Marriott                  []
2    India    Oberoi        [ildi, soup]
3    India       Taj  [ildi, dosa, soup]
4    Italy    Hilton                  []
5    Italy  Marriott      [pasta, pizza]
6    Italy    Oberoi                  []
7    Italy       Taj                  []
8      USA    Hilton     [pizza, burger]
9      USA  Marriott                  []
10     USA    Oberoi                  []
11     USA       Taj              [dosa]

如果需要，则只有从<<代码> CHM_DF 转换为空列表：

df = pd.merge(chm_df, cm_df, on=['country','menu']).groupby(['country','hotel'])['menu'].apply(list).reindex(pd.MultiIndex.from_frame(ch_df), fill_value=[]).reset_index(name='menu items')
    
print (df)
  country     hotel          menu items
0   India       Taj  [ildi, dosa, soup]
1   India    Oberoi        [ildi, soup]
2   India    Hilton                  []
3     USA       Taj              [dosa]
4     USA    Hilton     [pizza, burger]
5   Italy    Oberoi                  []
6   Italy  Marriott      [pasta, pizza]

Answer 2

我正在使用的桌子太大（〜100m行），因此我最初寻求的方法不起作用。因此，我现在已经做出了一种更好的方法。

import pandas as pd

ch = {'country':['India','India','India','USA','USA','Italy','Italy'],'hotel':['Taj','Oberoi','Hilton','Taj','Hilton','Oberoi','Marriott']}
ch_df = pd.DataFrame.from_dict(ch)

hm = {'hotel':['Taj','Taj','Taj','Oberoi','Oberoi','Marriott','Marriott','Marriott','Hilton','Hilton'],'menu':['ildi','dosa','soup','soup','ildi','soup','pasta','pizza','pizza','burger']}
hm_df = pd.DataFrame.from_dict(hm)

cm = {'country':['India','India','India','USA','USA','USA','Italy','Italy'],'menu':['ildi','dosa','soup','dosa','burger','pizza','pizza','pasta']}
cm_df = pd.DataFrame.from_dict(cm)

c_dict = cm_df.groupby('country')['menu'].apply(set).to_dict()
h_dict = hm_df.groupby('hotel')['menu'].apply(set).to_dict()

# This won't work if there are missing keys in the dictionary column, as it was in my case
# ch_df['new_col'] = ch_df.apply(lambda x: '|'.join(c_dict[x.country].intersection(h_dict[x.hotel])), axis=1)

def menu_list(a,b):
    if a in c_dict and b in h_dict:
        return '|'.join(c_dict[a].intersection(h_dict[b]))
    return ''

ch_df['menu_list'] = ch_df.apply(lambda x: menu_list(x.country,x.hotel), axis=1)

ch_df