Sum duplicate values in text file through awk between dates


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sum duplicate values in text file through awk between dates
# 1  
Old 09-03-2017
Sum duplicate values in text file through awk between dates

I need to sum values in text file in case duplicate row are present with same name and different value below is example of data in file i have and format i need.


Data in text file

Code:
20170308
PM,U,2
PM,U,113
PM,I,123
DA,U,135
DA,I,113
DA,I,1
20170309
PM,U,2
PM,U,1
PM,I,123
PM,I,1
DA,U,135
DA,I,1

Output i need in file

Code:
20170308
PM,U,115
PM,I,123
DA,U,135
DA,I,114
20170309
PM,U,3
PM,I,124
DA,U,135
DA,I,1


Last edited by Adfire; 09-03-2017 at 12:01 PM..
# 2  
Old 09-03-2017
Try:
Code:
awk '
  {
    i=$1 FS $2
  } 
  i!=p || NF==1 {
    if(t) print p, t
    p=t=x
  }
  NF==1 {
    print
    next
  }
  {
    p=i
    t+=$3
  } 
  END {
    if(t)print p, t
  }
' FS=, OFS=, file

Output:
Code:
20170308
PM,U,116
PM,I,123
PM,U,123
DA,U,135
DA,I,114
DA,U,22
20170309
PM,U,26
PM,I,124
PM,U,123
DA,I,114
DA,U,22

# 3  
Old 09-03-2017
Quote:
Originally Posted by Scrutinizer
Try:
Code:
awk '
  {
    i=$1 FS $2
  } 
  i!=p || NF==1 {
    if(t) print p, t
    p=t=x
  }
  NF==1 {
    print
    next
  }
  {
    p=i
    t+=$3
  } 
  END {
    if(t)print p, t
  }
' FS=, OFS=, file

Output:
Code:
20170308
PM,U,116
PM,I,123
PM,U,123
DA,U,135
DA,I,114
DA,U,22
20170309
PM,U,26
PM,I,124
PM,U,123
DA,I,114
DA,U,22

I am sorry i have typed wrong example.I have corrected example now please check.

Last edited by Adfire; 09-03-2017 at 12:02 PM..
# 4  
Old 09-03-2017
Have you tried the code?
With that new input file I get:

Code:
20170308
PM,U,115
PM,I,123
DA,U,135
DA,I,114
20170309
PM,U,3
PM,I,124
DA,U,135
DA,I,1

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

Find duplicate values in specific column and delete all the duplicate values

Dear folks I have a map file of around 54K lines and some of the values in the second column have the same value and I want to find them and delete all of the same values. I looked over duplicate commands but my case is not to keep one of the duplicate values. I want to remove all of the same... (4 Replies)
Discussion started by: sajmar
4 Replies

3. Shell Programming and Scripting

SUM semicolon-seperated values from txt-file

Hello, (I'm a shell beginner) how can I sum the bold values of the following txt-file (values.txt) with bash shell. The result of the sum should be written in a new txt file (sum.txt): ... Thanks in advance (5 Replies)
Discussion started by: milu
5 Replies

4. Shell Programming and Scripting

awk script to find duplicate values

The data below consits of items with Class, Sub Class and Property values. I would like to find out same value being captured for different property values for a same Class/Sub Class combination (with in an Item & across items). Like 123 being captured for PAD1, PAD2, PAD4 for ABC-DEF, 456 captured... (4 Replies)
Discussion started by: aramacha
4 Replies

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

6. Shell Programming and Scripting

How to find sum of any 'n' number of values from file matching target value?

I have a simple text file having payment amount value on each line. At the end of day 'n' number of payments created difference in amount that I need to match from this file. I have information about how many payments created difference and difference amount. Please help me to build shell... (3 Replies)
Discussion started by: swats007
3 Replies

7. UNIX for Dummies Questions & Answers

awk to sum column field from duplicate row/lines

Hello, I am new to Linux environment , I working on Linux script which should send auto email based on the specific condition from log file. Below is the sample log file Name m/c usage abc xxx 10 abc xxx 20 abc xxx 5 xyz ... (6 Replies)
Discussion started by: asjaiswal
6 Replies

8. Shell Programming and Scripting

Get the sum of values in between begin and end in the file

Hi All, test file Begin Script Run at Thu Mar 14 09:24:16 PDT 2013 tst_accounts: ws zip: WS_out_20130313.tar.gz dat: test_20130313.dat count: 63574 loaded: xx pre-merge: xx post-merge: xx timestamp: Thu Mar 14 09:30:42 PDT 2013 tst_accounts: ws zip: WS_out_20130313.tar.gz dat: s_20130313.dat... (6 Replies)
Discussion started by: bmk
6 Replies

9. Shell Programming and Scripting

Taking sum up all values inside the file

Hi, Taking sum up all values inside the file by using the below command: paste -sd+ filenmae | bc Getting some error like "0705-001: building space exceeded on line1 stdin" The original data looks like SPACE SPACE SPACE 0.123 JOBNAME1 SPACE SPACE 20.325 JOBNAME2 SPACE SPACE... (2 Replies)
Discussion started by: NareshN
2 Replies

10. Shell Programming and Scripting

Awk question: Sum dates

Hi there, I have a logfile which has the following layout: 20080812 0 20 20080812 12 10 20080812 12 10 20080812 12 10 I want to sum the "12" on the last 3 lines and save the "20" on the first line. The final output should be 20080812 36 20 I think that should me more easier with... (6 Replies)
Discussion started by: BufferExploder
6 Replies
Login or Register to Ask a Question

Featured Tech Videos