def get_data_from_excel():
    df = pd.read_excel(
        io="supermarkt_sales.xlsx",
        engine="openpyxl",
        sheet_name="Sales",
        skiprows=3,
        usecols="B:R",
        nrows=1000,
    )
    # 添加小時列數(shù)據(jù)
    df["小時"] = pd.to_datetime(df["時間"], format="%H:%M:%S").dt.hour
    return df

df = get_data_from_excel()
print(df)

# 設(shè)置網(wǎng)頁信息 
st.set_page_config(page_title="銷售數(shù)據(jù)大屏", page_icon=":bar_chart:", layout="wide")

# 側(cè)邊欄
st.sidebar.header("請在這里篩選:")
city = st.sidebar.multiselect(
    "選擇城市:",
    options=df["城市"].unique(),
    default=df["城市"].unique()
)

customer_type = st.sidebar.multiselect(
    "選擇顧客類型:",
    options=df["顧客類型"].unique(),
    default=df["顧客類型"].unique(),
)

gender = st.sidebar.multiselect(
    "選擇性別:",
    options=df["性別"].unique(),
    default=df["性別"].unique()
)

df_selection = df.query(
    "城市 == @city & 顧客類型 ==@customer_type & 性別 == @gender"
)

# 主頁面
st.title(":bar_chart: 銷售數(shù)據(jù)大屏")
st.markdown("##")

# 核心指標, 銷售總額、平均評分、星級、平均銷售額數(shù)據(jù)
total_sales = int(df_selection["總價"].sum())
average_rating = round(df_selection["評分"].mean(), 1)
star_rating = ":star:" * int(round(average_rating, 0))
average_sale_by_transaction = round(df_selection["總價"].mean(), 2)


# 3列布局
left_column, middle_column, right_column = st.columns(3)

# 添加相關(guān)信息
with left_column:
    st.subheader("銷售總額:")
    st.subheader(f"RMB {total_sales:,}")
with middle_column:
    st.subheader("平均評分:")
    st.subheader(f"{average_rating} {star_rating}")
with right_column:
    st.subheader("平均銷售額:")
    st.subheader(f"RMB {average_sale_by_transaction}")

# 分隔符
st.markdown("""---""")

# 各類商品銷售情況(柱狀圖)
sales_by_product_line = (
    df_selection.groupby(by=["商品類型"]).sum()[["總價"]].sort_values(by="總價")
)
fig_product_sales = px.bar(
    sales_by_product_line,
    x="總價",
    y=sales_by_product_line.index,
    orientation="h",
    title="<b>每種商品銷售總額</b>",
    color_discrete_sequence=["#0083B8"] * len(sales_by_product_line),
    template="plotly_white",
)
fig_product_sales.update_layout(
    plot_bgcolor="rgba(0,0,0,0)",
    xaxis=(dict(showgrid=False))
)

# 每小時銷售情況(柱狀圖)
sales_by_hour = df_selection.groupby(by=["小時"]).sum()[["總價"]]
print(sales_by_hour.index)
fig_hourly_sales = px.bar(
    sales_by_hour,
    x=sales_by_hour.index,
    y="總價",
    title="<b>每小時銷售總額</b>",
    color_discrete_sequence=["#0083B8"] * len(sales_by_hour),
    template="plotly_white",
)
fig_hourly_sales.update_layout(
    xaxis=dict(tickmode="linear"),
    plot_bgcolor="rgba(0,0,0,0)",
    yaxis=(dict(showgrid=False)),
)


left_column, right_column = st.columns(2)
left_column.plotly_chart(fig_hourly_sales, use_container_width=True)
right_column.plotly_chart(fig_product_sales, use_container_width=True)

# 隱藏streamlit默認格式信息
hide_st_style = """
            <style>
            #MainMenu {visibility: hidden;}
            footer {visibility: hidden;}
            header {visibility: hidden;}
            </style>
            """

st.markdown(hide_st_style, unsafe_allow_html=True)

# 安裝依賴庫
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple plotly==4.14.3
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple pandas==1.1.0
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple streamlit==0.86.0
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple openpyxl==3.0.6

# 運行
streamlit run app.py