Pandas dataframe : Group all columns in one with duplicated date

Question

I have a dataframe in Python 3.9 / Pandas

d = {'date': ['01/01/2022', '02/01/2022','03/01/2022','04/01/2022'], 'room1': [10,11,27,65], 'room2': [5,6,8,9], 'room3': [21,25,41,22], 'room4': [14,21,54,13]}
df_test = pd.DataFrame(data=d)
df_test

OUTPUT

I'd like to have a new dataframe with the following results

I try groupby, pivot...

How would you do it pandas?

Answer 1

make use of the pd.melt

df.melt(id_vars='date', var_name='rooms').sort_values('date')

          date  rooms   value
0   01/01/2022  room1   10
4   01/01/2022  room2   5
8   01/01/2022  room3   21
12  01/01/2022  room4   14
1   02/01/2022  room1   11
5   02/01/2022  room2   6
9   02/01/2022  room3   25
13  02/01/2022  room4   21
2   03/01/2022  room1   27
6   03/01/2022  room2   8
10  03/01/2022  room3   41
14  03/01/2022  room4   54
3   04/01/2022  room1   65
7   04/01/2022  room2   9
11  04/01/2022  room3   22
15  04/01/2022  room4   13