2025 Medical Expenditure Panel Survey, Household Component (MEPS-HC)

Reproducing the 2025 MEPS Workshop in Python (svy)

true
The 2025 MEPS-HC workshop, originally in R, reproduced in Python with svy: survey-weighted health expenditure estimates from the 2023 public-use files.
Author

Mamadou S. Diallo, Ph.D.

Published

October 27, 2025

Modified

October 8, 2026

In this document, we use Python and the svy library to reproduce the 2025 MEPS Workshop (originally conducted in R, see GitHub Repository).

To follow along and run the code locally, download the following 2023 MEPS public-use files:

TipCreate a Python environment using uv

The steps below give you a fast, reproducible setup for running the MEPS workshop with svy.

  • If you do not have uv, install it. See instructions at https://docs.astral.sh/uv/getting-started/installation/
  • restart your shell so uv is on PATH, and from your root project run: uv venv -p 3.13
  • Initialize the environment with uv init
  • Add the requirements: uv add "svy[report]==0.32.1"

Since it’s a simple analysis, you can store the datasets and code in the root project folder.

Imports used throughout the workshop and some general settings.

import polars as pl
import svy

svy.Estimate.PRINT_WIDTH = 89
svy.Estimate.DECIMALS = 4

Part I — Estimates for National Health Care for the U.S. Civilian Non-Institutionalized Population, 2023

Exploration of the Relevant Data from the 2023 MEPS-HC

First, we read the 2023 Full-Year Consolidated file from local storage using svy.read_stata. Then we subset the variables needed for this tutorial, derive two helper variables, and run quick QC checks.

Read the Stata file via svy

fyc23 = svy.read_stata(path="./h251.dta")

print(fyc23)
shape: (18_919, 1_374)
┌─────────┬─────┬────────────┬───────┬───┬──────────────┬──────────────┬────────┬────────┐
│ DUID    ┆ PID ┆ DUPERSID   ┆ PANEL ┆ … ┆ FAMWT23C     ┆ SAQWT23F     ┆ VARSTR ┆ VARPSU │
│ ---     ┆ --- ┆ ---        ┆ ---   ┆   ┆ ---          ┆ ---          ┆ ---    ┆ ---    │
│ i64     ┆ i64 ┆ str        ┆ i64   ┆   ┆ f64          ┆ f64          ┆ i64    ┆ i64    │
╞═════════╪═════╪════════════╪═══════╪═══╪══════════════╪══════════════╪════════╪════════╡
│ 2790002 ┆ 101 ┆ 2790002101 ┆ 27    ┆ … ┆ 11158.817826 ┆ 13221.315673 ┆ 2019   ┆ 1      │
│ 2790002 ┆ 102 ┆ 2790002102 ┆ 27    ┆ … ┆ 11158.817826 ┆ 0.0          ┆ 2019   ┆ 1      │
│ 2790004 ┆ 101 ┆ 2790004101 ┆ 27    ┆ … ┆ 28540.745942 ┆ 29999.277476 ┆ 2084   ┆ 1      │
│ 2790006 ┆ 101 ┆ 2790006101 ┆ 27    ┆ … ┆ 10821.040689 ┆ 11144.513916 ┆ 2113   ┆ 1      │
│ 2790006 ┆ 102 ┆ 2790006102 ┆ 27    ┆ … ┆ 10821.040689 ┆ 0.0          ┆ 2113   ┆ 1      │
│ …       ┆ …   ┆ …          ┆ …     ┆ … ┆ …            ┆ …            ┆ …      ┆ …      │
│ 2819784 ┆ 105 ┆ 2819784105 ┆ 28    ┆ … ┆ 5126.030033  ┆ 0.0          ┆ 2015   ┆ 1      │
│ 2819788 ┆ 101 ┆ 2819788101 ┆ 28    ┆ … ┆ 3608.933864  ┆ 4902.751776  ┆ 2005   ┆ 1      │
│ 2819792 ┆ 101 ┆ 2819792101 ┆ 28    ┆ … ┆ 26238.599825 ┆ 0.0          ┆ 2012   ┆ 3      │
│ 2819793 ┆ 101 ┆ 2819793101 ┆ 28    ┆ … ┆ 15780.213332 ┆ 0.0          ┆ 2004   ┆ 1      │
│ 2819793 ┆ 102 ┆ 2819793102 ┆ 28    ┆ … ┆ 15780.213332 ┆ 0.0          ┆ 2004   ┆ 1      │
└─────────┴─────┴────────────┴───────┴───┴──────────────┴──────────────┴────────┴────────┘

