Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Replicating rows by splitting column in text file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replicating rows by splitting column in text file
# 1  
Old 03-29-2012
Replicating rows by splitting column in text file
Hi, I have flat file with following format

Code:
Col1, Col2, Col3, Col4
---------------------------------
r1_c1, r1_c2, r1_c3,  abc | def  | efg 
r2_c1, r2_c2, r2_c3,  abcwdw | dweweef  | efg | ijk
r3_c1, r3_c2, r3_c3,  abaac
...........

The first three columns contain only one entry per row.
The 4th column contains one or more entries joined together by " | "

Need to convert this file into file of 4 columns only with first element of last column, and replicate rows for remaining elements of last row.. like below

Code:
r1_c1, r1_c2, r1_c3,  abc 
r1_c1, r1_c2, r1_c3,  def
r1_c1, r1_c2, r1_c3,  efg
r2_c1, r2_c2, r2_c3,  abcwdw
r2_c1, r2_c2, r2_c3,  dweweef
r2_c1, r2_c2, r2_c3,  efg
r2_c1, r2_c2, r2_c3,  ijk
r3_c1, r3_c2, r3_c3,  abaac

Thanks !!
Last edited by Scrutinizer; 03-29-2012 at 10:09 AM.. Reason: please Use [code] tags [/code], thank you..
# 2  
Old 03-29-2012
Code:
awk '{label=$1FS$2FS$3}{for(i=4; i<=NF; ++i) if ($i != "|") print label,$i}' infile

# 3  
Old 03-29-2012
Code:
awk -F', ' '{gsub(/\|/,RS $1 FS $2 FS $3 FS)}1' infile

# 4  
Old 03-29-2012
thanks Scrutinizer and
huaihaizi3! Got it working!!
Last edited by nick2011; 03-29-2012 at 10:56 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Extracting rows from a text file if the value of a column falls between a certain range

Hi, I have a file that looks like the following: 10 100080417 rs7915867 ILMN_1343295 12 6243093 7747537 10 100190264 rs2296431 ILMN_1343295 12 6643093 6647537 10 100719451 SNP94374 ILMN_1343295 12 6688093 7599537 ... (1 Reply)
Discussion started by: evelibertine
1 Replies

2. UNIX for Dummies Questions & Answers

[SOLVED] splitting a single column(with spaces) into multiple rows

Hi All, My requisite is to split a single column of phonemes seperated by spaces into multiple rows. my input file is: a dh u th a qn ch A v U r k my o/p should be like: adhu a dh u (3 Replies)
Discussion started by: girlofgenuine
3 Replies

3. UNIX for Dummies Questions & Answers

Extracting rows from a space delimited text file based on the values of a column

I have a space delimited text file. I want to extract rows where the third column has 0 as a value and write those rows into a new space delimited text file. How do I go about doing that? Thanks! (2 Replies)
Discussion started by: evelibertine
2 Replies

4. UNIX for Dummies Questions & Answers

Replicating many rows in unix

Hi If my input is abcdefgh ijklmnop then the output should be: abcdefgh abcdefgh abcdefgh abcdefgh ijklmnop (6 Replies)
Discussion started by: pandeesh
6 Replies

5. UNIX for Dummies Questions & Answers

Extracting rows from a text file based on numerical values of a column

I have a text file where the second column is a list of numbers going from small to large. I want to extract the rows where the second column is smaller than or equal to 0.0001. My input: rs10082730 9e-08 12 46002702 rs2544081 1e-07 12 46015487 rs1425136 1e-06 7 35396742 rs2712590... (1 Reply)
Discussion started by: evelibertine
1 Replies

6. Shell Programming and Scripting

splitting single column values into text and number component

Hey guys, I have a column that consists of string and integer values without a distinctive deliminator, looking like this... 7ASA 14LAL 245FOO 656MOM 87577DAD ... I want to split the column into two columns, one containing the numbers and one containing the text part. edit: numbers... (3 Replies)
Discussion started by: origamisven
3 Replies

7. UNIX for Dummies Questions & Answers

Extracting rows from a text file based on the first column

I have a tab delimited text file where the first column can take on three different values : 100, 150, 250. I want to extract all the rows where the first column is 100 and put them into a separate text file and so on. This is what my text file looks like now: 100 rs3794811 0.01 0.3434 100... (1 Reply)
Discussion started by: evelibertine
1 Replies

8. UNIX for Dummies Questions & Answers

Extracting rows from a text file based on the first column

I have a tab delimited text file where the first column can take on three different values : 100, 150, 250. I want to extract all the rows where the first column is 100 and put them into a separate text file and so on. This is what my text file looks like now: 100 rs3794811 0.01 0.3434... (1 Reply)
Discussion started by: evelibertine
1 Replies

9. Linux

Splitting a Text File by Rows

Hello, Please help me. I have hundreds of text files composed of several rows of information and I need to separate each row into a new text file. I was trying to figure out how to split the text file into different text files, based on each row of text in the original text file. Here is an... (2 Replies)
Discussion started by: dvdrevilla
2 Replies

10. UNIX for Dummies Questions & Answers

splitting a column into rows

I have a column of data of the format: EDITORIAL OPED 193987141 193986701 193987451 193986321 STATISTICS 193986351 COLUMN EDITORIAL OPED 193987171 NEWS 193321171 NEWS 193321111 NEWS 193320891 NEWS 193321841 (3 Replies)
Discussion started by: spindoctor
3 Replies
Login or Register to Ask a Question