This is an explanation of our efforts, challenges, and progress. Instructions for how to build the database are in How to Build
We started working from Afihn's repo, which enabled us to uncorrupt and unzip the provided zip file. His repo further processed two of the txt files (ComputedElements.txt, Pub_PCRevents.txt) into csvs using sed to replace the '|' with ',' to generate csvs. His src code further processes those csvs to extract a subset of the data (both in terms of cases and features). This subset is joined and output as a pickle file. He further renames the features in that file and stores as pickle_renamed.
We initially thought that these pickle files provided adequate data for our analysis. We did some visualizing to show that we now had data access.
After both 911 groups met with the stakeholder to discuss the project, it was determined that more data was needed than what was in the pickle files. One outcome of this conversation was a table with the features the stakeholder was interested in considering in the analysis. This meeting further illuminated that not all the features analyzed in the model article (see docs) were available in the pickle file. Through conversation with another 911 group, the idea of producing a sqlite database was floated.
After revisiting the article, additional fields were determined to be needed. The article also builds on other past work imputing presumed dead/alive (see docs folder for this additional article). Imputing dead/alive requires including more features/tables in the database.
As a result of these considerations, we developed a more comprehensive list of tables and features we wanted access to in our database. This group is attempting to generate the tools to make a reproducible sqlite database that includes all tables listed here.
Some critical data is still missing. The article includes eAirways-related features in the analysis, but we could not find this data. Ben Darby (on the other 911 team) contacted the NEMSIS administrator and determined that this is not included in the public dataset.
In addition, the article filters the cases to only include cases where just one ambulance responds to avoid double counting the same patient (the NEMSIS dataset is event-based and not patient-based). We think the critical feature for filtering out these cases is eResponse_03, but we could not find this feature in the documentation. We are currently assuming that it is not in the public dataset.
We attempted an alternative method to the other 911 group to see if it might be faster and enable building a DB with a larger number of tables and fields included. To do this, we used a shell script that imports csv's. Because the unzipped files were text files with a multi-character delimiter, we built on the code from Afihn's Makefile, which converted .txt files to csv's to convert target tables to csv's with a | delimiter. The sqlite .import doesn't handle multi-text delimiters. Using a ',' delimiter created other issues since some fields contain ,'s in the field.
We have a shell script file in project-ErinP123/data-processing/src/make_DB.sh This script creates a sqlite database from the converted csv's. To choose different tables, modify this code and ensure you have the required csv's. This takes quite some time, even though we hoped importing csv's would be quicker than a python script. We are still researching ways to optimize this step.
To produce a manageable dataset for analysis that parallels the work in the referenced study, we developed a query of the database. The file data-processing/src/cardiac-joins.py does this work for us and produces data-processing/data/processed/cardiac_joins.pickle.
It is very challenging to determine the exact query used in the study based on how it is described in the article (for example, cases involving trauma/injury need to be filtered out, and many features in the database contain codes that might indicate trauma/injury). Determining filtering criteria has proven to be one of the most challenging aspects of this project, even with correspondence with the article authors and conversations with the project stakeholders.
In addition, the hope is that with minor adjustments to the Makefile and shell script file, this work will be easily modifiable (it can include more tables, fewer tables, or be run for different years of NEMSIS data). Keep in mind that NEMSIS data does have version updates. These updates may mean table names are changed and could result in changes to feature names and codes. This repository does not replace the need for extensive consideration of the NEMSIS Manual and Data dictionary, both available here.