Transpose multi column to single column using pandas-CodePudding

I am trying to transpose a table, which will combine all the column and make it one without losing the information, my below input and expected output as follows,

I have attached the data link for further understading the data type, kindly use this one for data reference

link:

Expected output:

I am using below code, but its not generating as expected result,

df6=df5.melt(id_vars = "Date" , value_vars=["7","8","9","10"])

CodePudding user response：

The following code gives output you asked in the question. The df I create 7,8,9,10 are numeric. If you want the column as string, then use "7","8","9","10"

Updated code: using file shared. I was using date & in your file it's Date. Maybe this was giving the error.

df = pd.read_csv('file_path/file.csv')



Unnamed: 0       Date   7   8   9  10  11  12  13  14  15  16  17  18  19
0           0   6-Sep-21   5  14  21  16  25  33  29  31  31  31  34  27  36
1           1   7-Sep-21   6  23  34  33  28  32  32  31  23  32  33  35  42
2           2   8-Sep-21   8  21  32  40  33  30  26  30  30  35  41  41  46
3           3   9-Sep-21  15  26  28  30  26  25  43  31  33  33  40  56  50
4           4  10-Sep-21   8  27  29  32  46  41  36  41  40  64  54  47  55
5           5  11-Sep-21  14  36  29  37  47  49  41  52  52  58  63  59  72
6           6  12-Sep-21   6  30  42  48  43  51  52  51  35  42  42  57  65

Made it dynamic:

colList = df.columns[2::]

df1=df.melt(id_vars = ["Date"] , value_vars=colList, var_name='time', value_name='Value').sort_values('Date')

  
print(df1)



       Date time  Value
67  10-Sep-21   16     64
88  10-Sep-21   19     55
46  10-Sep-21   13     36
25  10-Sep-21   10     32
4   10-Sep-21    7      8
39  10-Sep-21   12     41
53  10-Sep-21   14     41
74  10-Sep-21   17     54
81  10-Sep-21   18     47
18  10-Sep-21    9     29
32  10-Sep-21   11     46
11  10-Sep-21    8     27
60  10-Sep-21   15     40
26  11-Sep-21   10     37
33  11-Sep-21   11     47
54  11-Sep-21   14     52
40  11-Sep-21   12     49
47  11-Sep-21   13     41
61  11-Sep-21   15     52
68  11-Sep-21   16     58
89  11-Sep-21   19     72
19  11-Sep-21    9     29
5   11-Sep-21    7     14
75  11-Sep-21   17     63
82  11-Sep-21   18     59
12  11-Sep-21    8     36
41  12-Sep-21   12     51
83  12-Sep-21   18     57
48  12-Sep-21   13     52
6   12-Sep-21    7      6
34  12-Sep-21   11     43
20  12-Sep-21    9     42
76  12-Sep-21   17     42
13  12-Sep-21    8     30
27  12-Sep-21   10     48
62  12-Sep-21   15     35
69  12-Sep-21   16     42
55  12-Sep-21   14     51
90  12-Sep-21   19     65
77   6-Sep-21   18     27
56   6-Sep-21   15     31
49   6-Sep-21   14     31
70   6-Sep-21   17     34
63   6-Sep-21   16     31
84   6-Sep-21   19     36
0    6-Sep-21    7      5
21   6-Sep-21   10     16
42   6-Sep-21   13     29
7    6-Sep-21    8     14
14   6-Sep-21    9     21
35   6-Sep-21   12     33
28   6-Sep-21   11     25
1    7-Sep-21    7      6
85   7-Sep-21   19     42
8    7-Sep-21    8     23
78   7-Sep-21   18     35
15   7-Sep-21    9     34
71   7-Sep-21   17     33
22   7-Sep-21   10     33
64   7-Sep-21   16     32
43   7-Sep-21   13     32
36   7-Sep-21   12     32
50   7-Sep-21   14     31
29   7-Sep-21   11     28
57   7-Sep-21   15     23
9    8-Sep-21    8     21
2    8-Sep-21    7      8
37   8-Sep-21   12     30
72   8-Sep-21   17     41
30   8-Sep-21   11     33
16   8-Sep-21    9     32
86   8-Sep-21   19     46
44   8-Sep-21   13     26
65   8-Sep-21   16     35
58   8-Sep-21   15     30
23   8-Sep-21   10     40
79   8-Sep-21   18     41
51   8-Sep-21   14     30
87   9-Sep-21   19     50
3    9-Sep-21    7     15
38   9-Sep-21   12     25
59   9-Sep-21   15     33
52   9-Sep-21   14     31
10   9-Sep-21    8     26
31   9-Sep-21   11     26
73   9-Sep-21   17     40
17   9-Sep-21    9     28
66   9-Sep-21   16     33
24   9-Sep-21   10     30
80   9-Sep-21   18     56
45   9-Sep-21   13     43