How to stack year-as-row, month-as-column data into a series

Question

There are lots of unstack examples, and not many on stack. I am trying to process a dataset in this format

        1     2     3     4     5     6     7     8     9     10    11    12
1870 -1.00 -1.20 -0.83 -0.81 -1.27 -1.08 -1.04 -0.88 -0.53 -0.92 -0.79 -0.79
1871 -0.25 -0.58 -0.43 -0.50 -0.70 -0.53 -0.60 -0.33 -0.24 -0.33 -0.31 -0.58
1872 -0.72 -0.62 -0.50 -0.77 -0.62 -0.52 -0.32 -0.85 -1.02 -0.94 -0.79 -0.88
1873 -0.78 -1.01 -1.31 -0.67 -0.53 -0.48 -0.58 -0.39 -0.34 -0.78 -0.77 -0.70
1874 -0.93 -1.06 -1.40 -0.94 -0.86 -0.72 -1.00 -1.05 -1.13 -1.25 -1.33 -1.14
...

into this:

1987-01-01 -1.00
1987-02-01 -1.20
1987-03-01 -0.83
1987-04-01 -0.81
...

What is the elegant way to do it?

Answer 1

You can use melt for this, first make sure to reset_index() on your dataframe to make Year a column, and then do this:

df1 = pd.melt(df, id_vars=['Year'], var_name=['Month'])
df1['Date'] = pd.to_datetime(df1['Year'].astype(str) + '-' + df1['Month'].astype(str))
df1 = df1.sort_values(by=['Date']).drop(columns=['Month', 'Year']).reset_index(drop=['index'])
print(df1)

Output:

    value       Date
0   -1.00 1870-01-01
1   -1.20 1870-02-01
2   -0.83 1870-03-01
3   -0.81 1870-04-01
4   -1.27 1870-05-01
5   -1.08 1870-06-01
6   -1.04 1870-07-01
7   -0.88 1870-08-01
8   -0.53 1870-09-01
9   -0.92 1870-10-01
10  -0.79 1870-11-01
11  -0.79 1870-12-01
12  -0.25 1871-01-01
13  -0.58 1871-02-01
14  -0.43 1871-03-01
15  -0.50 1871-04-01
16  -0.70 1871-05-01
17  -0.53 1871-06-01
18  -0.60 1871-07-01
19  -0.33 1871-08-01
20  -0.24 1871-09-01
21  -0.33 1871-10-01
22  -0.31 1871-11-01
23  -0.58 1871-12-01
24  -0.72 1872-01-01
25  -0.62 1872-02-01
26  -0.50 1872-03-01
27  -0.77 1872-04-01
28  -0.62 1872-05-01
29  -0.52 1872-06-01
30  -0.32 1872-07-01
31  -0.85 1872-08-01
32  -1.02 1872-09-01
33  -0.94 1872-10-01
34  -0.79 1872-11-01
35  -0.88 1872-12-01
36  -0.78 1873-01-01
37  -1.01 1873-02-01
38  -1.31 1873-03-01
39  -0.67 1873-04-01
40  -0.53 1873-05-01
41  -0.48 1873-06-01
42  -0.58 1873-07-01
43  -0.39 1873-08-01
44  -0.34 1873-09-01
45  -0.78 1873-10-01
46  -0.77 1873-11-01
47  -0.70 1873-12-01
48  -0.93 1874-01-01
49  -1.06 1874-02-01
50  -1.40 1874-03-01
51  -0.94 1874-04-01
52  -0.86 1874-05-01
53  -0.72 1874-06-01
54  -1.00 1874-07-01
55  -1.05 1874-08-01
56  -1.13 1874-09-01
57  -1.25 1874-10-01
58  -1.33 1874-11-01
59  -1.14 1874-12-01