Subset to columns used in the tutorial using polars.

fyc23_sub = fyc23.select(
    ["AGELAST", "TOTEXP23", "DUPERSID", "VARSTR", "VARPSU", "PERWT23F"]
)

print(fyc23_sub.head())
shape: (5, 6)
┌─────────┬──────────┬────────────┬────────┬────────┬──────────────┐
│ AGELAST ┆ TOTEXP23 ┆ DUPERSID   ┆ VARSTR ┆ VARPSU ┆ PERWT23F     │
│ ---     ┆ ---      ┆ ---        ┆ ---    ┆ ---    ┆ ---          │
│ i64     ┆ i64      ┆ str        ┆ i64    ┆ i64    ┆ f64          │
╞═════════╪══════════╪════════════╪════════╪════════╪══════════════╡
│ 58      ┆ 646      ┆ 2790002101 ┆ 2019   ┆ 1      ┆ 11664.426815 │
│ 27      ┆ 1894     ┆ 2790002102 ┆ 2019   ┆ 1      ┆ 32212.113596 │
│ 49      ┆ 986      ┆ 2790004101 ┆ 2084   ┆ 1      ┆ 21944.142826 │
│ 75      ┆ 1312     ┆ 2790006101 ┆ 2113   ┆ 1      ┆ 10328.00953  │
│ 23      ┆ 0        ┆ 2790006102 ┆ 2113   ┆ 1      ┆ 17430.521357 │
└─────────┴──────────┴────────────┴────────┴────────┴──────────────┘

Derive helper variables:

  • has_exp: indicator for any total expenditure
  • age_cat: <65 vs 65+
fyc23x = fyc23_sub.with_columns(
    has_exp=pl.col("TOTEXP23").gt(pl.lit(0)),
    age_cat=pl.when(pl.col("AGELAST") < 65)
    .then(pl.lit("<65"))
    .otherwise(pl.lit("65+")),
)

print(fyc23x.head())
shape: (5, 8)
┌─────────┬──────────┬────────────┬────────┬────────┬──────────────┬─────────┬─────────┐
│ AGELAST ┆ TOTEXP23 ┆ DUPERSID   ┆ VARSTR ┆ VARPSU ┆ PERWT23F     ┆ has_exp ┆ age_cat │
│ ---     ┆ ---      ┆ ---        ┆ ---    ┆ ---    ┆ ---          ┆ ---     ┆ ---     │
│ i64     ┆ i64      ┆ str        ┆ i64    ┆ i64    ┆ f64          ┆ bool    ┆ str     │
╞═════════╪══════════╪════════════╪════════╪════════╪══════════════╪═════════╪═════════╡
│ 58      ┆ 646      ┆ 2790002101 ┆ 2019   ┆ 1      ┆ 11664.426815 ┆ true    ┆ <65     │
│ 27      ┆ 1894     ┆ 2790002102 ┆ 2019   ┆ 1      ┆ 32212.113596 ┆ true    ┆ <65     │
│ 49      ┆ 986      ┆ 2790004101 ┆ 2084   ┆ 1      ┆ 21944.142826 ┆ true    ┆ <65     │
│ 75      ┆ 1312     ┆ 2790006101 ┆ 2113   ┆ 1      ┆ 10328.00953  ┆ true    ┆ 65+     │
│ 23      ┆ 0        ┆ 2790006102 ┆ 2113   ┆ 1      ┆ 17430.521357 ┆ false   ┆ <65     │
└─────────┴──────────┴────────────┴────────┴────────┴──────────────┴─────────┴─────────┘

QC 1: Two-way counts of derived variables

qc_exp_by_age = fyc23x.group_by(["has_exp", "age_cat"]).agg(pl.len())

