I have data that is poorly normalized. Something like this: student IDClassScoreGradeClass2Score2Grade2Class3Score3Grade31History95AEnglish65D-Science85B2Science90A-Math80C- And in the end I want this: Student IDClassScoreGrade1History95A1English65D-1Science85B2Science90A-2Math80C- I'm trying Transpose, then trimming the numbers off the name column, but I don't know how to bring the 3 records back together correctly. I'd rather not do it manually, because there are actually 121 of each field (Class, Score, Grade).

Transpose sets of multiple columns into rows?

I have data that is poorly normalized.

Something like this:

student ID	Class	Score	Grade	Class2	Score2	Grade2	Class3	Score3	Grade3
1	History	95	A	English	65	D-	Science	85	B
2	Science	90	A-	Math	80	C-

And in the end I want this:

Student ID	Class	Score	Grade
1	History	95	A
1	English	65	D-
1	Science	85	B
2	Science	90	A-
2	Math	80	C-

I'm trying Transpose, then trimming the numbers off the name column, but I don't know how to bring the 3 records back together correctly. I'd rather not do it manually, because there are actually 121 of each field (Class, Score, Grade).

Transformation

Accepted answers

JosephSerpis

Hi @pythorlh I have mocked up a workflow that produces you output. It uses a regex tool to replace the numbers in your column names. Then I use a Multirow tool to create a rank per student ID for each class they attend, which when you use a crosstab tool produces a row per class for each student ID.

Transpose140819.yxmd

All comments

JosephSerpis

Transpose140819.yxmd

Garrett

I think you can probably get this done with a single "Arrange" tool (perhaps followed by a "Filter" tool to get rid of any nulls):

Arrange Tool.PNG

pythorlh

Thank you, I was able to get this to work.

One note for anyone reading this, make sure if you have any Select tools in your workflow before the Multi-Row Formula Tool that they don't alter the sort of the incoming fields. I was having trouble until I realized this was happening.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335