Hello, slight problem I am having here with some excel files.
So I have received several excel files which contain records that have cells with multiple entries - column C. This table is generated from the Byonic software for peptide identification. Image below.
And this is what I want. A new record for each entry in modifications cell while retaining the rest of the information. I want a record row entries for each of the modifications.
I have tried splitting the columns by ";" in excel but that adds columns to the right for each entry. I want a new row for each instead. Has anyone been able to solve a problem like this below? It seems like it would be a pretty straight to the point solution.
If this can be done via another language that works too as I can export these as CSVs or what have you. Any help is appreciated. I can not simply do this by hand as one file alone can have as many as 3k+ records.
This came to my mind right away.
Yeah I have been working with R a bit, just this is a new area to me. Like the meme by the way :)
Dear colleagues, time to reset the counter. I know we all worked really hard to keep R and Python prominent on this forum, but today we're back to square one.
That is why I posted at the bottom of my post if there were better options out there. :)
perl :)
But he said better options!
In a ranking of options, Excel should be at the bottom so however you rank perl, it is better. :)
You can probably do this with R quite easily, here is some material to get you started: some several stackoverflow posts. Export the files as csv (quoting the fields), read them into R, and follow the posts.
@h.mon readxl or xlsx packages load xls(x) files direct. CSV export may not be necessary. @boomshackle.. Separating by columns is easy as you mentioned above. For adding new rows, keeping the information correct, hit stackoverflow. One such post: https://stackoverflow.com/questions/42425208/vba-split-cell-values-into-multiple-rows-and-keep-other-data
Thanks dude! The second solution worked the best for the situation.
I think you're better off saving the Excel file as a text tab-delimited file, and then processing it with a language