print(qc_exp_by_age)
shape: (4, 3)
┌─────────┬─────────┬───────┐
│ has_exp ┆ age_cat ┆ len   │
│ ---     ┆ ---     ┆ ---   │
│ bool    ┆ str     ┆ u32   │
╞═════════╪═════════╪═══════╡
│ true    ┆ 65+     ┆ 4486  │
│ true    ┆ <65     ┆ 11778 │
│ false   ┆ <65     ┆ 2497  │
│ false   ┆ 65+     ┆ 158   │
└─────────┴─────────┴───────┘

QC 2: Expenditure ranges by has_exp

qc_exp = fyc23x.group_by(["has_exp"]).agg(
    pl.col("TOTEXP23").min().alias("min"),
    pl.col("TOTEXP23").max().alias("max"),
)

print(qc_exp)
shape: (2, 3)
┌─────────┬─────┬────────┐
│ has_exp ┆ min ┆ max    │
│ ---     ┆ --- ┆ ---    │
│ bool    ┆ i64 ┆ i64    │
╞═════════╪═════╪════════╡
│ true    ┆ 1   ┆ 574675 │
│ false   ┆ 0   ┆ 0      │
└─────────┴─────┴────────┘

QC 3: Age ranges by age_cat

qc_age = fyc23x.group_by(["age_cat"]).agg(
    pl.col("AGELAST").min().alias("min"),
    pl.col("AGELAST").max().alias("max"),
)

print(qc_age)
shape: (2, 3)
┌─────────┬─────┬─────┐
│ age_cat ┆ min ┆ max │
│ ---     ┆ --- ┆ --- │
│ str     ┆ i64 ┆ i64 │
╞═════════╪═════╪═════╡
│ <65     ┆ 0   ┆ 64  │
│ 65+     ┆ 65  ┆ 85  │
└─────────┴─────┴─────┘

Estimation of Expenses

Sample design

First, we are going to define the sample

from svy import Sample, Design

# lonely PSUs: R's options(survey.lonely.psu = 'adjust')
fyc23_design = Design(stratum="VARSTR", psu="VARPSU", wgt="PERWT23F", singleton="center")

fyc23_sample = Sample(data=fyc23x, design=fyc23_design)

print(fyc23_sample)
╭───────────────── Sample ─────────────────╮
│ Survey Data                              │
│   Rows     : 18919                       │
│   Columns  : 8                           │
│   Strata   : 105                         │
│   PSUs     : 262                         │
│                                          │
│ Survey Design                            │
│   Case id            None                │
│   Wave               None                │
│   Stratum            VARSTR              │
│   PSU                VARPSU              │
│   SSU                None                │
│   Weight             PERWT23F            │
│   With replacement   False               │
│   Prob               None                │
│   Hit                None                │
│   MOS                None                │
│   Population size    None                │
│   Singleton          Singleton('center') │
│   Replicate weights  None                │
╰──────────────────────────────────────────╯

Checking for singletons

Strata with only one PSU (singletons) need a rule for variance estimation. The design above declares singleton="center", the equivalent of the workshop’s options(survey.lonely.psu = "adjust") in R. We can list the singletons as follows

# List of strata with only one PSU
fyc23_sample.singletons
shape: (0, 4)
VARSTR VARPSU n handled
i64 i64 u32 str

The sample does not have singletons.

Overall expenses (national totals)

tot_exp = fyc23_sample.estimation.total(y="TOTEXP23")

print(tot_exp)
╭────────────────────────────── Estimate: TOTAL (TAYLOR) ───────────────────────────────╮
│ y: TOTEXP23                                                                           │
│                                                                                       │
│                est                 se                lci                uci   cv (%)  │
│  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━  │
│  2,504,715,663,92…   72,782,531,974.…   2,360,956,395,5…   2,648,474,932,3…   2.9058  │
│                                                                                       │
╰───────────────────────────────────────────────────────────────────────────────────────╯

Percentage of persons with an expense

has_exp = fyc23_sample.estimation.prop(y="has_exp")

print(has_exp)
╭─────────────── Estimate: PROP (TAYLOR) ────────────────╮
│                                                        │
│  has_exp      est       se      lci      uci   cv (%)  │
│  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━  │
│  false     0.1445   0.0050   0.1348   0.1547   3.4807  │
│  true      0.8555   0.0050   0.8453   0.8652   0.5879  │
│                                                        │
╰────────────────────────────────────────────────────────╯

