I have two dataframes which look like the following. These are called alevel_count and gcse_count respectively.
Name ALevels
0 Joe Bloggs 3
1 Zane Valdez 1
2 Sherlock Holmes 4
3 Joseph Ryan 2
4 Christian Willis 1
Name GCSEs
0 Joe Bloggs 5
1 Zane Valdez 4
2 Sherlock Holmes 2
What I'm trying to do is to join them together so they look like the following below. Ideally I'd also like to replace the NaN values with 0s, but happy to look at that afterwards.
Name ALevels GCSEs
0 Joe Bloggs 3 5
1 Zane Valdez 1 4
2 Sherlock Holmes 4 2
3 Joseph Ryan 2 NaN
4 Christian Willis 1 NaN
I've tried using merge and join methods but having little luck, I keep getting something like the following below. It seems to be cutting off any entries that don't align.
merged_df = pd.merge(alevel_count, gcse_count, on='Name')
Name ALevels GCSEs
0 Joe Bloggs 3 5
1 Zane Valdez 1 4
2 Sherlock Holmes 4 2
Any help would be greatly appreciated. Also a note these are not real names and generated through PeopleLipsum.
Thanks