# +
import leafmap.foliumap as leafmap
url = "https://data.source.coop/cboettig/social-vulnerability/svi2020_us_tract.pmtiles"

metadata = leafmap.pmtiles_metadata(url)
print(f"layer names: {metadata['layer_names']}")


# +
fill = ["interpolate", ["linear"], ["get", "RPL_THEMES"],
        0, "#FFE6EE",
        1, "#850101"]

style = {
    "version": 8,
    "sources": {
        "source1": {
            "type": "vector",
            "url": "pmtiles://" + url,
            "attribution": "CDC Social Vulnerability Index"}
    },
    "layers": [{
            "id": "layer1",
            "source": "source1",
            "source-layer": "SVI2000_US_tract",
            "type": "fill",
            "paint": {"fill-color": fill, "fill-opacity": 0.8}}]
}


m = leafmap.Map(center=[35, -100], zoom=4)
m.add_pmtiles(url, name="Social Vulnerability Index", style=style, overlay=True, show=True, zoom_to_layer=False)
m.to_html("map.html")
m

# +

summary = (ibis
    .read_parquet("https://data.source.coop/cboettig/social-vulnerability/svi2020_us_county.parquet")
    .filter(_.RPL_THEMES > 0) # -999 is NA
    .group_by(_.STATE)
    .agg(mean = _.RPL_THEMES.mean())
    .order_by(ibis.desc(_.mean))
    .to_pandas()
)

summary

# +
import ibis
from ibis import _

summary = (ibis
    .read_parquet("https://data.source.coop/cboettig/social-vulnerability/svi2020_us_tract.parquet")
#    .group_by(_.StateName)
#    .agg(fraction_disadvantaged = (_.Disadvan * _.SHAPE_Area).sum() / _.SHAPE_Area.sum())
#    .order_by(ibis.desc(_.fraction_disadvantaged))
    .head()
)

summary.columns
# -