Mean expense per person

avg_exp = fyc23_sample.estimation.mean(y="TOTEXP23")

print(avg_exp)
╭───────────────── Estimate: MEAN (TAYLOR) ──────────────────╮
│ y: TOTEXP23                                                │
│                                                            │
│         est         se          lci          uci   cv (%)  │
│  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━  │
│  7,487.2616   173.3692   7,144.8246   7,829.6986   2.3155  │
│                                                            │
╰────────────────────────────────────────────────────────────╯

Mean expense per person with an expense

The workshop restricts these estimates with R’s subset(), which keeps the full design. In svy, the where= argument does the same; filtering the rows instead would drop PSUs from the design and change the standard errors.

avg_exp1 = fyc23_sample.estimation.mean(y="TOTEXP23", where=svy.col("has_exp"))

print(avg_exp1)
╭───────────────── Estimate: MEAN (TAYLOR) ──────────────────╮
│ y: TOTEXP23                                                │
│ where: has_exp                                             │
│                                                            │
│         est         se          lci          uci   cv (%)  │
│  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━  │
│  8,751.8209   187.2480   8,381.9707   9,121.6712   2.1395  │
│                                                            │
╰────────────────────────────────────────────────────────────╯

Mean expense per person with an expense, by age category

avg_exp1_by_age_cat = fyc23_sample.estimation.mean(
    y="TOTEXP23", by="age_cat", where=svy.col("has_exp")
)

print(avg_exp1_by_age_cat)
╭──────────────────────── Estimate: MEAN (TAYLOR) ────────────────────────╮
│ y: TOTEXP23                                                             │
│ where: has_exp                                                          │
│                                                                         │
│  age_cat           est         se           lci           uci   cv (%)  │
│  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━  │
│  65+       16,000.1324   517.8130   14,977.3538   17,022.9111   3.2363  │
│  <65        6,853.3919   196.1686    6,465.9217    7,240.8620   2.8624  │
│                                                                         │
╰─────────────────────────────────────────────────────────────────────────╯

Median expense per person with an expense, by age category

median_exp1_by_age_cat = fyc23_sample.estimation.median(
    y="TOTEXP23", by="age_cat", where=svy.col("has_exp")
)

print(median_exp1_by_age_cat)
╭───────────── Estimate: MEDIAN (TAYLOR, q_method=higher) ─────────────╮
│ y: TOTEXP23                                                          │
│ where: has_exp                                                       │
│                                                                      │
│  age_cat          est         se          lci          uci   cv (%)  │
│  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━  │
│  65+       6,080.0000   227.0669   5,764.0000   6,661.0000   3.7347  │
│  <65       1,751.0000    55.6909   1,653.0000   1,873.0000   3.1805  │
│                                                                      │
╰──────────────────────────────────────────────────────────────────────╯

Part II - Link the MEPS-HC Medical Conditions File to the Office-Based Medical Visits File for Estimation

Merging Relevant Files

Load Stata data files using svy.read_stata

ob23 = svy.read_stata(path="./h248g.dta")
cond23 = svy.read_stata(path="./h249.dta")
clnk23 = svy.read_stata(path="./h248if1.dta")
fyc23 = svy.read_stata(path="./h251.dta")

Keep only needed variables

ob23x = ob23.select(["DUPERSID", "EVNTIDX", "OBXP23X"])
cond23x = cond23.select(
    ["DUPERSID", "CONDIDX", "ICD10CDX", "CCSR1X", "CCSR2X", "CCSR3X", "CCSR4X"]
)
fyc23_sub = fyc23.select(
    ["DUPERSID", "AGELAST", "VARSTR", "VARPSU", "PERWT23F"]
)

Prepare data for estimation

cond_combos = cond23.group_by(["CCSR1X", "CCSR2X", "CCSR3X", "CCSR4X"]).agg(
    pl.len()
)

