get a value of a column based on common values between two data frame-CodePudding

I am trying to get a value of a column A only for common values between two different data frame.

Dataframe 1:

Dataframe 2:

Final Dataframe:

I tried the below code: it works if there is no null or string in X_2 as well as when there is only one value to assign. But it won't work when we have two values from x_2.

df2['X_2'] = np.nan
x_list = df1['x_1'].tolist()
for index in range(len(df2)):
    
    item = df2['A_1'][index]
    if item in x_list:
        value = df1.loc[df1['x_1)']== item,'X_2'].item()
        df2['X_2'][index] = value
    else:
        pass

CodePudding user response：

Try this:

# Optional
df1 = df1.replace(['No available', 'null'], np.nan)

df2 = df2.set_index('A_1').assign(X_2=df1.groupby('X_1')['X_2'].agg(list).explode().dropna().groupby(level=0).agg(list).rename().rename_axis(None)).reset_index()

Output:

>>> df2
        A_1                   X_2
0  99192401  [80306228, 12345678]
1  99192627            [30306711]
2  99192651            [10306222]
3  99192628                   NaN