CODEWITHSUNDEEP
pythonpandasnumpydataframe
bismo
bismo

Reputation: 1439

How to combine duplicate rows in python pandas

I have a data frame similar to the one listed below. For some reason, each team is listed twice, one listing corresponding to each column. 

import pandas as pd
import numpy as np
d = {'Team': ['1', '2', '3', '1', '2', '3'], 'Points for': [5, 10, 15, np.nan,np.nan,np.nan], 'Points against' : [np.nan,np.nan,np.nan, 3, 6, 9]}
df = pd.DataFrame(data=d)




Team    Points for  Points against
0   1        5            Nan
1   2       10            Nan
2   3       15            Nan
3   1       Nan            3
4   2       Nan            6
5   3       Nan            9

How can I just combine rows of duplicate team names so that there are no missing values? This is what I would like:

 Team   Points for  Points against
0   1        5             3
1   2       10             6
2   3       15             9

I have been trying to figure it out with pandas, but can't seem to get it. Thanks!

Upvotes: 1

Views: 1106

Answers (4)

dal233
dal233

Reputation: 80

pd.pivot_table(df, values = ['Points for','Points against'],index=['Team'], aggfunc=np.sum)[['Points for','Points against']]

Output

      Points for  Points against
Team                            
1            5.0             3.0
2           10.0             6.0
3           15.0             9.0

Upvotes: 0

sammywemmy
sammywemmy

Reputation: 28644

I made changes to your code, replacing string 'Nan' with numpy's nan.

One solution is to melt the data, drop the null entries, and pivot back to wide from long: 

df = (df
      .melt('Team')
      .dropna()
      .pivot('Team','variable','value')
      .reset_index()
      .rename_axis(None,axis='columns')
      .astype(int)
     )

df


  Team  Points against  Points for
0   1      3              5
1   2      6              10
2   3      9              15

Upvotes: 1

Eric Truett
Eric Truett

Reputation: 3010

You need to groupby the unique identifiers. If there is also a game ID or date or something like that, you might need to group on that as well.

df.groupby('Team').agg({'Points for': 'max', 'Points against': 'max'})

Upvotes: 0

Chris
Chris

Reputation: 29742

One way using groupby. :

df = df.replace("Nan", np.nan)
new_df = df.groupby("Team").first()
print(new_df)

Output:

      Points for  Points against
Team                            
1            5.0             3.0
2           10.0             6.0
3           15.0             9.0

Upvotes: 0

Related Questions