Convert many columns to rows in pandas while preserving data

Question

I have a very wide df that looks like this:

import pandas as pd
x = pd.DataFrame([
    {"letter": "a", "keepme": "alpha", "First": 1, "Second": 2, "Third": 3, "Fourth": 4, "One-Thousandth": 1000},
    {"letter": "b", "keepme": "beta", "First": 10, "Second": 20, "Third": 30, "Fourth": 40, "One-Thousandth": 10000},
    {"letter": "c", "keepme": "gamma", "First": 100, "Second": 200, "Third": 300, "Fourth": 400, "One-Thousandth": 100000}    
])
print(x)

    letter keepme  First  Second  Third  One-Thousandth
0      a  alpha      1       2      3          1000
1      b   beta     10      20     30          10000
2      c  gamma    100     200    300          100000

I would like to take the columns First through One-Thousandth, and make each number that appears in there correspond to a unique entry in the df, where the old column name is now the value of a new column called "Name", like this

letter keepme    Name           Value
  a     alpha    First           1
  a     alpha    Second          2
  a     alpha    Third           3
  a     alpha    One-Thousandth  1000
  b     beta     First           10
  b     beta     Second          20
  b     beta     Third           30
  b     beta     One-Thousandth  10000
  c     gamma    First           100
  c     gamma    Second          200
  c     gamma    Third           300
  c     gamma    One-Thousandth  100000

How can I do this? For what it's worth, speed is fairly important here as the actual df has about 4k columns and about 200k rows.

just `x.melt(['letter','keepme'],var_name='Name').sort_values('letter')` ? — anky, Jun 05 '20 at 15:42

score 1 · Answer 1 · answered Jun 05 '20 at 15:44

Use melt here:

df = x.melt(id_vars=['letter', 'keepme'],
            var_name='Name',
            value_name='Value')
print(df)

   letter keepme            Name   Value
0       a  alpha           First       1
1       b   beta           First      10
2       c  gamma           First     100
3       a  alpha          Second       2
4       b   beta          Second      20
5       c  gamma          Second     200
6       a  alpha           Third       3
7       b   beta           Third      30
8       c  gamma           Third     300
9       a  alpha          Fourth       4
10      b   beta          Fourth      40
11      c  gamma          Fourth     400
12      a  alpha  One-Thousandth    1000
13      b   beta  One-Thousandth   10000
14      c  gamma  One-Thousandth  100000

Convert many columns to rows in pandas while preserving data

1 Answers1