Reshape excel table with stacked date column

0 votes

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')
Feb 6, 2023 in Others by Kithuzzz
• 38,010 points
424 views

1 answer to this question.

0 votes

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      
answered Feb 6, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Convert Feet to meters in excel with macro for entire column

You can convert Feet to Kilometers using ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,720 points
543 views
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,720 points
1,320 views
0 votes
1 answer

How to subtract dates in Excel with different date formats?

You can use dates in any computation ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 63,720 points
846 views
0 votes
1 answer

Compare each excel sheets with a table in oracle

One way to verify the count and ...READ MORE

answered Feb 5, 2023 in Others by narikkadan
• 63,720 points
245 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,207 views
0 votes
1 answer
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,720 points
3,055 views
0 votes
1 answer

Get column names of Excel worksheet with OpenPyXL in readonly mode

This will print every thing from row ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 63,720 points
6,603 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP