Charts & Analysis - SB125 Fund Split Analysis

import altair as alt
import pandas as pd
from IPython.display import HTML, Image, Markdown, display, display_html
from calitp_data_analysis.gcs_pandas import GCSPandas

pd.set_option("display.max_columns", None)
pd.set_option("display.max_rows", None)

GCS_PATH = "gs://calitp-analytics-data/data-analyses/sb125/fund_split/"
gcs_pandas = GCSPandas()

# move filter location to the top

display(
    HTML(
        """<style>form.vega-bindings {  position: absolute;  left: 0px;  top: 0px;}</style>"""
    )
)

all_melt = gcs_pandas.read_parquet(f"{GCS_PATH}all_fund_requests_melt.parquet")

# Aggregations

by_source = (
    all_melt.groupby(["fund source"])
    .agg({"fund amount": "sum", "rtpa": "nunique"})
    .reset_index()
)

by_type = (
    all_melt.groupby(["project type"])
    .agg(
        {
            "fund amount": "sum",
        }
    )
    .reset_index()
)

by_year = (
    all_melt.groupby(["fiscal year", "project type"])
    .agg({"fund amount": "sum"})
    .reset_index()
)

by_rtpa = (
    all_melt.groupby(["rtpa", "project type", "fiscal year"])
    .agg({"fund amount": "sum"})
    .reset_index()
)

by_agency = (
    all_melt.groupby(["implementing agenc-y/-ies", "project type", "fiscal year"])
    .agg({"fund amount": "sum"})
    .reset_index()
)

# aggregate by rtpa, agency, project type and FY
rtpa_group = (
    all_melt.groupby(
        ["rtpa", "implementing agenc-y/-ies", "project type", "fiscal year"]
    )
    .agg({"fund amount": "sum"})
    .reset_index()
)

# Overall fund split bar chart
overall_chart = (
    alt.Chart(by_type)
    .mark_bar(point=True)
    .encode(
        y="project type",
        x="fund amount",
        color="project type",
        tooltip=["project type", "fund amount"],
    )
    .properties(
        title="Overall split of SB125 allocation funds by capital/operating expenses",
        width=600,
        height=300,
    )
)

overall_text = overall_chart.mark_text(
    align="left",
    dx=0,
    dy=10,
).encode(text=alt.Text("fund amount:Q", format="$,.2f"), color=alt.value("black"))

# Stacked bar chart showing fund split by FY
stack_chart = (
    alt.Chart(by_year)
    .mark_bar(point=True)
    .encode(
        y="fiscal year",
        x="fund amount",
        color="project type",
        tooltip=["project type", "fund amount"],
    )
    .properties(
        title="SB125 allocation funds by capital/operating expenses, by fiscal year",
        width=600,
        height=300,
    )
)

stack_text = stack_chart.mark_text(align="left", dx=-10, dy=10).encode(
    text=alt.Text("fund amount:Q", format="$,.2f"), color=alt.value("black")
)

# bar chart of fund amount by sources

source_chart = (
    alt.Chart(by_source)
    .mark_bar(point=True)
    .encode(
        y="fund source",
        x="fund amount",
        # color="project type",
        tooltip=["fund source", "fund amount"],
    )
    .properties(
        title="Allocation funds by funding source", 
        width=600, 
        height=300)
)

source_text = source_chart.mark_text(align="left", dx=0, dy=10).encode(
    text=alt.Text("fund amount:Q", format="$,.2f"), color=alt.value("black")
)

# Faceted line chart fund split by FY, by agencies. with drop down selector of RTPA

rtpa_bar_chart_2 = (
    alt.Chart(rtpa_group)
    .mark_line(point=True)
    .encode(
        alt.Y("fund amount"),
        alt.X("fiscal year"),
        color="project type",
    )
)

# create drop down

## list of rtpas
rtpa_list = list(rtpa_group["rtpa"].unique())

## actual drop down mechanism
rtpa_dropdown = alt.binding_select(options=rtpa_list, name="Select RTPA")

