{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Claims Denials Analysis\n", "\n", "Data from: https://www.cms.gov/marketplace/resources/data/public-use-files\n", "\n", "`Sheet IDs`\n", "- 2 = Individual Marketplace Medical Qualified Health Plans (QHPs)\n", "- 3 = Individual Marketplace Stand Alone Dental Plans (SADPs)\t\n", "- 4 = Small Business Health Options Program (SHOP) Medical and Dental Plans\t" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "![Alt text](image.png)" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import polars as pl\n", "import pandas as pd\n", "import altair as alt\n", "\n", "alt.data_transformers.disable_max_rows()\n", "\n", "\n", "# For some reason, I can't get Polars to skip the rows properly.\n", "# So using Pandas, for the reader.\n", "df = pl.from_pandas(pd.read_excel(\n", " \"data/Transparency_in_Coverage_PUF.xlsx\", \n", " sheet_name=1,\n", " skiprows=2,\n", " na_values=[\"**\", \"*\"]))" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "shape: (5, 30)
StateIssuer_NameIssuer_IDIs_Issuer_New_to_Exchange? (Yes_or_No)SADP_Only?Plan_IDQHP/SADPPlan_TypeMetal_LevelURL_Claims_Payment_PoliciesIssuer_Claims_ReceivedIssuer_Claims_DenialsIssuer_Internal_Appeals_FiledIssuer_Number_Internal_Appeals_OverturnedIssuer_Percent_Internal_Appeals_OverturnedIssuer_External_Appeals_FiledIssuer_Number_External_Appeals_OverturnedIssuer_Percent_External_Appeals_OverturnedPlan_Number_Claims_ReceivedPlan_Number_Claims_DeniedPlan_Number_Claims_Denied_Referral_RequiredPlan_Number_Claims_Denied_Out_of_NetworkPlan_Number_Claims_Denied_Services_ExcludedPlan_Number_Claims_Denied_Not_Medically_Necessary_Excl_Behavioral_HealthPlan_Number_Claims_Denied_Not_Medically_Necessary_Behavioral_Health_OnlyPlan_Number_Claims_Denied_OtherRate_ReviewFinancial_InformationAverage Monthly EnrollmentAverage Monthly Disenrollment
strstri64strstrstrstrstrstrstrf64f64f64f64f64f64f64f64f64f64f64f64f64f64f64f64strstrf64f64
"AK""Premera Blue C…38344"No""No""38344AK1060001…"QHP""PPO""Gold""https://www.pr…603348.0143208.02156.01079.050.05nullnullnull259304.059633.011433.0143.06071.0818.0null89942.0"https://ratere…"https://www.na…6428.0728.0
"AK""Premera Blue C…38344"No""No""38344AK1060002…"QHP""PPO""Silver""https://www.pr…603348.0143208.02156.01079.050.05nullnullnull100437.026239.04720.095.02663.0182.0null36773.0"https://ratere…"https://www.na…2109.0232.0
"AK""Premera Blue C…38344"No""No""38344AK1060004…"QHP""PPO""Bronze Expande…"https://www.pr…603348.0143208.02156.01079.050.05nullnullnull115878.026136.04786.020.02765.0127.0null43716.0"https://ratere…"https://www.na…3605.0479.0
"AK""Premera Blue C…38344"No""No""38344AK1070002…"QHP""PPO""Bronze Expande…"https://www.pr…603348.0143208.02156.01079.050.05nullnullnull75481.020063.01322.037.01586.087.0null31298.0"https://ratere…"https://www.na…3139.0391.0
"AK""Premera Blue C…38344"No""No""38344AK1080001…"QHP""PPO""Gold""https://www.pr…603348.0143208.02156.01079.050.05nullnullnullnullnullnullnullnullnullnullnull"https://ratere…"https://www.na…nullnull
" ], "text/plain": [ "shape: (5, 30)\n", "┌───────┬────────────┬───────────┬────────────┬───┬────────────┬───────────┬───────────┬───────────┐\n", "│ State ┆ Issuer_Nam ┆ Issuer_ID ┆ Is_Issuer_ ┆ … ┆ Rate_Revie ┆ Financial ┆ Average ┆ Average │\n", "│ --- ┆ e ┆ --- ┆ New_to_Exc ┆ ┆ w ┆ _Informat ┆ Monthly ┆ Monthly │\n", "│ str ┆ --- ┆ i64 ┆ hange? ┆ ┆ --- ┆ ion ┆ Enrollmen ┆ Disenroll │\n", "│ ┆ str ┆ ┆ (Yes_… ┆ ┆ str ┆ --- ┆ t ┆ ment │\n", "│ ┆ ┆ ┆ --- ┆ ┆ ┆ str ┆ --- ┆ --- │\n", "│ ┆ ┆ ┆ str ┆ ┆ ┆ ┆ f64 ┆ f64 │\n", "╞═══════╪════════════╪═══════════╪════════════╪═══╪════════════╪═══════════╪═══════════╪═══════════╡\n", "│ AK ┆ Premera ┆ 38344 ┆ No ┆ … ┆ https://ra ┆ https://w ┆ 6428.0 ┆ 728.0 │\n", "│ ┆ Blue Cross ┆ ┆ ┆ ┆ tereview.h ┆ ww.naic.o ┆ ┆ │\n", "│ ┆ Blue ┆ ┆ ┆ ┆ ealthcare. ┆ rg/cis_re ┆ ┆ │\n", "│ ┆ Shield o… ┆ ┆ ┆ ┆ go… ┆ fined… ┆ ┆ │\n", "│ AK ┆ Premera ┆ 38344 ┆ No ┆ … ┆ https://ra ┆ https://w ┆ 2109.0 ┆ 232.0 │\n", "│ ┆ Blue Cross ┆ ┆ ┆ ┆ tereview.h ┆ ww.naic.o ┆ ┆ │\n", "│ ┆ Blue ┆ ┆ ┆ ┆ ealthcare. ┆ rg/cis_re ┆ ┆ │\n", "│ ┆ Shield o… ┆ ┆ ┆ ┆ go… ┆ fined… ┆ ┆ │\n", "│ AK ┆ Premera ┆ 38344 ┆ No ┆ … ┆ https://ra ┆ https://w ┆ 3605.0 ┆ 479.0 │\n", "│ ┆ Blue Cross ┆ ┆ ┆ ┆ tereview.h ┆ ww.naic.o ┆ ┆ │\n", "│ ┆ Blue ┆ ┆ ┆ ┆ ealthcare. ┆ rg/cis_re ┆ ┆ │\n", "│ ┆ Shield o… ┆ ┆ ┆ ┆ go… ┆ fined… ┆ ┆ │\n", "│ AK ┆ Premera ┆ 38344 ┆ No ┆ … ┆ https://ra ┆ https://w ┆ 3139.0 ┆ 391.0 │\n", "│ ┆ Blue Cross ┆ ┆ ┆ ┆ tereview.h ┆ ww.naic.o ┆ ┆ │\n", "│ ┆ Blue ┆ ┆ ┆ ┆ ealthcare. ┆ rg/cis_re ┆ ┆ │\n", "│ ┆ Shield o… ┆ ┆ ┆ ┆ go… ┆ fined… ┆ ┆ │\n", "│ AK ┆ Premera ┆ 38344 ┆ No ┆ … ┆ https://ra ┆ https://w ┆ null ┆ null │\n", "│ ┆ Blue Cross ┆ ┆ ┆ ┆ tereview.h ┆ ww.naic.o ┆ ┆ │\n", "│ ┆ Blue ┆ ┆ ┆ ┆ ealthcare. ┆ rg/cis_re ┆ ┆ │\n", "│ ┆ Shield o… ┆ ┆ ┆ ┆ go… ┆ fined… ┆ ┆ │\n", "└───────┴────────────┴───────────┴────────────┴───┴────────────┴───────────┴───────────┴───────────┘" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.head(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Tackling datasets like this\n", "\n", "It's often overwhelming to tackle data you have no idea how it's structured.\n", "\n", "But what are data visualizations for? EXPLORATION." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Drop unnecessary columns\n", "\n", "First glance, I can tell the Excel file already separated:\n", "- `QHP/SADP` columns\n", "- `SADP_Only?` column is not useful since we're not workign dental plans\n", "- `Issuer_ID` most likely is not useful \n", "- `URL_Claims_Payment_Policies` for now, I don't need link" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "working_df = df.drop(\"SADP_Only?\", \"Issuer_ID\", \"URL_Claims_Payment_Policies\", \"URL_Claims_Payment_Policies\", \"Is_Issuer_New_to_Exchange? (Yes_or_No)\",\n", " \"Rate_Review\", \"Financial_Information\")" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "['State',\n", " 'Issuer_Name',\n", " 'Plan_ID',\n", " 'QHP/SADP',\n", " 'Plan_Type',\n", " 'Metal_Level',\n", " 'Issuer_Claims_Received',\n", " 'Issuer_Claims_Denials',\n", " 'Issuer_Internal_Appeals_Filed',\n", " 'Issuer_Number_Internal_Appeals_Overturned',\n", " 'Issuer_Percent_Internal_Appeals_Overturned',\n", " 'Issuer_External_Appeals_Filed',\n", " 'Issuer_Number_External_Appeals_Overturned',\n", " 'Issuer_Percent_External_Appeals_Overturned',\n", " 'Plan_Number_Claims_Received',\n", " 'Plan_Number_Claims_Denied',\n", " 'Plan_Number_Claims_Denied_Referral_Required',\n", " 'Plan_Number_Claims_Denied_Out_of_Network',\n", " 'Plan_Number_Claims_Denied_Services_Excluded',\n", " 'Plan_Number_Claims_Denied_Not_Medically_Necessary_Excl_Behavioral_Health',\n", " 'Plan_Number_Claims_Denied_Not_Medically_Necessary_Behavioral_Health_Only',\n", " 'Plan_Number_Claims_Denied_Other',\n", " 'Average Monthly Enrollment',\n", " 'Average Monthly Disenrollment']" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "working_df.columns" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Wide Dataframes\n", "\n", "Looking at the column names, you can see it's one of those \"wide\" dataframes that is good for reporting and traditional data visualization.\n", "\n", "Each plan gets a row of data, and all the metrics correspond to a column. \n", "\n", "Since we have a lot of plans/rows, best bet is to try to plot our first metric - `Plan_Number_Claims_Received` per plan" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# EDA: Scatter Graph\n", "\n", "First thing I'd like to see is number of claims for each plan, by state, since `State` definitely wouldn't overload one axis" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "
\n", "" ], "text/plain": [ "alt.Chart(...)" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "alt.Chart(df).mark_circle().encode(\n", " x=alt.X(\"State:O\"),\n", " y=alt.Y(\"Plan_Number_Claims_Received:Q\")\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Note: Missing States\n", "\n", "Hmm we're missing some states, x-axis clearly doesn't have 50 states. Where is NY, NJ, CT... all the northeast (I'm from NY)." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Issuer vs. Plan-level\n", "\n", "Because there are two levels of data here, we pretty much have to work with two different dataframes. \n", "\n", "Let's start with Issuer" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Issuer Level Data" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "shape: (5, 9)
StateIssuer_NamePlan_TypeIssuer_Claims_ReceivedIssuer_Claims_DenialsIssuer_Internal_Appeals_FiledIssuer_Number_Internal_Appeals_OverturnedIssuer_Percent_Internal_Appeals_OverturnedMetal_Level
strstrstrf64f64f64f64f64list[str]
"AK""Moda Assurance…"PPO"7965.0628.0nullnullnull["Gold", "Silver", … "Bronze"]
"AR""QualChoice Lif…"PPO"1.971681e6361647.0103.086.083.5["Silver", "Silver", … "Gold"]
"AZ""UnitedHealthca…"HMO"22221.08586.0329.0141.042.86["Gold", "Silver", … "Bronze Expanded"]
"FL""Florida Health…"POS"2.408855e6303057.0426.0177.041.55["Catastrophic", "Silver", … "Platinum"]
"FL""Celtic Insuran…"EPO"5.169919e62.167829e64177.02163.051.78["Bronze", "Gold", … "Gold"]
" ], "text/plain": [ "shape: (5, 9)\n", "┌───────┬────────────┬───────────┬────────────┬───┬────────────┬───────────┬───────────┬───────────┐\n", "│ State ┆ Issuer_Nam ┆ Plan_Type ┆ Issuer_Cla ┆ … ┆ Issuer_Int ┆ Issuer_Nu ┆ Issuer_Pe ┆ Metal_Lev │\n", "│ --- ┆ e ┆ --- ┆ ims_Receiv ┆ ┆ ernal_Appe ┆ mber_Inte ┆ rcent_Int ┆ el │\n", "│ str ┆ --- ┆ str ┆ ed ┆ ┆ als_Filed ┆ rnal_Appe ┆ ernal_App ┆ --- │\n", "│ ┆ str ┆ ┆ --- ┆ ┆ --- ┆ als_O… ┆ eals_… ┆ list[str] │\n", "│ ┆ ┆ ┆ f64 ┆ ┆ f64 ┆ --- ┆ --- ┆ │\n", "│ ┆ ┆ ┆ ┆ ┆ ┆ f64 ┆ f64 ┆ │\n", "╞═══════╪════════════╪═══════════╪════════════╪═══╪════════════╪═══════════╪═══════════╪═══════════╡\n", "│ AK ┆ Moda ┆ PPO ┆ 7965.0 ┆ … ┆ null ┆ null ┆ null ┆ [\"Gold\", │\n", "│ ┆ Assurance ┆ ┆ ┆ ┆ ┆ ┆ ┆ \"Silver\", │\n", "│ ┆ Company ┆ ┆ ┆ ┆ ┆ ┆ ┆ … │\n", "│ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ \"Bronze\"] │\n", "│ AR ┆ QualChoice ┆ PPO ┆ 1.971681e6 ┆ … ┆ 103.0 ┆ 86.0 ┆ 83.5 ┆ [\"Silver\" │\n", "│ ┆ Life & ┆ ┆ ┆ ┆ ┆ ┆ ┆ , │\n", "│ ┆ Health ┆ ┆ ┆ ┆ ┆ ┆ ┆ \"Silver\", │\n", "│ ┆ Insuran… ┆ ┆ ┆ ┆ ┆ ┆ ┆ … \"Gold\"] │\n", "│ AZ ┆ UnitedHeal ┆ HMO ┆ 22221.0 ┆ … ┆ 329.0 ┆ 141.0 ┆ 42.86 ┆ [\"Gold\", │\n", "│ ┆ thcare of ┆ ┆ ┆ ┆ ┆ ┆ ┆ \"Silver\", │\n", "│ ┆ Arizona, ┆ ┆ ┆ ┆ ┆ ┆ ┆ … \"Bronze │\n", "│ ┆ Inc… ┆ ┆ ┆ ┆ ┆ ┆ ┆ Exp… │\n", "│ FL ┆ Florida ┆ POS ┆ 2.408855e6 ┆ … ┆ 426.0 ┆ 177.0 ┆ 41.55 ┆ [\"Catastr │\n", "│ ┆ Health ┆ ┆ ┆ ┆ ┆ ┆ ┆ ophic\", │\n", "│ ┆ Care Plan, ┆ ┆ ┆ ┆ ┆ ┆ ┆ \"Silver\", │\n", "│ ┆ Inc. ┆ ┆ ┆ ┆ ┆ ┆ ┆ … \"Pl… │\n", "│ FL ┆ Celtic ┆ EPO ┆ 5.169919e6 ┆ … ┆ 4177.0 ┆ 2163.0 ┆ 51.78 ┆ [\"Bronze\" │\n", "│ ┆ Insurance ┆ ┆ ┆ ┆ ┆ ┆ ┆ , \"Gold\", │\n", "│ ┆ Company ┆ ┆ ┆ ┆ ┆ ┆ ┆ … \"Gold\"] │\n", "└───────┴────────────┴───────────┴────────────┴───┴────────────┴───────────┴───────────┴───────────┘" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "issuer_level_df = (\n", " working_df\n", " .filter(pl.col(\"Issuer_Claims_Received\").is_not_null())\n", " .groupby(\"State\", \"Issuer_Name\", \"Plan_Type\")\n", " .agg(\n", " pl.first(\"Issuer_Claims_Received\"),\n", " pl.first(\"Issuer_Claims_Denials\"),\n", " pl.first(\"Issuer_Internal_Appeals_Filed\"),\n", " pl.first(\"Issuer_Number_Internal_Appeals_Overturned\"),\n", " pl.first(\"Issuer_Percent_Internal_Appeals_Overturned\"), \n", " pl.col(\"Metal_Level\") \n", " )\n", ")\n", "\n", "issuer_level_df.head(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# View 1: Issuer-level Viz\n", "\n", "Lets get started. I want to look at distribution of \"denial\" rates\n", "\n", "One of the cool things that's built-in to Altair is ability to do transformations and calculations using Altair syntax, as to reduce data modeling required for data visualizations. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Add State-to-Region mapping\n", "\n", "Create a Polars DataFrame with the state abbreviation and region data" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "\n", "state_region = pl.DataFrame({\n", " \"State\": [\"AL\", \"AK\", \"AZ\", \"AR\", \"CA\", \"CO\", \"CT\", \"DE\", \"FL\", \"GA\",\n", " \"HI\", \"ID\", \"IL\", \"IN\", \"IA\", \"KS\", \"KY\", \"LA\", \"ME\", \"MD\",\n", " \"MA\", \"MI\", \"MN\", \"MS\", \"MO\", \"MT\", \"NE\", \"NV\", \"NH\", \"NJ\",\n", " \"NM\", \"NY\", \"NC\", \"ND\", \"OH\", \"OK\", \"OR\", \"PA\", \"RI\", \"SC\",\n", " \"SD\", \"TN\", \"TX\", \"UT\", \"VT\", \"VA\", \"WA\", \"WV\", \"WI\", \"WY\"],\n", " \"region\": [\"South\", \"West\", \"West\", \"South\", \"West\", \"West\", \"Northeast\", \"South\", \"South\", \"South\",\n", " \"West\", \"West\", \"Midwest\", \"Midwest\", \"Midwest\", \"Midwest\", \"South\", \"South\", \"Northeast\", \"South\",\n", " \"Northeast\", \"Northeast\", \"Midwest\", \"South\", \"Midwest\", \"West\", \"Midwest\", \"West\", \"Northeast\", \"Northeast\",\n", " \"West\", \"Northeast\", \"South\", \"Midwest\", \"Midwest\", \"South\", \"West\", \"Northeast\", \"Northeast\", \"South\",\n", " \"Midwest\", \"South\", \"West\", \"Northeast\", \"South\", \"West\", \"West\", \"South\", \"Midwest\", \"West\"]\n", "})\n", "issuer_level_df = issuer_level_df.join(state_region, on=[\"State\"], how='left')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# View 2: Bubble Charts, sized by Denial Rates\n", "\n", "I know I am using Percent Denial twice (generally a no-no) but since it's expected to be crowded, it'll help me develop the visualization further." ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "
\n", "" ], "text/plain": [ "alt.Chart(...)" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "percent_denial_fig = (\n", " alt.Chart(issuer_level_df.to_pandas()).mark_circle().encode(\n", " x=alt.X(\"Issuer_Claims_Received:Q\").title(\"Total Number of Claims\"),\n", " y=alt.Y(\"percent_denial:Q\").title(\"Percentage of Claims Denies (%)\"),\n", " size=alt.Size(\"percent_denial:Q\").title(None),\n", " color=alt.Color(\"State:N\"),\n", " tooltip=[\"State:N\", \"Issuer_Name:N\", \"Plan_Type:N\", \"Metal_Level:N\"],\n", " column=\"region:N\",\n", " )\n", " .transform_flatten(\n", " [\"Metal_Level\"]\n", " )\n", " .transform_calculate(\n", " percent_denial = \"datum.Issuer_Claims_Denials/datum.Issuer_Claims_Received\"\n", " )\n", ")\n", "percent_denial_fig\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Honestly, this still doesn't separate well, since there are so many dimensions.\n", "\n", "One of my curiosities is to compare mean denial rates for states, this is harder than you'd think, since you'd ideally " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Leveraging \"Selectors\" in Altair\n", "\n", "Selectors allow Altair to \"communicate\" using selections across different figures, even if they are completely different charts.\n", "\n", "It takes a bit of a learning curve, but once selection like `state_selection` is assigned to a column (in this case, `State`), you can use `state_selection` pretty universally across Altair's expressions:\n", "- `.add_params(state_selection)`\n", "- `.transform_filter(state_selection)`\n", "- `alt.condition(state_selection...)`" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [], "source": [ "state_selection = alt.selection_point(fields=['State'])" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [], "source": [ "state_level_average = (\n", " alt.Chart(issuer_level_df.to_pandas()).mark_bar().encode(\n", " x=alt.X(\"mean_percent_denial:Q\").title(\"Percentage of Claims Denies (%)\"),\n", " y=alt.Y(\"State:N\").title(\"State\").sort(\"-x\"),\n", " color=alt.condition(\n", " state_selection,\n", " alt.Color(\"region:N\"),\n", " alt.value(\"gray\")\n", " )\n", " )\n", " .transform_aggregate(\n", " total_claims = \"sum(Issuer_Claims_Received)\",\n", " total_denials = \"sum(Issuer_Claims_Denials)\",\n", " groupby = [\"State\", \"region\"]\n", " )\n", " .transform_calculate(\n", " mean_percent_denial = \"datum.total_denials / datum.total_claims\"\n", " )\n", " .add_params(state_selection)\n", ")" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "
\n", "" ], "text/plain": [ "alt.Chart(...)" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "state_level_average" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [], "source": [ "# This is likely a bad habit but it makes layout easier. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# View 3: Add State-level Deniers (Top 10)\n", "\n", "Since it's tough to show ALL issuers, lets go top 10 and bottom 10 deniers\n", "\n", "One of the key tool is to rank and then filter using Altair functions" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "
\n", "" ], "text/plain": [ "alt.Chart(...)" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "top_issuers = (\n", " alt.Chart(issuer_level_df.to_pandas()).mark_bar().encode(\n", " x=alt.X(\"Issuer_Name:N\").sort(\"-y\"),\n", " y=alt.Y(\"percent_denial:Q\").stack(None)\n", " ).transform_aggregate(\n", " claims_received = \"mean(Issuer_Claims_Received)\", # There is no \"drop duplicates\" related to Metal (Gold/Silver/Bronze)\n", " claims_denied = \"mean(Issuer_Claims_Denials)\", # There is no \"drop duplicates\" related to Metal (Gold/Silver/Bronze)\n", " groupby=[\"Issuer_Name\", \"State\", \"region\"]\n", " ).transform_calculate(\n", " percent_denial = \"datum.claims_denied / datum.claims_received\"\n", " ).transform_filter(\n", " alt.datum.Issuer_Claims_Received != None\n", " )\n", ")\n", "top_issuers" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Create an interactive chart, piecing view 1, view 2, view 3 together" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Bad habit " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "
\n", "" ], "text/plain": [ "alt.HConcatChart(...)" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " (state_level_average.encode(\n", " color=alt.condition(\n", " state_selection,\n", " alt.Color(\"region:N\"),\n", " alt.value(\"lightgray\")\n", " ),\n", " opacity=alt.condition(\n", " state_selection,\n", " alt.value(1.0),\n", " alt.value(0.2)\n", " )\n", " ).properties(\n", " width=100,\n", " height=500\n", " )\n", " ) | \n", " (\n", " percent_denial_fig.add_params(state_selection).encode(\n", " color=alt.condition(\n", " state_selection,\n", " alt.Color(\"region:N\"),\n", " alt.value(\"lightgray\")\n", " ),\n", " opacity=alt.condition(\n", " state_selection,\n", " alt.value(1.0),\n", " alt.value(0.2)\n", " )\n", " )\n", " .properties(\n", " width=100,\n", " height=200\n", " ) & top_issuers.encode(\n", " opacity=alt.condition(\n", " state_selection,\n", " alt.value(1.0),\n", " alt.value(0.2)\n", " ),\n", " color=alt.condition(\n", " state_selection,\n", " alt.Color(\"region:N\"),\n", " alt.value(\"lightgray\")\n", " ),\n", " ).add_params(state_selection).transform_filter(state_selection).transform_window(\n", " rank='rank(percent_denial)',\n", " sort=[alt.SortField('percent_denial', order='descending')] \n", " ).transform_filter(\n", " alt.datum.rank <= 20\n", " ).properties(\n", " height=200\n", " )\n", " )\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "data-expressions-XOHyUNvQ-py3.10", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.8" }, "orig_nbformat": 4 }, "nbformat": 4, "nbformat_minor": 2 }