I have a dataframe looking like this which is imported from an excel document. The format of the document is standarized and will always come like this.
Number | Country | 2020-10-30 | 2020-10-31 | 2020-11-01 | ------
----------------------------------------------------------------------------------------
0 123 | EN | 5431.40 | 9112847.23 | 65432.42 | ------
1 765 | EN | 2431.50 | 512267.43 | 1542.62 | ------
2 345 | EN | 461.50 | 1512532.63 | 22552.12 | ------
My desired output looks like this:
Number | Country | Date | Amount |
-----------------------------------------------------
0 123 | EN | 2020-10-30 | 5431.40 |
1 123 | EN | 2020-10-31 | 9112847.23 |
2 123 | EN | 2020-11-01 | 65432.42 |
3 765 | EN | 2020-10-30 | 2431.50 |
4 765 | EN | 2020-10-31 | 512267.43 |
5 765 | EN | 2020-11-01 | 1542.62 |
So, i want to replicate the first "Number" column and its rows as many times as i have dates and shift the entire dataframe to a different format. This would mean that instead of having e.g. 365 columns and 3 rows as in the 1st example, i would have 4 columns and 1095 rows. Is there any way to do this at all? Any help is appreciated.
>Solution :
Use melt:
out = df.melt(['Number', 'Country'], var_name='Date', value_name='Amount')
print(out)
# Output
Number Country Date Amount
0 123 EN 2020-10-30 5431.40
1 765 EN 2020-10-30 2431.50
2 345 EN 2020-10-30 461.50
3 123 EN 2020-10-31 9112847.23
4 765 EN 2020-10-31 512267.43
5 345 EN 2020-10-31 1512532.63
6 123 EN 2020-11-01 65432.42
7 765 EN 2020-11-01 1542.62
8 345 EN 2020-11-01 22552.12