Geek Answers Handbook

How can I use melt () to change the form of a pandas DataFrame to a list by creating an index from a crosstab column and creating a new variable instead?

I have a data matrix of 29,523 rows x 503 columns, of which 3 columns are indexes (below, for example, a subset).

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 in DataFramewith:

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

and then use pandas.melt()to rotate the data:

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

I also tried stack(), but melt()ended up better here.

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

Option mangle_dupe_colson read_csvif Trueput a suffix .intagainst all ValueTypethat are duplicated. This is not ideal, but without it it is impossible to associate the values ​​for the variables with the correct period.

, , Period (1984 Q1) 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

pandas numpy?

DataFrame 14 761 500 x 6 .

+4
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

, 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')])

DataFrame , 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 MultiIndex .

:

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

, , . , DataFrame 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
...
+2

More articles:


All Articles