Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Fill column from previous row

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Fill column from previous row
# 1  
Old 06-02-2017
Fill column from previous row
Hi,

I have the following content in file

Code:
ABBR  DESC          COL3 COL4 COL5 COL6
AAA   text desc aaa text text text text
                    text text text text
                    text text text text
BBB   text desc bbb text text text text
                    text text text text
CCC   text desc ccc text text text text
                    text text text text
                    text text text text
                    text text text text
DDD   test desc ddd text text text text

and I'm expecting the following

Code:
ABBR  DESC          COL3 COL4 COL5 COL6
AAA   text desc aaa text text text text
AAA   text desc aaa text text text text
AAA   text desc aaa text text text text
BBB   text desc bbb text text text text
BBB   text desc bbb text text text text
CCC   text desc ccc text text text text
CCC   text desc ccc text text text text
CCC   text desc ccc text text text text
CCC   text desc ccc text text text text
DDD   test desc ddd text text text text

Appreciate your help
# 2  
Old 06-02-2017
making some assumptions.... given myFile:
Code:
ABBR  DESC          COL3 COL4 COL5 COL6
AAA   text desc aaa text1 text text text
                    text text text text
                    text text text text
BBB   text desc bbb text2 text text text
                    text text text text
CCC   text desc ccc text3 text text text
                    text text text text
                    text text text text
                    text text text text
DDD   test desc ddd text4 text text text

Code:
awk '
  FNR>1 {
    if (NF>4)
       miss=substr($0,1, length-index($0, $(NF-3)))
    else
       $1=miss OFS $1
}
1' myFile

we get:
Code:
ABBR  DESC          COL3 COL4 COL5 COL6
AAA   text desc aaa text1 text text text
AAA   text desc aaa text text text text
AAA   text desc aaa text text text text
BBB   text desc bbb text2 text text text
BBB   text desc bbb text text text text
CCC   text desc ccc text3 text text text
CCC   text desc ccc text text text text
CCC   text desc ccc text text text text
CCC   text desc ccc text text text text
DDD   test desc ddd text4 text text text

# 3  
Old 06-02-2017
Dear bobbygsk ,

With 149 posts to date, I'm hoping you could answer these basic questions first:-
  • Is this homework/assignment? There are specific forums for these.
  • What have you tried so far?
  • What output/errors do you get?
  • What OS and version are you using?
  • What are your preferred tools? (C, shell, perl, awk, etc.)
  • What logical process have you considered? (to help steer us to follow what you are trying to achieve)
Most importantly, What have you tried so far?

There are probably many ways to achieve most tasks, so giving us an idea of your style and thoughts will help us guide you to an answer most suitable to you so you can adjust it to suit your needs in future.


We're all here to learn and getting the relevant information will help us all.


Kind regards,
Robin
# 4  
Old 06-02-2017
---------- Post updated at 11:24 AM ---------- Previous update was at 11:23 AM ----------

Quote:
Originally Posted by vgersh99
making some assumptions.... given myFile:
Code:
ABBR  DESC          COL3 COL4 COL5 COL6
AAA   text desc aaa text1 text text text
                    text text text text
                    text text text text
BBB   text desc bbb text2 text text text
                    text text text text
CCC   text desc ccc text3 text text text
                    text text text text
                    text text text text
                    text text text text
DDD   test desc ddd text4 text text text

Code:
awk '
  FNR>1 {
    if (NF>4)
       miss=substr($0,1, length-index($0, $(NF-3)))
    else
       $1=miss OFS $1
}
1' myFile

we get:
Code:
ABBR  DESC          COL3 COL4 COL5 COL6
AAA   text desc aaa text1 text text text
AAA   text desc aaa text text text text
AAA   text desc aaa text text text text
BBB   text desc bbb text2 text text text
BBB   text desc bbb text text text text
CCC   text desc ccc text3 text text text
CCC   text desc ccc text text text text
CCC   text desc ccc text text text text
CCC   text desc ccc text text text text
DDD   test desc ddd text4 text text text

Hi Vgersh99...

The data has tab between columns. Not working.
Appreciate your help though

---------- Post updated at 11:24 AM ---------- Previous update was at 11:24 AM ----------

Quote:
Originally Posted by rbatte1
Dear bobbygsk ,

With 149 posts to date, I'm hoping you could answer these basic questions first:-
  • Is this homework/assignment? There are specific forums for these.
  • What have you tried so far?
  • What output/errors do you get?
  • What OS and version are you using?
  • What are your preferred tools? (C, shell, perl, awk, etc.)
  • What logical process have you considered? (to help steer us to follow what you are trying to achieve)
Most importantly, What have you tried so far?

There are probably many ways to achieve most tasks, so giving us an idea of your style and thoughts will help us guide you to an answer most suitable to you so you can adjust it to suit your needs in future.


We're all here to learn and getting the relevant information will help us all.


Kind regards,
Robin
rbatte1... This is not homework question.
1) did not touch unix for long time.
2) I tried prev posts here. but none of them have space in a column value. So did not get the required results.
3) So much of other deadlines to meet so not able to think properly.
# 5  
Old 06-02-2017
should have mentioned the field separator in the beginning...
try this:
Code:
awk -F'\t' '
  FNR>1  && NF{
    if ($1)
      miss=$1 OFS $2
    else
      $0=(miss OFS $(NF-3) OFS $(NF-2) OFS $(NF-1) OFS $NF)
}
1' OFS='\t' myFile

