Pivoting your Data using Python, SQL or Spreadsheets

These days collecting data seems easier than ever, but that doesn’t mean you’ll always be able to collect it in a form that provides the insight you’re looking for. Sometimes you’ll need to manipulate your data in ways that alter its organization so you can see it in a new perspective. Pivoting the data is often a simple way to reorganize data columns and rows, transforming them into groups, statistics or summaries. This article will show you three simple ways to pivot data using tools like Google sheets, Microsoft SQL Server Management Studios, and the Pandas Python library.

If you want to follow along, you can download my options time and sales data from kaggle here:

Pivoting in Python using Pandas

Pandas is a powerful and popular data analysis library for Python. It allows you to create Data Frames to view and manipulate your data in a structure similar to a spreadsheet or database table. You can create pivot tables using Pandas in a couple lines of code! I’ll show you how you can pivot columns or rows:

1. Import your dependencies and data:

import pandas as pd
import numpy as np#read your csv data to a Data Frame
data = pd.read_csv(r'C:\Users\Admin\Desktop\Edge\optionsTradeData.csv')#check the data
data.head()

2. Use Pandas.pivot_table function to create a spreadsheet-like pivot table:

pd.pivot_table(data, index = ['Sector'], values = ['Spent'], aggfunc = np.sum)

ORyou can set the sectors to columns instead of using them as the index:

pd.pivot_table(data, columns = ['Sector'], values = ['Spent'], aggfunc = np.sum)