Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Auto incrementing a particular column values

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Auto incrementing a particular column values
# 1  
Old 04-25-2012
Auto incrementing a particular column values
Hi All,

I have the input file(.csv) like below.

Code:
SNo,Step,Data
1,0,1
2,0,2
3,1,1
4,2,2
5,0,3
6,0,4

and I need the following output.

Code:
1,0,1
2,0,2
3,1,1
4,2,2
5,3,3
6,3,4

Here is the 2nd column(step number) was supposed to be in increasing order. So all repeated 0's should be replaced with 3 in this case. (In my files only 0's appear again as shown in input file, no other numbers will come like that).

I need the script using awk which can do this conversion.

Thanks
Sid
# 2  
Old 04-25-2012
Try:
Code:
awk -F, 'NR==1{n=0} $2{n=$2+1} !$2 && n{$2=n}1' OFS=, file

Code:
$ cat file
1,0,1
2,0,2
3,1,1
4,2,2
5,0,3
6,0,4
$
$ awk -F, 'NR==1{n=0} $2{n=$2+1} !$2 && n{$2=n}1' OFS=, file
1,0,1
2,0,2
3,1,1
4,2,2
5,3,3
6,3,4

# 3  
Old 04-25-2012
The script provided by you is not working if the data has headers
Thanks Franklin for your prompt reply.

But if my data has column headers(all above the data) as shown in my original input file this script is not giving desired output.
I tried the modified version of your script, but still same problem.

Code:
awk -F, 'NR>1{n=0} $2{n=$2+1} !$2 && n{$2=n}1' OFS=, file

Can you correct the above script to ignore my first row(since it is column headers) and work on second row onwards.

Thanks
Sid
# 4  
Old 04-25-2012
Quote:
Originally Posted by ks_reddy
Thanks Franklin for your prompt reply.

But if my data has column headers(all above the data) as shown in my original input file this script is not giving desired output.
I tried the modified version of your script, but still same problem.

Code:
awk -F, 'NR>1{n=0} $2{n=$2+1} !$2 && n{$2=n}1' OFS=, file

Can you correct the above script to ignore my first row(since it is column headers) and work on second row onwards.

Thanks
Sid
Try this:
Code:
awk -F, 'NR==1{n=0;print;next} $2{n=$2+1} !$2 && n{$2=n}1' OFS=, file

# 5  
Old 04-25-2012
Thats awesome !!!
Thanks Franklin!
This saved me lot of time in processing headers and data part separately.
I owe you a lunch. Smilie

Sid
# 6  
Old 04-25-2012
Quote:
Originally Posted by ks_reddy
Thanks Franklin!
This saved me lot of time in processing headers and data part separately.
I owe you a lunch. Smilie

Sid
You're welcome! Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Add an incrementing identity column to a file

Two questions: 1) Is there a way to create a new column (i.e. ID) to an existing file containing data and the new column ID populate as an auto incrementing number? for example: (Current file has headers and is PIPE delimited.) **data looks like this** "COL1"|"COL2"|"COL3"|"COL4"|"COL5"... (4 Replies)
Discussion started by: timlisa20
4 Replies

2. UNIX for Advanced & Expert Users

Add an incrementing identity column to a file

Two questions: 1) Is there a way to create a new column (i.e. ID) to an existing file containing data and the new column ID populate as an auto incrementing number? for example: (Current file has headers and is PIPE delimited.) **data looks like this** "COL1"|"COL2"|"COL3"|"COL4"|"COL5"... (0 Replies)
Discussion started by: timlisa20
0 Replies

3. Shell Programming and Scripting

awk Print New Column For Every Two Lines and Match On Multiple Column Values to print another column

Hi, My input files is like this axis1 0 1 10 axis2 0 1 5 axis1 1 2 -4 axis2 2 3 -3 axis1 3 4 5 axis2 3 4 -1 axis1 4 5 -6 axis2 4 5 1 Now, these are my following tasks 1. Print a first column for every two rows that has the same value followed by a string. 2. Match on the... (3 Replies)
Discussion started by: jacobs.smith
3 Replies

4. Shell Programming and Scripting

Converting odd values to even values(or vice-versa) located in a column

Hello All, I have a below data in a .csv file where all rows where col1 is A, col2 is odd numbers, similarly even numbers for all rows where col1 is B. Note that my data has some other columns(not shown here) too (around 100) after col2. Tool,Data A,1 A,3 A,5 .... so on B,2 B,4 .... ... (4 Replies)
Discussion started by: ks_reddy
4 Replies

5. UNIX for Dummies Questions & Answers

shift values in one column as header for values in another column

Hi Gurus, I have a tab separated text file with two columns. I would like to make the first column values as headings for the second column values. Ex. >value1 subjects >value2 priorities >value3 requirements ...etc and I want to have a file >value1 subjects >value2 priorities... (4 Replies)
Discussion started by: Unilearn
4 Replies

6. Shell Programming and Scripting

Cat Values from Several files if it meets criteria for column values

I have results from some statistical analyses. The format of the results are as given below: I want to select lines that have a p-value (last column) less than 0.05 from all the results files (*.results) and cat to a new results file. It would be very nice if a new column is added that tells... (2 Replies)
Discussion started by: genehunter
2 Replies

7. Shell Programming and Scripting

Incrementing ascii values

Hi All, I require some help with the below: I am trying to incriment the ascii value of a letter and then print it. So basically "a" becomes "b" and "z" becomes "A". Does anyone have any pointers? Cheers, Parks (10 Replies)
Discussion started by: bParks
10 Replies

8. Shell Programming and Scripting

print unique values of a column and sum up the corresponding values in next column

Hi All, I have a file which is having 3 columns as (string string integer) a b 1 x y 2 p k 5 y y 4 ..... ..... Question: I want get the unique value of column 2 in a sorted way(on column 2) and the sum of the 3rd column of the corresponding rows. e.g the above file should return the... (6 Replies)
Discussion started by: amigarus
6 Replies

9. Shell Programming and Scripting

Unix c-shell - replacing/incrementing values in columns?

2 21 1 12 3 123 4 1234 6 49 0 49 33 212 I need to replace/increment all the values in the 2nd column that correspond to 0 in the first column. so for 0 49 i would get 0 50 this can be done through: paste num4.txt... (14 Replies)
Discussion started by: audrey_flox
14 Replies

10. Shell Programming and Scripting

How to pick values from column based on key values by usin AWK

Dear Guyz:) I have 2 different input files like this. I would like to pick the values or letters from the inputfile2 based on inputfile1 keys (A,F,N,X,Z). I have done similar task by using awk but in that case the inputfiles are similar like in inputfile2 (all keys in 1st column and values in... (16 Replies)
Discussion started by: repinementer
16 Replies
Login or Register to Ask a Question