Having a dataframe like this:
I would like to know what would be the most efficient way to transform it into this othe one:
I tried to generate all the combinations between Time column and days and then manually create the Value column by checking the given Day-Time cell, but Im sure it must be a more efficient way
IF the original index is not important for you,
You could also use the .melt() method which has the advantage of grouping the days so you have the values for 1 day after another:
df1 = df.melt(id_vars='Time', var_name='Day', value_name='Value')
Result:
| index | Time | Day | Value |
|---|---|---|---|
| 0 | 6am-2pm | Day1 | 15.4 |
| 1 | 2pm-10pm | Day1 | 15.0 |
| 2 | 10pm-6am | Day1 | 14.0 |
| 3 | 6am-2pm | Day2 | 13.4 |
| 4 | 2pm-10pm | Day2 | 2.1 |
| 5 | 10pm-6am | Day2 | 22.0 |
| 6 | 6am-2pm | Day3 | 45.0 |
| 7 | 2pm-10pm | Day3 | 3.4 |
| 8 | 10pm-6am | Day3 | 35.0 |
You could even rearrange the columns index like this to make it more readable in my own opinion:
df1 = df1.reindex(columns=['Day','Time','Value'])
Result:
| index | Day | Time | Value |
|---|---|---|---|
| 0 | Day1 | 6am-2pm | 15.4 |
| 1 | Day1 | 2pm-10pm | 15.0 |
| 2 | Day1 | 10pm-6am | 14.0 |
| 3 | Day2 | 6am-2pm | 13.4 |
| 4 | Day2 | 2pm-10pm | 2.1 |
| 5 | Day2 | 10pm-6am | 22.0 |
| 6 | Day3 | 6am-2pm | 45.0 |
| 7 | Day3 | 2pm-10pm | 3.4 |
| 8 | Day3 | 10pm-6am | 35.0 |
Use set_index and stack:
out = (df.set_index('Time').stack().rename_axis(index=['Time', 'Day'])
.rename('Value').reset_index())
print(out)
# Output
Time Day Value
0 6am – 2pm Day1 15.4
1 6am – 2pm Day2 13.4
2 6am – 2pm Day3 45.0
3 2pm – 10pm Day1 15.0
4 2pm – 10pm Day2 2.1
5 2pm – 10pm Day3 3.4
6 10pm – 6am Day1 14.0
7 10pm – 6am Day2 22.0
8 10pm – 6am Day3 35.0