Merge CSV files by rows

Question

tvshows.csv = tvID | tvName

ratings.csv = userID | tvID | userRating

I have the following 2 CSV files with these columns. I wish to merge the columns so that the tvName column from tvshows.csv is displayed in ratings.csv corresponding to the correct tvID.

The main issue I am facing is that there are several of the same tvID data in the ratings.csv file as there are many users who may rate the same tv shows. (e.g, 5 people will rate The Simpsons, creating 5 The Simpsons tvIDs). Is there a way for me to take the tvName from tvshows.csv and merge it with the correct tvID in ratings.csv, bearing in mind there may be several of the same tvID in ratings.csv.

I am hoping to complete the task in Python using Pandas, appreciate any help provided.

Example of the data for tvshows.csv:

tvID | tvName
862  |  The Simpsons
8844 |  Futurama
15602| Paw Patrol

Example of the data for ratings.csv:

userID | tvID  | rating
1      |  862  |  7
2      |  8844 |  6
3      |  862  |  8

Please don't add image of the test data as it can't be copy-pasted. I have added an answer from a toy data. — Grayrigel, Sep 23 '20 at 13:51
if I understand you, you need to a join on the tyID key on the rating table, if that's the case please see the duplicate question as it goes over `merge` in detail. — Umar.H, Sep 23 '20 at 13:59
https://www.youtube.com/watch?v=iYWKfUOtGaw I also found this youtube video which is very similar to the task I am working on. Starts at around 7:40min — Kieran Cullinan, Sep 23 '20 at 14:18

Grayrigel · Accepted Answer · 2020-09-23T14:02:57.720

Assuming you can read CSV files, you can use merge method from pandas:

ratings.merge(tvshows, on = 'tvID')

Here is a random toy example:

import pandas as pd

#generating test data
tvshows = pd.DataFrame({'tvName':['Ab','Bc','Cd','De','Ef'],'tvID':['A','B','C','D','E']})
ratings = pd.DataFrame({'userID':[1,2,3,4,5],'userRating':[5,5,3,5,5],'tvID':['A','B','C','D','E']})

#merging the dataframes
out = ratings.merge(tvshows, on = 'tvID')
print(out)

Output:

   userID  userRating tvID tvName
0       1           5    A     Ab
1       2           5    B     Bc
2       3           3    C     Cd
3       4           5    D     De
4       5           5    E     Ef

score 1 · Answer 2 · answered Sep 23 '20 at 13:40

1

You can try use merge:

tvshows = pd.read_csv('tvshows.csv')
ratings = pd.read_csv('ratings.csv')
tvshows_ratings = tvshows.merge(ratings)

answered Sep 23 '20 at 13:40

RKG

87
4

Merge CSV files by rows

2 Answers2