Python Pandas – stack , transpose , melt 與 Pivot_table 函數

測試環境為 CentOS 8 (虛擬機)

透過 Pandas 下面幾種函數將 行 (row) 與 列 (Column) 做轉置.

安裝所需模組

[root@localhost ~]# pip install pandas

匯入模組

[root@localhost ~]# python3
Python 3.6.8 (default, Sep 10 2021, 09:13:53)
[GCC 8.5.0 20210514 (Red Hat 8.5.0-3)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas as pd

資料範例

>>> df=pd.DataFrame({'Staff': ['Ben', 'Ben' , 'Mark' , 'Mark' , 'Ben' , 'Mark'],
                    'Item': ['Apple', 'Samsung' , 'Samsung' , 'Apple' , 'Apple' ,'Apple'],
                    'Num': [1 , 1 , 2 , 2 , 1 , 1],
                    'Price':[1000 , 1200 , 1100 , 1100 , 1200 , 900]})

>>> df
  Staff     Item  Num  Price
0   Ben    Apple    1   1000
1   Ben  Samsung    1   1200
2  Mark  Samsung    2   1100
3  Mark    Apple    2   1100
4   Ben    Apple    1   1200
5  Mark    Apple    1    900

stack 函數

>>> df.stack()
0  Staff        Ben
   Item       Apple
   Num            1
   Price       1000
1  Staff        Ben
   Item     Samsung
   Num            1
   Price       1200
2  Staff       Mark
   Item     Samsung
   Num            2
   Price       1100
3  Staff       Mark
   Item       Apple
   Num            2
   Price       1100
4  Staff        Ben
   Item       Apple
   Num            1
   Price       1200
5  Staff       Mark
   Item       Apple
   Num            1
   Price        900
dtype: object

transpose 函數

>>> df.transpose()
           0        1        2      3      4      5
Staff    Ben      Ben     Mark   Mark    Ben   Mark
Item   Apple  Samsung  Samsung  Apple  Apple  Apple
Num        1        1        2      2      1      1
Price   1000     1200     1100   1100   1200    900

melt 函數

>>> df.melt(id_vars=['Staff','Item'] , value_vars=['Num','Price'])
   Staff     Item variable  value
0    Ben    Apple      Num      1
1    Ben  Samsung      Num      1
2   Mark  Samsung      Num      2
3   Mark    Apple      Num      2
4    Ben    Apple      Num      1
5   Mark    Apple      Num      1
6    Ben    Apple    Price   1000
7    Ben  Samsung    Price   1200
8   Mark  Samsung    Price   1100
9   Mark    Apple    Price   1100
10   Ben    Apple    Price   1200
11  Mark    Apple    Price    900

Pivot_table 函數

其中的 aggfunc (Aggregation Function) 請參考 – https://benjr.tw/105479

>>> df.pivot_table(index= 'Staff', columns='Item' , values=['Num','Price'] ,aggfunc='sum')
        Num         Price
Item  Apple Samsung Apple Samsung
Staff
Ben       2       1  2200    1200
Mark      3       2  2000    1100

>>> df.pivot_table(index= 'Staff', columns='Item' , values=['Num','Price'] ,aggfunc=['sum','mean'])
        sum                        mean
        Num         Price           Num         Price
Item  Apple Samsung Apple Samsung Apple Samsung Apple Samsung
Staff
Ben       2       1  2200    1200   1.0     1.0  1100    1200
Mark      3       2  2000    1100   1.5     2.0  1000    1100