Error in creating new dataframe from comparison of 2 dataframe in puthon-CodePudding

I have 2 dataframe whose sample is as below:

df1:

                     Table                Field
0                    AOI                  AEDAT
1                    AEI                  AEDTZ
2                    AOI                  AEENR
3                    AEO                  AENAM
4                    AEO                  AEOST

df2:

        View       Field
0    Accounting 1  AEDAT
1    Accounting 1  AEDAT
2    Accounting 1  AEOST
3    Accounting 1  AEOST

What I want is compare Field columns of the 2 dataframe and if they are similar then in the third dataframe add the View field from the df2 or else add NA as the row to the 3rd dataframe.

Here is what I wrote so far:

df3 = pd.DataFrame(columns=['view'])
for index, row in df1.iterrows():
    for index2, row2 in df2.iterrows():
        if row['Field'] == row2['Field']:
            df3['view'].append(row2['View'])

When I run this code I get following error: TypeError: cannot concatenate object of type '<class 'str'>'; only Series and DataFrame objs are valid

How do I correct this?

CodePudding user response：

Check with merge

df3 = df1.merge(df2, how = 'left', on  = 'Field')

CodePudding user response：

Drop Table and Field columns

df3 = df1.merge(df2, how ='left', on='Field').drop(['Table', 'Field'], axis=1 )