我如何使用melt（）将pandas数据框架重塑为列表，从交叉表列创建索引并在其位置创建新变量？

Question

我有一个数据矩阵29523行x 503列，其中3列是索引（下面是一个子集示例）。

IDX1|  IDX2  | IDX3 | 1983 Q4   |  X  | Y |  Z  |1984 Q1 |   X  | Y | Z 
---------------------------------------------------------------------------
A   |   A1   |  Q   |   10      |  A  | F | NaN | 110    |   A  | F | NaN
A   |   A2   |  Q   |   20      |  B  | C | 40  | 120    |   B  | C | 240
A   |   A3   |  Q   |   30      |  A  | F | NaN | 130    |   A  | F | NaN
A   |   A4   |  Q   |   40      |  B  | C | 80  | 140    |   B  | C | 280
A   |   A5   |  Q   |   50      |  A  | F | NaN | 150    |   A  | F | NaN
A   |   A6   |  Q   |   60      |  B  | F | 120 | 160    |   B  | F | 320

我把它读成了一个DataFrame，其中：

>>> df = pd.read_csv(C:\filename.csv, low_memory=False, mangle_dupe_cols=False)

然后使用pandas.melt()来透视数据：

df1 = pd.melt(df, id_vars=['IDX1', 'IDX2', 'IDX3'], var_name='ValueType',
              value_name = 'Value')

我也试过stack()，但melt()在这里证明更好。

IDX1    |   IDX2    |   IDX3    |   ValueType   |   Value
---------------------------------------------------------------
A       |   A1      |   Q       |   1983 Q4     |   10
A       |   A1      |   Q       |   X           |   A
A       |   A1      |   Q       |   Y           |   F
A       |   A1      |   Q       |   Z           |   NaN
A       |   A1      |   Q       |   1984 Q1     |   110
A       |   A1      |   Q       |   X           |   A
A       |   A1      |   Q       |   Y           |   F
A       |   A1      |   Q       |   Z           |   NaN
A       |   A2      |   Q       |   1983 Q4     |   20
A       |   A2      |   Q       |   X           |   B
A       |   A2      |   Q       |   Y           |   C
A       |   A2      |   Q       |   Z           |   40

如果True将对所有重复的ValueType放置.int后缀，则mangle_dupe_cols上的选项read_csv。这并不理想，但没有它，就无法将变量的值链接到正确的周期。

我想做的是不要把Period{}作为ValueType，给Period对应的Value一个变量'W'，并让每个句点组成IDX的一部分，如下所示：

IDX1    |   IDX2    |   IDX3 | IDX4    |    ValueType   |   Value
---------------------------------------------------------------
A       |   A1      |   Q    |  1983 Q4|    W           |   10
A       |   A1      |   Q    |  1983 Q4|    X           |   A
A       |   A1      |   Q    |  1983 Q4|    Y           |   F
A       |   A1      |   Q    |  1983 Q4|    Z           |   NaN
A       |   A1      |   Q    |  1984 Q1|    W           |   110
A       |   A1      |   Q    |  1984 Q1|    X           |   A
A       |   A1      |   Q    |  1984 Q1|    Y           |   F
A       |   A1      |   Q    |  1984 Q1|    Z           |   NaN
A       |   A2      |   Q    |  1983 Q4|    W           |   20
A       |   A2      |   Q    |  1983 Q4|    X           |   B
A       |   A2      |   Q    |  1983 Q4|    Y           |   C
A       |   A2      |   Q    |  1983 Q4|    Z           |   40

上面提到的是熊猫还是裸体动物？

我最后的DataFrame将是14761500行x 6列。

Answer 1

给定

In [189]: df
Out[189]: 
  IDX1 IDX2 IDX3  1983 Q4  X  Y    Z  1984 Q1 X.1 Y.1  Z.1
0    A   A1    Q       10  A  F  NaN      110   A   F  NaN
1    A   A2    Q       20  B  C   40      120   B   C  240
2    A   A3    Q       30  A  F  NaN      130   A   F  NaN
3    A   A4    Q       40  B  C   80      140   B   C  280
4    A   A5    Q       50  A  F  NaN      150   A   F  NaN
5    A   A6    Q       60  B  F  120      160   B   F  320

让我们首先将['IDX1', 'IDX2', 'IDX3']设置为索引。

df = df.set_index(['IDX1', 'IDX2', 'IDX3'])

其他列具有周期性；我们希望每4列处理一次 列作为一个组。这种“群策群力”的思想自然导致 为列索引分配新的索引级别；一些相同的值 每4列。这将是理想的：

               1983 Q4            1984 Q1           
                     W  X  Y    Z       W  X  Y    Z
IDX1 IDX2 IDX3                                      
A    A1   Q         10  A  F  NaN     110  A  F  NaN
     A2   Q         20  B  C  240     120  B  C  240
     A3   Q         30  A  F  NaN     130  A  F  NaN
     A4   Q         40  B  C  280     140  B  C  280
     A5   Q         50  A  F  NaN     150  A  F  NaN
     A6   Q         60  B  F  320     160  B  F  320

我们可以通过构建多索引并将其分配给df.columns来实现这一点：

columns = [col for col in df.columns if col[0] not in set(list('XYZ'))]
df.columns = pd.MultiIndex.from_product([columns, list('WXYZ')])

现在可以通过调用df.stack来获得所需的长格式数据帧 将列级别移动到行索引中：

df.columns.names = ['IDX4', 'ValueType']
series = df.stack(['IDX4', 'ValueType'], dropna=False)

---

还要注意，当mangle_dupe_cols=False时，重复的列X，Y， Z，被覆盖。所以你用mangle_dupe_cols=False丢失数据。为了 例如，当您使用mangle_dupe_cols=False时，最后一行的Z值得到 分配给每个Z列，而不考虑句点。

所以我们必须使用mangle_dupe_cols=True，（或者把它省略掉，因为这是 并相应地调整代码。幸运的是，这并不难做到 因为我们正在将df.columns重新指派给自定义生成多索引。

---

总而言之：

import numpy as np
import pandas as pd
df = pd.read_table('data', sep=r'\s*[|]\s*')
df = df.set_index(['IDX1', 'IDX2', 'IDX3'])
columns = [col for col in df.columns if col[0] not in set(list('XYZ'))]
df.columns = pd.MultiIndex.from_product([columns, list('WXYZ')])
df.columns.names = ['IDX4', 'ValueType']
series = df.stack(['IDX4', 'ValueType'], dropna=False)
print(series.head())

收益率

IDX1  IDX2  IDX3  IDX4     ValueType
A     A1    Q     1983 Q4  W             10
                           X              A
                           Y              F
                           Z            NaN
                  1984 Q1  W            110
dtype: object

注意，由于我们已经删除了所有列级别，因此结果是一个系列。 如果您想要一个包含6列的数据帧，那么我们应该使用以下命令：

series.name = 'Value'
df = series.reset_index()
print(df.head())

会产生

  IDX1 IDX2 IDX3     IDX4 ValueType Value
0    A   A1    Q  1983 Q4         W    10
1    A   A1    Q  1983 Q4         X     A
2    A   A1    Q  1983 Q4         Y     F
3    A   A1    Q  1983 Q4         Z   NaN
4    A   A1    Q  1984 Q1         W   110
...