Continuing the last part, we are going deep into intermediate SQL translated into Pandas DataFrame.
The Playground Database
We will be using the same SQLite database from the previous post, but now we will use the invoice table and the csv file of the table.
Preparing the DataFrame
import pandas as pd
invoices_df = pd.read_csv("invoices.csv")
Aggregation Queries into Pandas DataFrame
We will cover some aggregation function and groupby function in both sql and pandas.
Basic Aggregation
SUM:
SELECT SUM(Total)
FROM invoices
invoices_df['Total'].sum()
COUNT():
SELECT COUNT(BillingState)
FROM invoices
invoices_df['BillingState'].count()
AVG():
SELECT AVG(Total)
FROM invoices
invoices_df['Total'].mean()
MAX():
SELECT MAX(Total)
FROM invoices
invoices_df['Total'].max()
MIN():
SELECT MIN(Total)
FROM invoices
invoices_df['Total'].min()
GROUP BY
SELECT
CustomerId,
SUM(Total) AS Total
FROM invoices
GROUP BY CustomerId
## grouping with all (number) columns aggregated
invoices_df.groupby(['CustomerId']).sum()
## the same as the sql result
invoices_df.groupby(['CustomerId']).sum().reset_index()[['CustomerId', 'Total']]
Top comments (0)