print(cond_combos.sort(by=["CCSR1X", "CCSR2X", "CCSR3X", "CCSR4X"]))
shape: (229, 5)
┌────────┬────────┬────────┬────────┬─────┐
│ CCSR1X ┆ CCSR2X ┆ CCSR3X ┆ CCSR4X ┆ len │
│ ---    ┆ ---    ┆ ---    ┆ ---    ┆ --- │
│ str    ┆ str    ┆ str    ┆ str    ┆ u32 │
╞════════╪════════╪════════╪════════╪═════╡
│ -15    ┆ -1     ┆ -1     ┆ -1     ┆ 396 │
│ BLD000 ┆ -1     ┆ -1     ┆ -1     ┆ 127 │
│ BLD000 ┆ CIR000 ┆ -1     ┆ -1     ┆ 1   │
│ BLD000 ┆ NEO000 ┆ -1     ┆ -1     ┆ 5   │
│ BLD001 ┆ -1     ┆ -1     ┆ -1     ┆ 71  │
│ …      ┆ …      ┆ …      ┆ …      ┆ …   │
│ SYM013 ┆ -1     ┆ -1     ┆ -1     ┆ 561 │
│ SYM014 ┆ -1     ┆ -1     ┆ -1     ┆ 313 │
│ SYM015 ┆ -1     ┆ -1     ┆ -1     ┆ 223 │
│ SYM016 ┆ -1     ┆ -1     ┆ -1     ┆ 681 │
│ SYM017 ┆ -1     ┆ -1     ┆ -1     ┆ 460 │
└────────┴────────┴────────┴────────┴─────┘
cols = ["CCSR1X", "CCSR2X", "CCSR3X", "CCSR4X"]

cancer = cond23x.filter(
    (
        pl.any_horizontal(pl.col(cols).str.contains("NEO"))
        | pl.any_horizontal(pl.col(cols) == "FAC006")
    )
    & pl.all_horizontal(pl.col(cols) != "NEO073")
)
cancers = cancer.group_by(
    ["ICD10CDX", "CCSR1X", "CCSR2X", "CCSR3X", "CCSR4X"]
).agg(pl.len())

print(cancers.sort(by=["ICD10CDX", "CCSR1X", "CCSR2X", "CCSR3X", "CCSR4X"]))
shape: (16, 6)
┌──────────┬────────┬────────┬────────┬────────┬─────┐
│ ICD10CDX ┆ CCSR1X ┆ CCSR2X ┆ CCSR3X ┆ CCSR4X ┆ len │
│ ---      ┆ ---    ┆ ---    ┆ ---    ┆ ---    ┆ --- │
│ str      ┆ str    ┆ str    ┆ str    ┆ str    ┆ u32 │
╞══════════╪════════╪════════╪════════╪════════╪═════╡
│ -15      ┆ BLD000 ┆ NEO000 ┆ -1     ┆ -1     ┆ 5   │
│ -15      ┆ NEO000 ┆ -1     ┆ -1     ┆ -1     ┆ 273 │
│ C18      ┆ NEO015 ┆ -1     ┆ -1     ┆ -1     ┆ 44  │
│ C34      ┆ NEO022 ┆ -1     ┆ -1     ┆ -1     ┆ 42  │
│ C43      ┆ NEO025 ┆ -1     ┆ -1     ┆ -1     ┆ 119 │
│ …        ┆ …      ┆ …      ┆ …      ┆ …      ┆ …   │
│ C73      ┆ NEO050 ┆ -1     ┆ -1     ┆ -1     ┆ 49  │
│ C95      ┆ NEO064 ┆ -1     ┆ -1     ┆ -1     ┆ 46  │
│ D04      ┆ NEO028 ┆ -1     ┆ -1     ┆ -1     ┆ 97  │
│ D48      ┆ NEO072 ┆ -1     ┆ -1     ┆ -1     ┆ 58  │
│ D49      ┆ NEO072 ┆ -1     ┆ -1     ┆ -1     ┆ 78  │
└──────────┴────────┴────────┴────────┴────────┴─────┘

Many-to-many joins via CLNK crosswalk, then OB events

cancer_merged = (
    cancer
    .join(clnk23, on=["DUPERSID", "CONDIDX"], how="inner")   # m:m join ok
    .join(ob23x,   on=["DUPERSID", "EVNTIDX"], how="inner")  # m:m join ok
    .with_columns(pl.lit(1).alias("ob_visit"))               # indicator
)

