2c:null 2d:["$","title",null,{"children":"Visualization - Average Duplicates in Medicaid | haerin99 | Observable"}] 32:T49f,clean_data = { let combined = [], res = [], nested = sorted_data[0], i = 0; if(sorted_data.length == 1) { combined = [sorted_data[0]]; } // Combine data of same medication across different years for(let i=0; i { res.push([]); for(let j=0; j < combined[i].length; j++) { let o = {ndc_description: combined[i][j].ndc_description, nadac_per_unit: parseFloat(combined[i][j].nadac_per_unit), // Convert string to float as_of_date: new Date(combined[i][j].as_of_date), // Convert string to date effective_date: new Date(combined[i][j].effective_date)} res[i].push(o) } }) return res; }33:[] 30:{"id":"eae842aabf3624d2","slug":"visualization-average-duplicates-in-medicaid-haerin","title":"Visualization - Average Duplicates in Medicaid","thumbnail":"36993e07d050a7e43c39bb2d3229b53dc134d3ad19fbee13a363291e52cec728","access_level":"public","create_time":"$D2023-07-12T16:57:22.380Z","update_time":"$D2023-08-04T16:25:08.351Z","view_time":null,"first_public_time":"$D2023-07-12T16:57:22.380Z","fork":true,"stars":0,"starred":false,"owner":{"type":"team","id":"c0e8b380dee1e1d5","login":"haerin99workspace","name":"haerin99","avatar_url":"https://avatars-next.observableusercontent.com/9afd2e14693a32440c6089cf7c2043771da4c0937c8ad0bf3f7f678266dcc49a","tier":"starter_2024"},"version":3660,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"9a41c27f85b5d6c6","login":"haerin99","name":"Hae Rin Kim","avatar_url":"https://avatars-next.observableusercontent.com/0fdfcb7981eb57c843329cd32bf4d53e012e7b3b1e61c95b89af630088c70ad6","tier":null}],"members":[{"type":"individual","id":"9a41c27f85b5d6c6","login":"haerin99","name":"Hae Rin Kim","avatar_url":"https://avatars-next.observableusercontent.com/0fdfcb7981eb57c843329cd32bf4d53e012e7b3b1e61c95b89af630088c70ad6","tier":null,"role":"editor"},{"type":"team","id":"c0e8b380dee1e1d5","login":"haerin99workspace","name":"haerin99","avatar_url":"https://avatars-next.observableusercontent.com/9afd2e14693a32440c6089cf7c2043771da4c0937c8ad0bf3f7f678266dcc49a","tier":"starter_2024","role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"title":"Visualization - Average Duplicates in Medicaid","cells":[{"id":0,"value":"# Visualization - Average Duplicates in Medicaid","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":2865,"value":"## Introduction","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":2902,"value":"Some of the Medicaid datasets have different prices on the same day. In the NADAC (National Average Drug Acquisition Cost) 2021 dataset, the ACNE MEDICATION 10% GEL has prices of approximately 0.2 and 0.08 on each recorded date (as_of_date) as shown below. This notebook averages different prices on the same date in order to visualize the data. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":2951,"value":"Additionally, there are two different types of dates in the datasets: as_of_date and effective_date. The prices were plotted across both date types.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":2960,"value":"nadac_2021_data","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":3345,"value":"variables = `ndc_description,ndc,nadac_per_unit,as_of_date,effective_date`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2906,"value":"nadac_2021_data = await sdk.getDatastoreQuerySql(`[SELECT ${variables} FROM ${\"88b0eac5-6164-5737-b746-16eaefd52664\"}]`);","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":29,"value":"## Import","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":32,"value":"sdk = await import (\"https://kunaalagarwal.github.io/medicaid/sdk.js\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":436,"value":"## Select Medicaid datasets","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":398,"value":"medicaid = (await fetch(\"https://data.medicaid.gov/api/1/metastore/schemas/dataset/items\")).json()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":401,"value":"viewof medicaid_datasets = Inputs.select( medicaid.map(({ title}) => title).sort().filter(el => el.includes(\"\")), {multiple: 14, width: '750px', label: \"Select Medicaid datasets\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":406,"value":"// raw distribution ids\ndistrIDs= Promise.all(medicaid_datasets.map(title=> {return medicaid.find(obj => obj.title === title)[\"identifier\"]}).map(id=> sdk.convertDatasetToDistributionId(id)))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":34,"value":"## Select drugs","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":40,"value":"viewof results = Inputs.select(await sdk.getNadacMeds(), {multiple: 10, width: 300})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":49,"value":"viewof selectedMeds = Inputs.table(sdk.getSimilarMeds(results))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":75,"value":"medicines = (sdk.parseSelectedMeds(selectedMeds)).flat()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2888,"value":"## Get data for selected drugs","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":3243,"value":"async function getAllData(items, vars, distributions){\n let varsString = \"\"\n const fetchDataPromises = [];\n Object.values(vars).forEach(v => {varsString += `,${v}`})\n varsString = varsString.slice(1, varsString.length)\n const fetchData = async (identifier, item) => {\n let sql = `[SELECT ${varsString} FROM ${identifier}][WHERE ${vars.filter} = \"${item}\"]`;\n return await sdk.getDatastoreQuerySql(sql);\n }\n\n for (let distributionId of distributions) {\n items.forEach(item => {\n fetchDataPromises.push(fetchData(distributionId, item));\n })\n }\n\n return await Promise.all(fetchDataPromises);\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3250,"value":"raw_data = getAllData(medicines, {filter: \"ndc_description\", xAxis: \"nadac_per_unit\", yAxis: \"as_of_date\", zAxis: \"effective_date\"}, distrIDs)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3393,"value":"sorted_data = (raw_data.filter(o => o.length !== 0)).sort((a,b) => (a[0].ndc_description < b[0].ndc_description) ? 1 : -1)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":393,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2351,"value":"clean_data.sort((a,b) => (a.as_of_date > b.as_of_date ? 1 : -1));","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":882,"value":"## Filter data","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":2954,"value":"An array of objects on either as_of_date or effective_date is needed for plotting. In the NADAC 2021 dataset, the ACNE MEDICATION 10% GEL has the same prices for the same values in effective_date as shown below. The filter_dt(date) method results in an array of objects that have a unique combination of date and price. In other words, filter_dt('effective_date') removes repeats from the array.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":2963,"value":"nadac_2021_data","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":3536,"value":"clean_data","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2590,"value":"filter_dt = function(date){\n let res = [];\n for(let i=0; i item[0] == o1[0] && item[1] == o1[1]))) {\n arr.push(o1)\n let o2 = {ndc_description: clean_data[i][j].ndc_description, \n nadac_per_unit: clean_data[i][j].nadac_per_unit, \n date: clean_data[i][j][date], \n pricetype: date}\n res[i].push(o2); \n }\n }\n res = res.sort((a,b) => (a.date > b.date ? 1 : -1));\n }\n \n return res;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":898,"value":"## Average prices","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":2788,"value":"// Accounts for two different prices on the same date\naveraged_prices = function(date) {\n let a = [];\n let sum = 0;\n let counter = 0;\n for(let i=0; i (a.date > b.date ? 1 : -1));","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2975,"value":"averaged_prices_effective_date = averaged_prices('effective_date').sort((a,b) => (a.date > b.date ? 1 : -1));","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2076,"value":"all_averaged_prices = {\n let res = [];\n for(let m=0; m < medicines.length; m++) {\n let a1 = [], a2 = [];\n for(let i=0; i < averaged_prices_asofdate[m].length; i++) {\n let o = {ndc_description: averaged_prices_asofdate[m][i].ndc_description, \n nadac_per_unit: averaged_prices_asofdate[m][i].nadac_per_unit, \n date_type: 'as_of_date', \n date: averaged_prices_asofdate[m][i].date}\n a1.push(o);\n }\n \n for(let j=0; j < averaged_prices_effective_date[m].length; j++) {\n let o = {ndc_description: averaged_prices_effective_date[m][j].ndc_description, \n nadac_per_unit: averaged_prices_effective_date[m][j].nadac_per_unit, \n date_type: 'effective_date', \n date: averaged_prices_effective_date[m][j].date}\n a2.push(o);\n }\n\n res.push(a1.concat(a2));\n }\n return res;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":93,"value":"## Plot average drug prices vs.year","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":3222,"value":"Here is a table of the raw data, as well as checkboxes to plot average drug prices per year over as_of_date or effective_date.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":3581,"value":"table_data = raw_data.flat()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2979,"value":"table_data","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":2373,"value":"viewof date_types1 = Inputs.checkbox([\"as_of_date\", \"effective_date\"], {label: \"Select a date type\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":525,"value":"Plot.plot({\n marginLeft: 80,\n marginRight: 200,\n grid: true,\n x: {label: \"Years\"},\n y: {label: \"NADAC per unit\"},\n color: {legend: true, type: \"categorical\"},\n marks: [\n Plot.frame(),\n Plot.lineY(averaged_prices_plot.sort((a,b) => (a.date > b.date ? 1 : -1)), {x: \"date\", y: \"nadac_per_unit\", fy: \"ndc_description\", stroke: \"date_type\"})\n ]\n})\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2379,"value":"averaged_prices_plot = {\n if(date_types1.includes('as_of_date') && date_types1.includes('effective_date')) {\n return all_averaged_prices.flat();\n } else if(date_types1.includes('as_of_date')) {\n return averaged_prices_asofdate.flat();\n } else if(date_types1.includes('effective_date')) {\n return averaged_prices_effective_date.flat();\n }\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":490,"value":"## Tips to improve code","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":495,"value":"// Condensed using map\ntip1 = {\n let res = [];\n medicaid_data.map(dataset => dataset.map( row=>{if(medicines.indexOf(Object.values(row)[0]) > -1) {\n res.push(row)\n }}))\n return res\n}\n","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1250,"value":"tip2 = medicaid_data.map(dataset => dataset.filter( row=> {return row.ndc_description ===\"ACNE MEDICATION 10% GEL\" }))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W33","stdlib":"1","titling":"auto"}} 31:"$30:body"