Exploring data insights with SQL and pandas

1 SELECT *2 FROM shopping_behavior3 LIMIT 10; 

• SELECT *: This selects all columns from the specified table.
• LIMIT 10: Limits the result to the first 10 rows returned by the query.

Pandas

1 import pandas as pd23 # Read the dataset into a DataFrame4 df = pd.read_csv('shopping_behavior.csv')56 # Display the first few rows of the DataFrame7  print(df.head(10))

• pd.read_csv(‘shopping_behavior.csv’): Reads the dataset into a data frame using Pandas.
• df.head(10): Returns the first 10 rows of the DataFrame.

1 SELECT2    SUM(CASE WHEN Age IS NULL THEN 1 ELSE 0 END) AS  null_age_count,3    SUM(CASE WHEN Gender IS NULL THEN 1 ELSE 0 END) AS  null_gender_count,4    SUM(CASE WHEN `Purchase Amount (USD)` IS NULL THEN 1 ELSE 0 END) AS  null_purchase_amount_count5    FROM shopping_behavior;

• ‘SUM(CASE …)’: Counts the occurrences of NULL values in specific columns.

Pandas

1 # Check for missing values in the dataset2 missing_values_count = df.isnull().sum()3 print("Missing Values:\n", missing_values_count)

• isnull(): Returns a DataFrame of the same shape as df where each value is either True or False, indicating missing values.
• .sum(): Sums up the number of True values (missing values) for each column.

1 SELECT2   AVG(Age) AS avg_age,3   AVG(`Purchase Amount (USD)`) AS avg_purchase_amount,4   COUNT(DISTINCT Category) AS num_categories,5   COUNT(DISTINCT Payment Method) AS num_payment_methods,6   COUNT(DISTINCT Location) AS num_locations7 FROM shopping_behavior;

• AVG: Calculates the average value.
• COUNT(DISTINCT …): Counts the number of distinct values in specified columns.

Pandas

1 # Display summary statistics2 summary_statistics = df.describe()3 print("Summary Statistics:\n", summary_statistics)

• .describe(): Generates descriptive statistics that summarize the central tendency, dispersion, and shape of the distribution of the DataFrame’s columns.

1 SELECT *2 FROM shopping_behavior3 WHERE Age > 304 AND Gender = 'Male';

• WHERE: Filters the rows based on specified conditions.

Pandas

1 # Filter data based on age and gender2 filtered_data = df[(df['Age'] > 30) & (df['Gender'] == 'Male')]3 print(filtered_data)

• df[‘Age’] > 30: Creates a boolean mask based on the condition ‘Age > 30’.
• &: Combines multiple conditions.
• df[…]: Filters the DataFrame based on the boolean mask.
  

1 SELECT *2 FROM shopping_behavior3 WHERE Category = 'Electronics';

• WHERE: Filters the rows where the category is ‘Electronics’.

Pandas:

1 # Filter data based on category2 electronics_data = df[df['Category'] == 'Electronics']3 print(electronics_data)

• df[‘Category’] == ‘Electronics’: Creates a boolean mask based on the condition ‘Category == Electronics’.
• df[…]: Filters the DataFrame based on the boolean mask.

1 SELECT Gender, AVG(`Purchase Amount (USD)`) AS  avg_purchase_amount2 FROM shopping_behavior3 GROUP BY Gender;

• AVG(“Purchase Amount (USD)“): Calculates the average purchase amount.
• GROUP BY Gender: Groups the result set by gender.

Pandas

1 avg_purchase_by_gender = df.groupby('Gender')['Purchase  Amount (USD)'].mean()2 print(avg_purchase_by_gender)

• groupby(‘Gender’)[‘Purchase Amount (USD)’]: Groups the DataFrame by gender and selects the ‘Purchase Amount (USD)’ column.
• .mean(): Calculates the mean of the selected column for each group.

1 SELECT Location, COUNT(DISTINCT CustomerID) AS num_customers2 FROM shopping_behavior3 GROUP BY Location;

• COUNT(DISTINCT CustomerID): Counts the number of distinct customers.
• GROUP BY Location: Groups the result set by location.

Pandas

1 customer_count_by_location =  df.groupby('Location')['CustomerID'].nunique()2 print(customer_count_by_location)

• groupby(‘Location’)[‘CustomerID’]: Groups the DataFrame by location and selects the ‘CustomerID’ column.
• .nunique(): Counts the number of unique values in the selected column for each group.

1 SELECT Gender,2 AVG(Age) AS avg_age,3 AVG(`Purchase Amount (USD)`) AS avg_purchase_amount,4 COUNT(*) AS num_transactions5 FROM shopping_behavior6 GROUP BY Gender;

• AVG(Age): Calculates the average age.
• AVG(`Purchase Amount (USD)`): Calculates the average purchase amount.
• COUNT(*): Counts the total number of transactions.
• GROUP BY Gender: Groups the result set by gender.

Pandas

1 gender_statistics = df.groupby('Gender').agg({'Age': 'mean',2 'Purchase Amount (USD)': 'mean', 'CustomerID': 'count'})3 gender_statistics.rename(columns={'Age': 'avg_age', 'Purchase4 Amount (USD)': 'avg_purchase_amount', 'CustomerID':5 'num_transactions'}, inplace=True)6 print(gender_statistics)

• groupby(‘Gender’).agg(…): Groups the DataFrame by gender and specifies multiple aggregation functions for different columns.
• .rename(columns={…}): Renames the columns for clarity in the output.

1 SELECT t1.CustomerID, t1.Age, t2.Category2 FROM table1 t13 JOIN table2 t2 ON t1.CustomerID = t2.CustomerID;

• JOIN: Combines rows from two or more tables based on a related column between them.

Pandas

1 # Assuming you have another DataFrame df2 for the second  table2 merged_data = pd.merge(df, df2, on='CustomerID', how='inner')3 print(merged_data[['CustomerID', 'Age', 'Category']])

• pd.merge(): Merges two DataFrames based on one or more common columns.
• how=’inner’: Specifies the type of join (inner in this case) to use.

You can utilize the attached CSV file to execute all the SQL and Pandas code snippets provided in this article, enabling you to observe first-hand how each function processes data.

shopping_behavior.csv

Now, let’s have a look at some of the differences between SQL and Python