How to change monthly table into one column with date index?

user_8062316 0
Viewed 27

I downloaded the Broad Dollar Index from FRED with the following format:

           DATE  RTWEXBGS
0    2006-01-01  100.0000
1    2006-02-01  100.2651
2    2006-03-01  100.5424
3    2006-04-01  100.0540
4    2006-05-01   97.8681
..          ...       ...
194  2022-03-01  111.2659
195  2022-04-01  111.8324
196  2022-05-01  114.6075
197  2022-06-01  115.6957
198  2022-07-01  118.2674

I also got an Excel file of inflation rate with a different format:

     Year      Jan      Feb      Mar      Apr      May      Jun      Jul      Aug      Sep      Oct      Nov      Dec   Annual
0    2022  0.07480  0.07871  0.08542  0.08259  0.08582  0.09060  0.08525      NaN      NaN      NaN      NaN      NaN      NaN
1    2021  0.01400  0.01676  0.02620  0.04160  0.04993  0.05391  0.05365  0.05251  0.05390  0.06222  0.06809  0.07036  0.04698
2    2020  0.02487  0.02335  0.01539  0.00329  0.00118  0.00646  0.00986  0.01310  0.01371  0.01182  0.01175  0.01362  0.01234
3    2019  0.01551  0.01520  0.01863  0.01996  0.01790  0.01648  0.01811  0.01750  0.01711  0.01764  0.02051  0.02285  0.01812
4    2018  0.02071  0.02212  0.02360  0.02463  0.02801  0.02872  0.02950  0.02699  0.02277  0.02522  0.02177  0.01910  0.02443
..    ...      ...      ...      ...      ...      ...      ...      ...      ...      ...      ...      ...      ...      ...
104  1918  0.19658  0.17500  0.16667  0.12698  0.13281  0.13077  0.17969  0.18462  0.18045  0.18519  0.20741  0.20438  0.17284
105  1917  0.12500  0.15385  0.14286  0.18868  0.19626  0.20370  0.18519  0.19266  0.19820  0.19469  0.17391  0.18103  0.17841
106  1916  0.02970  0.04000  0.06061  0.06000  0.05941  0.06931  0.06931  0.07921  0.09901  0.10784  0.11650  0.12621  0.07667
107  1915  0.01000  0.01010  0.00000  0.02041  0.02020  0.02020  0.01000 -0.00980 -0.00980  0.00990  0.00980  0.01980  0.00915
108  1914  0.02041  0.01020  0.01020  0.00000  0.02062  0.01020  0.01010  0.03030  0.02000  0.01000  0.00990  0.01000  0.01349

How do I change the inflation table into a format similar to the dollar index?

pandas dataframe
1 Answers

Something like this(didn't take column=Annual into account),

df
###
   Year      Jan      Feb      Mar      Apr      May      Jun      Jul  Aug  \
0  2022  0.07480  0.07871  0.08542  0.08259  0.08582  0.09060  0.08525  NaN   
1  2021  0.01400  0.01676  0.02620  0.04160  0.04993  0.05391  0.05365  NaN   
2  2020  0.02487  0.02335  0.01539  0.00329  0.00118  0.00646  0.00986  NaN   

   Sep  Oct  Nov  Dec  Annual  
0  NaN  NaN  NaN  NaN     NaN  
1  NaN  NaN  NaN  NaN     NaN  
2  NaN  NaN  NaN  NaN     NaN




month = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
df_melt = pd.melt(df, id_vars=['Year'], value_vars=month, var_name='Month', value_name='Sales')
df_melt['Date'] = pd.to_datetime(df_melt['Year'].astype(str) + '-' + df_melt['Month'].astype(str))
# convert Date column to datetime type
df_melt = df_melt[['Date', 'Sales']]
df_melt
###
         Date    Sales
0  2022-01-01  0.07480
1  2021-01-01  0.01400
2  2020-01-01  0.02487
3  2022-02-01  0.07871
4  2021-02-01  0.01676
5  2020-02-01  0.02335
6  2022-03-01  0.08542
7  2021-03-01  0.02620
8  2020-03-01  0.01539
9  2022-04-01  0.08259
10 2021-04-01  0.04160
11 2020-04-01  0.00329
12 2022-05-01  0.08582
13 2021-05-01  0.04993
14 2020-05-01  0.00118
15 2022-06-01  0.09060
16 2021-06-01  0.05391
17 2020-06-01  0.00646
18 2022-07-01  0.08525
19 2021-07-01  0.05365
20 2020-07-01  0.00986
21 2022-08-01      NaN
22 2021-08-01      NaN
23 2020-08-01      NaN
24 2022-09-01      NaN
25 2021-09-01      NaN
26 2020-09-01      NaN
27 2022-10-01      NaN
28 2021-10-01      NaN
29 2020-10-01      NaN
30 2022-11-01      NaN
31 2021-11-01      NaN
32 2020-11-01      NaN
33 2022-12-01      NaN
34 2021-12-01      NaN
35 2020-12-01      NaN
user_145278860
Related