Reshaping data for Excel via Python

In this post, I demonstrate an effective way to reshape a CSV or XLSX file that originally has multiple rows repeating for each column. For example, many raw data exports will include a country’s metrics (GDP for example) for the year in each row and it repeats for different years as rows.

Or, a data set on sales from a store by date may be collected as this:

But for analysis what we need is the following format, where each country (or item in second example) appears just once and its row has all the data separated into columns in the same row:

Or, for the store dataset example:

In order to get this format, we need to convert the data into a pivot table. We can do this in Excel easily using PivotTable feature or, we can also do this using Python without needing to open Excel as this example shows.
The code below reads a XLSX file with the raw unshaped data, and then reshapes all that using pandas’ pivot_table() function into a dataframe and writes that dataframe into a new XLSX file. And you’re done!

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

import pandas as pd

input_file = 'somepath/dastafile.xlsx'
df = pd.read_excel(input_file)

print(("\nPrinting head of datafile\n"), df.head(5))

pivot_df = df.pivot_table(index='Entity', columns='Year', values='GiniCoeff') 

print(f"\npivot_df: {pivot_df.head(5)}") 

output_file = 'somepath/Reshaped_datafile.xlsx'
pivot_df.to_excel(output_file)
print(f"Reshaped data is saved as: {output_file}")

All you have to do is change the path and file name for the input and outputfile files. And of course, you’ll need to have the pandas library installed.

The choice is yours whether you want to use Excel to do the reshaping, or this Python method. Hint: This takes no clicks and is very quick! Hope you found this helpful.

Related Posts:

▛
Interested in creating programmable, cool electronic gadgets? Give my newest book on Arduino a try: Hello Arduino!  
▟