I have the following dataframe:
Date Blue Red Green
-----------------------------------------
1 1/1/14 55 34 34
2 1/2/14 36 35 23
3 1/3/14 23 46 43
4 1/4/14 47 34 55
I want to "stack" these color-named columns with unique values into a single "Color" column with each corresponding value attributed, so that there are three columns with for each row, a date (in chronological order, a color label, and the attributed value), with dates repeating for each color.
And so I am trying to get this dataframe:
Date Color
--------------------------
1 1/1/14 Blue
2 1/2/14 Blue
3 1/3/14 Blue
4 1/4/14 Blue
5 1/1/14 Red
6 1/2/14 Red
7 1/3/14 Red
8 1/4/14 Red
9 1/1/14 Green
10 1/2/14 Green
11 1/3/14 Green
12 1/4/14 Green
------------------------
I am trying to "stack" my columns by color, but I am not sure if I should be stacking, melting, or concatenating. Which method should I use to get all of my colors and their values into one column?
I am trying to "stack" my columns by color, but I am not sure if I should be stacking, melting, or concatenating.
Use melt:
>>> df.melt(id_vars='Date', var_name='Color', value_name='Value')
Date Color Value
0 1/1/14 Blue 55
1 1/2/14 Blue 36
2 1/3/14 Blue 23
3 1/4/14 Blue 47
4 1/1/14 Red 34
5 1/2/14 Red 35
6 1/3/14 Red 46
7 1/4/14 Red 34
8 1/1/14 Green 34
9 1/2/14 Green 23
10 1/3/14 Green 43
11 1/4/14 Green 55
Alternative with a different output:
>>> (df.set_index('Date').stack()
.rename_axis(['Date', 'Color'])
.reset_index(name='Value'))
Date Color Value
0 1/1/14 Blue 55
1 1/1/14 Red 34
2 1/1/14 Green 34
3 1/2/14 Blue 36
4 1/2/14 Red 35
5 1/2/14 Green 23
6 1/3/14 Blue 23
7 1/3/14 Red 46
8 1/3/14 Green 43
9 1/4/14 Blue 47
10 1/4/14 Red 34
11 1/4/14 Green 55