如何在 pandas 中拆散(或旋转?)

2022-01-22 00:00:00 python pandas pivot stack

问题描述

我有一个如下所示的数据框:

I have a dataframe that looks like the following:

import pandas as pd
datelisttemp = pd.date_range('1/1/2014', periods=3, freq='D')
s = list(datelisttemp)*3
s.sort()
df = pd.DataFrame({'BORDER':['GERMANY','FRANCE','ITALY','GERMANY','FRANCE','ITALY','GERMANY','FRANCE','ITALY' ], 'HOUR1':[2 ,2 ,2 ,4 ,4 ,4 ,6 ,6, 6],'HOUR2':[3 ,3 ,3, 5 ,5 ,5, 7, 7, 7], 'HOUR3':[8 ,8 ,8, 12 ,12 ,12, 99, 99, 99]}, index=s)

这给了我:

Out[458]: df

             BORDER  HOUR1  HOUR2  HOUR3
2014-01-01  GERMANY      2      3      8
2014-01-01   FRANCE      2      3      8
2014-01-01    ITALY      2      3      8
2014-01-02  GERMANY      4      5     12
2014-01-02   FRANCE      4      5     12
2014-01-02    ITALY      4      5     12
2014-01-03  GERMANY      6      7     99
2014-01-03   FRANCE      6      7     99
2014-01-03    ITALY      6      7     99

我希望最终的数据框看起来像:

I want the final dataframe to look something like:

             HOUR  GERMANY  FRANCE  ITALY
2014-01-01   1     2        2       2     
2014-01-01   2     3        3       3
2014-01-01   3     8        8       8 
2014-01-02   1     4        4       4
2014-01-02   2     5        5       5
2014-01-02   3    12       12      12
2014-01-03   1     6        6       6
2014-01-03   2     7        7       7
2014-01-03   3    99       99      99

我已经完成了以下操作,但还没有完成:

I've done the following but I'm not quite there:

df['date_col'] = df.index

df2 = melt(df, id_vars=['date_col','BORDER'])  
#Can I keep the same index after melt or do I have to set an index like below?
df2.set_index(['date_col', 'variable'], inplace=True, drop=True)
df2 = df2.sort()

df

Out[465]: df2

                         BORDER   value
date_col   variable                 
2014-01-01 HOUR1           GERMANY   2
           HOUR1           FRANCE    2
           HOUR1           ITALY     2
           HOUR2           GERMANY   3
           HOUR2           FRANCE    3
           HOUR2           ITALY     3
           HOUR3           GERMANY   8
           HOUR3           FRANCE    8
           HOUR3           ITALY     8
2014-01-02 HOUR1           GERMANY   4
           HOUR1           FRANCE    4
           HOUR1           ITALY     4
           HOUR2           GERMANY   5
           HOUR2           FRANCE    5
           HOUR2           ITALY     5
           HOUR3           GERMANY  12
           HOUR3           FRANCE   12
           HOUR3           ITALY    12
2014-01-03 HOUR1           GERMANY   6
           HOUR1           FRANCE    6
           HOUR1           ITALY     6
           HOUR2           GERMANY   7
           HOUR2           FRANCE    7
           HOUR2           ITALY     7
           HOUR3           GERMANY  99
           HOUR3           FRANCE   99
           HOUR3           ITALY    99

我以为我可以取消堆叠 df2 以获得类似于我的最终数据帧的东西,但我得到了各种各样的错误.我也尝试过旋转这个数据框,但不能完全得到我想要的.

I thought I could unstack df2 to get something that resembles my final dataframe but I get all sorts of errors. I have also tried to pivot this dataframe but can't quite get what I want.


解决方案

我们希望值(例如'GERMANY')成为列名,列名(例如'HOUR1') 成为值——一种交换.

We want values (e.g. 'GERMANY') to become column names, and column names (e.g. 'HOUR1') to become values -- a swap of sorts.

stack 方法将列名转换为索引值,并且unstack 方法将索引值转换为列名.

The stack method turns column names into index values, and the unstack method turns index values into column names.

所以通过将值转移到索引中,我们可以使用 stackunstack 来执行交换.

So by shifting the values into the index, we can use stack and unstack to perform the swap.

import pandas as pd

datelisttemp = pd.date_range('1/1/2014', periods=3, freq='D')
s = list(datelisttemp)*3
s.sort()
df = pd.DataFrame({'BORDER':['GERMANY','FRANCE','ITALY','GERMANY','FRANCE','ITALY','GERMANY','FRANCE','ITALY' ], 'HOUR1':[2 ,2 ,2 ,4 ,4 ,4 ,6 ,6, 6],'HOUR2':[3 ,3 ,3, 5 ,5 ,5, 7, 7, 7], 'HOUR3':[8 ,8 ,8, 12 ,12 ,12, 99, 99, 99]}, index=s)

df = df.set_index(['BORDER'], append=True)
df.columns.name = 'HOUR'
df = df.unstack('BORDER')
df = df.stack('HOUR')
df = df.reset_index('HOUR')
df['HOUR'] = df['HOUR'].str.replace('HOUR', '').astype('int')
print(df)

产量

BORDER      HOUR  FRANCE  GERMANY  ITALY
2014-01-01     1       2        2      2
2014-01-01     2       3        3      3
2014-01-01     3       8        8      8
2014-01-02     1       4        4      4
2014-01-02     2       5        5      5
2014-01-02     3      12       12     12
2014-01-03     1       6        6      6
2014-01-03     2       7        7      7
2014-01-03     3      99       99     99

相关文章