Group/concatenate certain column and basis on this do addition on other column


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Group/concatenate certain column and basis on this do addition on other column
# 1  
Old 05-05-2018
Group/concatenate certain column and basis on this do addition on other column

Hi Experts,
Need your support
I want to group/concatenate column 1,2,12 and 13 and if found duplicate then need to sum value of column 17,20,21 and column22. After concatenation if found unique then no action to be taken.
Secondly want to make duplicate rows basis on grouping/concatenation of column 1,2,12 and 13 as single rows in outputfile and those single rows should have column sum (of column 17,20,21 and column22 from input file)

Input file :
Code:
Supp|ReturnPeriod|Month|OrgPOS|OrgHSNorSAC|OrgUnitOfMeasurement|OrgQuantity|OrgRate|OrgTaxableValue|OrgeComGSTIN|OrgeComSupplyValue|NewPOS|NewHSNorSAC|NewUnitOfMeasurement|NewQuantity|NewRate|NewTaxableValue|NeweComGSTIN|NeweComSupplyValue|IntegratedTaxAmount|CentralTaxAmount|StateUTTaxAmount|CessAmount
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|0|||||0||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|500||||45|45||
27AAACH1458C1ZZ|032018||||||||||27|9984|||1|7794||||701.46|701.46||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|500||||45|45||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|1000||||90|90||
27AAACH1458C1ZZ|102017||||||||||27|9984|||1|499.12||||44.92|44.92||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|500||||45|45||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|500||||45|45||
37AAACT2438A1ZS|042018||||||||||37|9984|||1|300||||27|27||
37AAACT2438A1ZS|042018||||||||||37|9984|||1|300||||27|27||
24AAACT2438A1ZZ|042018||||||||||24|9984|||1|500||||45|45||
36AAACT2438A1ZU|042018||||||||||36|9984|||1|300||||27|27||
33AAACT2438A1Z0|042018||||||||||33|9984|||1|500||||45|45||
24AAACT2438A1ZZ|042018||||||||||24|9984|||1|500||||45|45||
09AAACT2438A1ZR|042018||||||||||09|9984|||1|1000||||90|90||
37AAACT2438A1ZS|042018||||||||||37|9984|||1|300||||27|27||
37AAACT2438A1ZS|042018||||||||||37|9984|||1|300||||27|27||
24AAACT2438A1ZZ|042018||||||||||24|9984|||1|500||||45|45||
24AAACT2438A1ZZ|042018||||||||||24|9984|||1|500||||45|45||
36AAACT2438A1ZU|042018||||||||||36|9984|||1|300||||27|27||
32AAACT2438A1Z2|042018||||||||||32|9984|||1|500||||45|45||
09AAACT2438A1ZR|042018||||||||||09|9984|||1|1000||||90|90||
36AAACT2438A1ZU|042018||||||||||36|9984|||1|300||||27|27||
32AAACT2438A1Z2|042018||||||||||32|9984|||1|500||||45|45||

Sample Expected output
Code:
Supp|ReturnPeriod|Month|OrgPOS|OrgHSNorSAC|OrgUnitOfMeasurement|OrgQuantity|OrgRate|OrgTaxableValue|OrgeComGSTIN|OrgeComSupplyValue|NewPOS|NewHSNorSAC|NewUnitOfMeasurement|NewQuantity|NewRate|NewTaxableValue|NeweComGSTIN|NeweComSupplyValue|IntegratedTaxAmount|CentralTaxAmount|StateUTTaxAmount|CessAmount
37AAACT2438A1ZS|042018||||||||||37|9984|||1|600||||54|54||
36AAACT2438A1ZU|042018||||||||||36|9984|||1|600||||54|54||
09AAACT2438A1ZR|042018||||||||||09|9984|||1|2000||||180|180||



Code : i have tried

Code:
awk -F " " '
    NR==1 {print; next}
    NF {e[$1$2$12$13]++ ; a[$1$2$12$13]+=$17; b[$1$2$12$13]+=$20; c[$1$2$12$13]+=$21; d[$1$2$12$13]+=$22;}
    END {for(i in a)print i}
' input.txt

Code output : Not correct because is column1 value "27AAACH1458C1ZZ|042018" is repeating twice and also sum of these rows are not correct. Same problem is with others column 1 values like "09AAACT2438A1ZR"

Code:
Supp|ReturnPeriod|Month|OrgPOS|OrgHSNorSAC|OrgUnitOfMeasurement|OrgQuantity|OrgRate|OrgTaxableValue|OrgeComGSTIN|OrgeComSupplyValue|NewPOS|NewHSNorSAC|NewUnitOfMeasurement|NewQuantity|NewRate|NewTaxableValue|NeweComGSTIN|NeweComSupplyValue|IntegratedTaxAmount|CentralTaxAmount|StateUTTaxAmount|CessAmount
09AAACT2438A1ZR|042018||||||||||09|9984|||1|1000||||90|90||
24AAACT2438A1ZZ|042018||||||||||24|9984|||1|500||||45|45||
37AAACT2438A1ZS|042018||||||||||37|9984|||1|300||||27|27||
27AAACH1458C1ZZ|102017||||||||||27|9984|||1|499.12||||44.92|44.92||
33AAACT2438A1Z0|042018||||||||||33|9984|||1|500||||45|45||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|1000||||90|90||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|500||||45|45||
36AAACT2438A1ZU|042018||||||||||36|9984|||1|300||||27|27||
27AAACH1458C1ZZ|032018||||||||||27|9984|||1|7794||||701.46|701.46||
32AAACT2438A1Z2|042018||||||||||32|9984|||1|500||||45|45||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|0|||||0||

