pandas: search column values from one df in another df column that contains lists

user_3311213 0
Viewed 40

I need to search the values from the df1['numsearch'] column into the lists in df2['Numbers']. If the number is in those lists, then I want to add values from the df2['Score'] column to df1. See desired output below.

df1 = pd.DataFrame(
    {'Day':['M','Tu','W','Th','Fr','Sa','Su'],
     'numsearch':['1','20','14','99','19','6','101']
    })

df2 = pd.DataFrame(
    {'Letters':['a','b','c','d'],
     'Numbers':[['1','2','3','4'],['5','6','7','8'],['10','20','30','40'],['11','12','13','14']],
     'Score': ['1.1','2.2','3.3','4.4']})


desired output
  Day  numsearch            Score
0   M          1             1.1
1  Tu         20             3.3
2   W          4             4.4
3  Th         99          "No score"
4  Fr         19          "No score"
5  Sa          6             2.2 
6  Su        101          "No score"

I have written a for loop that works with the test data.

scores = []
for s,ns in enumerate(ppr_data['SN']):
    match = ''
    for k,q in enumerate(jcr_data['All_ISSNs']):    
        if ns in q:
            scores.append(jcr_data['Journal Impact Factor'][k])
            match = 1
        else:
            continue
    if match == "":
        scores.append('No score')
        match = ""
df1['Score'] = np.array(scores)

In my small test, but above code works, but when working with larger data files, it is creating duplicates. So this clearly isn't the best way to do this.

I'm sure there's a more pandas-proper line of code that ends in .fillna("No score") .

I tried to use a loc statement, but I get hung up on searching the values of one dataframe in a column that contains lists.

Can anyone shed some light?

pandas dataframe python-3.x list
2 Answers
df2=df2.explode('Numbers')#Explode df2 on Numbers
d=dict(zip(df2.Numbers, df2.Score))#dict Numbers and Scores
df1['Score']=df1.numsearch.map(d).fillna('No Score')#Map dict to df1 filling NaN with No Score

Can shorten it as follows:

df2=df2.explode('Numbers')#Explode df2 on Numbers
df1['Score']=df1.numsearch.map(dict(zip(df2.Numbers, df2.Score))).fillna('No Score')



   Day numsearch     Score
0   M         1       1.1
1  Tu        20       3.3
2   W        14       4.4
3  Th        99  No Score
4  Fr        19  No Score
5  Sa         6       2.2
6  Su       101  No Score
user_89869750

You can try left join and fillna:

df1.merge(df2.explode('Numbers'), 
          left_on='numsearch', 
          right_on='Numbers', how='left')[['Day', 'numsearch', 'Score']].fillna("No score")

Output:

  Day numsearch     Score
0   M         1       1.1
1  Tu        20       3.3
2   W        14       4.4
3  Th        99  No score
4  Fr        19  No score
5  Sa         6       2.2
6  Su       101  No score
user_63615310
Related