import pandas as pd
import numpy as np
import plotly.express as px

pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)

df = pd.read_csv("coffee.csv")

#convert euros to float
df['250gr'] = df['250gr'].replace('[^\d.]', '', regex=True).astype(float)
df['1kg'] = df['1kg'].replace('[^\d.]', '', regex=True).astype(float)
df['cpc_250g'] = df['cpc_250g'].replace('[^\d.]', '', regex=True).astype(float)
df['cpc_1kg'] = df['cpc_1kg'].replace('[^\d.]', '', regex=True).astype(float)

df

df_singleorigin = df[df['type'] == 'so']
fig = px.box(df_singleorigin, x='country', y='cpc_1kg', title='Distribution of Coffee Prices by Country (single origin only)')
fig.update_layout(xaxis_title='country', yaxis_title='cpc_1kg', xaxis_tickangle=-45)
fig.show()

shop_avg = df.groupby('shop')[['cpc_250g','cpc_1kg']].mean().round(2)
shop_avg = shop_avg.sort_values(by='cpc_1kg')
shop_avg = shop_avg.reset_index()
#shop_avg.dtypes

fig = px.bar(shop_avg, x='shop', y='cpc_1kg', title='Average cost per cup (when buying 1kg bags)', text_auto=True)
fig.show()

types = df.groupby('type')[['cpc_250g','cpc_1kg']].mean()
types = types.sort_values(by='cpc_250g')
types = types.reset_index()
types

df_type = df
fig = px.box(df_type, x='type', y='cpc_1kg', title='Single origin vs blend')
fig.update_layout(xaxis_title='type', yaxis_title='cpc_1kg')
fig.show()

mean_price_per_type = types.groupby('type')['cpc_250g'].mean()
blend_price = mean_price_per_type['blend']
so_price = mean_price_per_type['so']
percentage_difference = ((so_price - blend_price) / blend_price) * 100
print("Single origin is {:.2f}% more expensive than Blend, on average.".format(percentage_difference))

process = df.groupby('process').agg({'process': 'count', 'cpc_250g': 'mean', 'cpc_1kg': 'mean'})
process.rename(columns={'process': 'count'}, inplace=True)
process = process.sort_values(by='count', ascending=False)
process = process.reset_index()
process

df_process = df[df['type'] == 'so']
fig = px.box(df_process, x='process', y='cpc_1kg', title='Distribution of Coffee Prices by process (single origin only)')
fig.update_layout(xaxis_title='process', yaxis_title='cpc_1kg', xaxis_tickangle=-45)
fig.show()