Unmerge Columns to structure data

Hi All,

I am using the US census data and am trying to transpose the data however the challenge I am having is that the columns have merged cells. I dont think you can use multi-row since this the data is in the columns. Is there a way to unmerge the column header then repeat the data for each column?

Ideally I would like to have the final data look something like the picture attached

US Census income.xlsx

Alteryx US Census Data.PNG

Datasets

Parse

Accepted answers

SPetrie

Im not sure how applicable this will be for your uses but I think it at least gives an idea of one possible way to go about it.

In this case, I imported the file indicating that the first row has data. Then I split out the header row that has the zips and then the row that has the sub type.

I transposed the zips and cleaned them and used a multi-row to copy them down to the merged cells that show us a blank in Alteryx.

I filtered out the sub types to be only the one (or ones) I care about. I also filtered the main data to be only the income group rows I cared about.

After that, I used find/replace and the generic column headers to append the names back to the transposed main data.

Hopefully this makes sense and helps.

example.yxzp

All comments

SPetrie

Are you needing all the columns, or just the household estimate column? I only see household estimate in the example PNG

dj2dirk212

For this specific dataset I only need household however I was hopping to leverage a workflow for other datasets that may have two or more subcolumns.

SPetrie

Im not sure how applicable this will be for your uses but I think it at least gives an idea of one possible way to go about it.

In this case, I imported the file indicating that the first row has data. Then I split out the header row that has the zips and then the row that has the sub type.

I transposed the zips and cleaned them and used a multi-row to copy them down to the merged cells that show us a blank in Alteryx.

I filtered out the sub types to be only the one (or ones) I care about. I also filtered the main data to be only the income group rows I cared about.

After that, I used find/replace and the generic column headers to append the names back to the transposed main data.

Hopefully this makes sense and helps.

example.yxzp

dj2dirk212

This is fantastic. For the intended purposes this seems to have worked Ill have to see how I can scale this in other areas.

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167