How to perform groupby on pandas dataframe without losing other columns?

Question

I have one Dataframe like below:

df = pd.DataFrame({'sport_name': ['football','football','football','football','football','football','football','football','basketball','basketball'],
           'person_name': ['ramesh','ramesh','ramesh','ramesh','ramesh','ramesh','ramesh','ramesh','mahesh','mahesh'],
               'city': ['mumbai', 'mumbai','delhi','delhi','mumbai', 'mumbai','delhi','delhi','pune','nagpur'],
        'person_symbol': ['ram','mum','mum','ram','ram','mum','mum','ram','mah','mah'],
        'person_count': ['10','14','25','20','34','23','43','34','10','20'],
        'month': ['2017-01-23','2017-01-23','2017-01-23','2017-01-23','2017-02-26','2017-02-26','2017-02-26','2017-02-26','2017-03-03','2017-03-03'],
        'sir': ['a','a','a','a','b','b','b','b','c','c']})
df = df[['sport_name','person_name','city','person_symbol','person_count','month','sir']]

print df

   sport_name person_name    city person_symbol person_count       month sir
0    football      ramesh  mumbai           ram           10  2017-01-23   a
1    football      ramesh  mumbai           mum           14  2017-01-23   a
2    football      ramesh   delhi           mum           25  2017-01-23   a
3    football      ramesh   delhi           ram           20  2017-01-23   a
4    football      ramesh  mumbai           ram           34  2017-02-26   b
5    football      ramesh  mumbai           mum           23  2017-02-26   b
6    football      ramesh   delhi           mum           43  2017-02-26   b
7    football      ramesh   delhi           ram           34  2017-02-26   b
8  basketball      mahesh    pune           mah           10  2017-03-03   c
9  basketball      mahesh  nagpur           mah           20  2017-03-03   c

From this Dataframe, I want to create two column dataframe named as "derived_symbol" and "person_count". For creating it I need to focus on some condition like below:

derived_symbol need to form for each unique city and person_symbol.
person_count is calculated based on what the derived_symbol is.

For this above thing I did something and it is working fine:

df = pd.DataFrame({'sport_name': ['football','football','football','football','football','football','football','football','basketball','basketball'],
           'person_name': ['ramesh','ramesh','ramesh','ramesh','ramesh','ramesh','ramesh','ramesh','mahesh','mahesh'],
               'city': ['mumbai', 'mumbai','delhi','delhi','mumbai', 'mumbai','delhi','delhi','pune','nagpur'],
        'person_symbol': ['ram','mum','mum','ram','ram','mum','mum','ram','mah','mah'],
        'person_count': ['10','14','25','20','34','23','43','34','10','20'],
        'month': ['2017-01-23','2017-01-23','2017-01-23','2017-01-23','2017-02-26','2017-02-26','2017-02-26','2017-02-26','2017-03-03','2017-03-03'],
        'sir': ['a','a','a','a','b','b','b','b','c','c']})
df = df[['sport_name','person_name','city','person_symbol','person_count','month','sir']]

df['person_count'] = df['person_count'].astype(int)

df1=df.set_index(['sport_name','person_name','person_count','month','sir']).stack().reset_index(name='val')

df1['derived_symbol'] = df1['sport_name'] + '.' + df1['person_name'] + '.TOTAL.' + df1['val'] + '_count'

df2 = df1.groupby(['derived_symbol','month','sir','person_name'])['person_count'].sum().reset_index(name='person_count')
print (df2)

Output of the above code:

         derived_symbol                   month        sir sport_name  person_name  person_count
0      basketball.mahesh.TOTAL.mah_count  2017-03-03   c  basketball   mahesh            30 
1   basketball.mahesh.TOTAL.nagpur_count  2017-03-03   c  basketball   mahesh            20
2     basketball.mahesh.TOTAL.pune_count  2017-03-03   c  basketball   mahesh            10
3      football.ramesh.TOTAL.delhi_count  2017-01-23   a    football   ramesh            45
4      football.ramesh.TOTAL.delhi_count  2017-02-26   b    football   ramesh            77
5        football.ramesh.TOTAL.mum_count  2017-01-23   a    football   ramesh            39
6        football.ramesh.TOTAL.mum_count  2017-02-26   b    football   ramesh            66
7     football.ramesh.TOTAL.mumbai_count  2017-01-23   a    football   ramesh            24
8     football.ramesh.TOTAL.mumbai_count  2017-02-26   b    football   ramesh            57
9        football.ramesh.TOTAL.ram_count  2017-01-23   a    football   ramesh            30
10       football.ramesh.TOTAL.ram_count  2017-02-26   b    football   ramesh           68

But I want Dataframe with two additional columns which are "city" and "person_symbol" like below:

                          derived_symbol       month sir person_name  sport_name    person_count    city        person_symbol
