如何在不丢失其他列的情况下对数据帧执行groupby？

Question

我有一个数据帧如下：

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

从这个数据帧中，我想创建两列数据帧，分别命名为“派生的\u符号”和“个人\u计数”。为了创建它，我需要关注以下一些条件：

• 衍生的符号需要为每个独特的城市和人形成符号。你知道吗
• 人员计数是根据派生的符号计算的。你知道吗

对于上面这件事，我做了一些事情，而且效果很好：

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)

以上代码的输出：

         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

但我希望Dataframe包含两个附加列，分别是“城市”和“个人符号”，如下所示：

                          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

实际上，创造这两个符号背后的逻辑是：

• 如果当前行是为特定城市创建的，则“城市”列包含城市值，而“人员”符号包含“无条目”。你知道吗
• 如果当前行是为特定符号创建的，则person\u symbol列包含person\u symbol值，city不包含任何项。你知道吗

我怎样才能在不丢失以前行为的情况下对数据进行这样的操作呢？你知道吗

Answer 1

您可以首先将列level_5和val添加到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 

然后通过unstack重新整形，None转换为NO_ENTRY通过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