QC: Check EVENTYPE distribution

clnk_counts = (
    clnk23.group_by("EVENTYPE").agg(pl.len().alias("n")).sort("EVENTYPE")
)
print(clnk_counts)

cm_counts = (
    cancer_merged.group_by("EVENTYPE")
    .agg(pl.len().alias("n"))
    .sort("EVENTYPE")
)
print(cm_counts)
shape: (6, 2)
┌──────────┬────────┐
│ EVENTYPE ┆ n      │
│ ---      ┆ ---    │
│ i64      ┆ u32    │
╞══════════╪════════╡
│ 1        ┆ 145548 │
│ 2        ┆ 22177  │
│ 3        ┆ 4294   │
│ 4        ┆ 2153   │
│ 7        ┆ 9045   │
│ 8        ┆ 97941  │
└──────────┴────────┘
shape: (1, 2)
┌──────────┬──────┐
│ EVENTYPE ┆ n    │
│ ---      ┆ ---  │
│ i64      ┆ u32  │
╞══════════╪══════╡
│ 1        ┆ 4362 │
└──────────┴──────┘

Example: Same event treating multiple cancers for same person

example_evts = cancer_merged.filter(pl.col("DUPERSID") == "2790405102")

print(example_evts)
shape: (3, 13)
┌────────────┬───────────────┬──────────┬────────┬───┬──────────┬───────┬─────────┬──────────┐
│ DUPERSID   ┆ CONDIDX       ┆ ICD10CDX ┆ CCSR1X ┆ … ┆ EVENTYPE ┆ PANEL ┆ OBXP23X ┆ ob_visit │
│ ---        ┆ ---           ┆ ---      ┆ ---    ┆   ┆ ---      ┆ ---   ┆ ---     ┆ ---      │
│ str        ┆ str           ┆ str      ┆ str    ┆   ┆ i64      ┆ i64   ┆ f64     ┆ i32      │
╞════════════╪═══════════════╪══════════╪════════╪═══╪══════════╪═══════╪═════════╪══════════╡
│ 2790405102 ┆ 2790405102007 ┆ C61      ┆ NEO039 ┆ … ┆ 1        ┆ 27    ┆ 184.59  ┆ 1        │
│ 2790405102 ┆ 2790405102005 ┆ D04      ┆ NEO028 ┆ … ┆ 1        ┆ 27    ┆ 715.29  ┆ 1        │
│ 2790405102 ┆ 2790405102006 ┆ D48      ┆ NEO072 ┆ … ┆ 1        ┆ 27    ┆ 715.29  ┆ 1        │
└────────────┴───────────────┴──────────┴────────┴───┴──────────┴───────┴─────────┴──────────┘

De-Duplicate by EVNTIDX Per Person

cancer_unique = cancer_merged.unique(
    subset=["DUPERSID", "EVNTIDX"], keep="first", maintain_order=True
)

Check the example person after de-duplication

print(cancer_unique.filter(pl.col("DUPERSID") == "2790405102"))
shape: (2, 13)
┌────────────┬───────────────┬──────────┬────────┬───┬──────────┬───────┬─────────┬──────────┐
│ DUPERSID   ┆ CONDIDX       ┆ ICD10CDX ┆ CCSR1X ┆ … ┆ EVENTYPE ┆ PANEL ┆ OBXP23X ┆ ob_visit │
│ ---        ┆ ---           ┆ ---      ┆ ---    ┆   ┆ ---      ┆ ---   ┆ ---     ┆ ---      │
│ str        ┆ str           ┆ str      ┆ str    ┆   ┆ i64      ┆ i64   ┆ f64     ┆ i32      │
╞════════════╪═══════════════╪══════════╪════════╪═══╪══════════╪═══════╪═════════╪══════════╡
│ 2790405102 ┆ 2790405102007 ┆ C61      ┆ NEO039 ┆ … ┆ 1        ┆ 27    ┆ 184.59  ┆ 1        │
│ 2790405102 ┆ 2790405102005 ┆ D04      ┆ NEO028 ┆ … ┆ 1        ┆ 27    ┆ 715.29  ┆ 1        │
└────────────┴───────────────┴──────────┴────────┴───┴──────────┴───────┴─────────┴──────────┘

