Separating data from one column into two columns


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Separating data from one column into two columns
# 1  
Old 12-04-2009
Separating data from one column into two columns

Hello,
I have a file that contains 64,235 columns and over 1000 rows and looks similar to this:
Code:
 
ID   dad  mom  1  2  3  4  5.... 64232
1234 5678 6789 AA BB CC DD EE....ZZ
1342 5786 6897 BB CC DD EE FF....AA
1423 5867 6978 CC DD EE FF GG....BB

I need to leave the first three columns in tact while separating the letters into two columns (including the header) so that the data will look like this:
Code:
 
ID   dad  mom  1   2   3   4   5  . . . .  64232 
1234 5678 6789 A A B B C C D D E E . . . . Z Z
1342 5786 6897 B B C C D D E E F F . . . . A A
1423 5867 6978 C C D D E E F F G G . . . . B B

Any suggestions?
Thanks in advance!
# 2  
Old 12-04-2009
This is a catch 66 problem Smilie however you can start from here:
Code:
awk 'NR>1{for(i=3;++i<=NF;){$i=substr($i,1,1) FS substr($i,2)}}1' file

# 3  
Old 12-04-2009
With Perl:

Code:
$
$ cat -n f2
     1  ID   dad  mom  1  2  3  4  5  9  10 11 12 99 100 101 102 999 1000 1001 9999 10000 10001 64232
     2  1234 5678 6789 AA BB CC DD EE FF GG HH II JJ KK  LL  MM  NN  OO   PP   QQ   RR    SS    TT
     3  1342 5786 6897 BB CC DD EE FF AA BB CC DD EE FF  GG  HH  II  JJ   KK   LL   MM    NN    OO
     4  1423 5867 6978 CC DD EE FF GG BB CC DD EE FF GG  HH  II  JJ  KK   LL   MM   NN    OO    ZZ
$
$ perl -lne 'if ($.==1){s/(\d )/$1 /g} else {s/([A-Z]){2}/$1 $1/g} print' f2
ID   dad  mom  1   2   3   4   5   9   10  11  12  99  100  101  102  999  1000  1001  9999  10000  10001  64232
1234 5678 6789 A A B B C C D D E E F F G G H H I I J J K K  L L  M M  N N  O O   P P   Q Q   R R    S S    T T
1342 5786 6897 B B C C D D E E F F A A B B C C D D E E F F  G G  H H  I I  J J   K K   L L   M M    N N    O O
1423 5867 6978 C C D D E E F F G G B B C C D D E E F F G G  H H  I I  J J  K K   L L   M M   N N    O O    Z Z
$
$

tyler_durden
# 4  
Old 12-04-2009
Two spaces off for the header, but it isn't entirely clear what that looks like anyway
Code:
sed -r 's/(.)(\1)/\1 \2/g' file

-or-
Code:
sed 's/\(.\)\(\1\)/\1 \2/g' infile

# 5  
Old 12-07-2009
Code:
while(<DATA>){
	my @tmp = split(" ",$_,4);
	$tmp[3]=~s/(?<=[A-Z])(?=[A-Z])/ /g;
	print join " ",@tmp;
}
__DATA__
1234 5678 6789 AA BB CC DD EE....ZZ
1342 5786 6897 BB CC DD EE FF....AA
1423 5867 6978 CC DD EE FF GG....BB

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Combine columns from many files but keep them aligned in columns-shorter left column issue

Hello everyone, I searched the forum looking for answers to this but I could not pinpoint exactly what I need as I keep having trouble. I have many files each having two columns and hundreds of rows. first column is a string (can have many words) and the second column is a number.The files are... (5 Replies)
Discussion started by: isildur1234
5 Replies

2. Shell Programming and Scripting

Compare 2 files and match column data and align data from 3 column

Hello experts, Please help me in achieving this in an easier way possible. I have 2 csv files with following data: File1 08/23/2012 12:35:47,JOB_5330 08/23/2012 12:35:47,JOB_5330 08/23/2012 12:36:09,JOB_5340 08/23/2012 12:36:14,JOB_5340 08/23/2012 12:36:22,JOB_5350 08/23/2012... (5 Replies)
Discussion started by: asnandhakumar
5 Replies

3. Shell Programming and Scripting

Help with separating datatype, column name

Hi All, I am new to unix but have a requirement wherein I need to separate datatype,length, and column name from input file which is of below format -- record integer(10) empid; string(25) name; date("YYYY-MM-DD") dob; decimal(10) salary; end now after getting datatype,its length and... (4 Replies)
Discussion started by: phoenix09
4 Replies

4. Shell Programming and Scripting

Splitting the data in a column into several columns

Hi, I have the following input file 32895901-d17f-414c-ac93-3e7e0f5ec240 AND @GDF_INPUT 73b129e1-1fa9-4c0d-b95b-4682e5389612 AUS @GDF_INPUT 40f82e88-d1ff-4ce2-9b8e-d827ddb39447 BEL @GDF_INPUT 36e9c3f1-042a-43a4-a80e-4a3bc2513d01 BGR @GDF_INPUT I want to split column 3 into two columns:... (1 Reply)
Discussion started by: ramky79
1 Replies

5. UNIX for Dummies Questions & Answers

How to match 2 columns where one column has data as a range - extended

Dear all, there is a nice solution for a text merge where the second file has only variables with a numeric range ( sorry, cannot post URL + thread is closed ). The real world is however more complicated than in the earlier example. file1 A 1 A 2 A 3 B 1 B 2 B 3 B 4 C 1 C 2 C 3 C... (4 Replies)
Discussion started by: underscore
4 Replies

6. Ubuntu

How to compare two columns and fetch the common data with additional column

Dear All, I am new to this forum and please ignore my little knowledge :p I have two types of data (a subset is given below) data version 1: 439798 2 1 451209 1 2 508696 2 1 555760 2 1 582757 1 2 582889 1 2 691827... (2 Replies)
Discussion started by: evoll
2 Replies

7. UNIX for Dummies Questions & Answers

How to match 2 columns where one column has data as a range

Hi, I have a query about joining files using data ranges. Example files below - I want to join file1 to file2 with matches where file1 column 1 is equal to file2 column1, and file1 column 2 is within the range of file2 columns 3 and 4. I would like rows which don't match to be printed too. ... (4 Replies)
Discussion started by: auburn
4 Replies

8. Shell Programming and Scripting

How to convert 2 column data into multiple columns based on a keyword in a row??

Hi Friends I have the following input data in 2 columns. SNo 1 I1 Value I2 Value I3 Value SNo 2 I4 Value I5 Value I6 Value I7 Value SNo 3 I8 Value I9 Value ............... ................ SNo N (1 Reply)
Discussion started by: ks_reddy
1 Replies

9. Shell Programming and Scripting

Separating the Columns based on the ColumnSize

Hi Gurus, i had a Table with the columns col1 Decimal(3), col2 Decimal(2), col3 String(1), col4_blank1 String(2), col5_blank2 String(10), col6 String(3); the Data(Records) of the Table in flat file is 89522D 085NOVOLIN... (2 Replies)
Discussion started by: SeenuGuddu
2 Replies

10. Shell Programming and Scripting

Convert two column data into 8 columns

Apologies if this has been covered - I did search but couldn't find what I was looking for. I have a simple X-Y input file. I want to convert it from two columns into 8 columns - 4 pairs of X-Y data. So my input file looks like X1 Y1 X2 Y2 X3 Y3 X4 Y4 X5 Y5 etc And I want it to look... (8 Replies)
Discussion started by: NickC
8 Replies
Login or Register to Ask a Question