Orders_Analysis.py

# pip install kaggle
import kaggle
import os
import zipfile
from dotenv import load_dotenv
from kaggle.api.kaggle_api_extended import KaggleApi
import pandas as pd
import sqlalchemy as sql

# Load the API KEY
env_file_path = 'requirements.env'
load_dotenv(dotenv_path=env_file_path)
api_key = os.getenv("KAGGLE_API_TOKEN")

# Or Set the Env Varibale in CLI

# Download the Kaggle Dataset
# 1. Authenticate API
api = KaggleApi()
api.authenticate()

# 2. Define paths
dataset = 'ankitbansal06/retail-orders'
download_path = './'
zip_file_path = os.path.join(download_path, 'retail-orders.zip')

try:
    # Downloads the dataset zip file into the current directory
    api.dataset_download_files(dataset, path=download_path)
    print("Download complete!")
except Exception as e:
    print(f"An error occurred: {e}")


# Unzip the file
zip_file_path = './retail-orders.zip'
download_path = './'
try:
    print("Extracting files...")
    with zipfile.ZipFile(zip_file_path, 'r') as zip_ref:
        zip_ref.extractall(download_path)
    print("Extraction complete!")
except Exception as e:
    print(f"An error occurred: {e}")

# Transform the CSV data
# Change Unknown data to NaN
df = pd.read_csv('orders.csv', na_values = ['Not Available', 'unknown'])
print(df)
print(df['Ship Mode'].unique())

# Rename Columns
print(df.columns)
df.columns = df.columns.str.lower()
df.columns = df.columns.str.replace(' ', '_')
print(df.columns)

# Find the discount, sale_price and profit
df['discount']=df['list_price']*df['discount_percent']*.01
df['sale_price']= df['list_price']-df['discount']
df['profit']=df['sale_price']-df['cost_price']
print(df)

# Drop unnessasary columns
df.drop(columns=['list_price','cost_price','discount_percent'],inplace=True)
print(df)

# Convert order_date to dateTime type
print(df.dtypes)
df['order_date'] = pd.to_datetime(df['order_date'], format='%Y-%m-%d')
print(df)
print(df.dtypes)

# Loading the resulting data into SQL
engine = sql.create_engine('mssql://DESKTOP-139CR3F/master?driver=ODBC+DRIVER+17+FOR+SQL+SERVER')
conn=engine.connect()
df.to_sql('df_orders', con = conn, index = False, if_exists = 'replace')