0

My table looks like this:

no    type     2020-01-01    2020-01-02     2020-01-03     ...................
1      x          1               2              3
2      b          4               3              0

and what I want to do is to melt down the column date and also value to be in separated new columns. I have done it, but I specified the columns that I want to melt like this script below:

cols_dict = dict(zip(df.iloc[:, 3:100].columns, df.iloc[:, 3:100].values[0]))
id_vars = [col for col in df.columns if isinstance(col, str)]
df = df.melt(id_vars = [col for col in df.columns if isinstance(col, str)], var_name = "date", value_name = 'value')

The expected result I want is:

no     type     date           value
1       x      2020-01-01       1 
1       x      2020-01-02       2
1       x      2020-01-03       3
2       b      2020-01-01       4
2       b      2020-01-02       3
2       b      2020-01-03       0

I assume that the column dates will be always added into the data frame as time goes by, so my script would not be worked anymore when the column date is more than 100.

How should I write my script so it will provide any number of date column in the future, as basically my current script could only access until columns number 100.

Thanks in advance.

yangyang
  • 491
  • 4
  • 16
  • Does this answer your question? [Pandas Melt Function](https://stackoverflow.com/questions/34830597/pandas-melt-function) – sushanth Apr 22 '21 at 04:10
  • What is reason for generate columns dynamically? There is a lot of columns for `id_vars` ? – jezrael Apr 22 '21 at 04:40

1 Answers1

0
>>> df.set_index(["no", "type"]) \
      .rename_axis(columns="date") \
      .stack() \
      .rename("value") \
      .reset_index()

   no type        date  value
0   1    x  2020-01-01      1
1   1    x  2020-01-02      2
2   1    x  2020-01-03      3
3   2    b  2020-01-01      4
4   2    b  2020-01-02      3
5   2    b  2020-01-03      0
Corralien
  • 109,409
  • 8
  • 28
  • 52