import pandas as pd
import plotly.express as px
import plotly.graph_objs as go
import plotly.io as pio

pio.templates.default = 'plotly_white'
data = pd.read_excel("product1.xlsx")
data.head()

con = pd.DataFrame(data.groupby(['Item Type','Sales Channel','Order_Year'], as_index=False)
                   [['Total Revenue','Total Cost','Total Profit','Units Sold']].sum())
print(con)

baby =con[con['Sales Channel']=="Offline"]
offline =baby[baby['Item Type']=="Baby Food"]
print(offline)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=offline['Order_Year'],
    y=offline['Total Revenue'],
    name = 'Total revenue - Baby Food',
    marker_color = 'red'
))

fig.add_trace(go.Bar(
    x=offline['Order_Year'],
    y=offline['Total Cost'],
    name = 'Total Cost - Baby Food',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=offline['Order_Year'],
    y=offline['Total Profit'],
    name = 'Total Profit - Baby Food',
    marker_color ='green'
))

fig.update_layout(title_text= 'Baby product Revenue, Total cost and totalprofit offline')
fig.show()

food =con[con['Sales Channel']=="Online"]
online =food[food['Item Type']=="Baby Food"]
online

fig = go.Figure()
fig.add_trace(go.Bar(
    x=online['Order_Year'],
    y=online['Total Revenue'],
    name = 'Total revenue - Baby Food',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=online['Order_Year'],
    y=online['Total Cost'],
    name = 'Total Cost - Baby Food',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=online['Order_Year'],
    y=online['Total Profit'],
    name = 'Total Profit - Baby Food',
    marker_color ='green'
))

fig.update_layout(title_text= 'Baby product Revenue, Total cost and totalprofit by online')
fig.show()

cl =con[con['Sales Channel']=="Online"]
on =cl[cl['Item Type']=="Cereal"]
print(on)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=on['Order_Year'],
    y=on['Total Revenue'],
    name = 'Total revenue - Cereal',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=on['Order_Year'],
    y=on['Total Cost'],
    name = 'Total Cost - Cereal',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=on['Order_Year'],
    y=on['Total Profit'],
    name = 'Total Profit - Cereal',
    marker_color ='green'
))

fig.update_layout(title_text= 'Cereal Revenue, Total cost and totalprofit by online')
fig.show()

c2 =con[con['Sales Channel']=="Offline"]
of =c2[c2['Item Type']=="Cereal"]
print(of)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=of['Order_Year'],
    y=of['Total Revenue'],
    name = 'Total revenue - Cereal',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=of['Order_Year'],
    y=of['Total Cost'],
    name = 'Total Cost - Cereal',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=on['Order_Year'],
    y=on['Total Profit'],
    name = 'Total Profit - Cereal',
    marker_color ='green'
))

fig.update_layout(title_text= 'Cereal Revenue, Total cost and totalprofit by Offline')
fig.show()

c3 =con[con['Sales Channel']=="Offline"]
cs =c3[c3['Item Type']=="Cosmetics"]
print(cs)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=cs['Order_Year'],
    y=cs['Total Revenue'],
    name = 'Total revenue - Cosmetics',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=cs['Order_Year'],
    y=cs['Total Cost'],
    name = 'Total Cost - Cosmetics',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=cs['Order_Year'],
    y=cs['Total Profit'],
    name = 'Total Profit - Cosmetics',
    marker_color ='green'
))

fig.update_layout(title_text= 'Cosmetics Revenue, Total cost and totalprofit by Offline')
fig.show()

c4 =con[con['Sales Channel']=="Online"]
h1 =c4[c4['Item Type']=="Cosmetics"]
print(h1)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=h1['Order_Year'],
    y=h1['Total Revenue'],
    name = 'Total revenue - Cosmetics',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=h1['Order_Year'],
    y=h1['Total Cost'],
    name = 'Total Cost - Cosmetics',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=h1['Order_Year'],
    y=h1['Total Profit'],
    name = 'Total Profit - Cosmetics',
    marker_color ='green'
))

fig.update_layout(title_text= 'Cosmetics Revenue, Total cost and totalprofit by Online')
fig.show()

c5 =con[con['Sales Channel']=="Offline"]
h3 =c5[c5['Item Type']=="Beverages"]
print(h3)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=h3['Order_Year'],
    y=h3['Total Revenue'],
    name = 'Total revenue - Beverages',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=h3['Order_Year'],
    y=h3['Total Cost'],
    name = 'Total Cost - Beverages',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=h3['Order_Year'],
    y=h3['Total Profit'],
    name = 'Total Profit - Beverages',
    marker_color ='green'
))

fig.update_layout(title_text= 'Beverages Revenue, Total cost and totalprofit by Offline')
fig.show()

c6 =con[con['Sales Channel']=="Offline"]
h4 =c6[c6['Item Type']=="Beverages"]
print(h4)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=h4['Order_Year'],
    y=h4['Total Revenue'],
    name = 'Total revenue - Beverages',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=h4['Order_Year'],
    y=h4['Total Cost'],
    name = 'Total Cost - Beverages',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=h4['Order_Year'],
    y=h4['Total Profit'],
    name = 'Total Profit - Beverages',
    marker_color ='green'
))

