20/01/2026, 22:49 Analytics
In [52]: # ======================================================
# Quantium Virtual Internship – Task 1
# ======================================================
In [37]: # -----------------------
# 1. Import dependencies
# -----------------------
import pandas as pd
import numpy as np
import [Link] as plt
import seaborn as sns
# pandas display settings
pd.set_option('display.max_columns', None)
In [48]: # -----------------------
# 2. Load datasets
# -----------------------
transaction_df = pd.read_excel(file_path + "QVI_transaction_data.xlsx")
customer_df = pd.read_csv("QVI_purchase_behaviour.csv")
In [49]: # -----------------------
# 3. Exploratory Data Analysis
# -----------------------
# Preview datasets
print(transaction_df.head())
print(transaction_df.info())
print(customer_df.head())
print(customer_df.info())
[Link] 1/13
20/01/2026, 22:49 Analytics
DATE STORE_NBR LYLTY_CARD_NBR TXN_ID PROD_NBR \
0 43390 1 1000 1 5
1 43599 1 1307 348 66
2 43605 1 1343 383 61
3 43329 2 2373 974 69
4 43330 2 2426 1038 108
PROD_NAME PROD_QTY TOT_SALES
0 Natural Chip Compny SeaSalt175g 2 6.0
1 CCs Nacho Cheese 175g 3 6.3
2 Smiths Crinkle Cut Chips Chicken 170g 2 2.9
3 Smiths Chip Thinly S/Cream&Onion 175g 5 15.0
4 Kettle Tortilla ChpsHny&Jlpno Chili 150g 3 13.8
<class '[Link]'>
RangeIndex: 264836 entries, 0 to 264835
Data columns (total 8 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 DATE 264836 non-null int64
1 STORE_NBR 264836 non-null int64
2 LYLTY_CARD_NBR 264836 non-null int64
3 TXN_ID 264836 non-null int64
4 PROD_NBR 264836 non-null int64
5 PROD_NAME 264836 non-null object
6 PROD_QTY 264836 non-null int64
7 TOT_SALES 264836 non-null float64
dtypes: float64(1), int64(6), object(1)
memory usage: 16.2+ MB
None
LYLTY_CARD_NBR LIFESTAGE PREMIUM_CUSTOMER
0 1000 YOUNG SINGLES/COUPLES Premium
1 1002 YOUNG SINGLES/COUPLES Mainstream
2 1003 YOUNG FAMILIES Budget
3 1004 OLDER SINGLES/COUPLES Mainstream
4 1005 MIDAGE SINGLES/COUPLES Mainstream
<class '[Link]'>
RangeIndex: 72637 entries, 0 to 72636
Data columns (total 3 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 LYLTY_CARD_NBR 72637 non-null int64
1 LIFESTAGE 72637 non-null object
2 PREMIUM_CUSTOMER 72637 non-null object
dtypes: int64(1), object(2)
memory usage: 1.7+ MB
None
In [20]: # Checking for Null Values
print("Transaction data: ", transaction_df.isnull().sum())
print("Customer data: ", customer_df.isnull().sum())
[Link] 2/13
20/01/2026, 22:49 Analytics
Transaction data: DATE 0
STORE_NBR 0
LYLTY_CARD_NBR 0
TXN_ID 0
PROD_NBR 0
PROD_NAME 0
PROD_QTY 0
TOT_SALES 0
dtype: int64
Customer data: LYLTY_CARD_NBR 0
LIFESTAGE 0
PREMIUM_CUSTOMER 0
dtype: int64
The datasets has no null values
In [50]: #Checking for duplicate values
print("Transaction data: ", transaction_df.duplicated().sum())
print("Customer data: ", customer_df.duplicated().sum())
Transaction data: 1
Customer data: 0
Their is only one duplicate value in transaction dataset
In [51]: #Removing duplicates
transaction_df.drop_duplicates(inplace=True)
#After removing duplicates
print("Transaction data After removing duplicates: ", transaction_df.duplicated(
Transaction data After removing duplicates: 0
In [53]: # -----------------------
# 3a. Convert DATE column
# -----------------------
transaction_df['DATE'] = pd.to_datetime(transaction_df['DATE'], origin='1899-12-
Date column is converted into proper date format to analyze different trends over time
In [55]: # -----------------------
# 3b. Inspect product names
# -----------------------
transaction_df['PROD_NAME'].value_counts().head()
# extract all unique words for checking non-chip items
all_words = " ".join(transaction_df['PROD_NAME']).split()
unique_words = [Link](all_words).value_counts()
print(unique_words.head(20))
[Link] 3/13
20/01/2026, 22:49 Analytics
175g 60560
Chips 49770
150g 41633
Kettle 41288
& 35565
Smiths 28859
Salt 27976
Cheese 27890
134g 25102
Pringles 25102
Doritos 24962
Crinkle 23960
110g 22387
Corn 22063
Original 21560
Cut 20753
Chip 18645
170g 18502
Salsa 18094
Chicken 15406
Name: count, dtype: int64
In [61]: # Remove any products with 'salsa' in the name (case-insensitive)
transaction_df = transaction_df[~transaction_df['PROD_NAME'].[Link]("salsa
Removing salsa product because it is not included in chips
In [59]: # Extract the numeric value (pack size in grams)
transaction_df['PACK_SIZE'] = transaction_df['PROD_NAME'].[Link](r'(\d+)').
Converted string values into float for better analysis
In [63]: # Extract Brand names
# Take first word as brand
transaction_df['BRAND'] = transaction_df['PROD_NAME'].apply(lambda x: [Link]()[
# Clean any known duplicates (optional)
transaction_df['BRAND'] = transaction_df['BRAND'].replace({
"RED": "RRD",
"NATURAL": "NATURA"
})
We take the first word of PROD_NAME as the brand to standardize naming
In [65]: # Example: remove pack size and brand, keep the rest as flavor
transaction_df['FLAVOR'] = transaction_df['PROD_NAME'].[Link](r'\d+g', '',
#transaction_df['FLAVOR'] = transaction_df['FLAVOR'].[Link](transaction_df[
In [67]: transaction_df[['PROD_NAME','BRAND','PACK_SIZE','FLAVOR']]
[Link] 4/13
20/01/2026, 22:49 Analytics
Out[67]: PROD_NAME BRAND PACK_SIZE FLAVOR
Natural Chip Compny Natural Chip Compny
0 NATURA 175.0
SeaSalt175g SeaSalt
1 CCs Nacho Cheese 175g CCS 175.0 CCs Nacho Cheese
Smiths Crinkle Cut Chips Smiths Crinkle Cut Chips
2 SMITHS 170.0
Chicken 170g Chicken
Smiths Chip Thinly Smiths Chip Thinly
3 SMITHS 175.0
S/Cream&Onion 175g S/Cream&Onion
Kettle Tortilla ChpsHny&Jlpno Kettle Tortilla
4 KETTLE 150.0
Chili 150g ChpsHny&Jlpno Chili
... ... ... ... ...
Kettle Sweet Chilli And Sour Kettle Sweet Chilli And
264831 KETTLE 175.0
Cream 175g Sour Cream
264832 Tostitos Splash Of Lime 175g TOSTITOS 175.0 Tostitos Splash Of Lime
264833 Doritos Mexicana 170g DORITOS 170.0 Doritos Mexicana
Doritos Corn Chip Mexican Doritos Corn Chip Mexican
264834 DORITOS 150.0
Jalapeno 150g Jalapeno
264835 Tostitos Splash Of Lime 175g TOSTITOS 175.0 Tostitos Splash Of Lime
246741 rows × 4 columns
In [58]: all_words = " ".join(transaction_df['PROD_NAME']).split()
unique_words = [Link](all_words).value_counts()
unique_words
Out[58]: 175g 60560
Chips 49770
150g 41633
Kettle 41288
& 35565
...
Whlegrn 1432
Pc 1431
NCC 1419
Garden 1419
Fries 1418
Name: count, Length: 220, dtype: int64
In [71]: # -----------------------
# 3c. Summary for nulls & outliers
# -----------------------
print(transaction_df.describe())
[Link] 5/13
20/01/2026, 22:49 Analytics
DATE STORE_NBR LYLTY_CARD_NBR \
count 246741 246741.000000 2.467410e+05
mean 1899-12-30 00:00:00.000043264 135.051212 1.355311e+05
min 1899-12-30 00:00:00.000043282 1.000000 1.000000e+03
25% 1899-12-30 00:00:00.000043264 70.000000 7.001500e+04
50% 1899-12-30 00:00:00.000043520 130.000000 1.303670e+05
75% 1899-12-30 00:00:00.000043520 203.000000 2.030840e+05
max 1899-12-30 00:00:00.000043646 272.000000 2.373711e+06
std NaN 76.787231 8.071542e+04
TXN_ID PROD_NBR PROD_QTY TOT_SALES \
count 2.467410e+05 246741.000000 246741.000000 246741.000000
mean 1.351312e+05 56.351835 1.908061 7.321328
min 1.000000e+00 1.000000 1.000000 1.700000
25% 6.756900e+04 26.000000 2.000000 5.800000
50% 1.351840e+05 53.000000 2.000000 7.400000
75% 2.026540e+05 87.000000 2.000000 8.800000
max 2.415841e+06 114.000000 200.000000 650.000000
std 7.814786e+04 33.695488 0.659832 3.077833
PACK_SIZE
count 246741.000000
mean 175.585180
min 70.000000
25% 150.000000
50% 170.000000
75% 175.000000
max 380.000000
std 59.434847
In [81]: # Investigate the outlier (quantity = 200)
outliers = transaction_df[transaction_df['PROD_QTY'] == 200]
print(outliers)
Empty DataFrame
Columns: [DATE, STORE_NBR, LYLTY_CARD_NBR, TXN_ID, PROD_NBR, PROD_NAME, PROD_QTY,
TOT_SALES, PACK_SIZE, BRAND, FLAVOR]
Index: []
In [79]: # Box plot using Seaborn
# [Link](figsize=(4, 2))
# [Link](x=transaction_df['PROD_QTY'])
# [Link]('Box Plot of PROD_QTY to Detect Outliers')
# [Link]('Product Quantity')
# [Link]()
[Link] 6/13
20/01/2026, 22:49 Analytics
In [74]: # -----------------------
# 3d. Remove suspicious customer
# -----------------------
customer_id_to_remove = outliers['LYLTY_CARD_NBR'].iloc[0]
transaction_df = transaction_df[transaction_df['LYLTY_CARD_NBR'] != customer_id_
In [80]: # -----------------------
# 3f. Transactions over time
# -----------------------
transactions_by_date = transaction_df.groupby('DATE').size().reset_index(name="c
# create full date range
full_dates = [Link]({"DATE": pd.date_range("2018-07-01", "2019-06-30")})
# left merge to identify missing days
transactions_by_date = full_dates.merge(transactions_by_date, on="DATE", how="le
# Plot time series
[Link](figsize=(12,5))
[Link](transactions_by_date['DATE'], transactions_by_date['count'])
[Link]("Transactions Over Time")
[Link]("Date")
[Link]("Count")
[Link](rotation=90)
[Link]()
In [84]: # -----------------------
# 3g. Extract pack size
# -----------------------
transaction_df['PACK_SIZE'] = transaction_df['PROD_NAME'].[Link](r'(\d+)').
# Histogram of pack sizes
[Link](transaction_df['PACK_SIZE'], bins=20)
[Link]("Distribution of Pack Sizes")
[Link]("Pack Size (g)")
[Link]("Frequency")
[Link]()
[Link] 7/13
20/01/2026, 22:49 Analytics
C:\Users\freel\AppData\Local\Temp\ipykernel_208\[Link]: SettingWithCopyW
arning:
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead
See the caveats in the documentation: [Link]
e/user_guide/[Link]#returning-a-view-versus-a-copy
transaction_df['PACK_SIZE'] = transaction_df['PROD_NAME'].[Link](r'(\d
+)').astype(int)
This shows that which type of pack sizes are common among customers
In [91]: #transaction_df['BRAND'].unique()
In [90]: # -----------------------
# 3h. Extract brand name
# -----------------------
# Take the first word of PROD_NAME as brand
transaction_df['BRAND'] = transaction_df['PROD_NAME'].apply(lambda x: [Link]()[
# -----------------------
# Brand cleanup
# -----------------------
brand_corrections = {
"RED": "RRD",
"NATURAL": "NATURA",
"SMITH": "SMITHS",
"DORITO": "DORITOS",
"INFZNS": "INFUZIONS",
"BURGER": "BURGER RINGS", # example, adjust if needed
"SNBTS": "SUNBITES",
"CCS": "CC'S",
"WW": "WOOLWORTHS"
[Link] 8/13
20/01/2026, 22:49 Analytics
# add more corrections as you notice inconsistencies
}
transaction_df['BRAND'] = transaction_df['BRAND'].replace(brand_corrections)
# -----------------------
# Count transactions per brand
# -----------------------
brand_counts = transaction_df['BRAND'].value_counts().reset_index()
brand_counts.columns = ['BRAND', 'Transactions']
# -----------------------
# Visualization: Top 15 brands
# -----------------------
[Link](figsize=(12,6))
[Link](data=brand_counts.head(15), x='BRAND', y='Transactions', palette='vi
[Link]("Top 15 Brands by Number of Transactions")
[Link]("Brand")
[Link]("Number of Transactions")
[Link](rotation=45)
[Link]()
C:\Users\freel\AppData\Local\Temp\ipykernel_208\[Link]: SettingWithCopyW
arning:
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead
See the caveats in the documentation: [Link]
e/user_guide/[Link]#returning-a-view-versus-a-copy
transaction_df['BRAND'] = transaction_df['PROD_NAME'].apply(lambda x: [Link]()
[0].upper())
C:\Users\freel\AppData\Local\Temp\ipykernel_208\[Link]: SettingWithCopy
Warning:
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead
See the caveats in the documentation: [Link]
e/user_guide/[Link]#returning-a-view-versus-a-copy
transaction_df['BRAND'] = transaction_df['BRAND'].replace(brand_corrections)
C:\Users\freel\AppData\Local\Temp\ipykernel_208\[Link]: FutureWarning:
Passing `palette` without assigning `hue` is deprecated and will be removed in v
0.14.0. Assign the `x` variable to `hue` and set `legend=False` for the same effe
ct.
[Link](data=brand_counts.head(15), x='BRAND', y='Transactions', palette='v
iridis')
[Link] 9/13
20/01/2026, 22:49 Analytics
Plot top brands to understand customer preferences
In [92]: # -----------------------
# 4. Merge customer data for customer based transaction analysis
# -----------------------
merged_df = transaction_df.merge(customer_df, on="LYLTY_CARD_NBR", how="left")
# check for unmatched customers
print(merged_df.isnull().sum())
DATE 0
STORE_NBR 0
LYLTY_CARD_NBR 0
TXN_ID 0
PROD_NBR 0
PROD_NAME 0
PROD_QTY 0
TOT_SALES 0
PACK_SIZE 0
BRAND 0
FLAVOR 0
LIFESTAGE 0
PREMIUM_CUSTOMER 0
dtype: int64
In [93]: # -----------------------
# 5. Customer segment analysis
# -----------------------
# Total sales by lifestage & premium category
sales_summary = (
merged_df
.groupby(['LIFESTAGE', 'PREMIUM_CUSTOMER'])
.agg({'TOT_SALES': 'sum'})
.reset_index()
)
# Plot
[Link] 10/13
20/01/2026, 22:49 Analytics
[Link](figsize=(12,6))
[Link](data=sales_summary, x='LIFESTAGE', y='TOT_SALES', hue='PREMIUM_CUSTO
[Link](rotation=90)
[Link]("Total Sales by Customer Segment")
[Link]()
This shows which type of cutomers are premium members and identify their buying
patterns
In [94]: # -----------------------
# 6. Number of customers per segment
# -----------------------
customer_counts = (
merged_df.groupby(['LIFESTAGE', 'PREMIUM_CUSTOMER'])['LYLTY_CARD_NBR']
.nunique()
.reset_index(name='customers')
)
[Link](figsize=(12,6))
[Link](data=customer_counts, x='LIFESTAGE', y='customers', hue='PREMIUM_CUS
[Link](rotation=90)
[Link]("Number of Customers in Each Segment")
[Link]()
[Link] 11/13
20/01/2026, 22:49 Analytics
In [102… # -----------------------
# 7. Additional metrics
# -----------------------
# Chips bought per customer
chips_per_customer = (
merged_df.groupby(['LIFESTAGE', 'PREMIUM_CUSTOMER'])['PROD_QTY'].sum().reset
)
print("Chips bought per customer: ")
print(chips_per_customer)
Chips bought per customer:
LIFESTAGE PREMIUM_CUSTOMER PROD_QTY
0 MIDAGE SINGLES/COUPLES Budget 8883
1 MIDAGE SINGLES/COUPLES Mainstream 21213
2 MIDAGE SINGLES/COUPLES Premium 14400
3 NEW FAMILIES Budget 5241
4 NEW FAMILIES Mainstream 4060
5 NEW FAMILIES Premium 2769
6 OLDER FAMILIES Budget 41853
7 OLDER FAMILIES Mainstream 25804
8 OLDER FAMILIES Premium 20239
9 OLDER SINGLES/COUPLES Budget 32883
10 OLDER SINGLES/COUPLES Mainstream 32607
11 OLDER SINGLES/COUPLES Premium 31693
12 RETIREES Budget 26932
13 RETIREES Mainstream 37677
14 RETIREES Premium 23266
15 YOUNG FAMILIES Budget 34482
16 YOUNG FAMILIES Mainstream 23194
17 YOUNG FAMILIES Premium 20901
18 YOUNG SINGLES/COUPLES Budget 15500
19 YOUNG SINGLES/COUPLES Mainstream 36225
20 YOUNG SINGLES/COUPLES Premium 10575
[Link] 12/13
20/01/2026, 22:49 Analytics
In [103… # Average price per segment
average_price = (
merged_df.groupby(['LIFESTAGE', 'PREMIUM_CUSTOMER'])['TOT_SALES'].mean().res
)
print("Average price per segment: ")
print(average_price)
Average price per segment:
LIFESTAGE PREMIUM_CUSTOMER TOT_SALES
0 MIDAGE SINGLES/COUPLES Budget 7.108442
1 MIDAGE SINGLES/COUPLES Mainstream 7.637156
2 MIDAGE SINGLES/COUPLES Premium 7.152371
3 NEW FAMILIES Budget 7.297256
4 NEW FAMILIES Mainstream 7.313364
5 NEW FAMILIES Premium 7.231720
6 OLDER FAMILIES Budget 7.291241
7 OLDER FAMILIES Mainstream 7.281440
8 OLDER FAMILIES Premium 7.232779
9 OLDER SINGLES/COUPLES Budget 7.444305
10 OLDER SINGLES/COUPLES Mainstream 7.306049
11 OLDER SINGLES/COUPLES Premium 7.460085
12 RETIREES Budget 7.445786
13 RETIREES Mainstream 7.269352
14 RETIREES Premium 7.461315
15 YOUNG FAMILIES Budget 7.302705
16 YOUNG FAMILIES Mainstream 7.226772
17 YOUNG FAMILIES Premium 7.285951
18 YOUNG SINGLES/COUPLES Budget 6.663023
19 YOUNG SINGLES/COUPLES Mainstream 7.551279
20 YOUNG SINGLES/COUPLES Premium 6.673325
In [104… # -----------------------
# 8. Save cleaned data
# -----------------------
merged_df.to_csv(file_path + "QVI_data_cleaned_python.csv", index=False)
[Link] 13/13