awk script to append suffix to column when column has duplicated values


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk script to append suffix to column when column has duplicated values
# 1  
Old 05-09-2018
awk script to append suffix to column when column has duplicated values

Please help me to get required output for both scenario 1 and scenario 2 and need separate code for both scenario 1 and scenario 2

Scenario 1
i need to do below changes only when column1 is CR and column3 has duplicates rows/values. This inputfile can contain 100 of this duplicated rows of column3 Value in column 3 to be suffixe with sequence starting from "a" followed by CR.
if we have appended suffix till a to z with CR like(aCR, bCR, cCR till zCR), then next suffix will be aaCR, abCR, acCR ans so on for column3

Input File

Code:
a||c
CR||2157237496
CR||2157237496
CR||2157237496
INV||2157237496
RNV||3457634589

Output File

Code:
a||c
CR||2157237496aCR
CR||2157237496bCR
CR||2157237496cCR
INV||2157237496
RNV||3457634589

Scenario 2 Required separate code for this scenario Below is for different file i need to do below changes only when column1 is DR and column3 has duplicates rows/values.
Value in column 3 to be suffixe with sequence starting from "a" except for first document followed by DR.
if we have appended suffix till a to z with DR like(aDR, bDR, cDR till zDR), then next suffix will be aaDR, abDR, acDR ans so on for column3

Input File

Code:
a||c
DR||3770022521
DR||3770022521
DR||3770022521
INV||9876543738

output file

a||c
Code:
DR||3770022521
DR||3770022521aDR
DR||3770022521bDR
INV||9876543738

I tried below code, it is giving me output for scenario 2 but not able to append sequence number to column3. I am able to suffix only "a" which is static. Here need to consider column1 equal to CR for scenario 1 and DR for scenario2 as well(that im unable to do)

Code:
awk -F"|" -v OFS="|" '{if(++a[$3]>1)$3=$3"a"}1' d1.txt

Code output :

Code:
a||c
CR||2157237496
CR||2157237496a
CR||2157237496a
INV||2157237496a
RNV||3457634589

Please help me to get required output for both scenario 1 and scenario 2 and need separate code for both scenario 1 and scenario 2
# 2  
Old 05-09-2018
Scenario 1:
Code:
awk -F\| '
$1 == "CR"      {IX = $3
                 if (!T[IX]) T[IX] = S[IX] = 96
                 $3 = $3 sprintf ("%s%c%s", A[IX], ++T[IX], $1)
                 if (T[IX] > 121)       {A[IX] = sprintf ("%c", ++S[IX])
                                         T[IX] = 96
                                        }
                }
1
' OFS=\| file

Scenario 2:
Code:
awk -F\| '
$1 == "DR"      {IX = $3
                 if (!T[IX])    T[IX] = S[IX] = 96
                 else           $3 = $3 sprintf ("%s%c%s", A[IX], ++T[IX], $1)
                 if (T[IX] > 121)       {A[IX] = sprintf ("%c", ++S[IX])
                                         T[IX] = 96
                                        }
                }
1
' OFS=\| file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need awk or Shell script to compare Column-1 of two different CSV files and print if column-1 matche

Example: I have files in below format file 1: zxc,133,joe@example.com cst,222,xyz@example1.com File 2 Contains: hxd hcd jws zxc cst File 1 has 50000 lines and file 2 has around 30000 lines : Expected Output has to be : hxd hcd jws (5 Replies)
Discussion started by: TestPractice
5 Replies

2. Shell Programming and Scripting

awk to Sum columns when other column has duplicates and append one column value to another with Care

Hi Experts, Please bear with me, i need help I am learning AWk and stuck up in one issue. First point : I want to sum up column value for column 7, 9, 11,13 and column15 if rows in column 5 are duplicates.No action to be taken for rows where value in column 5 is unique. Second point : For... (1 Reply)
Discussion started by: as7951
1 Replies

3. Shell Programming and Scripting

Matching column value from 2 different file using awk and append value from different column

Hi, I have 2 csv files. a.csv HUAWEI,20LMG011_DEKET_1296_RTN-980_IDU-1-11-ISV3-1(to LAMONGAN_M),East_Java,20LMG011_DEKET_1296_RTN-980_IDU-1,20LMG011,20LMG 027_1287_LAMONGAN_RTN980_IDU1,20LMG027,1+1(HSB),195.675,20LMG011-20LMG027,99.9995,202.6952012... (7 Replies)
Discussion started by: tententen
7 Replies

4. Shell Programming and Scripting

Trying to get an awk script to replace values in column

I'm trying to make an awk script to compare values I've set as var1, var2, and var3 earlier in the script to the values in the userinputted column of four text files called Node1.txt, Node2.txt, Node3.txt, and Node4.txt and then replace the values in that userinputted column with either ttt or gcc,... (8 Replies)
Discussion started by: Eric1
8 Replies

5. Shell Programming and Scripting

How to delete 'duplicated' column values and make a delimited file too?

Hi, I have the following output from an Oracle SQL statement and I want to remove duplicated column values. I know it is possible using Oracle analytical/statistical functions but unfortunately I don't know how to use any of those. So now, I've gone to PLAN B using awk/sed maybe or any... (5 Replies)
Discussion started by: newbie_01
5 Replies

6. Shell Programming and Scripting

Match value in column and append file with new values

Hi, I need help to match two files based on two columns. file_1 ID AA An Ca Ele Pro Su Ot Tra g13950 No No Yes No Yes Yes Yes Yes g05760 Yes No No No No Yes Yes Yes g12640 No No No No No No No No k17720 No Yes No No No No No Yes g05640 Yes Yes Yes No No Yes Yes Yes file_2 ... (8 Replies)
Discussion started by: redse171
8 Replies

7. 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

8. UNIX for Dummies Questions & Answers

append column and row header to a file in awk script.

Hi! Is there a way to append column and row header to a file in awk script. For example if I have Jane F 39 manager Carlos M 40 system administrator Sam F 20 programmer and I want it to be # name gend age occup 1 Jane F 39 manager 2 Carlos M ... (4 Replies)
Discussion started by: FUTURE_EINSTEIN
4 Replies

9. Shell Programming and Scripting

AWK script to create max value of 3rd column, grouping by first column

Hi, I need an awk script (or whatever shell-construct) that would take data like below and get the max value of 3 column, when grouping by the 1st column. clientname,day-of-month,max-users ----------------------------------- client1,20120610,5 client2,20120610,2 client3,20120610,7... (3 Replies)
Discussion started by: ckmehta
3 Replies

10. Shell Programming and Scripting

for each different entry in column 1 extract maximum values from column 2 in unix/awk

Hello, I have 2 columns (1st column has multiple entries but the corresponding values in the column 2 may be the same or different.) however I want to extract unique values for each entry in column 1 by assigning the max value from column 2 SDF4 -0.211654 SDF4 0.978068 ... (1 Reply)
Discussion started by: Diya123
1 Replies
Login or Register to Ask a Question