Aggregate to person-Level

pers = (
    cancer_unique.group_by("DUPERSID")
    .agg(
        pl.col("OBXP23X")
        .sum()
        .alias("pers_ob_exp"),  # total cancer office-visit exp
        pl.col("ob_visit")
        .sum()
        .alias("ob_visits"),  # total number of cancer office visits
    )
    .with_columns(any_OB=pl.lit(1))  # indicator (all 1's in this subset)
)

Revisit the example person

print(pers.filter(pl.col("DUPERSID") == "2790405102"))
shape: (1, 4)
┌────────────┬─────────────┬───────────┬────────┐
│ DUPERSID   ┆ pers_ob_exp ┆ ob_visits ┆ any_OB │
│ ---        ┆ ---         ┆ ---       ┆ ---    │
│ str        ┆ f64         ┆ i32       ┆ i32    │
╞════════════╪═════════════╪═══════════╪════════╡
│ 2790405102 ┆ 899.88      ┆ 2         ┆ 1      │
└────────────┴─────────────┴───────────┴────────┘

Left-join person-level cancer statistics onto FYC

fyc23_cancer = (
    fyc23x.join(pers, on="DUPERSID", how="left")
    # replace NA with 0 for the joined measures
    .with_columns(pl.col(["ob_visits", "any_OB", "pers_ob_exp"]).fill_null(0))
)

print(fyc23_cancer)
shape: (18_919, 11)
┌─────────┬──────────┬────────────┬────────┬───┬─────────┬─────────────┬───────────┬────────┐
│ AGELAST ┆ TOTEXP23 ┆ DUPERSID   ┆ VARSTR ┆ … ┆ age_cat ┆ pers_ob_exp ┆ ob_visits ┆ any_OB │
│ ---     ┆ ---      ┆ ---        ┆ ---    ┆   ┆ ---     ┆ ---         ┆ ---       ┆ ---    │
│ i64     ┆ i64      ┆ str        ┆ i64    ┆   ┆ str     ┆ f64         ┆ i32       ┆ i32    │
╞═════════╪══════════╪════════════╪════════╪═══╪═════════╪═════════════╪═══════════╪════════╡
│ 58      ┆ 646      ┆ 2790002101 ┆ 2019   ┆ … ┆ <65     ┆ 0.0         ┆ 0         ┆ 0      │
│ 27      ┆ 1894     ┆ 2790002102 ┆ 2019   ┆ … ┆ <65     ┆ 0.0         ┆ 0         ┆ 0      │
│ 49      ┆ 986      ┆ 2790004101 ┆ 2084   ┆ … ┆ <65     ┆ 0.0         ┆ 0         ┆ 0      │
│ 75      ┆ 1312     ┆ 2790006101 ┆ 2113   ┆ … ┆ 65+     ┆ 283.28      ┆ 3         ┆ 1      │
│ 23      ┆ 0        ┆ 2790006102 ┆ 2113   ┆ … ┆ <65     ┆ 0.0         ┆ 0         ┆ 0      │
│ …       ┆ …        ┆ …          ┆ …      ┆ … ┆ …       ┆ …           ┆ …         ┆ …      │
│ 11      ┆ 4585     ┆ 2819784105 ┆ 2015   ┆ … ┆ <65     ┆ 0.0         ┆ 0         ┆ 0      │
│ 37      ┆ 6820     ┆ 2819788101 ┆ 2005   ┆ … ┆ <65     ┆ 0.0         ┆ 0         ┆ 0      │
│ 24      ┆ 2549     ┆ 2819792101 ┆ 2012   ┆ … ┆ <65     ┆ 0.0         ┆ 0         ┆ 0      │
│ 22      ┆ 1680     ┆ 2819793101 ┆ 2004   ┆ … ┆ <65     ┆ 0.0         ┆ 0         ┆ 0      │
│ 50      ┆ 7802     ┆ 2819793102 ┆ 2004   ┆ … ┆ <65     ┆ 0.0         ┆ 0         ┆ 0      │
└─────────┴──────────┴────────────┴────────┴───┴─────────┴─────────────┴───────────┴────────┘

QC: Should match original FYC row count

fyc23x.height == fyc23_cancer.height
True
Back to top