Python数据处理——Pandas模块使用（五）

你的名字 2022-05-23 03:51 278阅读 0赞

### 本文主要介绍pandas数据转换操作 ###

#### 1.分组 ####

In [42]: df = DataFrame({
       'key1':['a','a','b','b','a'],'key2':['one','two','one','two','one'],'data1':np.random.randn(5)
        ...: ,'data2':np.random.randn(5)})
    
    In [43]: df
    Out[43]:
          data1     data2 key1 key2
    0  0.762448  0.816634    a  one
    1  1.412613  0.867923    a  two
    2  0.899297 -1.049657    b  one
    3  0.912080  0.628012    b  two
    4 -0.549258 -1.327614    a  one
    
    In [44]: grouped = df['data1'].groupby(df['key1'])    #按key1列分组，计算data1列的平均值
    In [45]: grouped
    Out[45]: <pandas.core.groupby.SeriesGroupBy object at 0x00000000073C97F0>
    
    In [46]: grouped.mean()
    Out[46]:
    key1
    a    0.541935
    b    0.905688
    Name: data1, dtype: float64
    
    
    In [48]: df['data1'].groupby([df['key1'],df['key2']]).mean()
    Out[48]:
    key1  key2
    a     one     0.106595
          two     1.412613
    b     one     0.899297
          two     0.912080
    Name: data1, dtype: float64
    
    In [49]: df.groupby('key1').mean()    #根据列名分组
    Out[49]:
             data1     data2
    key1
    a     0.541935  0.118981
    b     0.905688 -0.210822
    
    In [50]: df.groupby(['key1','key2']).mean()
    Out[50]:
                  data1     data2
    key1 key2
    a    one   0.106595 -0.255490
         two   1.412613  0.867923
    b    one   0.899297 -1.049657
         two   0.912080  0.628012
    
    In [51]: df.groupby('key1')['data1'].mean()    #选取部分列进行聚合
    Out[51]:
    key1
    a    0.541935
    b    0.905688
    Name: data1, dtype: float64
    
    In [52]: df.groupby(['key1','key2'])['data1'].mean()
    Out[52]:
    key1  key2
    a     one     0.106595
          two     1.412613
    b     one     0.899297
          two     0.912080
    Name: data1, dtype: float64
    
    In [53]: people = DataFrame(np.random.randn(5,5),columns = ['a','b','c','d','e'],index = ['Joe','Steve','Wes','Jim','Tr ...: avis'])
    
    In [54]: people
    Out[54]:
                   a         b         c         d         e
    Joe     0.223628 -0.282831  0.368583  0.246665 -0.815742
    Steve   0.662181  0.187961  0.515883 -2.021429 -0.624596
    Wes    -1.009086  0.450082 -0.819855 -1.626971  0.632064
    Jim     1.593881  0.803760 -0.209345 -1.295325 -0.553693
    Travis -0.041911  1.115285 -1.648207  0.521751 -0.414183
    
    In [55]: mapping = {
       'a':'red','b':'red','c':'blue','d':'blue','e':'red','f':'orange'}
    In [56]: map_series = Series(mapping)
    In [57]: map_series
    Out[57]:
    a       red
    b       red
    c      blue
    d      blue
    e       red
    f    orange
    dtype: object
    
    In [58]: people.groupby(map_series,axis = 1).count()  #根据series分组
    Out[58]:
            blue  red
    Joe        2    3
    Steve      2    3
    Wes        2    3
    Jim        2    3
    Travis     2    3
    
    In [59]: by_columns = people.groupby(mapping,axis =1)  #根据字典分组
    In [60]: by_columns.sum()
    Out[60]:
                blue       red
    Joe     0.615248 -0.874945
    Steve  -1.505546  0.225546
    Wes    -2.446826  0.073060
    Jim    -1.504670  1.843948
    Travis -1.126456  0.659191
    
    In [61]: people.groupby(len).sum()  #根据函数分组
    Out[61]:
              a         b         c         d         e
    3  0.808423  0.971012 -0.660617 -2.675632 -0.737371
    5  0.662181  0.187961  0.515883 -2.021429 -0.624596
    6 -0.041911  1.115285 -1.648207  0.521751 -0.414183
    
    In [63]: columns = pd.MultiIndex.from_arrays([['US','US','US','JP','JP'],[1,3,5,1,3]],names = ['city','tennor'])
    In [65]: df1 = DataFrame(np.random.randn(4,5),columns = columns)
    In [66]: df1
    Out[66]:
    city          US                            JP
    tennor         1         3         5         1         3
    0       1.103548  1.087425  0.717741 -0.354419  1.294512
    1      -0.247544 -1.247665  1.340309  1.337957  0.528693
    2       2.168903 -0.124958  0.367158  0.478355 -0.828126
    3      -0.078540 -3.062132 -2.095675 -0.879590 -0.020314
    
    In [67]: df1.groupby(level = 'city',axis = 1).count()   #根据索引级别分组
    Out[67]:
    city  JP  US
    0      2   3
    1      2   3
    2      2   3

