Project

import pandas as pd

brands = pd.read_csv("brands.csv") 
finance = pd.read_csv("finance.csv")
info = pd.read_csv("info.csv")
reviews = pd.read_csv("reviews.csv")

# Start coding here...

data = pd.merge(brands, finance, on='product_id').merge(info, on='product_id').merge(reviews, on='product_id')

# remove missing values
data.dropna(inplace=True)
data.head()

# label the products according to their quartile
quartiles = data['listing_price'].quantile([0, .25, .5, .75, 1.])
labels = pd.cut(data['listing_price'], bins=quartiles, labels=['Budget', 'Average', 'Expensive', 'Elite'])
data['price_label'] = labels
data

# group by brand and price_label
adidas_vs_nike = data.groupby(['brand', 'price_label', ]).agg({'product_id':'count', 'revenue':'mean'})

# rename the columns to match the spec
adidas_vs_nike = adidas_vs_nike.rename(columns={'product_id': 'num_products', 'revenue': 'mean_revenue'})

# round to 2 digits
adidas_vs_nike = adidas_vs_nike.round(2)

# reset the index to show brand and price label
adidas_vs_nike = adidas_vs_nike.reset_index()

adidas_vs_nike

# calculate the length of each description in words
data['description_length'] = data['description'].str.split().str.len()

# create bins of 100 words
bins = range(0, data['description_length'].max()+100, 100)

# group by bins and calculate the average rating and number of reviews
desc_bins = data.groupby(pd.cut(data['description_length'], bins=bins)).agg({'rating': 'mean', 'reviews': 'count'})
desc_bins = desc_bins.rename(columns={'rating': 'mean_rating', 'reviews': 'num_reviews'}).round(2)

description_lengths = desc_bins.reset_index()
description_lengths

footwear = data[data['description'].str.contains('shoe|trainer|foot', case=False)]
# footwear
footwear_stats = footwear.agg({'revenue': 'median', 'product_id': 'count'})
footwear_stats = footwear_stats.rename({'revenue': 'median_footwear_revenue', 
                              'product_id': 'num_footwear_products'})

clothing = data[~data.index.isin(
    footwear.index)]
clothing_stats = clothing.agg({'revenue': 'median', 'product_id': 'count'})
clothing_stats = clothing_stats.rename({'revenue': 'median_clothing_revenues', 'product_id': 'num_clothing_products'})

product_types = pd.DataFrame([footwear_stats, clothing_stats])
product_types = product_types[['num_clothing_products', 'median_clothing_revenues', 'num_footwear_products', 'median_footwear_revenue']]
product_types

# Start coding here... 
import pandas as pd

# Read in the data
info = pd.read_csv("info.csv")
finance = pd.read_csv("finance.csv")
reviews = pd.read_csv("reviews.csv")
brands = pd.read_csv("brands.csv")

# Merge the data
merged_df = info.merge(finance, on="product_id", how="outer")
merged_df = merged_df.merge(reviews, on="product_id", how="outer")
merged_df = merged_df.merge(brands, on="product_id", how="outer")

# Drop null values
merged_df.dropna(inplace=True)

# Add price labels based on listing_price quartiles
merged_df["price_label"] = pd.qcut(merged_df["listing_price"], q=4, labels=["Budget", "Average", "Expensive", "Elite"])

# Group by brand and price_label to get volume and mean revenue
adidas_vs_nike = merged_df.groupby(["brand", "price_label"], as_index=False).agg(
    num_products=("price_label", "count"), 
    mean_revenue=("revenue", "mean")
).round(2).reset_index(drop=True)

# Find the largest description_length
max(merged_df["description"].str.len())

# Store the length of each description
merged_df["description_length"] = merged_df["description"].str.len()

# Upper description length limits
lengthes = [0, 100, 200, 300, 400, 500, 600, 700]

# Description length labels
labels = ["100", "200", "300", "400", "500", "600", "700"]

# Cut into bins
merged_df["description_length"] = pd.cut(merged_df["description_length"], bins=lengthes, labels=labels)

# Group by the bins
description_lengths = merged_df.groupby("description_length", as_index=False).agg(
    mean_rating=("rating", "mean"), 
    num_reviews=("reviews", "count")
).round(2)

# List of footwear keywords
mylist = "shoe*|trainer*|foot*"

# Filter for footwear products
shoes = merged_df[merged_df["description"].str.contains(mylist)]

# Filter for clothing products
clothing = merged_df[~merged_df.isin(shoes["product_id"])]

# Remove null product_id values from clothing DataFrame
clothing.dropna(inplace=True)

# Create product_types DataFrame
product_types = pd.DataFrame({"num_clothing_products": len(clothing), 
                              "median_clothing_revenue": clothing["revenue"].median(), 
                              "num_footwear_products": len(shoes), 
                              "median_footwear_revenue": shoes["revenue"].median()}, 
                              index=[0])