Reshape excel table with stacked date column

Question

I have an excel file that has diffrent weather stations and the minimum and maximum temprature of every month in a year like this:

Name	Time	Minimum	Maximum
Station 1	2020-01	-10	2
	...	...	...
	2020-12	-5	0
Station 2	2020-01	-8	4
	...	...	...
	2020-12	-6	4
Station 3	2020-01	-8	5
	...	...	...
	2020-12	-5	5
Station 4	2020-01	-9	5
	...	...	...
	2020-12	-11	4

Not quite well versed in Python, but have been trying to follow the pandas user guide and looking at forums using different methods to pivot the table with pandas so the header with the values below becomes like this, but without luck.

Date	Minimum - Station 1	Maximum - Station 1	...	Minimum - Station 4	Maximum - Station 4

This was my final attempt. It results in outputting the header in the wrong order, and the first row of values.

import pandas as pd

df = pd.read_excel('input.xlsx')

result = df.pivot_table(index='Time', columns='Name', values=['Minimum', 'Maximum'])

result.columns = ['_'.join(col) for col in result.columns]

result.to_excel('output.xlsx')

Answer 1

I agree that using a pivot makes sense. Is that the location of your issues? Then perhaps some of the columns in your example data are included in the input table's index. Getting all the columns sorted and titled according to your desired output is the most challenging aspect. With some test data, this works for me:

import numpy as np
import pandas as pd

# Sample data
df = pd.DataFrame({
    "Name": np.repeat([f"Station {i}" for i in range(1, 5)], 12),
    "Time": np.tile([f"2020-{i:02d}" for i in range(1, 13)], 4),
    "Minimum": np.random.randint(-10, 0, size=48),
    "Maximum": np.random.randint(0, 10, size=48)
})

df = df.pivot(index="Time", columns="Name")
df = df.sort_index(axis=1, level=[1, 0], ascending=[True, False])
df = df.set_axis(df.columns.to_flat_index().map(" - ".join), axis=1)
df = df.reset_index(names="Date")

output:

       Date  Minimum - Station 1  Maximum - Station 1  ...  Maximum - Station 3  Minimum - Station 4  Maximum - Station 4
0   2020-01                   -9                    2  ...                    1                   -4                    5
1   2020-02                   -4                    5  ...                    2                   -9                    5
2   2020-03                   -1                    8  ...                    2                   -7                    0
3   2020-04                   -9                    0  ...                    9                   -7                    7
4   2020-05                   -4                    6  ...                    1                   -4                    9
5   2020-06                   -9                    2  ...                    6                   -2                    5
6   2020-07                   -9                    2  ...                    1                   -5                    5
7   2020-08                   -6                    2  ...                    8                   -6                    0
8   2020-09                   -3                    2  ...                    2                  -10                    0
9   2020-10                   -2                    5  ...                    1                   -2                    4
10  2020-11                  -10                    7  ...                    7                   -2                    5
11  2020-12                   -6                    8  ...                    3