These 2 Users Gave Thanks to vgersh99 For This Post:
bobbygsk rbatte1
# 6  
Old 06-02-2017
Hello bobbygsk,

Could you please try following and let me know if this helps you.
Code:
awk 'FNR==1{$2=$2 OFS OFS;print;next}!/^ /{$1=$1;VAL=$1 OFS $2 OFS $3 OFS $4;print;next} {$1=$1;print VAL,$0}' OFS="\t"   Input_file

If you have TAB delimited Input_file then add FS="\t" before OFS="\t".

Thanks,
R. Singh
# 7  
Old 06-02-2017
Quote:
Originally Posted by vgersh99
should have mentioned the field separator in the beginning...
try this:
Code:
awk -F'\t' '
  FNR>1  && NF{
    if ($1)
      miss=$1 OFS $2
    else
      $0=(miss OFS $(NF-3) OFS $(NF-2) OFS $(NF-1) OFS $NF)
}
1' OFS='\t' myFile

Thanks. It works. Appreciate your help

---------- Post updated at 01:40 PM ---------- Previous update was at 01:37 PM ----------

Quote:
Originally Posted by RavinderSingh13
Hello bobbygsk,

Could you please try following and let me know if this helps you.
Code:
awk 'FNR==1{$2=$2 OFS OFS;print;next}!/^ /{$1=$1;VAL=$1 OFS $2 OFS $3 OFS $4;print;next} {$1=$1;print VAL,$0}' OFS="\t"   Input_file

If you have TAB delimited Input_file then add FS="\t" before OFS="\t".

Thanks,
R. Singh
Tried with FS="\t" before OFS="\t" but did not work.

Appreciate your time trying to solve.

Thanks
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Perl script to fill the entire row of Excel file with color based on pattern match

Hi All , I have to write one Perl script in which I need to read one pre-existing xls and based on pattern match for one word in some cells of the XLS , I need to fill the entire row with one color of that matched cell and write the content to another excel Please find the below stated... (2 Replies)
Discussion started by: kshitij
2 Replies

2. Shell Programming and Scripting

Fill data in column with previous value

Gents, Kindly help me. I have a file with empty values in selected column, I will like to fill the empty values with the previous value. Example Input file X 4959 30010 66727.00 20457.001 1 1441 66512.00 20234.00 20520.001 X 4959 30010 66727.00 20457.001 145 ... (7 Replies)
Discussion started by: jiam912
7 Replies

3. UNIX for Dummies Questions & Answers

awk to print first row with forth column and last row with fifth column in each file

file with this content awk 'NR==1 {print $4} && NR==2 {print $5}' file The error is shown with syntax error; what can be done (4 Replies)
Discussion started by: cdfd123
4 Replies

4. Shell Programming and Scripting

Subtracting each row from the first row in a single column file using awk

Hi Friends, I have a single column data like below. 1 2 3 4 5 I need the output like below. 0 1 2 3 4 where each row (including first row) subtracting from first row and the result should print below like the way shown in output file. Thanks Sid (11 Replies)
Discussion started by: ks_reddy
11 Replies

5. Shell Programming and Scripting

fill in last column of data

Hello, I am fairly new to awk, and I have the following problem. My file has missing data in the last column, and the program I am pre-processing this file for cannot interpret correctly shortened rows (it just wraps the data around). Is there a way to force awk to create the same... (6 Replies)
Discussion started by: timert34
6 Replies

6. Shell Programming and Scripting

Fill missing numbers in second column with zeros

Hi All, I have 100 files with names like this: 1.dat, 2.dat, 3.dat until 100.dat. My dat files look like this: 42323 0 438939 1 434 0 0.9383 3434 120.23 3 234 As you can see in the second column, some numbers are missing. I want to fill those missing places with 0's in all... (3 Replies)
Discussion started by: shoaibjameel123
3 Replies

7. Shell Programming and Scripting

remove row if string is same as previous row

I have data like: Blue Apple 6 Red Apple 7 Yellow Apple 8 Green Banana 2 Purple Banana 8 Orange Pear 11 What I want to do is if $2 in a row is the same as $2 in the previous row remove that row. An identical $2 may exist more than one time. So the out file would look like: Blue... (4 Replies)
Discussion started by: dcfargo
4 Replies

8. Shell Programming and Scripting

Moving data from a specified column/row to another column/row

Hello, I have an input file like the following: 11_3_4 2_1_35 3_15__ _16989 Where '_' is a space. The data is in a table. Is there a way for the program to prompt the user for x1,y1 and x2,y2, where x1,y1 is the desired number (for example x=6 y=4 is a value of 4) and move to a desired spot... (2 Replies)
Discussion started by: jl487
2 Replies

9. Shell Programming and Scripting

Changing the column for a row in a text file and adding another row

Hi, I want to write a shell script which increments a particular column in a row from a text file and then adds another row below the current row with the incremented value . For Eg . if the input file has a row : abc xyz lmn 89 lm nk o p I would like the script to create something like... (9 Replies)
Discussion started by: aYankeeFan
9 Replies

10. Shell Programming and Scripting

Value of previous row using awk

I would like to know value for previous row, in addition to current row. How would I will get value for previous row? How can I perform this with awk? (2 Replies)
Discussion started by: videsh77
2 Replies
Login or Register to Ask a Question