import ibis from ibis import _ import streamlit as st import altair as alt 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 import leafmap.maplibregl as leafmap 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 curated by the Trust for Public Land. ''' st.caption("We visualize each voting jurisdiction with green if a conservation measure passed and orange if it failed. The intensity of green or orange reflects the level of support or opposition, with darker green representing stronger support for passed measures and darker orange representing lower support for failed measures. The height of county and city jurisdictions represents the amount of funding proposed by the measure.") COLORS = { "dark_orange": "#ab5601", "light_orange": "#f3d3b1", "grey": "#d3d3d3", "light_green": "#c3dbc3", "dark_green": "#417d41", "dem_blue": "#1b46c2", "rep_red": "#E81B23" } ## chatbot 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}) year = st.slider("Select a year", 1988, 2024, 2022, 1) votes_pmtiles = "https://huggingface.co/datasets/boettiger-lab/landvote/resolve/main/votes.pmtiles" votes_parquet = "https://huggingface.co/datasets/boettiger-lab/landvote/resolve/main/votes.parquet" # get parquet data for charts con = ibis.duckdb.connect(extensions=["spatial"]) votes = (con .read_parquet(votes_parquet) .cast({"geometry": "geometry"}) ) def create_chart(df, y_column, ylab, title, color, chart_type="line"): # color encoding - color is a list or single value color_encoding = ( alt.Color('party:N', scale=alt.Scale(domain=["DEMOCRAT", "REPUBLICAN"], range=color)) if isinstance(color, list) else alt.value(color) ) # Set the mark type based on chart_type mark = alt.Chart(df).mark_line(strokeWidth=3) if chart_type == "line" else alt.Chart(df).mark_bar() return mark.encode( x=alt.X('year:N', title='Year'), y=alt.Y(f'{y_column}:Q', title=ylab), color=color_encoding ).properties( title=title ) # percentage of measures passing, per party def get_passes(votes): return (votes # .filter(_.year >= 2000) .group_by("year", "party") .aggregate(total=_.count(), passes=_.Status.isin(["Pass", "Pass*"]).sum()) .mutate(percent_passed=(_.passes / _.total).round(2), color=ibis.case().when(_.party == "DEMOCRAT", COLORS["dem_blue"]).else_(COLORS["rep_red"]).end()) .to_pandas()) # cumulative funding over time def funding_chart(votes): return (votes # .filter(_.year >= 2000) .mutate(amount=_.amount.replace('$', '') .replace(',', '') .cast('float64')) .filter(_.Status.isin(["Pass", "Pass*"])) .group_by("year") .aggregate(total_funding=_.amount.sum()) .order_by("year") .mutate(cumulative_funding=_.total_funding.cumsum()/1e9) .to_pandas() ) #color fill for measure status paint_fill = { "fill-color": [ "case", ["==", ["get", "Status"], "Pass"], [ "interpolate", ["linear"], [ "to-number", ["slice", ["get", "yes"], 0, -1] # convert 'yes' string to number ], 50, COLORS["grey"], 55, COLORS["light_green"], 100, COLORS["dark_green"] # higher yes % -> darker green ], ["==", ["get", "Status"], "Fail"], [ "interpolate", ["linear"], [ "to-number", ["slice", ["get", "yes"], 0, -1] ], 0, COLORS["dark_orange"], 50, COLORS["light_orange"], # lower yes % -> darker orange 67, COLORS["grey"] # 67 is max in our data ], COLORS["grey"] ] } # for status, height depends on funding paint_extrusion = { "fill-extrusion-color": paint_fill["fill-color"], "fill-extrusion-height": ["*", ["to-number", ["get", "log_amount"]], 5000] } # pmtiles style for status def get_style_status(jurisdiction): if jurisdiction == "State": name = "state" label = "States" paint_type = paint_fill layer_type = "fill" elif jurisdiction == "County": name = "county" label = "Counties" paint_type = paint_extrusion layer_type = "fill-extrusion" else: # Municipal name = "municipal" label = "Cities" paint_type = paint_extrusion layer_type = "fill-extrusion" return { "layers": [ { "id": label, "source": name, "source-layer": name, "type": layer_type, "filter": ["==", ["get", "year"], year], "paint": paint_type } ] } # pmtiles style for party def get_style_party(jurisdiction): if jurisdiction == "State": name = "state" label = "States" elif jurisdiction == "County": name = "county" label = "Counties" else: # Municipal name = "municipal" label = "Cities" # Return style dictionary for political party return { "layers": [ { "id": label, "source": name, "source-layer": name, "type": "fill", "filter": [ "==", ["get", "year"], year ], "paint": { "fill-color": { "property": "party", "type": "categorical", "stops": [ ["DEMOCRAT", COLORS["dem_blue"]], ["REPUBLICAN", COLORS["rep_red"]] ] } } } ] } justice40 = "https://data.source.coop/cboettig/justice40/disadvantaged-communities.pmtiles" justice40_fill = { 'property': 'Disadvan', 'type': 'categorical', 'stops': [ [0, "rgba(255, 255, 255, 0)"], [1, "rgba(0, 0, 139, 1)"]]} justice40_style = { "version": 8, "sources": { "source1": { "type": "vector", "url": "pmtiles://" + justice40, "attribution": "Justice40"} }, "layers": [{ "id": "Justice40", "source": "source1", "source-layer": "DisadvantagedCommunitiesCEJST", "type": "fill", "paint": {"fill-color": justice40_fill, "fill-opacity": 0.6}}] } sv_pmtiles = "https://data.source.coop/cboettig/social-vulnerability/svi2020_us_county.pmtiles" sv_style = { "layers": [ { "id": "SVI", "source": "Social Vulnerability Index", "source-layer": "SVI2020_US_county", "type": "fill", "paint": { "fill-color": ["interpolate", ["linear"], ["get", "RPL_THEMES"], 0, "#FFE6EE", 1, "#850101"] } } ] } party_pmtiles = "https://huggingface.co/datasets/boettiger-lab/landvote/resolve/main/party_polygons_all.pmtiles" recent_election_year = year - year%4 party_style = { "layers": [ { "id": "Party", "source": "Political Parties", "source-layer": "county", "type": "fill", "filter": [ "==", ["get", "year"], recent_election_year ], "paint": { "fill-color": { "property": "party", "type": "categorical", "stops": [ ["DEMOCRAT", COLORS["dem_blue"]], ["REPUBLICAN", COLORS["rep_red"]] ] } } } ] } with st.sidebar: color_choice = st.radio("Color by:", ["Measure Status", "Political Party"]) st.divider() "Data Layers:" # with st.expander("Social Justice"): social_toggle = st.toggle("Social Vulnerability Index") justice_toggle = st.toggle("Climate and Economic Justice") party_toggle = st.toggle("Political Parties") st.divider() ''' ## 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) m = leafmap.Map(style="positron", center=(-100, 40), zoom=3) if social_toggle: m.add_pmtiles(sv_pmtiles, style = sv_style ,visible=True, opacity=0.3, tooltip=True) if party_toggle: m.add_pmtiles(party_pmtiles, style = party_style ,visible=True, opacity=0.3, tooltip=True) if justice_toggle: m.add_pmtiles(justice40, style=justice40_style, visible=True, name="Justice40", opacity=0.3, tooltip=True) #compute percentage passed in given year passed_year = votes.filter(_.year == year).filter(_.Status.isin(["Pass","Pass*"])).count().execute() total_year= votes.filter(_.year == year).count().execute() year_passed = (passed_year/total_year*100).round(2) f"{year_passed}% Measures Passed in {year}" #compute percentage passed over entire dataset passed = votes.filter(_.Status.isin(["Pass","Pass*"])).count().execute() total = votes.count().execute() overall_passed = (passed/total*100).round(2) f"{overall_passed}% Measures Passed from 1988 - 2024 \n" if color_choice == "Measure Status": m.add_pmtiles(votes_pmtiles, style=get_style_status("State"), visible=True, opacity=0.8, tooltip=True) m.add_pmtiles(votes_pmtiles, style=get_style_status("County"), visible=True, opacity=1.0, tooltip=True) m.add_pmtiles(votes_pmtiles, style=get_style_status("Municipal"), visible=True, opacity=1.0, tooltip=True) elif color_choice == "Political Party": m.add_pmtiles(votes_pmtiles, style=get_style_party("State"), visible=True, opacity=0.8, tooltip=True) m.add_pmtiles(votes_pmtiles, style=get_style_party("County"), visible=True, opacity=1.0, tooltip=True) m.add_pmtiles(votes_pmtiles, style=get_style_party("Municipal"), visible=True, opacity=1.0, tooltip=True) m.add_layer_control() m.to_streamlit() # display charts df_passes = get_passes(votes) st.altair_chart(create_chart(df_passes, "percent_passed", "Percent Passed","% of Measures Passed", [COLORS["dem_blue"], COLORS["rep_red"]], chart_type="line"), use_container_width=True) df_funding = funding_chart(votes) st.altair_chart(create_chart(df_funding, "cumulative_funding", "Billions of Dollars", "Cumulative Funding", COLORS["dark_green"], chart_type="bar"), use_container_width=True) st.divider() footer = st.container() ''' # Credits Authors: Cassie Buhler & Carl Boettiger, UC Berkeley License: BSD-2-clause ## Data sources - TPL LandVote Database by Trust for Public Land. Data: https://tpl.quickbase.com/db/bbqna2qct?a=dbpage&pageID=8. Citation: The Trust for Public Land, LandVote®, 2024, www.landvote.org., License: Public Domain - Climate and Economic Justice Screening Tool, US Council on Environmental Quality, Justice40, Data: https://beta.source.coop/repositories/cboettig/justice40/description/. License: Public Domain - CDC 2020 Social Vulnerability Index by US Census Track. Data: https://source.coop/repositories/cboettig/social-vulnerability/description. License: Public Domain - County Presidential Election Returns 2000-2020 by MIT Election Data and Science Lab. Citation: https://doi.org/10.7910/DVN/VOQCHQ. License: Public Domain. - U.S. President 1976–2020 by MIT Election Data and Science Lab. Citation: https://doi.org/10.7910/DVN/42MVDX. License: Public Domain. ''' st.caption("***Political affiliation is determined by the party that received the majority vote in the most recent presidential election for each jurisdiction. For counties and states, this reflects the majority vote in that area. For cities, affiliation is based on the party of the county in which the city is located.")