Reshaping an Excel Spreadsheet using Python

Tracy Wilson 0
Viewed 18

Given an Excel worksheet with information presented in this manner: (note that the dates are header columns)

RESOURCENAME        RESOURCETYPENAME    8/13/2022   8/6/2022    7/30/2022   7/23/2022   7/16/2022   7/9/2022    7/2/2022

LASTNAME:FIRSTNAME1 3P          41      43      45      41      42      40      44

LASTNAME:FIRSTNAME2 FTE         50      42      41      46      48      41      42  

LASTNAME:FIRSTNAME3 FTE         40      42      41      41      41      40      40  

LASTNAME:FIRSTNAME4 FTE         40      43      44      41      42      41      42

How do you convert it to this format using Python?

RESOURCENAME        RESOURCETYPENAME    DATE        HOURS
LASTNAME:FIRSTNAME1 3P          8/13/2022   41
LASTNAME:FIRSTNAME1 3P          8/6/2022    43
LASTNAME:FIRSTNAME1 3P          7/30/2022   45
LASTNAME:FIRSTNAME1 3P          7/23/2022   41
LASTNAME:FIRSTNAME1 3P          7/16/2022   42
LASTNAME:FIRSTNAME1 3P          7/9/2022    40
LASTNAME:FIRSTNAME1 3P          7/2/2022    44
LASTNAME:FIRSTNAME2 FTE         8/13/2022   50
LASTNAME:FIRSTNAME2 FTE         8/6/2022    42
LASTNAME:FIRSTNAME2 FTE         7/30/2022   41
LASTNAME:FIRSTNAME2 FTE         7/23/2022   46
LASTNAME:FIRSTNAME2 FTE         7/16/2022   48
LASTNAME:FIRSTNAME2 FTE         7/9/2022    41
LASTNAME:FIRSTNAME2 FTE         7/2/2022    42
etc...
python
2 Answers

By importing the excel file to pandas you can then melt the dataframe and rename the columns and then write back to a csv file. Here is the approach I would use:

import pandas as pd
def reshape_CSV(filepath):
    df = pandas.read_excel(filepath)
    df = df.melt(['RESOURCENAME', 'RESOURCETYPENAME'])
    df.rename(columns={'variable':'Date'}, inplace=True)
    df.to_excel(filepath)

Note: This will overwrite the existing file with the new newly ordered data. see pandas docs for specific arguments for reading and writing excel files

itprorh660

I would recommend using some sort of list here. Create a two-dimensional list that you then, in a second step, rearrange to have the correct order.

You should be able to put the data of the excel-file into a list that looks just like your first table. Then create a second list (or rearrange the first one), to look alike the second table.

Kneidl12020
Related