如何使用np.哪里在不同大小的数据帧之间操作数不能一起广播'

Question

我有两个不同大小的数据帧。你知道吗

df1有地址，没有zipcodes。 df2有地址和zipcodes。你知道吗

我正在尝试使用np.where匹配从df1到df2的地址，如果存在匹配，则将相应的zipcode带到df1。你知道吗

不过，我刚刚意识到，这不适用于不同大小的数据帧。你知道吗

第一个没有zipcodes的数据帧：

df1 = pd.DataFrame({'address1':['1 o\'toole st','2 main st','3 high street','5 foo street','10 foo street'],
                   'address2':['town1',np.nan,np.nan,'Bartown',np.nan],
                   'address3':[np.nan,'village','city','county2','county3']})
df1['zipcode']=''
print(df1)

        address1 address2 address3 zipcode
0   1 o'toole st    town1      NaN        
1      2 main st      NaN  village        
2  3 high street      NaN     city        
3   5 foo street  Bartown  county2        
4  10 foo street      NaN  county3       

要从中获取zipcodes的第二个数据帧：

df2 = pd.DataFrame({'address1':['1 o\'toole st','2 main st','7 mill street','5 foo street','10 foo street','asda'],
                   'address2':['town1','village','city','Bartown','county3','efsefs'],
                   'address3':[np.nan,np.nan,np.nan,'county2','USA','asdasd'],
                   'zipcode': ['er45','qw23','rt67','yu89','yu83','aedsa']})
print(df2)

        address1 address2 address3 zipcode
0   1 o'toole st    town1      NaN    er45
1      2 main st  village      NaN    qw23
2  7 mill street     city      NaN    rt67
3   5 foo street  Bartown  county2    yu89
4  10 foo street  county3      USA    yu83
5           asda   efsefs   asdasd   aedsa

使用np.where填充df1['zipcode']列。如果两个地址都匹配，则返回df2['zipcode']否则'no_match'：

df1['zipcode'] = np.where(df1['address1'].isin(df2['address1']), df2['zipcode'], 'no_match')



ValueError                                Traceback (most recent call last)
<ipython-input-176-499624d43d5c> in <module>
----> 1 df1['zipcode'] = np.where(df1['address1'].isin(df2['address1']), df2['zipcode'], 'no_match')
      2 df1

ValueError: operands could not be broadcast together with shapes (5,) (6,) ()

有没有可能这样做np.哪里'和不同大小的数据帧？或者有没有更好的方法来搜索匹配项并跨越zipcode？你知道吗

Answer 1

对fillna创建的新列key使用^{}，对于no match get missing value，最后添加fillna('no_match')：

df1['key'] = df1['address1'] + df1['address2'].fillna(df1['address3'])
df2['key'] = df2['address1'] + df2['address2'].fillna(df2['address3'])

df1['zipcode'] =  df1['key'].map(df2.set_index('key')['zipcode']).fillna('no_match')

print (df1)
        address1 address2 address3                   key   zipcode
0   1 o'toole st    town1      NaN     1 o'toole sttown1      er45
1      2 main st      NaN  village      2 main stvillage      qw23
2  3 high street      NaN     city     3 high streetcity  no_match
3   5 foo street  Bartown  county2   5 foo streetBartown      yu89
4  10 foo street      NaN  county3  10 foo streetcounty3      yu83

Answer 2

可以使用合并：

df_new = df1.merge(df2[['address1', 'zipcode']], on='address1', how='left')
df_new = df_new.fillna('no_match')