Transpose columns to rows

Support and Helpdesk
,
1

Hello everybody.
Thank you for allowing me to access the forum.
I’m facing a problem and I haven’t found a solution yet. I’ve tried the OpenRefine feature without success. Afterwards, I tried exporting to txt and using regex, but I wasn’t successful either.
I’ve looked through the forum archives, but haven’t found an answer yet.
The scenario is as follows:
I used the ‘add columns form reconciled values’ option to retrieve data from Wikidata such as ‘alma mater’, occupation etc. It turns out that there are multivalued fields in Wikidata.
The problem is that multiple values are arranged in columns. I would need them transposed to lines.
The idea is to use the values to fill in the 37X fields in our MARC21 authority catalog.
I tried the ‘transpose cells in rows into columns’ option, it didn’t work.
So, I ask if there is anyone who has gone through something similar that can give direction on what I should do.
Thank you for your time.

Best regards

Dimas Justo
from Brazil

2

Hi Dimas,

not sure I understand your data structure correctly. It would have been helpful if you would have provided a small (dummy) sample of the current data and your expected outcome. But let’s guess what should happen =)

The Transposing feature in OpenRefine is great but sometimes has unexpected effects, as it also does some grouping on the key column (simplified).

In your case I am usually using the following approach:

  1. Join the multi-valued cells on a unique separator.
  2. Split the column on the unique separator.

Not sure whether you need step 2, as I am not that firm in the MARC21 format.

Cheers Benjamin

2 Likes
3

Hi Benjamin,

My apologies for the lack of clarity in the questioning.
I followed your advice to use the option “Edit cells > join multi-valued cells” and then divide into columns, considered the separator.
It perfectly solved my issue.
I am very happy with your support as it saved me a lot of time.
Many, many thanks indeed.
Best
Dimas

2 Likes