--- title: "Exploring Descriptive Statistics in HHS's Medical Expenditure Panel Survey Data" author: "NURS 60N" execute: echo: false message: false error: false warning: false format: html: toc: true toc-depth: 5 toc-location: left number-sections: true downloads: [pdf] theme: light: lumen dark: solar pdf: toc: true toc-depth: 5 number-sections: true --- ```{r eval = FALSE, include=FALSE, cache=TRUE} library(readxl) library(dplyr) file_path <- "h239e.xlsx" df <- read_excel(file_path) df_selected <- df %>% select( DUPERSID, EKG_M18, ERDTC22X, ERDMD22X, ERFTC22X, ERTC22X, LABTEST_M18, MAMMOG_M18, MEDPRESC, MRI_M18, RCVVAC_M18, SONOGRAM_M18, SURGPROC, VSTCTGRY, VSTRELCN, XRAYS_M18 ) %>% filter(EKG_M18 %in% c(1, 2)) %>% filter(if_any(everything(), ~ . > 0 | is.character(.))) %>% distinct(DUPERSID, .keep_all = TRUE) %>% rename( "Did Pt Have EKG During Visit" = EKG_M18, "Total Doctor Charge" = ERDTC22X, "Doctor Amount Paid, Medicaid" = ERDMD22X, "Total Facility Charge" = ERFTC22X, "Total Charge for Event" = ERTC22X, "This Visit Did Pt Have Lab Tests" = LABTEST_M18, "This Visit Did Pt Have a Mammogram" = MAMMOG_M18, "Any Medicine Prescribed for Pt This Visit" = MEDPRESC, "This Visit Did Pt Have an MRI/CATscan" = MRI_M18, "This Visit Did Pt Receive A Vaccination" = RCVVAC_M18, "This Visit Did Pt Have Sonogram Or Ultrsd" = SONOGRAM_M18, "Was Surg Proc Performed on Pt This Visit" = SURGPROC, "Best Category for Care Pt Recv on Visit Dt" = VSTCTGRY, "This Visit Related to Spec Condition" = VSTRELCN, "This Visit Did Pt Have X-Rays" = XRAYS_M18 ) ``` ```{r eval = FALSE, include=FALSE, cache=TRUE} file_path <- "h243.xlsx" df_h243 <- read_excel(file_path) %>% select( DUPERSID, ADAGE42, ADMOOD42, ADSEX42, AFRDCA42, ERTOT22, FAMINC22, FAMS1231, FOODST22, GENDRP42, HISPANX, INSURC22, RACETHX ) %>% distinct(DUPERSID, .keep_all = TRUE) %>% rename( "Patient Age" = ADAGE42, "Anxious/Depressed" = ADMOOD42, "Gender" = ADSEX42, "Could Not Afford Medical Care" = AFRDCA42, "Emergency Room Visits 2022" = ERTOT22, "Family's Total Income" = FAMINC22, "Family Size of Responding Family" = FAMS1231, "Did Anyone Purchase Food Stamps" = FOODST22, "Is Provider Male or Female" = GENDRP42, "Hispanic Ethnicity" = HISPANX, "Full Year Insurance Coverage Status 2022" = INSURC22, "Race/Ethnicity" = RACETHX ) ``` ```{r eval = FALSE, include=FALSE, cache=TRUE} mdf <- merge(df_selected, df_h243, by = "DUPERSID", all.x = TRUE) ``` ```{r eval = FALSE, include=FALSE, cache=TRUE} meps_2022_ed <- mdf %>% mutate(across(everything(), ~ ifelse(. < 0 | . == 95, NA, .))) meps_2022_ed <- meps_2022_ed %>% mutate(across(everything(), ~ ifelse(. == 2, 0, .))) meps_2022_ed_sample <- meps_2022_ed %>% slice_sample(n = 500) meps_2022_ed_sample <- meps_2022_ed_sample %>% rename("Patient ID" = DUPERSID) write.table(meps_2022_ed, file = "meps_2022_ed_all.csv", sep = ",", row.names = FALSE) write.table(meps_2022_ed_sample, file = "meps_2022_ed_sample.csv", sep = ",", row.names = FALSE) ``` \newpage # About the Medical Expenditure Panel Survey and These Data At least until our current increasingly-fascist government takes it down, the [Department of Health and Human Services'](https://www.hhs.gov/) Agency for Healthcare Research and Quality ([AHRQ](https://www.ahrq.gov/)) maintains a yearly survey of medical expenditures, the Medical Expenditure Panel Survey ([MEPS](https://meps.ahrq.gov/mepsweb/index.jsp)). According to their site, MEPS "is a set of large-scale surveys of families and individuals, their medical providers, and employers across the United States. MEPS is the most complete source of data on the cost and use of health care and health insurance coverage." Among these publicly-available data are per annum visits to emergency departments. Excerpts from the most recent data, for 2022, are prepared for you in at [meps_2022_ed_sample.csv](https://wesamuels.net/courses/statistics/60N/assignments/meps/meps_2022_ed_sample.csv). I have randomly selected and prepped 500 of the ~4500 rows available in the [main data set](https://meps.ahrq.gov/mepsweb/data_stats/download_data_files_detail.jsp?cboPufNumber=HC-239E)^[In the off chance you're interested, how I did so is given this assignment's [.qmd file](MEPS_Descriptives_Assignment.qmd), which was processed via [RStudio](https://posit.co/downloads/).]. The main data set itself is "gathered from a nationally representative sample of the civilian, noninstitutionalized population of the United States and can be used to make estimates of emergency room utilization and expenditures for calendar year 2022. Each record comprises data for one household-reported emergency room [ED] visit." I have combined these with some of the [many other avaialable factors](https://meps.ahrq.gov/mepsweb/data_stats/download_data_files_codebook.jsp?PUFId=H243) to give some insights into possible differences between patients and their costs in the EDs. ***Please note:* Definitions of the variables in the data are given at the end of this assignment in @sec-explanations** # Assignment ## Assignment Goals The goals of this assignment are: 1. Create descriptive statistics that summarize important information about variables 1. Analyze relationships descriptively between variables 1. Present results from the site graphically 1. Write about those results for a general audience ## Instructions 1. Download the prepared [data set](https://wesamuels.net/courses/statistics/60N/assignments/meps/meps_2022_ed_sample.csv) that includes medical expenditures for emergency department visits along with a few demographic variables for those patients. Please note that the data are in a `.csv` file---a "comma-separated values" file---that contains very little formatting and can thus be easily opened in either a spreadsheet or SPSS. 1. Open those data in either MS Excel / [LO](https://www.libreoffice.org/) [Calc](https://www.libreoffice.org/discover/calc/) or SPSS. 1. Use the respective app to explore some of the data, focusing on the use of descriptive statistics (central tendency, dispersion; even correlations are essentially descriptive). - Please review numeric results as well as some graphical ones, such as histograms and bar charts. - Please report the results of 2 -- 4 findings. Among these, please have at least one address a relationship between two variables. This relationship could be a cross table, bar chart comparing means, odds ratios, etc. You do not need to present your results in APA format nor, e.g., include a cover page or reference section. My interest here is in your ability to explore data to find and clearly present interesting findings. ### Using Excel/Calc Use may [use formulas](https://wesamuels.net/courses/statistics/concepts_activities_book/Intro_to_Excel.html#formulas) to compute descriptive statistics, but [using pivot tables](https://wesamuels.net/courses/statistics/concepts_activities_book/Intro_to_Excel.html#pivot-tables-charts) and charts is often faster and certainly more flexible. The [pivot chart interface](https://www.excel-easy.com/examples/pivot-chart.html) in Excel is especially handy sine you can also access the pivot tables there. #### Pivot Tables & Charts in Excel The links in the previous paragraphs should help, but the steps to accessing these pivot tables & charts in Excel are: 1. Insert a pivot table template 1. Select any set of columns (or the whole data set) in your data 1. Go to `Insert > PivotTable` 1. Choose `New Worksheet` and click `OK` 1. Build your pivot table 1. Drag numerical fields (e.g., `Could Not Afford Medical Care`) to `Values` (set to `Sum`, `Average`, `Count`, etc.). 1. Drag categorical fields (e.g., `This Visit Related to Spec Condition`) to `Rows` or `Columns` to group data. 1. Insert a pivot chart 1. Click anywhere in your pivot table 1. Go to `Insert > PivotChart` 1. Choose a chart type (bar, pie, etc.) and click `OK` 1. Adjust & analyze 1. Use filters to explore specific groups 1. Change summary stats by right-clicking `Values > Summarize Values By` (e.g., `Average`, `Count`) The steps to create [pivot tables](https://help.libreoffice.org/latest/en-US/text/scalc/guide/datapilot_createtable.html?DbPAR=CALC) and [charts](https://help.libreoffice.org/latest/en-US/text/scalc/guide/pivotchart_create.html?DbPAR=CALC&System=WIN) are similar in LO Calc. ### Using SPSS SPSS is available through an online virtual interface, [Apporto](https://cuny.apporto.com/). For more on accessing it there, please refer to [these instructions](https://wesamuels.net/courses/statistics/concepts_activities_book/Intro_to_SPSS.html#accessing-spss). (That page also has some basic information about SPSS's [windows](https://wesamuels.net/courses/statistics/concepts_activities_book/Intro_to_SPSS.html#spss-windows), but note that the example there uses different data.) - Viewing descriptive statistics 1. Click `Analyze > Descriptive Statistics > Descriptives` 1. Select variables and move them to the `Variables` box; then click `OK` - Creating a histogram 1. Click `Graphs > Chart Builder` 1. Select `Histogram` and drag it to the preview area 1. Drag your numeric variable to the `X-Axis`; then click `OK` - Creating other simple charts 1. Click `Graphs > Chart Builder` 1. Choose the desired chart type (bar, boxplot, etc.) 1. Drag variables to the appropriate axes or sections; then click `OK` ### Create a Simple Report on Your Findings 1. Copy your selected figures/tables created to a MS Word / LO [Writer](https://www.libreoffice.org/discover/writer/), [etc.](https://www.techrepublic.com/article/5-free-alternatives-to-microsoft-word/) document. (We cannot copy or save the figures created in SPSS on Apporto. We must instead either download the output file onto our own computer or take screen captures of them. Fortunately, this is easy to do in [Windows](https://www.microsoft.com/en-us/windows/learning-center/how-to-screenshot-windows-11), [Mac OS](https://support.apple.com/en-us/HT201361), [Android](https://support.google.com/android/answer/9075928?hl=en), [Firefox](https://support.mozilla.org/en-US/kb/take-screenshots-firefox?redirectslug=firefox-screenshots&redirectlocale=en-US), [etc.](https://www.howtogeek.com/268036/how-to-take-a-screenshot-on-linux/).) 1. In that same document, write about 1 -- 2 paragraphs to describe the results in each figure/table. Please use clear, objective language accessible to a wide audience. This does not need to be in APA format (although it's easy to do if you use a [style template](https://wesamuels.net/courses/statistics/concepts_activities_book/Using_Templates_and_a_Reference_Manager_with_MS_Word.html#style)). 1. After those two figures and concomitant paragraphs, please write ~2 paragraphs summarizing all of your presented results and their possible implications to practice. Thanks. \newpage # Explanations of These Data {#sec-explanations} Many of these variables are "dummy" coded as either `0`s or `1`s. For these variables, a `0` means that that condition is not present while a `1` means that it is present. For example, if there is a `0` in the variable "Did Pt Have EKG During Visit," then that patient did not have an EKG done during that visit; a `1` means they did indeed have an EKG done. The following explanations and tables are from the "[codebook](https://meps.ahrq.gov/mepsweb/data_stats/download_data_files_codebook.jsp?PUFId=H239E)" for these data. [Their full descriptions](https://meps.ahrq.gov/mepsweb/data_stats/download_data_files_codebook.jsp?PUFId=H239E) and more are on [AHRQ](https://www.ahrq.gov/)'s [MEPS website](https://meps.ahrq.gov/mepsweb/). ## Table of Most Variables | Variable | Explanation/Definition | |-------------------------------------------|-------------------------------------------------------| | Patient ID | Unique ID for that visit | | | | | Did Pt Have EKG During Visit | 0 = No EKG; 1 = Had EKG | | | | | Total Doctor Charge | Dollars billed by doctor | | | | | Doctor Amount Paid by Medicaid | Amount of billed doctor dollars were paid by Medicaid | | | | | Total Facility Charge | Total dollars billed to facility | | | | | Total Charge for Event | Total dollar charge for visit | | | | | This Visit Did Pt Have Lab Tests | 0 = No lab tests; 1 = Had lab tests | | | | | This Visit Did Pt Have a Mammogram | 0 = Did not have mammo; 1 = Had mammo | | | | | Any Medicine Prescribed for Pt This Visit | 0 = No med prescribed; 1 = Had meds prescribed | | | | | This Visit Did Pt Have an MRI/CAT Scan | 0 = No MRI/CT; 1 = Had MRI/CAT | | | | | This Visit Did Pt Receive a Vaccination | 0 = No vaccination; 1 = Had vaccine | | | | | This Visit Did Pt Have Sonogram or Ultrsd | 0 = No sono/ultra; 1 = Had sono/ultra | | | | | Was Surg Proc Performed on Pt This Visit | 0 = No surgery; 1 = Had surgery | | | | | This Visit Related to Spec Condition | 0 = Not related to a specific condition;\ 1 = Related to a specific condition | | | | | This Visit Did Pt Have X-Rays | 0 = No X-rays; 1 = Had X-rays | | | | | Patient Age | Patient's age in years | | | | | Was Pt Asked About Anxiety or Depression | 0 = Patient was **not** asked about anxiety and/or depression;\ 1 = Patient was asked about anxiety and/or depression | | | | | Pt Gender | 0 = Patient did **not** identify as male;\ 1 = Patient identified as male | | | | | Could Not Afford Medical Care | 0 = Patient could not afford care;\ 1 = Patient could | | | | | Number of Emergency Room Visits 2022 | Count of number of times patient has been admitted into the ED in 2022 | | | | | Family Total Income | Family's total income in dollars | | | | | Family Size of Responding Family| Number of individuals in patient's family (in addition to the patient themself) | | | | | Did Anyone Purchase with Food Stamps| 0 = Did **not** purchase with food stamps;\ 1 = Purchased with food stamps | | | | | Is Provider Male| 0 = Care provider did **not** identify as male;\ 1 = Care provider identified as male | | | | | Hispanic Ethnicity| 0 = Patient did **not** identify as Hispanic;\ 1 = Patient identified as Hispanic | \newpage ## Best Category for Care Pt Recv This Visit Explanation of levels in [`Best Category for Care Pt Recv This Visit`](https://meps.ahrq.gov/mepsweb/data_stats/download_data_files_codebook.jsp?PUFId=H239E&varName=VSTCTGRY) follows. The "Unweighted" column represents the actual number of survey respondents in each category. The "Weighted" column adjusts these counts to better reflect national population estimates. This is necessary because the MEPS survey over-samples certain groups to improve the accuracy of its estimates. Larger weights mean that responses in that category are scaled up more to represent a larger number of individuals in the U.S. population. For example: - Each respondent who refused care (-7) represents $\frac{69095}{2} = 34,547.5$ people in the U.S. - Each respondent who received psychotherapy/mental health counseling (3) represents $\frac{664954}{50} = 13,299.08$ people in the U.S. This weighting ensures that the survey results are statistically adjusted to match the real-world population rather than just reflecting the sample of people who participated in MEPS. To achieve this, MEPS assigns statistical weights that correct for survey design factors, such as selection probability and non-response adjustments. This allows researchers to generate accurate, representative national estimates from the data. Please note, too, that not all levels appear in the sample I provided for this and some other variables. (Here, e.g., none of the negative levels appear.) | Value | AHRQ's Brief Description | Unweighted | Weighted | |:---:|:-------------------------------|---:|----:| | -15 | CANNOT BE COMPUTED | 4 | 39608 | | -8 | DON'T KNOW | 24 | 368373 | | -7 | REFUSED | 2 | 69095 | | 1 | DIAGNOSIS OR TREATMENT | 2108 | 27608409 | | 2 | EMERGENCY (E.G., ACCIDENT OR INJURY) | 2166 | 28093383 | | 3 | PSYCHOTHERAPY/MENTAL HEALTH COUNSELING | 50 | 664954 | | 4 | FOLLOW-UP OR POST-OPERATIVE VISIT | 47 | 601311 | | 5 | IMMUNIZATIONS OR SHOTS | 4 | 41113 | | 6 | PREGNANCY-RELATED (INC PRENATAL/ DELV) | 56 | 1327886 | | 91 | OTHER | 123 | 1409174 | | Total | | 4584 | 60223304 | \newpage ## Full Year Insurance Coverage Status `Full Year Insurance Coverage Status 2022` indicates whether a person had any health insurance coverage for the entire year of 2022, and---if so---in which of these types: | Value | Description | |:---:|:----------------------------| | 0 | Uninsured all year | | 1 | Any private insurance all year | | 2 | Public insurance only all year (e.g., Medicaid, Medicare, TRICARE) | | 3 | Uninsured part of the year | | 4 | Any private insurance part of the year | | 5 | Public insurance only part of the year | | 6 | Both private & public insurance during the year | | 7 | Insurance type unknown | | 8 | Other/Not ascertained | ## Race and Ethnicity [`Race/Ethnicity`](https://meps.ahrq.gov/mepsweb/data_stats/download_data_files_codebook.jsp?PUFId=H243&varName=RACETHX) levels denote the following categories. Keep in mind that we are working with a random subset of the full dataset. The totals presented here (22,431 unweighted cases) reflect the complete dataset, not the number of cases in our specific sample. However, the unweighted and weighted values still provide insight into the distribution of individuals across categories and the relative importance of each group in the population. The weighted column accounts for the fact that some groups are oversampled to improve statistical estimates. The [U.S. Census Bureau](https://www.census.gov/newsroom/press-kits/2022/2022-national-state-population-estimates.html) estimated the total U.S. population at approximately 333 million in 2022. These weights adjust the MEPS data to match that estimate as closely as possible. Please note that it may be more difficult now to automattically assume U.S. population are accurate. The last census (from which these data came) was conducted under Trump’s administration, and despite [multiple lawsuits]((https://www.nytimes.com/2020/09/10/us/trump-census-undocumented-immigrants.html)) and [expert warnings]((https://www.brennancenter.org/our-work/research-reports/census-and-litigation-what-you-need-know)), his team sought to [undercount specific populations]((https://www.npr.org/2022/07/20/1044944618/census-citzenship-question-history-oversight-committee)). | Value | Description | Unweighted | Weighted | |:-----:|:--------------------------------------|------------:|-----------:| | 0 | Unknown | | | | 1 | Hispanic | 4,883 | 64,164,409 | | 2 | Non-Hispanic White Only | 12,211 | 192,947,932 | | 3 | Non-Hispanic Black Only | 3,244 | 41,481,721 | | 4 | Non-Hispanic Asian Only | 1,220 | 20,997,758 | | 5 | Non-Hispanic Other Race or Multiple Race | 873 | 13,461,423 | | Total | | 22,431 | 333,053,243 |