Add x amount of rows to dataframe

Question

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.

Why Number=345 rows disappear in your output? – Corralien Jan 27 '22 at 12:42 — Corralien, Jan 27 '22 at 12:42

score 4 · Accepted Answer · answered Jan 27 '22 at 12:39

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

Add x amount of rows to dataframe

1 Answers1