Search code examples
rlibreofficelibreoffice-calclibreoffice-basic

LIBRECALC Separate cell contents into new row (comma separated), but duplicate other contents

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.

Solution

  • 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.