0
$\begingroup$

I want to join datasets of data I have scraped from Open Critic, vgchartz, and Steam. All of these datasets have different fields but, I want to be able to join the 3 datasets to make one dataset with all of the fields so I can try running it through a machine learning model to predict game sales for my research Project.

I know I can join datasets to single column https://datagy.io/pandas-merge-concat/ but, what if I want to join not same name but, similar names as the way the name is done is different across the sites and I don't know how to standardize the names.

Not sure how to add files here but

Sample open critic: 
[
{"OpenCritic_Rating": "Mighty", "TopCritic_Average": 96, "Critics_Recommend": 96, "title": "The Legend of Zelda: Breath of the Wild", "publisher": "Nintendo", "platform": ["Wii U", "Nintendo Switch"], "date": "2017-03-03", "reviews": {"Eurogamer": "Essential", "IGN": "10 / 10", "GamesRadar+": "5 / 5", "Metro GameCentral": "10 / 10", "Easy Allies": "4.5 / 5", "Game Informer": "10 / 10", "Polygon": "10 / 10", "GameSpot": "10 / 10"}},
{"OpenCritic_Rating": "Mighty", "TopCritic_Average": 96, "Critics_Recommend": 99, "title": "Baldur's Gate 3", "publisher": "Larian Studios", "platform": ["PC", "PlayStation 5"], "date": "2023-08-03", "reviews": {"Eurogamer": "4 / 5", "IGN": "10 / 10", "PC Gamer": "97 / 100", "Metro GameCentral": "9 / 10", "GamesRadar+": "5 / 5", "Game Informer": "9.5 / 10", "GameSpot": "10 / 10"}},

Sample vgchartz:
[
{"img": "/games/boxart/full_5741036AmericaFrontccc.jpg", "title": "God of War    ", "console": "Series", "publisher": "Sony Interactive Entertainment  ", "developer": "SIE Santa Monica Studio  ", "vg_score": "N/A  ", "critic_score": "N/A  ", "user_score": "N/A  ", "total_shipped": "76.55m", "total_sales": "N/A", "na_sales": "N/A", "pal_sales": "N/A", "jp_sales": "N/A", "other_sales": "N/A", "release_date": "22nd Mar 05  ", "last_update": "04th Mar 20", "genre": "Action"},
{"img": "/games/boxart/full_3351915AmericaFrontccc.jpg", "title": "Warriors    ", "console": "Series", "publisher": "KOEI  ", "developer": "Omega Force  ", "vg_score": "N/A  ", "critic_score": "N/A  ", "user_score": "N/A  ", "total_shipped": "49.95m", "total_sales": "N/A", "na_sales": "N/A", "pal_sales": "N/A", "jp_sales": "N/A", "other_sales": "N/A", "release_date": "30th Jun 97  ", "last_update": "24th Mar 20", "genre": "Action"}]

Sample steam:
[
{"name": "Palworld", "date": "2024-01-18", "price": 29.99, "total_positive": 74323, "total_negative": 3428, "total_reviews": 77751, "review_score": 9, "review_score_desc": "Overwhelmingly Positive"},
{"name": "Apex Legends™", "date": "2020-11-04", "price": 0, "total_positive": 808, "total_negative": 131, "total_reviews": 939, "review_score": 8, "review_score_desc": "Very Positive"}
]

All have name attributes but, not all of them share same names or even if same game some same spelling but, others slightly different spacing or extra words to describe I can clean spaces or special characters but, not the extra words

How do I join datasets if they have similar not same name values?

$\endgroup$

1 Answer 1

1
$\begingroup$

Standardizing names may not be your only issue here. You should understand the significance of each column before trying to join them in some way as they may not represent the same ground truths. With any ML model or statistical methods - a common phrase you may have already heard is garbage in, garbage out.

I would suggest figuring out which attributes of each dataset are of most importance to you and which you think may contribute to your model.

Remove insignificant columns:

You may find that some columns are completely useless like the img column in the vgchartz data - this seems to be a URL which probably won't provide much use to you in your model without additional pre-processing, it's also not present in any of your other data.

It would be wise to familiarize yourself with statistical feature selection techniques. You should go through cleaning each dataset individually to end up with the remaining columns, with normalized shapes (i.e. deconstructing the reviews object from the Open-Critic data, possibly create an average of the reviews from all in the object) and types (i.e. total_shipped is a string of 76.55m which can be converted into 76550000).

Modify columns with data imbalances This can be done either by removing the columns with a majority of null values, or look at data imputation.

Once each of your datasets are cleaned and normalized, you can start considering joining the data based on similarities, further normalizing values as you join them, be weary of things like currencies and what dates actually represent - are they release date? last updated?

Hope this helps, let me know if you have any other questions!

$\endgroup$
10
  • $\begingroup$ So I should start removing insignificant columns and modifying columns majority of null, I will do that so I don't get garbage out. I can try working on this but, not sure how to join the datasets. $\endgroup$ Mar 14 at 20:53
  • $\begingroup$ Once you've cleaned your datasets and you're left with the remaining columns, an approach would be to rename all the columns in each dataset the same (assuming they all represent the same thing): df.rename(columns={'old_column_name': 'new_column_name'}, inplace=True). Then you can use pd.merge(df1, df2, on='id', how='inner') and repeat for the 3rd dataset. There are many ways to merge datasets and the pandas documentation does a pretty good job of explaining how. Make sure the columns you're joining represent the same thing. Let me know if anything is unclear and mark resolved otherwise! :) $\endgroup$
    – RegressIt
    Mar 17 at 0:34
  • $\begingroup$ Will be trying this out later $\endgroup$ Mar 18 at 1:03
  • $\begingroup$ Cleaned dataset to remove img, standardizing dates, changed reviews to average review. $\endgroup$ Mar 25 at 3:07
  • $\begingroup$ Names not similar enough one dataset " { "OpenCritic_Rating": "Mighty", "TopCritic_Average": 94, "Critics_Recommend": 98, "title": "God of War", "publisher": "Sony Interactive Entertainment", "date": "2018-04-20", "platform_count": 2, "average_review": 9.685714285714285 }" $\endgroup$ Mar 25 at 3:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.