According to Excel Easy, a pivot table “allows you to extract the significance from a large, detailed data set. After noticing what Pivot Tables can do in Microsoft Excel, I wanted to look at recreating this using Python. In this tutorial, I will guide you through creating a pivot table using Python IDLE without a .csv file.
Disclaimer: This information reflects my personal experience with using pivot tables.
Note: This tutorial assumes that Python is installed and ready for use.
Step 1: Open Python IDLE
Step 2: Import Necessary Libraries
import pandas as pd
Step 3: Create data.
Note: We will use sales_data as an example
import pandas as pd
sales_data = {
‘Company’: [‘B Grocers’, ‘B Grocers’, ‘B Grocers’, ‘B Grocers’, ‘Family Food Market’, ‘Family Food Market’, ‘Family Food Market’, ‘Family Food Market’, ‘Milken Market’,’Milken Market’, ‘Milken Market’, ‘Milken Market’],
‘City’: [‘Las Vegas’,’Las Vegas’, ‘Las Vegas’, ‘Las Vegas’, ‘Portland’, ‘Portland’, ‘Portland’,’Portland’, ‘Seattle’, ‘Seattle’, ‘Seattle’, ‘Seattle’] ,
‘Quantity’ : [100, 30, 4, 10, 10, 15, 20, 30, 31, 200, 17, 300],
‘Unit Price’: [14, 24, 31, 22, 35, 45, 24, 31, 42, 36, 25, 37],
‘Total Sales’: [1400, 720, 124, 220, 350, 675, 480, 910, 1020, 7200, 425, 11100]}
# Create a DataFrame from the dictionary
df = pd.DataFrame(sales_data, columns=[‘Company’, ‘City’, ‘Quantity’, ‘Unit Price’, ‘Total Sales’])
# Display the DataFrame
print(df)
Step 4: Create a Pivot Table
Find the mean, minimum, maximum, and standard deviation
a = pd.pivot_table(df, index = [‘Company’, ‘City’], aggfunc=(‘mean’, ‘min’, ‘max’, ‘std’, ‘sum’))
print(a)
Find the sum
b = df[[“Unit Price”, “Total Sales”]].sum()
print(b)
Conclusion
This example demonstrates how to create a pivot table in Python without needing a CSV file. The data is defined using a dictionary, and then converted to a DataFrame. This approach is useful for quick analysis.