How To Perform Groupby On Pandas Dataframe Without Losing Other Columns?

I have one Dataframe like below: df = pd.DataFrame({'sport_name': ['football','football','football','football','football','football','football','football','basketball','basketball'

Solution 1:

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_symbolmonthsirperson_name\0basketball.mahesh.TOTAL.mah_count2017-03-03   cmahesh1basketball.mahesh.TOTAL.nagpur_count2017-03-03   cmahesh2basketball.mahesh.TOTAL.pune_count2017-03-03   cmahesh3football.ramesh.TOTAL.delhi_count2017-01-23   aramesh4football.ramesh.TOTAL.delhi_count2017-02-26   bramesh5football.ramesh.TOTAL.mum_count2017-01-23   aramesh6football.ramesh.TOTAL.mum_count2017-02-26   bramesh7football.ramesh.TOTAL.mumbai_count2017-01-23   aramesh8football.ramesh.TOTAL.mumbai_count2017-02-26   bramesh9football.ramesh.TOTAL.ram_count2017-01-23   aramesh10football.ramesh.TOTAL.ram_count2017-02-26   brameshlevel_5valperson_count0person_symbolmah301citynagpur202citypune103citydelhi454citydelhi775person_symbolmum396person_symbolmum667citymumbai248citymumbai579person_symbolram3010person_symbolram68

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_symbolmonthsirperson_name\0basketball.mahesh.TOTAL.mah_count2017-03-03   cmahesh1basketball.mahesh.TOTAL.nagpur_count2017-03-03   cmahesh2basketball.mahesh.TOTAL.pune_count2017-03-03   cmahesh3football.ramesh.TOTAL.delhi_count2017-01-23   aramesh4football.ramesh.TOTAL.delhi_count2017-02-26   bramesh5football.ramesh.TOTAL.mum_count2017-01-23   aramesh6football.ramesh.TOTAL.mum_count2017-02-26   bramesh7football.ramesh.TOTAL.mumbai_count2017-01-23   aramesh8football.ramesh.TOTAL.mumbai_count2017-02-26   bramesh9football.ramesh.TOTAL.ram_count2017-01-23   aramesh10football.ramesh.TOTAL.ram_count2017-02-26   brameshperson_countcityperson_symbol030NO_ENTRYmah120nagpurNO_ENTRY210puneNO_ENTRY345delhiNO_ENTRY477delhiNO_ENTRY539NO_ENTRYmum666NO_ENTRYmum724mumbaiNO_ENTRY857mumbaiNO_ENTRY930NO_ENTRYram1068NO_ENTRYram