CODEWITHSUNDEEP
pythonpandas
Qasim Khan
Qasim Khan

Reputation: 19

Unable to create a column in a dataframe from another dataframe

I am trying to create a new column in an existing dataframe by matching values with the other dataframe.

Dataframe 1 - (movies)

index  |  rating  | movie_id  |  movie_title
--------------------------------------------
0      |    5     |    100    |  Inception
1      |    4     |    101    |  Starwars

Dataframe 2 - (recommendations)

index  |  rating   | movie_id  
------------------------------
0      |    3.9    |    101    
1      |    4.7    |    100

What I would like to have - (recommendations):

index  |  rating    | movie_id  |  movie_title
--------------------------------------------
0      |    3.9     |    101    |  Starwars
1      |    4.7     |    100    |  Inception

What I tried to do : (It doesn't make sense though)

pd.merge(movies , recommendations, on ='movie_id', how ='left')

This doesn't make sense because both dataframes are not of same sizes. Recommendation dataframe's size is given by user through console.

Upvotes: 0

Views: 81

Answers (2)

MrNobody33
MrNobody33

Reputation: 6483

You could try this:

newdf=pd.merge(recommendations,movies[movies.columns[1:]], how='left',on='movie_id')
print(newdf)

Output:

   rating  movie_id movie_title
0     3.9       101    Starwars
1     4.7       100   Inception

Upvotes: 1

wwnde
wwnde

Reputation: 26676

Create dictionary of one matching row in df1 as key and the values to be transferred as dictionary value

d=dict(zip(df1.movie_id,df1.movie_title))

Use df.map() method to map values in the dictionary to df2

df2['movie_title']=df2['movie_id'].map(d)



 index  rating  movie_id movie_title
0      0     3.9       101    Starwars
1      1     4.7       100   Inception

Upvotes: 1

Related Questions