如何在大 pandas 中拆堆(或旋转?) [英] how to unstack (or pivot?) in pandas
问题描述
我有一个如下数据框:
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.
因此，通过将值移入索引，我们可以使用stack和unstack进行交换.
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
这篇关于如何在大 pandas 中拆堆(或旋转?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
