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.
0 answers
No answers yet.
Log in to answer this question.
-
Count multi range + Same Column
written by shabbas12 •
-
R - Merging and aligning two CSVs using common values in multiple columns
written by Michael •
-
extract the rows from multiple excel files and print the output as the same name of input files - S…
written by Baka •
-
What's the 'field' meaning in DNA FISH data?
written by q086536 •
-
How to read this type of data in R?
written by simek371998 •
-
Use data frame row data to solve systems of linear equations with solve function in R
written by rshipman •
-
High A in "Per base sequence content" of fastQC report
written by DVA
-
Remove entries from table based on another table and output new table in R -- Edit.
written by rshipman •
-
fastqc: kmer-content failed
written by shuchen •
-
Strange Pattern in Bam File
written by Can Holyavkin
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.
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 :)
I think you're better off saving the Excel file as a text tab-delimited file, and then processing it with a language
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. :)