I am working in a python pandas environment :D
Currently, I have a dataframe that looks like this :
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 ex9 ex10 ex11 ex12 ex13 ex14 ex15 ex16 ex17 ex18
My goal is to make the dataframe look like this :
0 1 2 3 4 5 6 7 8 category amount ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 9 ex9 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 10 ex10 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 11 ex11 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 12 ex12 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 13 ex13 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 14 ex14 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 15 ex15 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 16 ex16 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 17 ex17 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 18 ex18
Basically, I want the last 9 column titles and values to become their own rows on 2 new columns while keeping the first 8 columns and rows the same. I am aware that this means the data will be duplicated.
I saw some other answers on stackoverflow use the following code for smaller dataframes but it hasn’t worked for me :
df.melt(['Type', 'Class'], var_name='Date', value_name='Value') (df.set_index(['Type', 'Class']) .stack() .rename_axis(['Type', 'Class', 'Date']) .reset_index(name='Value') )
Any and all help is appreciated ! Thank you
Advertisement
Answer
You are almost there with melt
df.melt(id_vars=df.columns[:9], var_name='category', value_name='amount')
Out[469]:
0 1 2 3 4 5 6 7 8 category amount
0 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 9 ex9
1 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 10 ex10
2 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 11 ex11
3 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 12 ex12
4 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 13 ex13
5 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 14 ex14
6 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 15 ex15
7 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 16 ex16
8 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 17 ex17
9 ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 18 ex18