fig.update_layout(title_text= 'Beverages Revenue, Total cost and totalprofit by Online')
fig.show()

c7 =con[con['Sales Channel']=="Offline"]
h5 =c7[c7['Item Type']=="Fruits"]
print(h5)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=h5['Order_Year'],
    y=h5['Total Revenue'],
    name = 'Total revenue - Fruits',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=h5['Order_Year'],
    y=h5['Total Cost'],
    name = 'Total Cost - Fruits',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=h5['Order_Year'],
    y=h5['Total Profit'],
    name = 'Total Profit - Fruits',
    marker_color ='green'
))

fig.update_layout(title_text= 'Fruits Revenue, Total cost and totalprofit by Offline')
fig.show()

c8 =con[con['Sales Channel']=="Online"]
h6 =c8[c8['Item Type']=="Fruits"]
print(h6)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=h6['Order_Year'],
    y=h6['Total Revenue'],
    name = 'Total revenue - Fruits',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=h6['Order_Year'],
    y=h6['Total Cost'],
    name = 'Total Cost - Fruits',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=h6['Order_Year'],
    y=h6['Total Profit'],
    name = 'Total Profit - Fruits',
    marker_color ='green'
))

fig.update_layout(title_text= 'Friuts Revenue, Total cost and totalprofit by Online')
fig.show()

c9 =con[con['Sales Channel']=="Online"]
h7 =c9[c9['Item Type']=="Clothes"]
print(h7)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=h7['Order_Year'],
    y=h7['Total Revenue'],
    name = 'Total revenue - Clothes',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=h7['Order_Year'],
    y=h7['Total Cost'],
    name = 'Total Cost - Clothes',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=h7['Order_Year'],
    y=h7['Total Profit'],
    name = 'Total Profit - Clothes',
    marker_color ='green'
))

fig.update_layout(title_text= 'Clothes Revenue, Total cost and totalprofit by Online')
fig.show()

d1 =con[con['Sales Channel']=="Offline"]
h8 =d1[d1['Item Type']=="Clothes"]
print(h8)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=h8['Order_Year'],
    y=h8['Total Revenue'],
    name = 'Total revenue - Clothes',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=h8['Order_Year'],
    y=h8['Total Cost'],
    name = 'Total Cost - Clothes',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=h8['Order_Year'],
    y=h8['Total Profit'],
    name = 'Total Profit - Clothes',
    marker_color ='green'
))

fig.update_layout(title_text= 'Clothes Revenue, Total cost and totalprofit by Offline')
fig.show()

d2 =con[con['Sales Channel']=="Offline"]
h9 =d2[d2['Item Type']=="Household"]
print(h9)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=h9['Order_Year'],
    y=h9['Total Revenue'],
    name = 'Total revenue - Houdehold',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=h9['Order_Year'],
    y=h9['Total Cost'],
    name = 'Total Cost - Household',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=h9['Order_Year'],
    y=h9['Total Profit'],
    name = 'Total Profit - Household',
    marker_color ='green'
))

fig.update_layout(title_text= 'Household Revenue, Total cost and totalprofit by Offline')
fig.show()

d3 =con[con['Sales Channel']=="Online"]
i1 =d3[d3['Item Type']=="Household"]
print(i1)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=i1['Order_Year'],
    y=i1['Total Revenue'],
    name = 'Total revenue - Household',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=i1['Order_Year'],
    y=i1['Total Cost'],
    name = 'Total Cost - Household',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=i1['Order_Year'],
    y=i1['Total Profit'],
    name = 'Total Profit - Household',
    marker_color ='green'
))

fig.update_layout(title_text= 'Household Revenue, Total cost and totalprofit by Online')
fig.show()

d4 =con[con['Sales Channel']=="Online"]
i2 =d4[d4['Item Type']=="Meat"]
print(i2)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=i2['Order_Year'],
    y=i2['Total Revenue'],
    name = 'Total revenue - Meat',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=i2['Order_Year'],
    y=i2['Total Cost'],
    name = 'Total Cost - Meat',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=i2['Order_Year'],
    y=i2['Total Profit'],
    name = 'Total Profit - Meat',
    marker_color ='green'
))

fig.update_layout(title_text= 'Meat Revenue, Total cost and totalprofit by Online')
fig.show()

d5 =con[con['Sales Channel']=="Offline"]
i3 =d5[d5['Item Type']=="Meat"]
print(i3)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=i3['Order_Year'],
    y=i3['Total Revenue'],
    name = 'Total revenue - Meat',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=i3['Order_Year'],
    y=i3['Total Cost'],
    name = 'Total Cost - Meat',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=i3['Order_Year'],
    y=i3['Total Profit'],
    name = 'Total Profit - Meat',
    marker_color ='green'
))

fig.update_layout(title_text= 'Meat Revenue, Total cost and totalprofit by Offline')
fig.show()

