I have data files that are in stanzas:

Item 1: Attribute 1 Item 2 Attibute 2 Item 3: Attr 3 Item 4: Attr 4

E.g. The following is a typical stanza.

Typical stanza

This is difficult to work with. I want to normalize it so the items become column ids and the attributes become data, with 1 row per stanza. At that point chucking data I don't need becomes a lot easier.

This is possible in excel or google sheets but it requires some fairly messy lookups in each column, along with a ton of offsets. Doing in a way that is robust against slight changes in the orginal is even harder.

Does anyone know of an easier way to do this?


Easier way for whom? As I understand, you would like to have the data broadly in the form:

name1  balance1  cardid1 firstaddress1 ...
name2  balance2  cardid2 firstaddress2 ...

This seems to mostly transforming data from long format to wide format and most popular data science tools will do that. I personally would go for R and dplyr. However, it also seems that your data does not conform any format perfectly, so you also need some tinkering. This is easy for people with sufficient coding knowledge but not for those without it. I am afraid there is no "easy" way to do it as whichever software you end up using, you have to formally describe the way your data should be transformed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.