python - Pandas - 取消堆叠/使用多个索引进行透视

Question

我有一个融化的 DataFrame，我想进行数据透视，但无法使用 2 列作为索引来做到这一点。

import pandas as pd
df = pd.DataFrame({'A': {0: 'XYZ', 1: 'XYZ', 2: 'XYZ', 3: 'XYZ', 4: 'XYZ', 5: 'XYZ', 6: 'XYZ', 7: 'XYZ', 8: 'XYZ', 9: 'XYZ', 10: 'ABC', 11: 'ABC', 12: 'ABC', 13: 'ABC', 14: 'ABC', 15: 'ABC', 16: 'ABC', 17: 'ABC', 18: 'ABC', 19: 'ABC'}, 'B': {0: '01/01/2017', 1: '02/01/2017', 2: '03/01/2017', 3: '04/01/2017', 4: '05/01/2017', 5: '01/01/2017', 6: '02/01/2017', 7: '03/01/2017', 8: '04/01/2017', 9: '05/01/2017', 10: '01/01/2017', 11: '02/01/2017', 12: '03/01/2017', 13: '04/01/2017', 14: '05/01/2017', 15: '01/01/2017', 16: '02/01/2017', 17: '03/01/2017', 18: '04/01/2017', 19: '05/01/2017'}, 'C': {0: 'Price', 1: 'Price', 2: 'Price', 3: 'Price', 4: 'Price', 5: 'Trading', 6: 'Trading', 7: 'Trading', 8: 'Trading', 9: 'Trading', 10: 'Price', 11: 'Price', 12: 'Price', 13: 'Price', 14: 'Price', 15: 'Trading', 16: 'Trading', 17: 'Trading', 18: 'Trading', 19: 'Trading'}, 'D': {0: '100', 1: '101', 2: '102', 3: '103', 4: '104', 5: 'Yes', 6: 'Yes', 7: 'Yes', 8: 'Yes', 9: 'Yes', 10: '50', 11: nan, 12: '48', 13: '47', 14: '46', 15: 'Yes', 16: 'No', 17: 'Yes', 18: 'Yes', 19: 'Yes'}})

所以:

A   B   C   D
XYZ 01/01/2017  Price   100
XYZ 02/01/2017  Price   101
XYZ 03/01/2017  Price   102
XYZ 04/01/2017  Price   103
XYZ 05/01/2017  Price   104
XYZ 01/01/2017  Trading Yes
XYZ 02/01/2017  Trading Yes
XYZ 03/01/2017  Trading Yes
XYZ 04/01/2017  Trading Yes
XYZ 05/01/2017  Trading Yes
ABC 01/01/2017  Price   50
ABC 02/01/2017  Price   
ABC 03/01/2017  Price   48
ABC 04/01/2017  Price   47
ABC 05/01/2017  Price   46
ABC 01/01/2017  Trading Yes
ABC 02/01/2017  Trading No
ABC 03/01/2017  Trading Yes
ABC 04/01/2017  Trading Yes
ABC 05/01/2017  Trading Yes

会变成:

A   B   Trading Price
ABC 01/01/2017  Yes 50
    02/01/2017  No  
    03/01/2017  Yes 48
    04/01/2017  Yes 47
    05/01/2017  Yes 46
XYZ 01/01/2017  Yes 100
    02/01/2017  Yes 101
    03/01/2017  Yes 102
    04/01/2017  Yes 103
    05/01/2017  Yes 104

或:

    ABC     XYZ 
    Trading Price   Trading Price
01/01/2017  Yes 50  Yes 100
02/01/2017  No      Yes 101
03/01/2017  Yes 48  Yes 102
04/01/2017  Yes 47  Yes 103
05/01/2017  Yes 46  Yes 104

我认为这可以简单地通过 pivot 来完成，但出现错误:

df.pivot(index=['A', 'B'], columns = ['C'], values = ['D'] )
Traceback (most recent call last):

  File "<ipython-input-41-afcc34979ff8>", line 1, in <module>
    df.pivot(index=['A', 'B'], columns = ['C'], values = ['D'] )

  File "C:\Miniconda\lib\site-packages\pandas\core\frame.py", line 3951, in pivot
    return pivot(self, index=index, columns=columns, values=values)

  File "C:\Miniconda\lib\site-packages\pandas\core\reshape\reshape.py", line 377, in pivot
    index=MultiIndex.from_arrays([index, self[columns]]))

  File "C:\Miniconda\lib\site-packages\pandas\core\series.py", line 248, in __init__
    raise_cast_failure=True)

  File "C:\Miniconda\lib\site-packages\pandas\core\series.py", line 3027, in _sanitize_array
    raise Exception('Data must be 1-dimensional')

Exception: Data must be 1-dimensional

在 R 中，这将通过收集/传播快速完成。

谢谢!

Answer 1

这是你想要的吗？

In [23]: df.pivot_table(index=['A','B'], columns='C', values='D', aggfunc='first')
Out[23]:
C              Price Trading
A   B
ABC 01/01/2017    50     Yes
    02/01/2017   NaN      No
    03/01/2017    48     Yes
    04/01/2017    47     Yes
    05/01/2017    46     Yes
XYZ 01/01/2017   100     Yes
    02/01/2017   101     Yes
    03/01/2017   102     Yes
    04/01/2017   103     Yes
    05/01/2017   104     Yes