Pivot/Group By - Make Multiple rows into one row and add columns

Data_Devon

Hi Community - thanks as always for the consistent great help.

I'm attempting to Group/Pivot/Join/Append (not sure exactly what the methodology will be) multiple rows of Patients into single rows with additional columns.

Right now, each patient event is on it's own row. So, a patient seen on Monday and then also on Tuesday will have two separate rows.

I want all of these extra rows to be added as new columns.

My purpose is to eventually find the nurse that did an evaluation prior to a surgery. If each patient is on it's own row, then I can filter down to "Surgery" rows and see which nurse did the Evaluation.

The screenshot above shows my intent, even though it's woefully simplified - each patient will have 20+ rows, with 100s of columns each… The theory/logic will be the same though.

david_cunningham

@Data_Devon - yes, unfortunately that is a limitation of the Pivot tile. There is no dynamic generation. Keep in mind that you wouldn't have to code every single patient ID, just the appointment types. The patient ID would be your group (row) identifier. Not sure how many appointment types that you have. If that's too cumbersome as well, below is a pathway to achieve what you want.

One workaround is to use the python tile instead and pandas Pivot() function.

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.pivot.html

MichelleH

@Data_Devon How strict are the requirements for how the data is formatted? I often find that "pivoting" in card form is much more flexible than hard-coding columns into the dataset.

For your use case, it may make more sense to create a separate branch of your ETL to filter or aggregate your data so you have a single row per Patient ID with the Nurse details. Then you can join that back to the original branch by Patient ID so all rows for that patient contain a column with the Evaluation nurse.

david_cunningham

@Data_Devon I’d recommend looking at and using MagicETL pivot roles. Here is a link to the documentation. Feel free to reach out if you have any questions.

https://domo-support.domo.com/s/article/360044951294?language=en_US

Data_Devon

Thanks @david_cunningham !

That's exactly what I was thinking too, but the Pivot tile in Magic ETL forces you to hard-code each patient ID. (see Screenshot below)

With 10,000+ patients, that is not possible.

Does that make sense? w

david_cunningham

@Data_Devon - yes, unfortunately that is a limitation of the Pivot tile. There is no dynamic generation. Keep in mind that you wouldn't have to code every single patient ID, just the appointment types. The patient ID would be your group (row) identifier. Not sure how many appointment types that you have. If that's too cumbersome as well, below is a pathway to achieve what you want.

One workaround is to use the python tile instead and pandas Pivot() function.

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.pivot.html

MichelleH

@Data_Devon How strict are the requirements for how the data is formatted? I often find that "pivoting" in card form is much more flexible than hard-coding columns into the dataset.

For your use case, it may make more sense to create a separate branch of your ETL to filter or aggregate your data so you have a single row per Patient ID with the Nurse details. Then you can join that back to the original branch by Patient ID so all rows for that patient contain a column with the Evaluation nurse.

Data_Devon

@MichelleH thanks for extra input.

That is what I ended up doing - I filteredd out Evals and then I filtered out Surgeries, and then I joined them back in on eachother to get an output that had both on one line.