rtpa_selector = alt.selection_point(fields=["rtpa"], value="VCTC", bind=rtpa_dropdown)

# data label text
rtpa_chart_text_2 = rtpa_bar_chart_2.mark_text(
    align="center",
    dx=0,
    dy=-10,
).encode(text=alt.Text("fund amount:Q", format="$,.2f"), color=alt.value("black"))

# combine bar and text charts
rtpa_chart_combo_2 = (rtpa_bar_chart_2 + rtpa_chart_text_2).properties(
    #title="Implementing agency funding split",
    width=300,
    height=150
)

# add facets by agency
rtpa_facet_2 = (
    rtpa_chart_combo_2.add_params(rtpa_selector)
    .facet(
        facet=alt.Facet(
            "implementing agenc-y/-ies",
            title=" ",
        ),
        columns=3,
    )
    .resolve_scale(x="independent", y="independent")
    .transform_filter(rtpa_selector)
)

# Split chart of Funds by fund category for FY, by agency. with drop down selection for RTPA

# list of RTPAs
rtpa_list = list(rtpa_group["rtpa"].unique())

# selectors
rtpa_dropdown = alt.binding_select(options=rtpa_list, name="Select RTPA")

rtpa_selector = alt.selection_point(fields=["rtpa"], value="VCTC", bind=rtpa_dropdown)

# opx chart
opx_chart = (
    alt.Chart(
        rtpa_group[rtpa_group["project type"] == "operating"], title="Operating Funds"
    )
    .mark_bar()
    .encode(
        alt.Y("implementing agenc-y/-ies").title("Agency"),
        alt.X("fund amount"),
        alt.YOffset("fiscal year"),
        alt.Color("implementing agenc-y/-ies"),
        tooltip=["fund amount", "fiscal year"],
    )
    .add_params(rtpa_selector)
    .transform_filter(rtpa_selector)
    .properties(
        width=400,
        height=400,
    )
    .resolve_scale(x="shared", y="independent")
)

opx_text = opx_chart.mark_text(align="left", dx=0, dy=0).encode(
    text=alt.Text("fund amount:Q", format="$,.2f"), color=alt.value("black")
)


opx_chart_text = opx_chart + opx_text


# capx chart
capx_chart = opx_chart.properties(
    data=rtpa_group[rtpa_group["project type"] == "capital"], title="Capital Funds"
).transform_filter(rtpa_selector)

capx_text = capx_chart.mark_text(align="left", dx=0, dy=0).encode(
    text=alt.Text("fund amount:Q", format="$,.2f"), color=alt.value("black")
)

capx_chart_text = capx_chart + capx_text


# concat capx & opx charts
opx_capx_chart = (
    alt.hconcat(capx_chart, opx_chart)
    .resolve_scale(x="shared", y="independent")
    .properties(
        title=alt.TitleParams(
            text=" ",
            subtitle=" ",
        )
    )
)

Total Split of SB125 allocation funds by capital and operating expenses¶

This chart shows the total dollar amount of funds all RTPAs commit to capital and operating expense for 4 state fiscal years.

overall_chart + overall_text

Split of SB125 allocation funds by capital and operating expenses, by state fiscal year.¶

Similar to the previous chart, this chart breaks down the amount of funds all RTPAs commited, by state fiscal year.

stack_chart + stack_text

Split of SB125 Allocation Funds by Funding Source¶

RTPAs are required to specify the source of of their request funds, this chart shows that distribution.

source_chart + source_text

SB125 Fund Split breakdown by transit agencies¶

Similar to the previous charts, this chart breaks down the funding amount by capital/operating expense, and by state fiscal year. This interactive chart can filter between different RTPAs, and will display their associated transit agencies

# render chart
rtpa_facet_2

Side-by-Side Comparison of Capital and Operating funds¶

Similar to the previous chart, this charts compares operating vs capital funds for each transit agency by fiscal year. This chart can also be filtered by RTPAs. Useful when comparing solely-capital or solely-operating funds against other transit agencies in the same RTPA.

opx_capx_chart