pandas 如何使用melt() 将pandas DataFrame 重塑为列表，从交叉表列创建索引并在其位置创建新变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29941384/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I use melt() to reshape a pandas DataFrame to a list, creating an index from a crosstab column and creating a new variable in its place?
提问by ctrl-alt-delete
I have a matrix of data 29523 rows x 503 cols of which 3 cols are indices (below is a subset for example).
我有一个数据矩阵 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
I read this into a DataFramewith:
我把它读成一个DataFrame：
>>> df = pd.read_csv(C:\filename.csv, low_memory=False, mangle_dupe_cols=False)
and then use pandas.melt()to pivot the data:
然后用于pandas.melt()透视数据：
df1 = pd.melt(df, id_vars=['IDX1', 'IDX2', 'IDX3'], var_name='ValueType',
value_name = 'Value')
I have also tried stack()but melt()proved better here.
我也尝试过，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
The option mangle_dupe_colson the read_csvif Truewill place a .intsuffix against all ValueTypes that are duplicated. This is not ideal, but without it there is no way of linking the values for the variables to the correct period.
ifmangle_dupe_cols上的选项将为所有重复的 s放置一个后缀。这并不理想，但没有它就无法将变量的值与正确的时期联系起来。read_csvTrue.intValueType
What I would prefer to do is instead of having the Period(1984 Q1)as a ValueType, give the Periods corresponding Valuea variable 'W'and have each period form part of the IDXas below:
我更喜欢做的是，而不是将Period(1984 Q1)作为 a ValueType，给Periods 对应的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
Is the above possible with pandas or numpy?
Pandas 或 numpy 可以实现以上内容吗？
My final DataFrameis going to be 14,761,500 rows x 6 cols.
我的最终结果DataFrame将是 14,761,500 行 x 6 列。
采纳答案by unutbu
Given
给定的
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
Let us first set ['IDX1', 'IDX2', 'IDX3']as the index.
让我们先设置['IDX1', 'IDX2', 'IDX3']为索引。
df = df.set_index(['IDX1', 'IDX2', 'IDX3'])
The other columns have a periodic quality to them; we want to handle every 4 columns as a group. This idea of "handling as a group" leads naturally to assigning a new index level to the column index; some value which is the same for every 4 columns. This would be ideal:
其他列对它们具有周期性；我们希望将每 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
We can achieve this by building a MultiIndex and assigning it to df.columns:
我们可以通过构建一个 MultiIndex 并将其分配给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')])
Now the desired long-format DataFrame can be obtained by calling df.stackto
move the column levels into the row index:
现在可以通过调用df.stack将列级别移动到行索引中来获得所需的长格式 DataFrame ：
df.columns.names = ['IDX4', 'ValueType']
series = df.stack(['IDX4', 'ValueType'], dropna=False)
Note also that when mangle_dupe_cols=False, the duplicate columns, X, Y,
Z, get overwritten. So you lose data with mangle_dupe_cols=False. For
example, when you use mangle_dupe_cols=Falsethe last row's Zvalue gets
assigns to every Zcolumn regardless of the period.
还需要注意的是，当mangle_dupe_cols=False，重复列X，Y，
Z，会被覆盖。所以你会丢失数据mangle_dupe_cols=False。例如，当您使用mangle_dupe_cols=False最后一行的Z值时，Z不管时间段如何，都将分配给每一列。
So we must use mangle_dupe_cols=True, (or just leave it out since that is the
default) and adjust the code accordingly. That, fortunately, is not hard to do
since we are reassigning df.columnsto a custom-build MultiIndex anyway.
因此，我们必须使用mangle_dupe_cols=True, （或将其省略，因为这是默认设置）并相应地调整代码。幸运的是，这并不难做到，因为df.columns无论如何我们都要重新分配给自定义构建的 MultiIndex。
Putting it all together:
把它们放在一起：
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())
yields
产量
IDX1 IDX2 IDX3 IDX4 ValueType
A A1 Q 1983 Q4 W 10
X A
Y F
Z NaN
1984 Q1 W 110
dtype: object
Note that since we've removed all the column levels, the result is a Series. If you want a DataFrame with 6 columns, then we should follow it up with:
请注意，由于我们已经删除了所有列级别，因此结果是一个系列。如果你想要一个有 6 列的 DataFrame，那么我们应该跟进它：
series.name = 'Value'
df = series.reset_index()
print(df.head())
which yields
这产生
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
...
