CODEWITHSUNDEEP
pythonpandas
Eisen
Eisen

Reputation: 1887

Pandas merging record with same ID

League   GameID  Date        Y   G_Home  G_Away   isHome  Team         Differential   Avg_Diff
Ligue_1  6446    2017-08-05  17  4       0        1       Lyon         4              0.86087
Ligue_1  6446    2017-08-05  17  4       0        0       Strasbourg   -4            -4.00000

I have the above dataframe which has the same GameID but it has two different instances for the home and away team. I want to merge them to one record to get the following output:

League   GameID  Date        Y   G_Home  G_Away   HomeTeam  AwayTeam    HomeAvgDiff  AwayAvgDiff           
Ligue_1  6446    2017-08-05  17  4       0        Lyon      Strasbourg  0.86087      -4.00000

How exactly can I do this in pandas?

Upvotes: 1

Views: 44

Answers (1)

Epsi95
Epsi95

Reputation: 9047

Assuming each group will have isHome only 0,1 you can sort them so that it is (home,1 then away,0). Then apply groupby and aggregate to get the output.

df.sort_values(['isHome'], ascending=False).groupby(['GameID']).agg(
                                                    League = ('League', 'first'),
                                                    Date = ('Date', 'first'),
                                                    Y= ('Y', 'first'),
                                                    G_Home = ('G_Home', 'first'),
                                                    G_Away = ('G_Away', 'first'),
                                                    HomeTeam = ('Team', 'first'),
                                                    AwayTeam = ('Team', 'last'),
                                                    HomeDiff =  ('Avg_Diff', 'first'),
                                                    AwayDiff =  ('Avg_Diff', 'last'))

    
GameID  League  Date        Y   G_Home  G_Away  HomeTeam    AwayTeam    HomeDiff AwayDiff                               
6446    Ligue_1 2017-08-05  17  4       0       Lyon       Strasbourg   0.86087 -4.0

Upvotes: 2

Related Questions