Preparing Excel Data

ACES2019

I'm trying to prepare an excel file for data analysis that requires the first three lines to be manipulated. First Line contains a year that I need merged with the third line. The second line needs to be removed all together. I can remove the second line and transpose lines 1 and 3 then write a formula to concatenate the two columns but can't get past that. This new row should become the header for the rest of the data.

Data Download-14.xlsx

Datasets

Preparation

Accepted answers

All comments

DataNath

Hey @ACES2019, how does this look? The bottom stream is the main focus here where we isolate lines 1 & 3, transpose them and then concatenate them to create the new headers, before looking for the old headers in the original data set and replacing them with the newly-created ones. The top stream literally just skips the first 3 lines to discard the old 'header' rows from the data:

I wasn't sure you wanted to do with the first 2 fields i.e. the Alabama Limited/Kentucky General etc but these should be easy enough for you to clean up as you wish.

Fix Excel stacked headers.yxzp

IraWatt

Hey @ACES2019,

Is this the kind of output your looking for?

If it is I can give you a walkthrough of how the steps work.

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

Workflow.yxmd

ACES2019

Exactly what I'm looking for. I can rename the first two columns. I'm going to download the workflow and test. Thank you.

IraWatt

Great to hear @ACES2019, one thing to note on the data input is I selected that the first row contains data:

DataNath

Let us know how you get on @ACES2019. One thing to note is that, the reason I define a range in my Input Data is, without it, Alteryx doesn't start importing from your first 2 fields:

You can either put a name in them within Excel before running the flow, or define a range like above. Doing the former will also remove the name to do the final rename within Alteryx.

ACES2019

I have a new one for you using the same data. I no longer need to merge row 1 and 3 into a header line. I need Line 3 to become the header for the data set. However, the headers would be duplicated for as many years. In the end the headers I need are:

F1 (rename to State), F2 (rename to CourtID), Year, Begin Pending, Income Cases, Rate per 100k Population, Outgoing Cases, Ending Pending, Clearance Rate, Percent of Outgoing Cases with SRLs, Dispositions, Bench Trial Rate, Jury Trial Rate, Total Population.

After that the headers start repeating themselves.

Thank you,

DataNath

Hey @ACES2019 that's fine, just a very slight amendment to the previously offered solutions. However, there is an issue (or it might not be, unsure on your case!) that - in Alteryx - you can't have fields with the same name and so when the fields begin to repeat, they'll be called X2, X3, X4 & Y2, Y3, Y4 etc.. New amended flow attached where you can see what I mean:

Fix Excel stacked headers - updated.yxmd

ACES2019

I have amended the workflow but think there might be an easier way.

Fix Excel stacked headers.yxmd

DataNath

@ACES2019 did you test the workflow above? Was just a slight tweak of the original:

ACES2019

I did and thank you. The updated workflow is missing the year and duplicates the headers (as you mentioned it would). I think the workflow I put out there fixes the issues but starts with the original workflow you provided then backs out of the concatenation. Just wondering if there is an easier way to arrive at the final result (which I no have thanks to you).

DataNath

@ACES2019 just had a little play around and I think I'd probably go about it in a very similar way to be honest!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703