CODEWITHSUNDEEP
pythonpandasdataframe
yangyang
yangyang

Reputation: 531

How to melt multiple columns into one column?

I have this table:

a   b   c   d    e    f   19-08-06   19-08-07   19-08-08   g    h    i
1   2   3   4    5    6      7         8           9      10    11   12

I have 34 columns of the date, so I want to melt the date columns to be into one column only.

How can I do this in pyhton?

Thanks in advance

Upvotes: 1

Views: 2096

Answers (1)

Ch3steR
Ch3steR

Reputation: 20669

You can use pd.Series.fullmatch to create a boolean mask for extracting date columns, then use df.melt

m = df.columns.str.fullmatch("\d{2}-\d{2}-\d{2}")
cols = df.columns[m]

df.melt(value_vars=cols, var_name='date', value_name='vals')

       date  vals
0  19-08-06     7
1  19-08-07     8
2  19-08-08     9

If you want to melt while keeping other columns then try this.

df.melt(
    id_vars=df.columns.difference(cols), var_name="date", value_name="vals"
)

  a  b  c  d  e  f   g   h   i      date  vals
0  1  2  3  4  5  6  10  11  12  19-08-06     7
1  1  2  3  4  5  6  10  11  12  19-08-07     8
2  1  2  3  4  5  6  10  11  12  19-08-08     9

Here I did not use value_vars=cols as it's done implicitly

value_vars: tuple, list, or ndarray, optional
   Column(s) to unpivot. If not specified, uses all columns that are 
   not set as id_vars.

Upvotes: 3

Related Questions