Multiple columns dataframe to two columns only-CodePudding

Is there a pandas' trick to transform a dataframe of the form:

           12345    98765
XYXABC   182.000      0.0
JKLVWX   140.000      0.0
RSTUVW   110.000      0.0

to the form:

          Col_1     Col_2
12345    XYXABC   182.000
12345    JKLVWX   140.000
12345    RSTUVW   110.000
98765    XYXABC       0.0
98765    JKLVWX       0.0
98765    RSTUVW       0.0

PS: There are many more rows and columns, I just put a few ones for better visualization.

CodePudding user response：

Use DataFrame.unstack with DataFrame.reset_index and DataFrame.set_axis:

df1 = df.unstack().reset_index(level=1).set_axis(['Col_1','Col_2'], axis=1)
print (df1)
        Col_1  Col_2
12345  XYXABC  182.0
12345  JKLVWX  140.0
12345  RSTUVW  110.0
98765  XYXABC    0.0
98765  JKLVWX    0.0
98765  RSTUVW    0.0

Or solution with DataFrame.melt:

df1 = (df.rename_axis('Col_1')
         .melt(ignore_index=False, value_name='Col_2')
         .reset_index()
         .set_index('variable')
         .rename_axis(None))
print  (df1)
        Col_1  Col_2
12345  XYXABC  182.0
12345  JKLVWX  140.0
12345  RSTUVW  110.0
98765  XYXABC    0.0
98765  JKLVWX    0.0
98765  RSTUVW    0.0

CodePudding user response：

You can try

out = (df.T.melt(value_vars=df.index,
                var_name='Col_1', value_name='Col_2',
                ignore_index=False))

print(out)

        Col_1  Col_2
12345  XYXABC  182.0
98765  XYXABC    0.0
12345  JKLVWX  140.0
98765  JKLVWX    0.0
12345  RSTUVW  110.0
98765  RSTUVW    0.0