How? Merge text columns, add seperator

[stats2019]

Hi,

I have five columns with text values. I need to combine those, but add seperators between.
I tried (A..E represent column names)

Code: Select all

A+ (IF(B!="",","+ B))+ (IF(C!="",","+ C))+ (IF(D!="",","+ D))+ (IF(E!="",","+ E))

But this also adds a comma (,) to the beginning of the new column if A is empty.
I have seen, that a SPLIT command exists - but is there a similar thing for COMBINE / MERGE?

Oh, if there is not, may be this could be introduced?

Thanks already
Olaf

[jonathon]

oh yup, that's not a bad idea. does excel have something equivalent?

jonathon

[stats2019]

Hi Jonathon,

I am not sure it helps, but I search in the net. Depending on the office version there is the function TEXTJOIN (in Office 365)

Sources:

1. https://support.office.com/en-us/articl ... 340eb164a6 and
2. https://support.office.com/en-us/articl ... 8fc845691c
3. https://trumpexcel.com/concatenate-excel-ranges/
4. https://www.extendoffice.com/documents/ ... colon.html)

For R it seems to work like this:
https://www.tutorialkart.com/r-tutorial ... ings-in-r/
But how do I get the result into a new variable (is there an update to your post viewtopic.php?f=11&t=526&p=2029#p2033 ? If this could be done with Rj Editor it would be an intermediate solution (until implemented ).

Olaf

[stats2019]

Afterword: It's possible to create a formula in older versions of XLS which would recreate this behaviour. If needed, I can quickly create a demo file.
Olaf

[MAgojam]

If this could be done with Rj Editor it would be an intermediate solution (until implemented ).
Olaf

Hi, Olaf.
If you want to use Rj temporarily, with four lines of code you can do it.
I'm attaching a ScreenShot which gives a small example.

ScreenShot.PNG (44.71 KiB) Viewed 5680 times

Cheers,
Maurizio

[stats2019]

Hi Maurizio,

that's a great solution which totally helps me. Actually it's two solutions in one post:
a) combination of the columns and
b) writing back from Rj to jamovi

Yepee!
Thank you very much.
Olaf

[stats2019]

Hi Maurizio,

I have to ask as I can not solve it myself.
Your suggestion is great, but how can I throw in column (variable) names instead of the range? As I have probaly around 100 columns by now that, using col 78:83 would not be so nice and problematic if variables are added before.

I tried several things but not one worked. Some of what I tried:
Mydf <- tidyr::unite(c(colA,colB,colC,colD,colE), col="", sep=",")
and
Mydf <- tidyr::unite((colA,colB,colC,colD,colE)), col="", sep=",")
and
Mydf <- tidyr::unite(('colA','colB','colC','colD','colE')), col="", sep=",")
and
Mydf <- tidyr::unite(('colA':'colE')), col="", sep=",")


Thanks already again
Olaf

[MAgojam]

I have to ask as I can not solve it myself.
Olaf

Hi, Olaf.
No problem, let's see together how to call the variables you loaded into jamovi into Rj.
Let's assume we have 5 variables that we have named from A to E, as in my previous example.
In Rj with:

Code: Select all

# create a data frame with all the variables
Mydf <- data []
# or, you can achieve the same thing by indicating:
# the range of variables from first to last
Mydf <- data [1: 5]
# or using the name of the variables
Mydf <- data [c ("A", "B", "C", "D", "E")]
# example: 
# you are interested only interested in the variables A, B, D, E
Mydf <- data [c (1: 2, 4: 5)]


#--- Beginning code to use ---#
# for the only variables of interest the code could be in these 6 lines
Mydf <- data [1: 5]
Mydf <- tidyr :: unite (Mydf, col = "", sep = ",")
Mydf <- stringr :: str_remove_all (Mydf [, 1], "NA,")
Mydf <- stringr :: str_remove_all (Mydf, ", NA")
as.data.frame (Mydf)
# if you want to save the data of the new variable in a text file,
# for a simple copy paste in jamovi, you can add this:
write.table (Mydf,
            file = "D: /temp/new_war.txt",    # change your path here
            row.names = FALSE,
            col.names = FALSE)
#--- end of code to use --- #

Cheers,
Maurizio

[stats2019]

Hi Maurizio,

that was a wonderful explanation with the different options I have. Thank you for taking your time and teaching me.
It works well and I used the possibility to choose which columns I want

Code: Select all

# or using the name of the variables
Mydf <- data [c ("A", "B", "C", "D", "E")]

Do I understand correct, that in your first example (screenshot) the column new_var was added by hand after conversion and not added using the code?
In other words, as the data will be replaced with a new version of it, is there a way to write the calculated new column back to jamovi (data columns)?

Olaf

[stats2019]

Hi,

I want to report, that I succesfully managed combining the columns with tools already available in jamovi.
My setup: 5 columns, at least one of each contains something.
I added 5 transformed colums which test, if in the corresponding original column is a value. If there is a value I set this column (a_count, b_count ...) to 1, if no value in column a, b ... then to 0.
Once this is done, one can use this formula to compute the combination:

Code: Select all

colA + (IF(colB!="",IF(colA_Count>0,",")+ colB))+(IF(colC!="",IF(colA_Count+colB_Count>0,",")+ colC))+(IF(colD!="",IF(colA_Count+colB_Count+colC_Count>0,",")+ colD))+ (IF(colE!="",IF(colA_Count+colB_Count+colC_Count+colD_Count>0,",")+ colE))

Thank you all for your help.
Olaf