app.py

import ibis
from ibis import _
import streamlit as st

st.set_page_config(layout="wide",
                   page_title="TPL LandVote",
                   page_icon=":globe:")

'''
# LandVote Prototype

An experimental platform for visualizing data on ballot measures for conservation, based on data from <https://landvote.org/> curated by the Trust for Public Land.

'''


## Chatbot
import os
import pandas as pd
import matplotlib.pyplot as plt
from pandasai.llm.openai import OpenAI
from pandasai import Agent
from pandasai.responses.streamlit_response import StreamlitResponse

llm = OpenAI(api_token=st.secrets["OPENAI_API_KEY"])
df1 = pd.read_csv("data.csv")

agent = Agent(
    [df1],
    config={"verbose": True, "response_parser": StreamlitResponse, "llm": llm},
)



with st.sidebar:

    '''
    ## Data Assistant (experimental)

    Ask questions about the landvote data, like:

    - What are the top states for approved conservation funds?
    - Plot the total funds spent in conservation each year.
    - What city has approved the most funds in a single measure? What was the description of that vote?
    - Which state has had largest number measures fail? What is that as a fraction of it's total measures?
    '''
    
    prompt = st.chat_input("Ask about the data")
    if prompt:
        with st.spinner():
            resp = agent.chat(prompt)
            if os.path.isfile('exports/charts/temp_chart.png'):
                im = plt.imread('exports/charts/temp_chart.png')
                st.image(im)
                os.remove('exports/charts/temp_chart.png')
            st.write(resp)
        


# year = st.slider("Select a year", min_value=1988, max_value=2024, value=2022, step=2)
year = st.slider("Select a year", min_value=1988, max_value=2024, value=2022, step=1)

import leafmap.maplibregl as leafmap
m = leafmap.Map(style="positron",   center=(-100, 40), zoom=3)

# url = "https://huggingface.co/datasets/boettiger-lab/landvote/resolve/main/landvote_polygons.pmtiles"
url = "https://huggingface.co/datasets/boettiger-lab/landvote/resolve/main/votes.pmtiles"
parties = "https://huggingface.co/datasets/boettiger-lab/landvote/resolve/main/votes.parquet"

dark_orange = 'rgba(171, 86, 1, 1)'   # dark orange - min value 
light_orange = 'rgba(243, 211, 177, 1)' # light orange 
grey = 'rgba(211, 211, 211, 1)'        # grey
light_green = 'rgba(195, 219, 195, 1)' # light green 
dark_green = 'rgba(65, 125, 65, 1)'    # dark green  - max value 

con = ibis.duckdb.connect(extensions=["spatial"])

party = (con
            .read_parquet(parties)
            .cast({"geometry": "geometry"})
            )

    
def get_summary(party, year):
    total_measures = party.filter(_.year == year).count().execute()
    
    df = (party
        .filter(_.year == year)
        .mutate(
            # Convert 'amount' from string with '$' and ',' to numeric
            amount_numeric=_.amount.replace('$', '').replace(',', '').cast('float64')
        )
        .group_by("party")
        .aggregate(
            percent_passed= (_.Status.isin(["Pass", "Pass*"]).sum() / total_measures).round(2),
            approved_funds= ibis.case()
                            .when(_.Status.isin(["Pass", "Pass*"]), _.amount_numeric)
                            .else_(ibis.literal(0))
                            .end()
                            .sum()
        )
        .mutate(color=ibis.case()
            .when(_.party == "DEMOCRAT", ibis.literal("#083A90"))
            .else_(ibis.literal("#E81B23"))
            .end())
    )
    
    df = df.to_pandas()
    return df

