Pandas using stack or melt(using columns as rows) with multiple rows as column header

Question

I need to use the melt function(pandas) in order to turn my data table into a 1-dimensional format, but I have columns with two rows, and based on my research I had to use multi indexing for defining those rows as columns.

for instance:

df.columns = pd.MultiIndex.from_arrays(
        df.iloc[:2].apply(list, 1))
df = df.iloc[2:].reset_index(drop=True)

After this when I apply melt function:

        df = df.melt(id_vars=df.columns[[0,1]],
                 var_name='columns', value_name='Value')

I get this error: ValueError("Can only tuple-index with a MultiIndex") ValueError: Can only tuple-index with a MultiIndex

Data format that I have:

    A B C
    D E F 
X Y 1 2 3
Z T 4 5 6

Data format that I need to reach:

X Y A D 1
X Y B E 2
X Y C F 3
Z T A D 4
Z T B E 5
Z T C F 6

I am not very experienced in python, so if you can help me I would be so happy. Thanks in advance

kindly share sample data with expected output. Not pics. – sammywemmy Nov 04 '20 at 23:01 — sammywemmy, Nov 04 '20 at 23:01

jezrael · Answer 1 · 2020-11-04T12:49:42.057

Use DataFrame.rename_axis for set new columns names with reshape by DataFrame.stack, last convert MultiIndex Series to DataFrame by Series.reset_index:

df1 = df.rename_axis(['a','b'], axis=1).stack(level=[0,1]).reset_index(name='c')

In DataFrame.melt is possible use only one level, not both:

df2 = df.melt(col_level=0, ignore_index=False)
df3 = df.melt(col_level=1, ignore_index=False)
#failed
df4 = df.melt(col_level=[0,1], ignore_index=False)

Pandas using stack or melt(using columns as rows) with multiple rows as column header

1 Answers1