d6 =con[con['Sales Channel']=="Online"]
i4 =d6[d6['Item Type']=="Office Supplies"]
print(i4)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=i4['Order_Year'],
    y=i4['Total Revenue'],
    name = 'Total revenue - Office supplies',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=i4['Order_Year'],
    y=i4['Total Cost'],
    name = 'Total Cost - Office supplies',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=i4['Order_Year'],
    y=i4['Total Profit'],
    name = 'Total Profit - OFfice supplies',
    marker_color ='green'
))

fig.update_layout(title_text= 'Office supplies Revenue, Total cost and totalprofit by Online')
fig.show()

d7 =con[con['Sales Channel']=="Offline"]
i5 =d7[d7['Item Type']=="Office Supplies"]
print(i5)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=i5['Order_Year'],
    y=i5['Total Revenue'],
    name = 'Total revenue - Office supplies',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=i5['Order_Year'],
    y=i5['Total Cost'],
    name = 'Total Cost - Office supplies',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=i5['Order_Year'],
    y=i5['Total Profit'],
    name = 'Total Profit - Office Supplies',
    marker_color ='green'
))

fig.update_layout(title_text= 'Office supplies Revenue, Total cost and totalprofit by Offline')
fig.show()

d8 =con[con['Sales Channel']=="Online"]
i6 =d8[d8['Item Type']=="Personal Care"]
print(i6)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=i6['Order_Year'],
    y=i6['Total Revenue'],
    name = 'Total revenue - Personal Care',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=i6['Order_Year'],
    y=i6['Total Cost'],
    name = 'Total Cost - Personal Care',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=i6['Order_Year'],
    y=i6['Total Profit'],
    name = 'Total Profit - Personal Care',
    marker_color ='green'
))

fig.update_layout(title_text= 'Personal Care Revenue, Total cost and totalprofit by Online')
fig.show()

d9 =con[con['Sales Channel']=="Offline"]
i7 =d9[d9['Item Type']=="Personal Care"]
print(i7)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=i7['Order_Year'],
    y=i7['Total Revenue'],
    name = 'Total revenue - Personal Care',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=i7['Order_Year'],
    y=i7['Total Cost'],
    name = 'Total Cost - Personal Care',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=i7['Order_Year'],
    y=i7['Total Profit'],
    name = 'Total Profit - Personal Care',
    marker_color ='green'
))

fig.update_layout(title_text= 'Personal Care Revenue, Total cost and totalprofit by Offline')
fig.show()

e1 =con[con['Sales Channel']=="Online"]
i8 =e1[e1['Item Type']=="Snacks"]
print(i8)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=i8['Order_Year'],
    y=i8['Total Revenue'],
    name = 'Total revenue - Snacks',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=i8['Order_Year'],
    y=i8['Total Cost'],
    name = 'Total Cost - Snacks',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=i8['Order_Year'],
    y=i8['Total Profit'],
    name = 'Total Profit - Snacks',
    marker_color ='green'
))

fig.update_layout(title_text= 'Snacks Revenue, Total cost and totalprofit by Online')
fig.show()

e2 =con[con['Sales Channel']=="Offline"]
i9 =e2[e2['Item Type']=="Snacks"]
print(i9)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=i9['Order_Year'],
    y=i9['Total Revenue'],
    name = 'Total revenue - Snacks',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=i9['Order_Year'],
    y=i9['Total Cost'],
    name = 'Total Cost - Snacks',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=i9['Order_Year'],
    y=i9['Total Profit'],
    name = 'Total Profit - Snacks',
    marker_color ='green'
))

fig.update_layout(title_text= 'Snacks Revenue, Total cost and totalprofit by Offline')
fig.show()

e3=con[con['Sales Channel']=="Online"]
j1 =e3[e3['Item Type']=="Vegetables"]
print(j1)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=j1['Order_Year'],
    y=j1['Total Revenue'],
    name = 'Total revenue - Vegtables',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=j1['Order_Year'],
    y=j1['Total Cost'],
    name = 'Total Cost - Vegtables',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=j1['Order_Year'],
    y=j1['Total Profit'],
    name = 'Total Profit - Vegetables',
    marker_color ='green'
))

fig.update_layout(title_text= 'Vegetables Revenue, Total cost and totalprofit by Online')
fig.show()

e4 =con[con['Sales Channel']=="Offline"]
j2 =e4[e4['Item Type']=="Vegetables"]
print(j2)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=j2['Order_Year'],
    y=j2['Total Revenue'],
    name = 'Total revenue - Vegetables',
    marker_color ='red'
))

fig.add_trace(go.Bar(
    x=j2['Order_Year'],
    y=j2['Total Cost'],
    name = 'Total Cost - Vegetables',
    marker_color ='blue'
))

fig.add_trace(go.Bar(
    x=j2['Order_Year'],
    y=j2['Total Profit'],
    name = 'Total Profit - Vegtables',
    marker_color ='green'
))

fig.update_layout(title_text= 'Vegetables Revenue, Total cost and totalprofit by Offline')
fig.show()