How do I align two pandas dataframes on their values in a column?

user_12514344 0
Viewed 50

I am doing som analysis on football data for the fantasy premier league game. I have downloaded .txt files for each gameweek containing every player's stats for that game.

However, because of players leaving and arriving to premier league, the players are not perfectly aligned in each gameweek file. The image shows how player Aaron Connolly entered the league between gameweek 1 and 4, and thereby offset all other players.

enter image description here

What I want to achieve is two datasets where the same player is on the same row in both.

The reason why I want this is that there are 40 gameweeks, and I use loops to combine data between different gameweeks and make new columns.

I have looked at the pandas.align() method, but could not figure out how to align on a column value 'name'

edit: there is 40 gameweeks and over 500 entries pr. gameweek, so I can not manually go in and align them. Aaron Connolly was just an example, but there are multiple offsets in different gameweeks. I want each player to be on the same row in all 40 datasets.

pandas python
2 Answers

Just use setindex:

df1 = pd.DataFrame({'a': [1,2, 3], 'b': [2, 34, 4]})
df2 = pd.DataFrame({'a': [1,4, 3], 'b': [2, 34, 4]})
aligned1, aligned2 = df1.set_index('a').align(df2.set_index('a'))

Then to get your name column back you can do:

aligned1 = aligned.reset_index()
user_49160750

All your concern is conflict among columns name of different gameweeks datas? You can merge them after rename all dataframe.

user_119512200
Related