需要匹配2列2个不同的Pandas数据帧如果它匹配我们需要附加新的d

Question

嗨，我有2个csv文件，这是非常巨大的

df1型

x   y  z      keywords
a   b  c  [apple,iphone,watch,newdevice]
e   w  q   NaN
w   r  t  [pixel,google]
s   t  q  [india,computer]
d   j  o  [google,apple]

df2型

name       stockcode   
apple.inc      appl   
lg.inc          weew   
htc.inc         rrr    
google.com     ggle   

现在我需要用df2中的新值检查df1中的m值，如果它匹配，我需要将新值的细节合并到df1中，否则我们需要用空值填充

我需要用python请帮帮我

样本输出

x   y  z      keywords                        stockcode    
a   b  c  [apple,iphone,watch,newdevice]       aapl    
e   w  q   NaN                                 null    
w   r  t  [pixel,google,]                      ggle    
s   t  q  [india,computer]                     null    
d   j  o  [google,apple]                      aapl,ggle 

我写了这个代码，但它只是比较一个关键字，并给出一个股票代码，我需要出2个股票代码，如果我们有2个关键字是在df2匹配

df1['stockcode'] = np.nan
#mapping data 
for indexKW,valueKW in df1.keyword.iteritems():
    for innerVal in valueKW.split():
        for indexName, valueName in df2['Name'].iteritems():
            for outerVal in valueName.split():
                if outerVal.lower() == innerVal.lower():
                    df1['stockcode'].loc[indexKW] = df2.Identifier.loc[indexName]

上述程序的输出

x   y  z      keywords                        stockcode    
a   b  c  [apple,iphone,watch,newdevice]       aapl    
e   w  q   NaN                                 null    
w   r  t  [pixel,google,]                      ggle    
s   t  q  [india,computer]                     null    
d   j  o  [google,apple]                       ggle

对于最后一行，我有两个关键字在df2中匹配，但我只得到一个关键字google的匹配stockcode，我还需要得到苹果的stockcode，如示例输出所示。你知道吗

样品输出：-你知道吗

x   y  z      keywords                        stockcode    
a   b  c  [apple,iphone,watch,newdevice]       aapl    
e   w  q   NaN                                 null    
w   r  t  [pixel,google,]                      ggle    
s   t  q  [india,computer]                     null    
d   j  o  [google,apple]                      aapl,ggle 

请帮帮我伙计们

Answer 1

可以将apply和map与join一起用作：

df2.set_index('name',inplace=True)
df1.apply(lambda x: pd.Series(x['keywords']).map(df2['stockcode']).dropna().values,1)

0          [appl]
1              []
2          [ggle]
3              []
4    [ggle, appl]
dtype: object

或：

df1.apply(lambda x: ','.join(pd.Series(x['keywords']).map(df2['stockcode']).dropna()),1)

0         appl
1             
2         ggle
3             
4    ggle,appl
dtype: object

或：

df1.apply(lambda x: ','.join(pd.Series(x['keywords']).map(df2['stockcode']).dropna()),1)\
                       .replace('','null')
0         appl
1         null
2         ggle
3         null
4    ggle,appl
dtype: object

---

df1['stockcode'] = df1.apply(lambda x: ','.join(pd.Series(x['keywords'])\
                                          .map(df2['stockcode']).dropna()),1)\
                             .replace('','null')
print(df1)
   x  y  z                           keywords  stockcode
0  a  b  c  [apple, iphone, watch, newdevice]       appl
1  e  w  q                                NaN       null
2  w  r  t                    [pixel, google]       ggle
3  s  t  q                  [india, computer]       null
4  d  j  o                    [google, apple]  ggle,appl

Answer 2

可以将df2转换为查找字典，然后将其映射到df1；）

import numpy as np
import pandas as pd


data1 = {'x':'a,e,w'.split(','),
         'keywords':['apple,iphone,watch,newdevice'.split(','),
                    np.nan,
                    'pixel,google'.split(',')]}
data2 = {'name':'apple lg htc google'.split(),
        'stockcode':'appl weew rrr ggle'.split()}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

mapper = df2.set_index('name').to_dict()['stockcode']

df1['stockcode'] = df1['keywords'].replace(np.nan,'').apply(lambda x : [mapper[i] for i in x if (i and i in mapper.keys())])
df1['stockcode'] = df1['stockcode'].apply(lambda x: x[0] if x else np.nan)