Ask Your Question
1

Reshaping csv data [closed]

asked 2013-04-21 20:13:25 +0200

bitinn gravatar image

updated 2015-10-30 19:37:11 +0200

Alex Kemp gravatar image

Say I have a csv file with following data format:

ID, Name, Gender, Q1
1, ABC, Male, "A1;A2"
2, ACB, Male, "A2;A3"
3, BAC, Female, "A1"

I would like to transform it into following format so that my data analysis tool can process it properly:

ID, Name, Gender, Questions, Responses
1, ABC, Male, Q1, A1
1, ABC, Male, Q1, A2
2, ACB, Male, Q1, A2
2, ACB, Male, Q1, A3
3, BAC, Female, Q1, A1

Using Text-to-Columns feature I can easily separate Q1 column into different columns, but I am stuck after that. Anyone had experience reshaping such data?

(data is collected via google form, unfortunately google store multiple choice question responses in such format, so not much I can do on data collection.)

EDITED: I have posted a question with more details on [StackOverflow].(http://stackoverflow.com/questions/16139313/transforming-csv-data-for-analysis-and-visualization)

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-30 19:38:41.019457

Comments

Intriguing problem: is it better asked on StackOverflow? It looks like a Perl problem, or maybe one of those Vi(m) guys will say "14 keystrokes to solve". But it doesn't look especially like a LibreOffice issue. IMO, of course. YMMV, and someone may come up with genius solution. [Also, would be interested to know: how many responses could Q1 (etc.) have? more than two?]

David gravatar imageDavid ( 2013-04-21 21:02:40 +0200 )edit

I have posted on superuser, so far no response, probably should have cross-post to SO instead... I cannot guarantee a question will have only N<3 responses (but likely N<10).

bitinn gravatar imagebitinn ( 2013-04-22 05:48:19 +0200 )edit

@ROSt52, for unknown reason I cannot comment directly under your answer, I will post it here for now: regarding breaking "A1;A2", as it may have more than 2 responses, there is no easy way to use left/mid/right function to break down responses then repeat row. as for LimeSurvey, I will check it out.

bitinn gravatar imagebitinn ( 2013-04-22 05:57:11 +0200 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2013-04-22 03:17:41 +0200

ROSt52 gravatar image

@bitinn - If I understand your problem correct. I would open the CSV file in Calc, save it as an ods-file and then work on it. At the end save it again as a CSV if needed.

Q1 position - looks in your example like a no-brainer. just add column and file it with Q1

Breaking the "A1:A2" string pattern, I would use =left(....), = mid(....) and =right(...) formulas and combine it with a macro to repeat the lines you need.

You also can consider to look at LimeSurvey (FOSSW) and see if your data set can be imported as it is and export it to Excel.

edit flag offensive delete link more
0

answered 2013-04-23 06:03:49 +0200

bitinn gravatar image

Not exactly a LibreOffice solution, but I manage to do it with google spreadsheet and google app script. Since that's my data collection platform, I am ok with it.

See this SO post for solution: http://stackoverflow.com/questions/16139313/transforming-csv-data-for-analysis-and-visualization, basically it's a simple script that iterate and produce a new sheet that met my requirement.

Maybe someone can find a way to do the same scriping in LibreOffice?

(Also thanks, @ROSt52 for suggesting alternatives ^_^)

edit flag offensive delete link more
0

answered 2013-04-22 16:03:39 +0200

ROSt52 gravatar image

@bitinn - No problem with your answer higher up.

If LimeSurvey isn't the solution of your problem, than it would be helpful to provide access to a sample file. This enables a better assessment of you data structure an hopefully leads quickly to solution.

I gave you some karma thus you should be able to upload a file. If ask for more karma and someone else will help.

edit flag offensive delete link more

Question Tools

Stats

Asked: 2013-04-21 20:13:25 +0200

Seen: 738 times

Last updated: Apr 23 '13