0      basketball.mahesh.TOTAL.mah_count  2017-03-03   c      mahesh   basketball   30          NO_ENTRY        mah
1   basketball.mahesh.TOTAL.nagpur_count  2017-03-03   c      mahesh   basketball   20          nagpur      NO_ENTRY
2     basketball.mahesh.TOTAL.pune_count  2017-03-03   c      mahesh   football     10          pune        NO_ENTRY
3      football.ramesh.TOTAL.delhi_count  2017-01-23   a      ramesh   football     45          delhi       NO_ENTRY
4      football.ramesh.TOTAL.delhi_count  2017-02-26   b      ramesh   football     77          delhi       NO_ENTRY
5        football.ramesh.TOTAL.mum_count  2017-01-23   a      ramesh   football     39          NO_ENTRY    mum
6        football.ramesh.TOTAL.mum_count  2017-02-26   b      ramesh   football     66          NO_ENTRY    mum
7     football.ramesh.TOTAL.mumbai_count  2017-01-23   a      ramesh   football     24          mumbai      NO_ENTRY
8     football.ramesh.TOTAL.mumbai_count  2017-02-26   b      ramesh   football     57          mumbai      NO_ENTRY
9        football.ramesh.TOTAL.ram_count  2017-01-23   a      ramesh   football     30          NO_ENTRY    ram
10       football.ramesh.TOTAL.ram_count  2017-02-26   b      ramesh   football     68          NO_ENTRY    ram

Actually the logic behind creation of these two symbols is:

If the current row is created for particular city then city column contains city value and person_symbol contains "NO_ENTRY".
If the current row is created for particular symbol then person_symbol columns contains person_symbol value and city contains NO_ENTRY.

How I can do such manipulation of the data without losing my previous behavior?

jezrael · Accepted Answer · 2017-09-13T06:23:11.613

You can first add columns level_5 and val to groupby:

df2 = df1.groupby(['derived_symbol',
                    'month','sir',
                    'person_name', 
                    'level_5',
                    'val'])['person_count'].sum().reset_index(name='person_count')
print (df2)
                          derived_symbol       month sir person_name  \
0      basketball.mahesh.TOTAL.mah_count  2017-03-03   c      mahesh   
1   basketball.mahesh.TOTAL.nagpur_count  2017-03-03   c      mahesh   
2     basketball.mahesh.TOTAL.pune_count  2017-03-03   c      mahesh   
3      football.ramesh.TOTAL.delhi_count  2017-01-23   a      ramesh   
4      football.ramesh.TOTAL.delhi_count  2017-02-26   b      ramesh   
5        football.ramesh.TOTAL.mum_count  2017-01-23   a      ramesh   
6        football.ramesh.TOTAL.mum_count  2017-02-26   b      ramesh   
7     football.ramesh.TOTAL.mumbai_count  2017-01-23   a      ramesh   
8     football.ramesh.TOTAL.mumbai_count  2017-02-26   b      ramesh   
9        football.ramesh.TOTAL.ram_count  2017-01-23   a      ramesh   
10       football.ramesh.TOTAL.ram_count  2017-02-26   b      ramesh   

          level_5     val  person_count  
0   person_symbol     mah            30  
1            city  nagpur            20  
2            city    pune            10  
3            city   delhi            45  
4            city   delhi            77  
5   person_symbol     mum            39  
6   person_symbol     mum            66  
7            city  mumbai            24  
8            city  mumbai            57  
9   person_symbol     ram            30  
10  person_symbol     ram            68

And then reshape back by unstack, None convert to NO_ENTRY by fillna.

df3=df2.set_index(['derived_symbol',
                   'month',
                   'sir',
                   'person_name',
                   'person_count', 
                   'level_5'])['val'] \
       .unstack() \
       .fillna('NO_ENTRY') \
       .rename_axis(None, 1) \
       .reset_index()

print (df3)
                          derived_symbol       month sir person_name  \
0      basketball.mahesh.TOTAL.mah_count  2017-03-03   c      mahesh   
1   basketball.mahesh.TOTAL.nagpur_count  2017-03-03   c      mahesh   
2     basketball.mahesh.TOTAL.pune_count  2017-03-03   c      mahesh   
3      football.ramesh.TOTAL.delhi_count  2017-01-23   a      ramesh   
4      football.ramesh.TOTAL.delhi_count  2017-02-26   b      ramesh   
5        football.ramesh.TOTAL.mum_count  2017-01-23   a      ramesh   
6        football.ramesh.TOTAL.mum_count  2017-02-26   b      ramesh   
7     football.ramesh.TOTAL.mumbai_count  2017-01-23   a      ramesh   
8     football.ramesh.TOTAL.mumbai_count  2017-02-26   b      ramesh   
9        football.ramesh.TOTAL.ram_count  2017-01-23   a      ramesh   
10       football.ramesh.TOTAL.ram_count  2017-02-26   b      ramesh   

    person_count      city person_symbol  
0             30  NO_ENTRY           mah  
1             20    nagpur      NO_ENTRY  
2             10      pune      NO_ENTRY  
3             45     delhi      NO_ENTRY  
4             77     delhi      NO_ENTRY  
5             39  NO_ENTRY           mum  
6             66  NO_ENTRY           mum  
7             24    mumbai      NO_ENTRY  
8             57    mumbai      NO_ENTRY  
9             30  NO_ENTRY           ram  
10            68  NO_ENTRY           ram

@jezrael- I'm seeing 100 different columns while doing last step unstack() like df2.set_index(['derived_symbol','month','sir','person_name','person_count', 'level_5'])['val'].unstack() on real time data? — ketan, Sep 13 '17 at 07:13
@jezrael- Try same command df2.set_index(['derived_symbol','month','sir','person_name',‌'level_5','person_count'])['val'].unstack() by changing the parameter positions. This was the issue. Solved it. — ketan, Sep 13 '17 at 07:40
Supeeer, please accept solution if helpul (and maybe https://stackoverflow.com/a/46170681/2901002 too). thanks. — jezrael, Sep 13 '17 at 07:42

How to perform groupby on pandas dataframe without losing other columns?

1 Answers1

Linked