How to get Jamovi to Recognize a Delimiter

[symiatl]

I have imported a dataset from excel where variable attributes are separated with a comma, the delimiter. For example, a cell is filled in like "1,11,20,28". Each number represents a different attribute of the variable. How do I get jamovi to recognize the comma as a delimiter and each numeric attribute individually instead of considering each different set and order of numbers a completely different attribute.

[jonathon]

i'm surprised you're having this issue opening a .xlsx file.

could you share it with us?

kind regards

jonathon

[symiatl]

I can't share the file for confidentiality purposes, but I have attached images of what the data looks like in excel versus how it was imported into jamovi. Again, the comma is the delimiter in excel and I need a way to get Jamovi to recognize each number as individual instead of grouping each set of numbers as a new attribute. Thank you

[jonathon]

hi,

it looks to me like the data in jamovi looks the same as it looks in excel (assuming the top screenshot is excel?)

jonathon

[symiatl]

Yes that's right
I am asking how to get jamovi to recognize the commas as delimiters as they are programmed in excel as. That way Jamovi recognizes each number individually instead of grouping them together as they are in excel. Does that make sense?

[reason180]

Hi symiatl,

I think the solution depends on the meaning of you data organization and what you are trying to do with it. I'm not sure what you mean by "attribute" of a variable.

One useful set of terms would be that there are 'variables,' with each variable represented by a 'column.' Within each column are the 'values' of the variable (one value per row). When a variable is nominal or ordinal, we can also speak of it having a set of unique "levels."

If you want each of text within a particular column to be split across multiple, new variables (new columns), you could do the following (see the attached image and jamovi file), which involves adding Computed Variables, and using the SPLIT function. However, I would first open your Excel data into a new jamovi file while specifying Default Missings as: nr (assuming that nr stands for 'no response').

temppicture73645Untitled.png (58.78 KiB) Viewed 2697 times

[jonathon]

That way Jamovi recognizes each number individually instead of grouping them together as they are in excel. Does that make sense?

hmm, this is a tricky one, and "repairing files" was not really the sort of thing jamovi was designed to do.

the way i would do this would be to save this as a .csv file, and then edit it in a text editor.

in the text editor it would appear something like:

"1,2,","1,5,15,11","1,5,22,6", ...

in this case, the fix is very easy, you just need to remove all the '","' and '"' marks ... usually a text editor will have a "find and replace" feature. you can search for ", and replace them with nothing. once you've done that, you should be looking at something like:

1,2,1,5,15,11,1,5,22,6, ...

save that, and you should find that both excel and jamovi will be able to open this correctly.

pull this off, and you'll have earned a level 1 hacking badge.

jonathon

[reason180]

RE: "remove all the '","' and '"' marks ... usually a text editor will have a "find and replace" feature. you can search for ", and replace them with nothing"

A drawback of that approach is that, as part of the hack, you would need to type-in additional, comma-delimited column names. Since those column names aren't already there, it suggests to me that the data are in their original, raw form (e.g., there are circumstances where Qualtrics has multiple, comma-separated values as the single value in a particular cell within a column). If that's the case, then it isn't that Excel or jamovi fail to open then data file correctly. Instead, the data just need to be preprocessed, and one reproducible procedure would be the SPLIT method that I described, above.