Project: Analyzing Online Sports Revenue

# Start coding here... 
import pandas as pd

df1 = pd.read_csv('info.csv')
df2 = pd.read_csv('finance.csv')
df3 = pd.read_csv('reviews.csv')
df4 = pd.read_csv('traffic.csv')
df5 = pd.read_csv('brands.csv')

print(df1.head(3))
print(df2.head(3))
print(df3.head(3))
print(df4.head(3))
print(df5.head(3))

df1.head(3)

df= df1.merge(df2, on= 'product_id')
df= df.merge(df3, on= 'product_id')
df= df.merge(df4, on= 'product_id')
df= df.merge(df5, on= 'product_id')

df.head(3)

df.shape

df.dropna(inplace=True)

df.head()

# Find the number of products and mean revenue for brand and price label, using quartiles of "listing_price" to partition each price label, storing as a pandas DataFrame called adidas_vs_nike. 
# It should contain four columns: "brand", "price_label", "num_products", and "mean_revenue", and numeric values should be rounded to two decimal places.

# Define the function to get the price label based on quartiles

df['price_label'] = pd.qcut(df['listing_price'], 4, labels=["Budget", "Average", "Expensive", "Elite"])

# Group by brand and price label, and aggregate the number of products and mean revenue
adidas_vs_nike = df.groupby(['brand', 'price_label']).agg({'product_id': 'count', 'revenue': 'mean'})

# Rename the columns
adidas_vs_nike.columns = ['num_products', 'mean_revenue']

# Round the numeric values to two decimal places
adidas_vs_nike = adidas_vs_nike.round(2)

# Reset the index to make brand and price_label as columns
adidas_vs_nike = adidas_vs_nike.reset_index()

# Print the resulting dataframe
adidas_vs_nike

# Split product description length into bins of 100 words and calculate the average rating and number of reviews. 
# Store the results as a pandas DataFrame called description_lengths containing "description_length", "mean_rating", and "num_reviews", 
# again rounding numeric values to two decimal places.

# Create a new column 'description_length' in the dataframe
df['length'] = df['description'].apply(lambda x: len(str(x)))
# Bin the Age column into 3 equal-sized bins
df['description_length'] = pd.cut(df['length'], bins=[0, 100, 200, 300, 400, 500, 600, 700] , labels = ["100", "200", "300", "400", "500", "600", "700"])


# added closing parenthesis
#df['description_length'] = df['description'].apply(lambda x:len(x.split()))
print(df.head(3))
print(max(df['length']))

description_lengths = df.groupby('description_length').aggregate({'rating':'mean', 'reviews':'count'}).round(2)
description_lengths = description_lengths.reset_index()[['description_length', 'rating', 'reviews']]
description_lengths.columns = ['description_length', 'mean_rating', 'num_reviews']
description_lengths

# Search "description" for "shoe*", "trainer*", or "foot*" and use the results to calculate the number of footwear products versus clothing products sold by the company 
# and the median revenue for each of the two product types. Store the results in a pandas DataFrame called product_types containing "num_clothing_products", 
# "median_clothing_revenue", "num_footwear_products", "median_footwear_revenue"

df['IsFoot'] = df.description.str.contains("shoe*|trainer*|foot*", na=False)

df['description'].dropna(inplace=True)

# Calculate the number of clothing products and median revenue
num_clothing_products = df[df['IsFoot'] == False]['product_id'].count()
median_clothing_revenue = df[df['IsFoot'] == False]['revenue'].median()

# Calculate the number of footwear products and median revenue
num_footwear_products = df[df['IsFoot'] == True]['product_id'].count()
median_footwear_revenue = df[df['IsFoot'] == True]['revenue'].median()

# Store the results in a pandas DataFrame called product_types
product_types = pd.DataFrame({
    'num_clothing_products': [num_clothing_products],
    'median_clothing_revenue': [median_clothing_revenue],
    'num_footwear_products': [num_footwear_products],
    'median_footwear_revenue': [median_footwear_revenue]
})
product_types