CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
Wisdom258
Wisdom258

Reputation: 173

Adding observations having same column and create one unique row for each observation

I am having a dataset as shown below:

          batsman                 batting_team  2008  2009  2010  2011  2012  2013  2014  2015  2016  2017  2018
0  A Ashish Reddy              Deccan Chargers     0     0     0     0    35     0     0     0     0     0     0
1  A Ashish Reddy          Sunrisers Hyderabad     0     0     0     0     0   125     0    73    47     0     0
2      A Chandila             Rajasthan Royals     0     0     0     0     0     4     0     0     0     0     0
3        A Chopra        Kolkata Knight Riders    42    11     0     0     0     0     0     0     0     0     0
4     A Choudhary  Royal Challengers Bangalore     0     0     0     0     0     0     0     0     0    25     0

I am trying to add the columns with same name as per their scores in each year, also, for example, if A Ready is appearing two times, that means,

I am just trying to add to create if else, but not able to get anywhere.

we create one observation from these two, as the following

Name - A Reddy

Team - second observation team name

2008,2009,...,2018 - and add columns data from year columns.

Upvotes: 0

Views: 61

Answers (1)

Scott Boston
Scott Boston

Reputation: 153460

Try:

df_out = df.groupby('batsman').sum()
#Sums all numeric columns of the dataframe

df_out['batting_team'] = df_out.index.map(df.drop_duplicates(['batsman'], keep='last').set_index('batsman')['batting_team'])
#Use drop duplicates to keep the last team and set_index to use in map 

df_out.reset_index().reindex(df.columns, axis=1)
#Reset index and reorder dataframe columns like input dataframe

Output:

          batsman                 batting_team  2008  2009  2010  2011  2012  2013  2014  2015  2016  2017  2018
0  A Ashish Reddy          Sunrisers Hyderabad     0     0     0     0    35   125     0    73    47     0     0
1      A Chandila             Rajasthan Royals     0     0     0     0     0     4     0     0     0     0     0
2        A Chopra        Kolkata Knight Riders    42    11     0     0     0     0     0     0     0     0     0
3     A Choudhary  Royal Challengers Bangalore     0     0     0     0     0     0     0     0     0    25     0

Upvotes: 2

Related Questions