style_municipals = {
    "layers": [
        {
            "id": "cities",
            "source": "municipal",
            "source-layer": "municipal",
            "type": "fill-extrusion",
            "filter": [
                "==",
                ["get", "year"],
                year,
            ],
            "paint": {
                "fill-extrusion-color": [
                    "case",
                    # if passed, color green
                    ["==", ["get", "Status"], "Pass"],
                    [
                        "interpolate", ["linear"], [
                            "to-number", ["slice", ["get", "yes"], 0, -1]  # convert 'yes' string to number
                        ],
                        50, grey,    
                        55, light_green,     # higher yes % -> darker green
                        100, dark_green     # 100 is the max of data 
                    ],
                    # if failed, color orange
                    ["==", ["get", "Status"], "Fail"],
                    [
                        "interpolate", ["linear"], [
                            "to-number", ["slice", ["get", "yes"], 0, -1]  # convert 'yes' string to number
                        ],
                        0, dark_orange,     # higher yes % -> lighter orange 
                        50, light_orange,   
                        67, grey # 67 is the max of data.
                    ],
                    grey  # if no match 
                ],
                "fill-extrusion-height": ["*", ["get", "log_amount"], 5000],
            }
        },
    ],
}

style_counties = {
    "layers": [
        {
            "id": "counties",
            "source": "county",
            "source-layer": "county",
            "type": "fill-extrusion",
            "filter": [
                "==",
                ["get", "year"],
                year,
            ],
            "paint": {
                "fill-extrusion-color": [
                    "case",
                    # if passed, color green
                    ["==", ["get", "Status"], "Pass"],
                    [
                        "interpolate", ["linear"], [
                            "to-number", ["slice", ["get", "yes"], 0, -1]  # convert 'yes' string to number
                        ],
                        50, grey,    
                        55, light_green,     # higher yes % -> darker green
                        100, dark_green     # 100 is the max of data 
                    ],
                    # if failed, color orange
                    ["==", ["get", "Status"], "Fail"],
                    [
                        "interpolate", ["linear"], [
                            "to-number", ["slice", ["get", "yes"], 0, -1]  # convert 'yes' string to number
                        ],
                        0, dark_orange,     # higher yes % -> lighter orange 
                        50, light_orange,   
                        67, grey # 67 is the max of data.
                    ],
                    grey  # if no match 
                ],
                "fill-extrusion-height": ["*", ["get", "log_amount"], 5000],
            }
        },
    ],
}

style_states = {
    "layers": [
        {
            "id": "states",
            "source": "state",
            "source-layer": "state",
            "type": "fill",
            "filter": [
                "==",
                ["get", "year"],
                year,
            ],
            "paint": {
                "fill-color": [
                    "case",
                    # if passed, color green
                    ["==", ["get", "Status"], "Pass"],
                    [
                        "interpolate", ["linear"], [
                            "to-number", ["slice", ["get", "yes"], 0, -1]  # convert 'yes' string to number
                        ],
                        50, grey,    
                        55, light_green,     # higher yes % -> darker green
                        100, dark_green     # 100 is the max of data 
                    ],
                    # if failed, color orange
                    ["==", ["get", "Status"], "Fail"],
                    [
                        "interpolate", ["linear"], [
                            "to-number", ["slice", ["get", "yes"], 0, -1]  # convert 'yes' string to number
                        ],
                        0, dark_orange,     # higher yes % -> lighter orange 
                        50, light_orange,   
                        67, grey # 67 is the max of data.
                    ],
                    grey  # if no match 
                ]
            }
        },
    ],
}


#states are 2D and transparent, thus added separately.
m.add_pmtiles(
    url,
    style=style_states,
    visible=True,
    opacity=0.6,
    tooltip=True,
    fit_bounds=False
)

#states are 2D and transparent, thus added separately.
m.add_pmtiles(
    url,
    style=style_counties,
    visible=True,
    opacity=1.0,
    tooltip=True,
    fit_bounds=False
)


m.add_pmtiles(
    url,
    style=style_municipals,
    visible=True,
    opacity=1.0,
    tooltip=True,
    fit_bounds=False
)


m.add_layer_control()
m.to_streamlit()
# st.dataframe(df)

df = get_summary(party, year)
st.bar_chart(df, x= "party",y = "percent_passed",color="color")

st.bar_chart(df, x= "party",y = "approved_funds",color="color")

# st.divider()
# footer = st.container()