How to flip a large text file (horizontally vertically)

pablointhebox · Post by **pablointhebox** » Tue Jun 23, 2009 4:39 pm

Hello,

I would be very very happy if someone could help me on this:
I have a couple of very large tab separated txt files (200Mb), an all I have to do is to flip every line into a row.
Sounds very simple, doesn't it? I still can't figure out how to do it...

So, I have this:

Code: Select all

person1 A B C D E F G H I J
person2 K L M N O P Q R S T
person3 G G G G G G G G G G
person4 C C C C C C C C C C
person5 T T T T T T X X X X

And I really need this:

Code: Select all

person1 person2 person3 person4 person5
A K G C T
B L G C T
C M G C T
D N G C T
E O G C T
F P G C T
G Q G C X
H R G C X
I S G C X
J T G C X

Many thanks,

Pablo

talleyrand · Post by **talleyrand** » Tue Jun 23, 2009 5:23 pm

While TextPad is pretty awesome, it's not going to be able to handle what you're asking. Search for pivot text file and there are some solutions out there using Excel. Personally, I'd write up a quick script using the language of your choice to accomplish this task.

Various implementations here
http://blogs.msdn.com/jomo_fisher/archi ... -in-f.aspx

Bob Hansen · Post by **Bob Hansen** » Tue Jun 23, 2009 6:05 pm

You can probably do this in multiple steps. Define the steps, then make a macro. Then assign a hot key to the macro so it only takes a keystroke to do the work.

Here is an general (UNTESTED) outline of the steps to do this:
1. Replace all Return codes "\n" with a unique char, like the tilde "~". (Results in one long line)
2. Replace all the delimiters with a return code "\n". (Results in all data groups being on individual lines).
3. Turn Word Wrap OFF
4. Go to next tilde. Use CLT-X to cut and copy all data in block to the next tilde. (Copies the next group to the clipboard, and removes those lines).
5. Turn Block Select Mode, to ON.
6. Go to the end of line 1, and insert the data block from the clipboard. (Results in the next data block going to the right of the previous data block)
7. Turn Block Select Mode to OFF.
8. Repeat steps 4-7 as necessary until all data has been moved.
9. Remove all tildes as needed.

pablointhebox · Post by **pablointhebox** » Wed Jun 24, 2009 9:12 am

Thank you talleyrand, but I am unfortunately not familiar with script writing, and I don't really have a language of choice.
Excel yes, the transpose function would do the job, if only my (end) table had less then ca. 15000 columns, which is by far not the case. Excel (and similars) limit the number of columns quite low. And splitting the data is not really a smart alternative in this case.
Thanks anyway,
Pablo.

pablointhebox · Post by **pablointhebox** » Wed Jun 24, 2009 9:21 am

Bob, the problem is that the files are really big, and all the replacing and moving of data seem to need a lot of processing. I have a quadcore running since 2 hours only for the first replacement of the return codes. I'll let you know. Many thanks for your help again.
Pablo.

pablointhebox · Post by **pablointhebox** » Wed Jun 24, 2009 9:44 am

And yes, I tested your suggestion in a smaller file and it WORKS perfectly. The macro is done, now it is just a matter of computer power for the whole replacing and moving.
Thank you,

Pablo

pablointhebox · Post by **pablointhebox** » Wed Jun 24, 2009 12:01 pm

Bob, it didn't work that way. The long line generated by removing the return codes is too long. TextPad tells me "Operation Interrupted - it would make line 1 longer than 33554429 characters". I changed your suggestion a bit to the following, and it now works:

1. With help of a macro, insert a unique char, like the tilde "~" in the very beginning of every line.
2. Replace all the delimiters with a return code "\n". (Results in all data groups being on individual lines).
3. Turn Word Wrap OFF
4. Record a macro that does the following:

1. Go to next tilde. Use CLT-X to cut and copy all data in block until the next tilde. (Copies the next group to the clipboard, and removes those lines).
2. Go to the end of line 1, and insert the data block from the clipboard using the "Paste as block" option. (Results in the next data block going to the right of the previous data block).

5. Run the macro as necessary until all data has been moved.
6. Remove all tildes as needed.

Thanks a lot!
Pablo.

Bob Hansen · Post by **Bob Hansen** » Wed Jun 24, 2009 9:27 pm

I'm not sure, but it sounds like you did get a working solution.

I warned you that my outline was untested.

Well done.