pengshihyu/week-9_SQL

1import pandas as pd2import streamlit as st3 4import altair as alt5import duckdb6 7con = duckdb.connect(database='Job.db', read_only=True) 8 9# Countries10query="""11   SELECT * 12   FROM job13"""14Countries=list(con.execute(query).df().columns)[2:]15 16 17st.subheader('Investingation')18 19col1, col2 = st.columns(2)20 21with col1:22    query="""23            SELECT 24                 DISTINCT variable25            From job        26            ORDER BY variable       27          """28 29    kinds=con.execute(query).df()30    kind = st.selectbox('Kind of Statistics',kinds)31with col2: 32    country = st.multiselect('Country',Countries)33 34 35result_df = con.execute("""36    SELECT 37        *38    FROM Job 39    WHERE variable=?40    """, [kind]).df()41 42#chart = alt.Chart(result_df).mark_circle().encode(43#    x = 'date',44#    y = country,45#    #color = 'carrier'46#).interactive()47#st.altair_chart(chart, theme="streamlit", use_container_width=True)48 49# Create a new DataFrame containing only the selected countries50selected_data = result_df[['date'] + country]51 52# Melt the DataFrame to create a "long" format for plotting53melted_data = pd.melt(selected_data, id_vars=['date'], var_name='country', value_name='value')54 55# Plot the chart using the melted DataFrame56chart = alt.Chart(melted_data).mark_circle().encode(57    x = 'date',58    y = 'value',59    color = 'country'60).interactive()61 62st.altair_chart(chart, theme="streamlit", use_container_width=True)