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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to Sum columns when other column has duplicates and append one column value to another with Care
# 1  
Old 05-05-2018
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 duplicate rows, need to make them as single row in output file.
Third point : For duplicate rows, want to put all value of column 2 other from input file in column 16 in format (IN272018000235^IN27201523963) in output file other than value which is stored(IN27201800023963) in column 2 in output file.

Ex - In output file, please see column 16 it should have value as IN272018000235^IN27201523963(taken from column2) only from first three rows(duplicate) .Here we have not taken 3rd value(IN27201800023963) in column 16 of output file from column 2 of input file bcz we have already stored in column 2(IN27201800023963) of output file.

Have record of thousand of line..so duplicate rows are not limited to three, there can be 40, 50 any number of duplicate rows. So accordingly need to have value in column 57.

Please let me know if still my query is not clear

Input File


Code:
a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p
    POS|IN27201800023963|2018-04-24||27AACCE5198E1ZJ||500|0||9|45|9|45|||
    POS|IN272018000235|2018-04-24||27AACCE5198E1ZJ||500|0||9|45|9|45|||
    POS|IN27201523963|2018-04-24||27AACCE5198E1ZJ||500|0||9|45|9|45|||
    POS|IN27201800022938|2018-04-05||27AAJFH2012G1ZS||2|4||6||7|8|||

Expected Output File

Code:
   a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p
    POS|IN27201800023963|2018-04-24||27AACCE5198E1ZJ||1500|0||9|135|9|135|||IN272018000235^IN27201523963|
    POS|IN27201800022938|2018-04-05||27AAJFH2012G1ZS||2|4||6||7|8|||


Below is code tried to sum up value but giving output as
Code:
awk 'BEGIN{ FS = OFS = "|" }
       NR > 1{
           if ($5 == f5) {
               $7 += f7; $9 += f9; $11 += f11; $13 += f13;
               $15 += f15; $16 = $16 f2"^"$2
           } else { print rec }
       }
       {
           rec = $0; f5 = $5; f7 = $7; f9 = $9;
           f11 = $11; f13 = $13; f15 = $15; f2 = $2
       }
       END{ print rec }' input.txt

Code output: Not correct because getting column 2 value "IN27201523963" also in column 16 instead it should be like "IN272018000235^IN27201800023963"
The value which is present in column13 should not be present in column16.


Code:
POS|IN27201523963|2018-04-24||27AACCE5198E1ZJ||1500|0|0|9|135|9|135||0|IN272018000235^IN27201523963
POS|IN27201800022938|2018-04-05||27AAJFH2012G1ZS||2|4||6||7|8|||

# 2  
Old 05-05-2018
Moderator's Comments:
Mod Comment Double posting is not allowed. Having three threads to discuss the same topic is a great way to confuse anyone trying to follow what is going on in your threads.

Continue any discussion on this topic in your original thread: Do replace operation and awk to sum multiple columns if another column has duplicate values

This thread is closed.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Copy columns from one file into another and get sum of column values and row count

I have a file abc.csv, from which I need column 24(PurchaseOrder_TotalCost) to get the sum_of_amounts with date and row count into another file say output.csv abc.csv- UTF-8,,,,,,,,,,,,,,,,,,,,,,,,, ... (6 Replies)
Discussion started by: Tahir_M
6 Replies

2. Shell Programming and Scripting

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... (1 Reply)
Discussion started by: as7951
1 Replies

3. Shell Programming and Scripting

Do replace operation and awk to sum multiple columns if another column has duplicate values

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... (12 Replies)
Discussion started by: as7951
12 Replies

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

5. Shell Programming and Scripting

Sum of Columns Base on First Column

Input :- Hd1;Hd2:hd3;Hd4;Hd5 X;1;2;3;4 Y;2;3;5;6 Z;3;5;6;7 X;10;11;24;16 Y;11;23;21;1 Z;10;13;14;15 X;0;1;2;0 K;0;0;0;0 K;0;0;0;0 I want Sum Data base on first column; Hd1;Hd2:hd3;Hd4;Hd5 X;11;14;29;20 Y;12;26;26;7 Z;13;18;20;22 K;0;0;0;0 (4 Replies)
Discussion started by: pareshkp
4 Replies

6. Shell Programming and Scripting

awk to sum a column based on duplicate strings in another column and show split totals

Hi, I have a similar input format- A_1 2 B_0 4 A_1 1 B_2 5 A_4 1 and looking to print in this output format with headers. can you suggest in awk?awk because i am doing some pattern matching from parent file to print column 1 of my input using awk already.Thanks! letter number_of_letters... (5 Replies)
Discussion started by: prashob123
5 Replies

7. Shell Programming and Scripting

Remove Duplicates on multiple Key Columns and get the Latest Record from Date/Time Column

Hi Experts , we have a CDC file where we need to get the latest record of the Key columns Key Columns will be CDC_FLAG and SRC_PMTN_I and fetch the latest record from the CDC_PRCS_TS Can we do it with a single awk command. Please help.... (3 Replies)
Discussion started by: vijaykodukula
3 Replies

8. UNIX for Dummies Questions & Answers

append a column by concatenating other columns

Hi In a tab delimited file how can I add a column that have values concatenated from all columns. For example input.txt test1 test2 test3 zz2 mm uu pp3 yy kk ss2 tt ll zz3 mm uu pp23 yy kk ss3 tt ll 11e 22 44 33c 22 99 output.txt test1 test2 test3 reslt (6 Replies)
Discussion started by: mary271
6 Replies

9. Shell Programming and Scripting

sum multiple columns based on column value

i have a file - it will be in sorted order on column 1 abc 0 1 abc 2 3 abc 3 5 def 1 7 def 0 1 -------- i'd like (awk maybe?) to get the results (any ideas)??? abc 5 9 def 1 8 (2 Replies)
Discussion started by: jjoe
2 Replies

10. Shell Programming and Scripting

help sum columns by break in first column with awk or sed or something.

I have some data that is something like this? item: onhand counted location ITEM0001 1 0 a1 ITEM0001 0 1 a2 ITEM0002 5 0 b5 ITEM0002 0 6 c1 I want to sum up... (6 Replies)
Discussion started by: syadnom
6 Replies
Login or Register to Ask a Question