Scribd
Upload
16 views2 pages

Assignment-104 (SQL Data Extraction)

The document outlines a process for calculating RFM (Recency, Frequency, Monetary) scores using Python and pandas, including handling NaN values and converting scores to integers. It also includes a script for extracting data from an SQLite database and saving it as a CSV file. Finally, it demonstrates how to query the database and save the SQL extraction script locally.

Uploaded by

727823tuit023
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
16 views2 pages

Assignment-104 (SQL Data Extraction)

The document outlines a process for calculating RFM (Recency, Frequency, Monetary) scores using Python and pandas, including handling NaN values and converting scores to integers. It also includes a script for extracting data from an SQLite database and saving it as a CSV file. Finally, it demonstrates how to query the database and save the SQL extraction script locally.

Uploaded by

727823tuit023
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

11/22/25, 11:48 AM Assignment-104

In [35]: # ----- RFM SCORE CALCULATION (Fixed Version) -----

# Handle NaN values before type conversion


rfm['r_score'] = [Link](rfm['recency'].rank(method='first'), 4, labels=range(1,
rfm['f_score'] = [Link](rfm['frequency'].rank(method='first'), 4, labels=range(
rfm['m_score'] = [Link](rfm['monetary'].rank(method='first'), 4, labels=range(1

# Convert to numeric
rfm['r_score'] = rfm['r_score'].astype('float')
rfm['f_score'] = rfm['f_score'].astype('float')
rfm['m_score'] = rfm['m_score'].astype('float')

# Fill NaN with median values


rfm['r_score'] = rfm['r_score'].fillna(rfm['r_score'].median())
rfm['f_score'] = rfm['f_score'].fillna(rfm['f_score'].median())
rfm['m_score'] = rfm['m_score'].fillna(rfm['m_score'].median())

# Now safely convert to int


rfm['r_score'] = rfm['r_score'].astype(int)
rfm['f_score'] = rfm['f_score'].astype(int)
rfm['m_score'] = rfm['m_score'].astype(int)

# Final RFM Score


rfm['rfm_score'] = (

[Link] 16/20
11/22/25, 11:48 AM Assignment-104

rfm['r_score']*100 +
rfm['f_score']*10 +
rfm['m_score']
)

display([Link]())

save_csv(rfm, "rfm_scores.csv")

customer_id recency frequency monetary r_score f_score m_score rfm_score

0 1 NaN 4 4696.910 2 2 2 222

1 2 NaN 2 3224.580 2 1 1 211

2 3 NaN 2 1983.305 2 1 1 211

3 4 NaN 2 6154.380 2 1 2 212

4 5 NaN 3 9021.940 2 1 3 213

Saved: eda_outputs\rfm_scores.csv
Out[35]: WindowsPath('eda_outputs/rfm_scores.csv')

In [36]: # Cell 18 - create an extraction script file and example query


sql_script = f"""
# sql_extract.py - example script to extract from sqlite database
import sqlite3
import pandas as pd

conn = [Link](r"{sqlite_db_path}")
df = pd.read_sql_query('SELECT * FROM {table_to_load} LIMIT 1000;', conn)
df.to_csv('extracted_{table_to_load}.csv', index=False)
[Link]()
print('Saved extracted_{table_to_load}.csv')
"""
# save locally in OUT_DIR
with open(OUT_DIR / 'sql_extract.py', 'w', encoding='utf-8') as f:
[Link](sql_script)
print("Saved SQL extraction script:", OUT_DIR / 'sql_extract.py')

# Also run a SELECT/JOIN example here (if products and sales are available)
try:
conn = [Link](sqlite_db_path)
# example: join between sqlite products table and sales if product id exists
q = "SELECT * FROM sqlite_master WHERE type='table';"
display(pd.read_sql_query(q, conn))
[Link]()
except Exception as e:
print("Could not query sqlite file here:", e)

Saved SQL extraction script: eda_outputs\sql_extract.py


type name tbl_name rootpage sql

CREATE TABLE "products" (\n"product_id"


0 table products products 2
INTEGE...

1 table returns returns 3 CREATE TABLE "returns" (\n"return_id" INTEGER,...

[Link] 17/20

You might also like