Vending Machines

We will use the following Python libraries:

• Status : Represents if the machine data is successfully processed
• Device ID : Unique electronic identifier ( also called as ePort) for the vending machine. A machine is allocated a unique ePrt * device
• Location : Indicates location of the vending machine
• Machine : User-friendly machine name
• Product : Product vended from the machine
• Category : Carbonated / Food / Non-carbonated / Water
• Transaction : Unique identifier for every transaction
• TransDate : The Date & time of transaction
• Type : Type of transaction ( Cash / Credit )
• RCoil : Coil # used to vend the product
• RPrice : Price of the Product
• RQty : Quantity sold. This is usually one but machines can be configured to sell more items in a single transaction
• MCoil : Mapped coil # used to vend the product ( from toucan )
• MPrice : Mapped price of the Product
• MQty : Mapped quantity sold. This is usually one but machines can be configured to sell more items in a single transaction
• LineTotal : Total sale per transaction
• TransTotal : Represents total of all transactions that will show up on the Credit Card. A user could vend a drink for $3 and a snack for $1.5 making a total of $4.50
• Prcd Date : Date when the transaction was processed by SeedLive ( an entity that is used to aggregate all transactions electronically )

import pandas as pd

# Load the dataset
file_path = r'C:\Users\Francesco\Desktop\PROJECTS\Data Project\Vending Machines\vending_machine_sales.csv'
data = pd.read_csv(file_path)

# Display the first rows
data.head()

# Step 1: Check for missing values
missing_values = data.isnull().sum()

# Step 2: Check for duplicate entries
duplicates = data.duplicated().sum()

# Step 3 & 4: Checking data types 
data_types = data.dtypes

# Checking for consistency in 'Category' and 'Type' columns (Step 3)
category_uniques = data['Category'].unique()
type_uniques = data['Type'].unique()

# Initial exploration for outliers in numeric columns (Step 5)
describe = data.describe()

missing_values, duplicates, data_types, category_uniques, type_uniques, describe

# Deleting rows where 'Product' and 'MPrice' have missing values
cleaned_data = data.dropna(subset=['Product', 'MPrice'])

# Filtering products with missing 'Category' values and listing unique ones
missing_category_products = cleaned_data[cleaned_data['Category'].isnull()]['Product'].unique()

missing_category_products.sort()
missing_category_products

After the result seems acceptable to manually map the categories for the products.

# Assigning categories manually
category_mapping = {
    'Canada Dry - Ginger Ale': 'Carbonated',
    'Canada Dry - Ginger Ale & Lemonde': 'Carbonated',
    'Doritos Dinamita Chile Lemon': 'Food',
    'Doritos Spicy Nacho': 'Food',
    'Mini Chips Ahoy - Go Paks': 'Food',
    'Oreo Mini - Go Paks': 'Food',
    'Starbucks Doubleshot Energy - Coffee': 'Non Carbonated',
    'Teddy Grahams - Go Paks': 'Food'
}

# Applying category 
for product, category in category_mapping.items():
    cleaned_data.loc[cleaned_data['Product'] == product, 'Category'] = category

# Checking if there are still missing categories
remaining_missing_categories = cleaned_data['Category'].isnull().sum()

remaining_missing_categories

import matplotlib.pyplot as plt
import seaborn as sns

# Setting up visualization styles
sns.set(style="whitegrid")

# 1. Sales Distribution by Category
sales_by_category = cleaned_data.groupby('Category')['LineTotal'].sum().sort_values(ascending=False)

# 2. Transaction Count by Location
transaction_count_by_location = cleaned_data['Location'].value_counts()

# Plotting Sales Distribution by Category
plt.figure(figsize=(10, 6))
sales_by_category.plot(kind='bar', color='skyblue')
plt.title('Sales Distribution by Category')
plt.xlabel('Category')
plt.ylabel('Total Sales ($)')
plt.xticks(rotation=45)
plt.show()

# Plotting Transaction Count by Location
plt.figure(figsize=(10, 6))
transaction_count_by_location.plot(kind='bar', color='lightgreen')
plt.title('Transaction Count by Location')
plt.xlabel('Location')
plt.ylabel('Number of Transactions')
plt.xticks(rotation=45)
plt.show()

# Adding a new column for the food-drinks category split
cleaned_data['Big Category'] = cleaned_data['Category'].apply(lambda x: 'Food' if x == 'Food' else 'Drinks')

# Calculating sales distribution for the new broad categories
sales_distribution_broad = cleaned_data.groupby('Big Category')['LineTotal'].sum()

# Plotting the sales distribution for Food vs Drinks
plt.figure(figsize=(8, 6))
sales_distribution_broad.plot(kind='pie', autopct='%1.1f%%', startangle=140, colors=['tomato', 'royalblue'])
plt.title('Sales Distribution: Food vs Drinks')
plt.ylabel('')  # Removing the y-label as it's not needed for pie chart
plt.show()

# Average Transaction Total by Location
average_transaction_by_location = cleaned_data.groupby('Location')['TransTotal'].mean().sort_values(ascending=False)
# Plotting Average Transaction Total by Location
plt.figure(figsize=(10, 6))
average_transaction_by_location.plot(kind='bar', color='blue')
plt.title('Average Transaction Total by Location')
plt.xlabel('Location')
plt.ylabel('Average Transaction Total ($)')
plt.xticks(rotation=45)
plt.show()

As is clear from the statistical data generated before, the average transaction of the data is around $2. This line graph shows the distribution, that is expected, considering the mean.

# Converting 'TransDate' to datetime format for time series analysis
cleaned_data['TransDate'] = pd.to_datetime(cleaned_data['TransDate'])

# 4. Sales Over Time (Monthly Sales)
monthly_sales = cleaned_data.set_index('TransDate').resample('M')['LineTotal'].sum()

# Plotting Sales Over Time (Monthly Sales)
plt.figure(figsize=(12, 6))
monthly_sales.plot(marker='o', linestyle='-', color='orange')
plt.title('Sales Over Time (Monthly)')
plt.xlabel('Month')
plt.ylabel('Total Sales ($)')
plt.xticks(rotation=45)
plt.show()

# 5. Top Selling Products
top_selling_products = cleaned_data.groupby('Product')['LineTotal'].sum().sort_values(ascending=False).head(10)

# Plotting Top Selling Products
plt.figure(figsize=(10, 6))
top_selling_products.plot(kind='barh', color='green')
plt.title('Top Selling Products')
plt.xlabel('Total Sales ($)')
plt.ylabel('Product')
plt.show()

cleaned_data.to_csv("vending_machine_clean2.csv", index=False)

LineTotal divided by Count of Day = 
DIVIDE(
	SUM('vending_machine_clean2'[LineTotal]),
	COUNTA('vending_machine_clean2'[TransDate].[Day])
)

---