Making the observations.csv more user friendly

[rehoyt]

We are writing a textbook No Code Data Science and plan to use some of the data in Synthea for examples and exercises. It's great that the data is longitudinal like it would be in an EHR but the format is not easy to mine.

We would like to see the lab values as columns with the results in rows. For example, hemoglobin would be a column with the result (e.g. 14) in a row below that. A patient would have more than one result for the lab test done at a different time. Do you have any suggestions how to convert observations to this type of format?

[rehoyt]

We will try an Excel pivot table insertion and if that fails use pandas/Python script unless you have other suggestions. Keep up the great work

[jawalonoski]

The CSV exporter and the observations.csv file has each lab result on a different row in the value column. See https://github.com/synthetichealth/synthea/wiki/CSV-File-Data-Dictionary#observations

You could try an Excel pivot table (I often do this to examine data when I'm making changes), but the observations.csv file can get very large and sometimes exceeds Excel's maximum file size (in which case it truncates the data).

The other complication is that the file includes many different panels... CBC, metabolic panel, lipid panel, SDoH questionnaires, etc. The result being that not every row will have a hemoglobin value (to follow your example), or you'll have an extremely large amount of columns.

Your project sounds cool. Good luck!

[rehoyt]

Thanks so much. The pivot table worked. FYI - we are using Orange data mining and JASP statistical package to do everything. Programming is great and important but an obstacle for many.....Bob