Icon

Challenge 33 - Medical Procedure Prices - Solution

In America the prices of medical procedures can vary greatly, so savvy Americans tend to shop around for a good deal. In this challenge you will take the role of a data journalist trying to investigate price differences among medical procedures. More specifically, you want to find the top 5 procedures that show the largest variety in terms of pricing from hospital to hospital (in statistics, you would call this high standard deviation). The data you have at hand for this investigation is not uniform and requires inspection in order to be properly read and processed. For simplicity, compare all average charges for the 25 most common outpatient procedures performed by hospitals from the Kaiser Foundation Hospitals and Sutter Hospitals only. For Kaiser Foundation, the relevant data is in Excel files with "...Common25..." in their names; for Sutter, the relevant Excel files contain "...CDM_All..." in their names. Beware of hidden sheets.

Download the data from here: https://data.chhs.ca.gov/dataset/chargemasters/resource/857e49c2-8318-4e7f-810d-6008bc3734ab. The data is not uniform and requires looking through to figure out how to read it in properly.

医疗程序价格分析

在美国,医疗程序的价格差异很大,因此精明的美国人往往会对比不同医院的报价,以找到最合适的价格。在本次挑战中,您将扮演数据记者的角色,调查不同医院之间医疗程序价格的差异。

具体来说,您的目标是找出 价格波动最大(即标准差最高)的 前 5 种 医疗程序。

您可以使用的数据并不统一,因此需要仔细检查以确保正确读取和处理数据。为了简化分析,您仅需比较 Kaiser Foundation 医院 和 Sutter 医院 这两家机构的 最常见的 25 种门诊医疗程序的平均收费。

  • 对于 Kaiser Foundation 医院,相关数据存储在文件名包含 "...Common25..." 的 Excel 文件中。

  • 对于 Sutter 医院,相关数据存储在文件名包含 "...CDM_All..." 的 Excel 文件中。

  • 注意隐藏工作表,数据可能存储在其中。

数据下载链接:
California Health and Human Services Open Data Portal

由于数据格式不统一,您需要先检查数据结构,以正确读取和分析这些数据。
Download the data from here: https://data.chhs.ca.gov/dataset/chargemasters/resource/857e49c2-8318-4e7f-810d-6008bc3734ab. The data is not uniform and requires looking through to figure out how to read it in properly.

make code and priceintegers
String to Number
if code or price are blankremove those rows
Rule-based Row Filter
Calculatestandard deviation
Statistics
change shape of data for statistical analysis
Pivot
Read Excel Sheets
unify procedure names
GroupBy
filter to Sutter orKaiser Foundation Hospital xyz
Row Filter (deprecated)
Select top 5 procedures with the most variety of pricing
Top k Row Filter
...to extracthospital name
String Manipulation
Column Rename (deprecated)
used innext node...
Path to String
use unified procedure namesand remove codes
Joiner
Get all file paths
List Files/Folders

Nodes

Extensions

Links