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

CodePudding user response：

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:

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:

CodePudding user response：

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