2

I mostly use R for my data analyses, but I was hoping for an easier upfront fix for my dataset within LibreCalc. Essentially, I have a dataset I am collecting for research in the field and, for expediency's sake, I am using commas to separate simultaneous behaviors. I want to, now, separate the behaviors into new rows for subsequent analyses, BUT I want to duplicate the other information within the same row.

So, if I have:

Time    Date     Focal    Behaviors    Actor    Target    Observer
2:00    01/22/18 QN       a, pg, w     QN       NU        AJP
2:01    01/22/18 QN       g            QN       NU        AJP
2:02    01/22/18 QN       z, 3         QN       NU        AJP

I would want: 

Time    Date     Focal    Behaviors    Actor    Target    Observer
2:00    01/22/18 QN       a            QN       NU        AJP
2:00    01/22/18 QN       pg           QN       NU        AJP
2:00    01/22/18 QN       w            QN       NU        AJP
2:01    01/22/18 QN       g            QN       NU        AJP
2:02    01/22/18 QN       z            QN       NU        AJP
2:02    01/22/18 QN       3            QN       NU        AJP

Please note that I have some intervening columns with other information that I have omitted here for clarity. Also, I have not coded in LibreCalc, so please provide annotation if you are (kindly) providing a useful script.

I really hope someone has a clear answer, and thank you for your time in reading this, irrespective of whether you have a solution!

Also, I cross-listed this under 'R' incase someone has concise code to automatically import, make the change, and export to LibreCalc file (as .xls, or .odf); I have multiple sheets though.

Alex Pritchard
  • 23
  • 4
  • 3
    For post-processing in R, this is a dupe of [Split comma-separated column into separate rows](https://stackoverflow.com/questions/13773770/split-comma-separated-column-into-separate-rows) – Henrik Feb 11 '18 at 15:09
  • Thank you! Any takers to fix this in Libre-Calc? Or to assist me in running this in R on a .xls or .odf file while preserving the other sheets? – Alex Pritchard Feb 12 '18 at 12:51

1 Answers1

0

In Calc, go to File -> Save As -> Text CSV (.csv). Run code in R to fix it, then import the modified CSV file back into Calc.

There is also https://extensions.openoffice.org/project/R4Calc, but it requires linking the LO SDK with R, which will not be easy.

Currently, LibreOffice lacks TEXTSPLIT to conveniently split comma-delimited cells. Lupp has implemented it as a macro written in Basic at https://ask.libreoffice.org/en/question/143252/how-to-find-values-present-in-different-sheets-and-copy-them-in-calc/?answer=143289#post-id-143289.

LO Base can also be used to solve this type of problem, as described in my answer to that same question. However, in this case I believe exporting to CSV is your best option.

Jim K
  • 12,824
  • 2
  • 22
  • 51
  • Thank you, very much, for the articulate and comprehensive response! I marked your post as useful, but my reputation is not high enough for my opinion to be valued for the public. – Alex Pritchard Feb 14 '18 at 13:36