I'm trying to create a mapping file. The main issue is to compare two dataframes by using one column, then return a file of all matchine strings in both dataframes alongside some columns from the dataframes.

Example data

df1 = pd.DataFrame({
    'Artist':
    ['50 Cent', 'Ed Sheeran', 'Celine Dion', '2 Chainz', 'Kendrick Lamar'],
    'album':
    ['Get Rich or Die Tryin', ' ', 'Courage', 'So Help Me God!', 'DAMN'],
    'album_id': ['sdf34', '34tge', '34tgr', '34erg', '779uyj']
})

df2 = pd.DataFrame({
    'Artist': ['Beyonce', 'Ed Sheeran', '2 Chainz', 'Kendrick Lamar', 'Jay-Z'],
    'Artist_ID': ['frd345', '3te43', '32fh5', '235he', '345fgrt6']
})

So the main idea is to create a function that provides a mapping file that will take an item in artist name column from df1 and then check df2 artist name column to see if there are any similarities then create a mapping dataframe which contains the similar artist column, the album_id and the artist_id.

I tried the code below but I'm new to python so I got lost in the function. I would appreciate some help on a new function or a build up on what I was trying to do. Thanks!

Code I failed to build:

def get_mapping_file(df1, df2):
# I don't know what I'm doing :'D
    for i in df2['Artist']:
        if i == df1['Artist'].any():
            name = i
            df1_id = df1.loc[df1['Artist'] == name, ['album_id']]
            id_to_use = df1_id.album_id[0]
            df2.loc[df2['Artist'] == i, 'Artist_ID'] = id_to_use
    return df2

The desired output is:

CodePudding user response：

I am not sure if this is actually what you need, but your desired output is an inner join between the two dataframes:

pd.merge(df1, df2, on='Artist', how='inner')

This will give you the rows for Artists present in both dataframes.

CodePudding user response：

For me, it's easy to find that result. So you may do this:

frame = df1.merge(df2, how='inner')

frame = frame.drop('album', axis=1)

and then you'll have your result. Thanks !