#### 2.透视表 ####

pandas为我们提供了实现数据透视表功能的函数pivot\_table()，该函数参数说明如下：

<table> 
 <thead> 
  <tr> 
   <th align="left">参数</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">data</td> 
   <td align="left">需要进行透视的数据</td> 
  </tr> 
  <tr> 
   <td align="left">value</td> 
   <td align="left">指定需要聚合的字段</td> 
  </tr> 
  <tr> 
   <td align="left">index</td> 
   <td align="left">指定值为行索引</td> 
  </tr> 
  <tr> 
   <td align="left">columns</td> 
   <td align="left">指定值为列索引</td> 
  </tr> 
  <tr> 
   <td align="left">aggfunc</td> 
   <td align="left">聚合函数</td> 
  </tr> 
  <tr> 
   <td align="left">fill_value</td> 
   <td align="left">常量替换缺失值，默认不替换</td> 
  </tr> 
  <tr> 
   <td align="left">margins</td> 
   <td align="left">是否合并，默认否</td> 
  </tr> 
  <tr> 
   <td align="left">dropna</td> 
   <td align="left">是否观测缺失值，默认是</td> 
  </tr> 
 </tbody> 
</table>

举例：

In [68]: dic = {
       'Name':['LiuShunxiang','Zhangshan','ryan'],
        ...:    ...: 'Sex':['M','F','F'],
        ...:    ...: 'Age':[27,23,24],
        ...:    ...: 'Height':[165.7,167.2,154],
        ...:    ...: 'Weight':[61,63,41]}
        ...:
    In [69]: student = pd.DataFrame(dic)
    In [70]: student
    Out[70]:
       Age  Height          Name Sex  Weight
    0   27   165.7  LiuShunxiang   M      61
    1   23   167.2     Zhangshan   F      63
    2   24   154.0          ryan   F      41
    
    In [71]: pd.pivot_table(student,values = ['Height'],columns = ['Sex'])   #'Height'作为数值变量，'Sex'作为分组变量
    Out[71]:
    Sex         F      M
    Height  160.6  165.7
    
    In [72]: pd.pivot_table(student,values = ['Height','Weight'],columns = ['Sex','Age'])
    Out[72]:
            Sex  Age
    Height  F    23     167.2
                 24     154.0
            M    27     165.7
    Weight  F    23      63.0
                 24      41.0
            M    27      61.0
    dtype: float64
    
    In [73]: pd.pivot_table(student,values = ['Height','Weight'],columns = ['Sex','Age']).unstack()
    Out[73]:
    Age            23     24     27
           Sex
    Height F    167.2  154.0    NaN
           M      NaN    NaN  165.7
    Weight F     63.0   41.0    NaN
           M      NaN    NaN   61.0
    
    In [74]: pd.pivot_table(student,values = ['Height','Weight'],columns = ['Sex'],aggfunc = [np.mean,np.median,np.std])
    Out[74]:
             mean        median               std
    Sex         F      M      F      M          F   M
    Height  160.6  165.7  160.6  165.7   9.333810 NaN
    Weight   52.0   61.0   52.0   61.0  15.556349 NaN