Cafe Performance Insights

import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings("ignore", category=RuntimeWarning)

print("Setup Complete")

# Fill in the line below: Specify the path of the CSV file to read
my_filepath = "/kaggle/input/datasets/ahmedmohamed2003/cafe-sales-dirty-data-for-cleaning-training/dirty_cafe_sales.csv"

# Fill in the line below: Read the file into a variable my_data
my_data = pd.read_csv(my_filepath)

# Print the first five rows of the data
my_data.head()

# Visualize the unique values in my dataset.
for col in my_data.columns:
    print("\n")
    print("=====", col, "=====")
    print(my_data[col].value_counts())

# Replace it into NaN:
my_data = my_data.replace(["UNKNOWN", "ERROR"], np.nan)

my_data.dtypes

# Convert numeric columns to floats so you can do math on them
my_data[["Quantity", "Price Per Unit", "Total Spent"]] = my_data[["Quantity", "Price Per Unit", "Total Spent"]].astype(float)

# Convert the text date into an actual Date object
my_data["Transaction Date"] = pd.to_datetime(my_data["Transaction Date"])

#Checks for Error, since we have replaced it into NaN (Null)
my_data.isnull().sum()

## Fill missing Quantity, Rule : Quantity = (Total Spent * Price Per Unit)

# It's like SQL : 
# WHERE QUANTITY IS NULL
# AND PRICE PER UNIT IS NOT NULL
# AND TOTAL SPENT IS NOT NULL

quantity_filldata = (
    my_data["Quantity"].isnull()
    &
    my_data[["Price Per Unit", "Total Spent"]].notna().all(axis=1)
)

my_data[quantity_filldata]

# Locate and Replace Empty Quantity by multiply toal spent and price per unit
my_data.loc[quantity_filldata, "Quantity"] = (
    my_data.loc[quantity_filldata, "Total Spent"]
    / 
    my_data.loc[quantity_filldata, "Price Per Unit"]
)

my_data.isnull().sum()

## Fill missing Price per Unit ## 

price_filldata = (
    my_data["Price Per Unit"].isnull()
    &
    my_data[["Quantity","Total Spent"]].notna().all(axis=1)
)

my_data.loc[price_filldata, "Price Per Unit"] = (
    my_data.loc[price_filldata, "Total Spent"]
    / 
    my_data.loc[price_filldata, "Quantity"]
)

my_data.isnull().sum()

## Fill missing Total Spent ## 

spent_filldata = (
    my_data["Total Spent"].isnull()
    &
    my_data[["Quantity","Price Per Unit"]].notna().all(axis=1)
)

# Total Spent = Quantity * Price Per Unit
my_data.loc[spent_filldata,"Total Spent"] = (
    my_data.loc[spent_filldata,"Quantity"]
    *
    my_data.loc[spent_filldata,"Price Per Unit"]
)

my_data.isnull().sum()

Total_Spent = my_data[my_data["Total Spent"].isnull()]
Total_Spent

# Drop Unfixed Data
my_data = my_data.dropna(subset=["Quantity","Total Spent", "Price Per Unit"]).copy()

# From 10000 to 9942, we didn't lose too much rows, but rather we fix a lot
my_data.shape

## Recover the Item Null value from Price Per Unit ##
# We can do it by mapping the Price back too the Item Name
# What we will do, is to create a dict of these values, and then we will affect them, and fix those missing values in Item colum

temp_my_data = my_data[["Item","Price Per Unit"]].drop_duplicates(subset=["Item"])
temp_my_data

# There's a NaN there, we will ignore it, we now can create the dict
# We can see that Cake and Juice have the same value, it's fine, we alter between them, same goes for Smoothie and Sandwich
items = {
    "Coffee":2,
    "Cake":3,
    'Cookie':1,
    "Salad":5,
    "Smoothie":4,
    "Sandwich":4,
    "Juice":3,
    "Tea":1.5
}

reverse_items = {
    price : item 
    for item, price in items.items()
}

print(reverse_items)

my_data["Item"] = my_data["Item"].fillna(
    my_data["Price Per Unit"].map(reverse_items)
)

my_data.isnull().sum()

## Fill Null Payment Method and Location into Unknown ##

my_data["Payment Method"] = my_data["Payment Method"].fillna("Unknown")
my_data["Location"] = my_data["Location"].fillna("Unknown")

# drop rows that does not have transaction date
my_data = my_data.dropna(subset=["Transaction Date"])

my_data.isnull().sum()

my_data.shape

# Save your DataFrame to a new file named 'cleaned_cafe_sales.csv'
my_data.to_csv('cleaned_cafe_sales.csv', index=False)