2

I have a csv file that looks like this:

ID, term, functional category
GO:0008166,viral replication,P,
GO:0008167,sigma virus replication,P,
GO:0008168,GO:0004480,methyltransferase activity,F,
GO:0008169,C-methyltransferase activity,F,

I just need to use notepad++ to move IDs to their own row and copy what was in that same row to the bottom. So ideally the final output would look like:

ID, term, functional category
GO:0008166,viral replication,P,
GO:0008167,sigma virus replication,P,
GO:0008168,,methyltransferase activity,F,
GO:0004480,methyltransferase activity,F,
GO:0008169,C-methyltransferase activity,F,

I tried using the replace function, finding instances of IDs right next to each other using: (\w+:\d+),(\w+:\d+),(.*),[A-Z] and replacing with $1,$3,$4\r$2,$3,$4.

However, every time I try and "find" these instances in notepad++ it'll hi-light the entire list and the command will not work.

I'm not very experienced with this, so if somebody can help me I'd appreciate it! Thank you!

original file: http://www.geneontology.org/doc/GO.terms_alt_ids

I got rid of header and turned it into a csv by replacing all tabs with a comma.

1

Find / Replace Repeated IDs in CSV

The not-entirely-optimised-but-functional regex I used was:

(GO:[\d]+),(GO:[\d]+)(.*)

replacing with:

$1$3\n$2$3

Giving the output:

ID, term, functional category
GO:0008166,viral replication,P,
GO:0008167,sigma virus replication,P,
GO:0008168,methyltransferase activity,F,
GO:0004480,methyltransferase activity,F,
GO:0008169,C-methyltransferase activity,F,

Note that this doesn't duplicate the comma in your desired output as I thought that might be a side effect, rather than desired.

If the duplicated comma is desired, use:

$1,$3\n$2$3

as the replacement instead.

Explanation

(GO:[\d]+) - 1st capturing group, match literal "GO:" followed by one or more digits ($1)

, - match literal comma (used to detect repeated IDs / ignore non-repeated ones)

(GO:[\d]+) - 2nd capturing group, same as first! ($2)

(.*) - 3rd capturing group, match 0 or more of "anything" - shorthand for "rest of the line" here ($3)

$1$3\n$2$3 - print id 1 followed by rest of line; then a newline; print id 2 followed by rest of line

Caveat

Be advised that for the above regular expression to work you must have the search option . matches newline disabled. Otherwise, the 3rd capturing group will match too much.

dot matches newline

  • Thank you! I feel like I was close, I've been learning this text editing slowly and I hit problems all the time. Have a good day – kevluv93 Aug 11 '15 at 11:44
  • My pleasure, good luck, and you too :) PS regex101 is your friend for these sorts of things! – bertieb Aug 11 '15 at 11:46
  • I hate doing this, although I did just award the right answer. I didn't realize it but this function is copying everything under the instances of the duplicate IDs and pasting it over and over again. Is there a way to make the (.*) end at a single row? – kevluv93 Aug 11 '15 at 14:20
  • @kevluv93 Add a $ to the end of the find section to make it terminate at EoL (which it did in my testing, but obviously real testing > small sample data!) ie (GO:[\d]+),(GO:[\d]+)(.*)$. You could also prepend with ^ to match start-of-line for completeness: ^(GO:[\d]+),(GO:[\d]+)(.*)$ – bertieb Aug 11 '15 at 14:22
  • I still can't seem to get this command to work. It's still selecting every data point under the instances of duplicate IDs. – kevluv93 Aug 11 '15 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.