# 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. Shell Programming and Scripting

Concatenate values in the first column based on the second column.

I have a file (myfile.txt) with contents like this: 1.txt apple is 3.txt apple is 5.txt apple is 2.txt apple is a 7.txt apple is a 8.txt apple is a fruit 4.txt orange not a fruit 6.txt zero isThe above file is already sorted using this command: sort -k2 myfile.txtMy objective is to get... (3 Replies)
Discussion started by: shoaibjameel123
3 Replies

2. Shell Programming and Scripting

Group by column and concatenate

Hi, Can you please help in resolving below issue: I have input file as below. COL1 COL2 ABC 111asdf ABC 222dfgh ABC 333fghy ABC 4rtyu XYZ 1yuio XYZ 2ytre Lookg for the Output File as below COL1 COL2 ABC '111asdf','222dfgh','333fghy','4rtyu' XYZ ... (6 Replies)
Discussion started by: Phani_Raghava
6 Replies

3. Shell Programming and Scripting

Replace column by random number addition

Here is my problem:- I have a file with pipe separated values. CR|20121021|079|ABC|N|DLS|00038|DLS|04750|1330597704|634234|634|0 CR|20121021|079|ABC|N|DLS|00038|DLS|05118|2071690102|354|351|3 CR|20121021|079|ABC|N|DLS|00038|DLS|05140|960051505|1088|1088|0... (4 Replies)
Discussion started by: Yoda
4 Replies

4. Shell Programming and Scripting

print least value of a column on the basis of another column

Hi, I am new to linux... I have a file which looks like: I want to print the entire row in which 5th column is having minimum value for every first column (i.e min for 9 and min for 16). Along with the condition awk -F" " 'b < $5 {b=$5; a=$0} END {for (i in a) {print a}}' inputfile >... (5 Replies)
Discussion started by: CAch
5 Replies

5. Shell Programming and Scripting

print least value of a column on the basis of another column

Hi, I am new to linux... I have a file which looks like: I want to print the entire row in which 5th column is having minimum value for every first column (i.e min for 9 and min for 16). Along with the condition awk -F" " 'b < $5 {b=$5; a=$0} END {for (i in a) {print a}}' inputfile >... (1 Reply)
Discussion started by: CAch
1 Replies

6. Shell Programming and Scripting

Delete Duplicates on the basis of two column values.

Hi All, i need ti delete two duplicate processss which are running on the same device type (column 1) and port ID (column 2). here is the sample data p1sc1m1 15517 11325 0 01:00:24 ? 0:00 scagntclsx25octtcp 2967 in3v mvmp01 0 8000 N S 969 750@751@752@ p1sc1m1 15519 11325 0 01:00:24 ? ... (5 Replies)
Discussion started by: neeraj617
5 Replies

7. Shell Programming and Scripting

split on the basis of 2nd and 3rd column

file A aa 22 48 ab 22 48 tcf 50 76 gf 50 76 h 89 100 yh 89 100 how can we split the file on the basis of common 2 and third column output like file A-1 aa 22 48 ab 22 48 file A-2 cf 50 76 gf 50 76 (3 Replies)
Discussion started by: cdfd123
3 Replies

8. Shell Programming and Scripting

Previous Column Value (addition)

Good day, First off, I would just like to say that I've been helped by a lot of the posts here in my own scripting work in the past few months. I've been a long time roamer, but first time poster. That being said, I'm stuck trying to figure out this issue (For computational research, not a... (4 Replies)
Discussion started by: Eblue562
4 Replies

9. Shell Programming and Scripting

Merge group numbers and add a column containing group names

I have a file in the following format. Groups of data merge together and the group number is indicated above each group. 1 adrf dfgr dfg 2 dfgr dfgr 3 dfef dfr fd 4 fgrt fgr fgg 5 fgrt fgr (3 Replies)
Discussion started by: Lucky Ali
3 Replies

10. Shell Programming and Scripting

Count first column on the basis of two other columns

Hello, I have a file ================= 12 SRV1 GRP1 19 SRV1 GRP1 19 SRV1 GRP2 3 SRV1 GRP1 3 SRV1 GRP2 30 SRV1 GRP2 7 SRV1 GRP1 8 SRV1 GRP3 =========== I want output like =============== 41 SRV1 GRP1 52 SRV1 GRP2 8 SRV1 GRP3 (1 Reply)
Discussion started by: kaustubh137
1 Replies
Login or Register to Ask a Question