Site icon EASY2DIGITAL

Pandas Pivot Table() – Transpose Data in Column Sequence to Horizontal One in Bulk Using Pandas & Python

Louis Lu

Pandas pivot_table() is super powerful for developers to manipulate the data, such as data visualization, data inventory, API development, etc. In terms of dashboard development or data visualization, transposing specific data objectives from column order to row sequence is very common. So in this article, I’ll go through how to transpose specific data in bulk in a second using Pandas pivot_table() and Python

Ingredients on data transposing using Pandas Pivot table() and Python

Python3, Pandas, Google sheet API (Optional), Microsoft Excel (Optional)

Table of Contents

Pivot_table()

Pivot table is not a strange term because most people hear it from Microsoft excel. In Pandas, it functions similarly and it’s super powerful to manipulate data as well, which can be applied to multiple areas, such as API data, AI algorithms, etc.

There is a long list of parameters that provide people to use and apply those to their application development. In terms of this article, I’ll take stock company CAGR calculation as an example. The raw data is like the picture listed below, in which each stock symbol has annual revenue over the past 5 years, but it orders the yearly performance in a column format.

I’ll transpose thousands of data lines to a horizontal view in a second using 4 parameters as follows:

Thinking Flow to Manipulate Data using Pandas Pivot_table

Abc = df.pivot_table(index='Symbol', columns='Year', values='Revenue',aggfunc='first')

First thing first, we need to tell the system that we’ve selected a key or an index. The rest of the data following this key will be transposed.

Index: column, Grouper, array, or list of the previous

If an array is passed, it must be the same length as the data. The list can contain any of the other types (except the list). Keys to group by on the pivot table index. If an array is passed, it is used in the same manner as column values.

Based on the definition of the index parameter, we should fill in the symbol because the unique key is the stock company symbol. People use this only key to fetch the related data.

Column: column, Grouper, array, or list of the previous

The column parameter is similar to Index, but the column is not the unique list or array. Thus, here we would fill in the year list because we like to transpose it from column to row.

Values: column to aggregate, optional

Without a double, here we should fill in the revenue data set because it’s the basic dataset to calculate the CAGR of each symbol.

Aggfunc: function, list of functions, dict, default numpy.mean

This parameter is a must when the index key has more than one, which easily misleads the system to the dataset having duplicate keys. The duplicate key is not allowable. So this parameter tells please assign the same key to the first one you meet in the transposing process.

aggfunc='first'

Full Python Script of stock company CAGR transposed using Pivot_table()

If you are interested in the full Python scripts of Pandas Pivot Table() – Transpose Data in Column Sequence to Horizontal One in Bulk Using Pandas & Python,  please subscribe to our newsletter by adding the message “Pandas pivot table() to transpose”. We would send you the script immediately to your mailbox.

I hope you enjoy reading Pandas Pivot Table() – Transpose Data in Column Sequence to Horizontal One in Bulk Using Pandas & Python. If you did, please support us by doing one of the things listed below, because it always helps out our channel.
Exit mobile version