Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Copy columns from one file into another and get sum of column values and row count
# 1  
Old 07-16-2018
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-
Code:
UTF-8,,,,,,,,,,,,,,,,,,,,,,,,,

Id,EventDate,EventStatus,EventType,Title,PONumber,Version,ProcurementUnit,OriginatingSystem,OriginatingSystemReferenceID,Supplier,SupplierLocation,PaymentTerms,ShipNoticeDate,ShipmentDate,DeliveryDate,ShippingCarrier,TrackingNumber,ASNNumber,ClosePOStatus,Comments,PurchaseOrder_cus_RequesterPIN,PurchaseOrder_cus_SupplierName,PurchaseOrder_TotalCost,PurchaseOrder_TotalCost_InBaseCurrency,PurchaseOrder_TotalCost_Currency

147366257,2/2/2018 7:11,Ordered,Ordered,Testing PO Closing at Line Level,PO5460,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,LV,3740,3740,USD

147369568,2/2/2018 7:26,Ordered,Ordered,Staples test 1,PO5461,1,,,,334741,334741,Net 30,,,,,,,Open,,334741,Abc,30,30,USD

147372761,2/2/2018 7:40,Ordered,Ordered,Testing PO Closing at Line Level,PO5460,2,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,xvz,5610,5610,USD

147373497,2/2/2018 7:43,Ordered,Ordered,Staples test 2,PO5462,1,,,,334741,334741,Net 30,,,,,,,Open,,334741,Arrow,100,100,USD

147374360,2/2/2018 7:47,Ordered,Ordered,Staples test 3,PO5463,1,,,,334741,334741,Net 30,,,,,,,Open,,334741,LV,50,50,USD

147374487,2/2/2018 7:48,Ordered,Ordered,Copy of Testing PO Closing at Line Level,PO5464,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,Abc,5610,5610,USD

147375802,2/2/2018 7:54,Ordered,Ordered,Staples test 4,PO5465,1,,,,334741,334741,Net 30,,,,,,,Open,,334741,xvz,200,200,USD

147894306,2/6/2018 8:35,Ordered,Ordered,testing,PO5502,1,,,,474087,474087,Net 30,,,,,,,Open,,474087,Arrow,0,0,USD

147921985,2/6/2018 10:44,Ordered,Ordered,TYest with 101 Supplier - Consulting,PO5503,1,,,,GPB101,GPB101,Net 00,,,,,,,Open,,GPB101,LV,2250,2250,USD

148420762,2/8/2018 11:12,Ordered,Ordered,Testing manual invoice,PO5520,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,Abc,2750,2750,USD

148423383,2/8/2018 11:27,Ordered,Ordered,Copy of Copy of Copy of Testing,PO5521,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,xvz,2400,2400,USD

148424067,2/8/2018 11:31,Ordered,Ordered,Copy of Copy of Copy of Copy of Testing,PO5522,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,Arrow,2400,2400,USD

148424763,2/8/2018 11:34,Ordered,Ordered,Copy of Testing,PO5523,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,LV,2400,2400,USD

149545031,2/15/2018 8:25,Ordered,Ordered,WD_Rejection Reason Code test PO,PO5584,1,,,,BIR05,BIR05,Net 00,,,,,,,Open,,BIR05,Abc,1200,1200,USD

149952795,2/19/2018 4:54,Ordered,Ordered,Staples Promo Cipher Test,PO5613,1,,,,303304,303304,Net 30,,,,,,,Open,,303304,xvz,5.25,5.25,USD

151200715,2/26/2018 9:25,Ordered,Ordered,Testing Invoice Variance,PO5678,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,Arrow,2400,2400,USD

151206109,2/26/2018 9:53,Ordered,Ordered,Testing Invoice Variance - Separate Line,PO5679,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,ROBIN HOOD ENTERPRISES,2400,2400,USD

151764465,2/28/2018 11:14,Ordered,Ordered,Testing invoice approval,PO5692,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,ROBIN HOOD ENTERPRISES,500,500,USD

152184301,3/2/2018 7:05,Ordered,Ordered,Testing partial invoice,PO5713,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,ROBIN HOOD ENTERPRISES,2000,2000,USD

152758385,3/6/2018 10:34,Ordered,Ordered,Test Team Requisitioning,PO5742,1,,,,5002362,5002362,Net 01,,,,,,,Open,,5002362,LV,10,10,USD

153665200,3/12/2018 7:02,Ordered,Ordered,testing blanket,PO5791,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,Abc,12000,12000,USD

153713486,3/12/2018 10:44,Ordered,Ordered,Test AN direct,PO5792,1,,,,GPB101,GPB101,Net 00,,,,,,,Open,,GPB101,xvz,130,130,USD

153714281,3/12/2018 10:48,Ordered,Ordered,test,PO5793,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,Arrow,5500,5500,USD

153909455,3/13/2018 8:12,Ordered,Ordered,Testing,PO5802,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,ROBIN HOOD ENTERPRISES,1200,1200,USD

153968315,3/13/2018 13:03,Ordered,Ordered,Testing,PO5803,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,ROBIN HOOD ENTERPRISES,1200,1200,USD

154210009,3/14/2018 15:22,Ordered,Ordered,Team Req Test 3,PO5808,1,,,,408107,408107,Net 45,,,,,,,Open,,408107,HUMANSCALE CORPORATION,20,20,USD

154329083,3/15/2018 5:54,Ordered,Ordered,Testing Tax Approver Flow,PO5817,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,ROBIN HOOD ENTERPRISES,220.8,220.8,USD

154704441,3/17/2018 23:39,Ordered,Ordered,my test order,PO5842,1,,,,318218,318218,Net 30,,,,,,,Open,,318218,LV,338.5,338.5,USD

155058345,3/20/2018 6:57,Ordered,Ordered,testing PO change,PO5863,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,Abc,1200,1200,USD

155058845,3/20/2018 6:59,Ordered,Ordered,computer accesories,PO5864,1,,,,333366,333366,Net 30,,,,,,,Open,,333366,xvz,129,129,USD

155058911,3/20/2018 7:00,Ordered,Ordered,Test Team Requisitioning,PO5742,2,,,,5002362,5002362,Net 01,,,,,,,Open,,5002362,Arrow,40,40,USD

155060880,3/20/2018 7:07,Ordered,Ordered,testing,PO5869,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,ROBIN HOOD ENTERPRISES,1200,1200,USD

155564616,3/22/2018 10:09,Ordered,Ordered,Testing Line Close,PO5886,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,LV,9750,9750,USD

155569233,3/22/2018 10:29,Ordered,Ordered,Copy of Testing Line Close - fourth PO for test,PO5887,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,Abc,9750,9750,USD

155569271,3/22/2018 10:29,Ordered,Ordered,Testing Line Close - third PO for test,PO5888,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,xvz,9750,9750,USD

155569422,3/22/2018 10:30,Ordered,Ordered,Testing Line Close - second PO for test,PO5889,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For Various Actions,,BIR03,Arrow,9750,9750,USD

155572115,3/22/2018 10:43,Ordered,Ordered,Testing Line Close - fifth PO for test,PO5890,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,ROBIN HOOD ENTERPRISES,1250,1250,USD

I've been writing something like this..

Code:
Code :

set -x # turn on debug

 

#!/bin/bash/sh

outputfile="Cloudfile.csv"

curr_date=`date '+%Y%m%d_%H%M%S'`

### Copy Column from a file and paste into another

cut -d, -f24 abc.csv | sed s/\"//g > file1.csv

### Remove first Row ## Remove Trailing Zero(s) and print only non null Values

awk 'NR>2' file1.csv | awk ' { if($1 ~ /\./) sub("\\.*0+$","");print}'| awk -F , '$1 > "0" { print }' > file2.csv

awk -F, ' {rows++; sum+=$1}

        END{print "Date=", $curr_date, "Row_count=", rows, "Sum Amounts=", sum} ' file2.csv > $outputfile

Output:
Code:
Date= 1250 Row_count= 36 Sum Amounts= 99483.6

but I want output to be like this:
expected output:
Code:
DATE                           Sum_Amount     RECORD_COUNT

06/15/2018                      29039.44         

06/20/2018                     314038.03         

07/14/2018                      41424.75         

07/15/2018                      90314.27         

07/22/2018                      66416.22         

07/23/2018                       2013.55         

07/24/2018                       1172.35         

Total:                         544418.61        51

Thanks in Advance!

---------- Post updated at 03:00 PM ---------- Previous update was at 02:46 PM ----------

** The date column has the current date values for all rows returned in sum_amounts.
# 2  
Old 07-16-2018
A few questions to clarify:
- in your input, does that UTF-8,,,,,, line really exist? Do the empty lines?
- where do those "DATE" values come from - they're not in the input file? Some seem to be in the future?
- are you sure all amounts are in the same currency, or shouldn't they be controlled / converted?
# 3  
Old 07-16-2018
Yes UTF-8 does exist in the file.
The Date Column should have the current date values: example

Code:
DATE                           Sum_Amount     RECORD_COUNT

07/15/2018                      29039.44         

07/15/2018                     314038.03         

07/15/2018                      41424.75         

07/15/2018                      90314.27         

07/15/2018                      66416.22         

07/15/2018                       2013.55         

07/15/2018                       1172.35         

Total:                             544418.61        51

All the amounts are in same currency i.e USD.
Last edited by Tahir_M; 07-16-2018 at 10:50 PM.. Reason: adding proper code tags
# 4  
Old 07-17-2018
And by what criterion should the subtotals be grouped? Which items / lines have to be excluded?
This User Gave Thanks to RudiC For This Post:
Neo
# 5  
Old 07-17-2018
I want to print sum of values from column PurchaseOrder_TotalCost into the output file but the output file should not include null and Zero(if any).
# 6  
Old 07-17-2018
I give up - don't understand this vague specification and inconsistency between input, logics, and desired output, and thus can't propose anything serious.
# 7  
Old 07-17-2018
If this helps...
Sum_amounts in the output should have the values from column PurchaseOrder_TotalCost(from abc.csv) with last value being sum of all the values.

The output which I have put previuosly was just an example how the output should be generated.

Here is the actual output that I should get :
Code:
DATE,ROW_COUNT,Amount_Totals

20180712,,"   3740.0000"

20180712,,"   5610.0000"

20180712,,"   100.0000"

20180712,,"   50.0000"

20180712,,"   5610.0000"

20180712,,"   200.0000"

20180712,,"   2250.0000"

20180712,,"   2750.0000"

20180712,,"   2400.0000"

20180712,,"   2400.0000"

20180712,,"   2400.0000"

20180712,,"   1200.0000"

20180712,,"   2400.0000"

20180712,,"   2400.0000"

20180712,,"   500.0000"

20180712,,"   2000.0000"

20180712,,"   12000.0000"

20180712,,"   130.0000"

20180712,,"   5500.0000"

20180712,,"   1200.0000"

20180712,,"   1200.0000"

20180712,,"   220.8000"

20180712,,"   338.5000"

20180712,,"   1200.0000"

20180712,,"   129.0000"

20180712,,"   40.0000"

20180712,,"   1200.0000"

20180712,,"   9750.0000"

20180712,,"   9750.0000"

20180712,,"   9750.0000"

20180712,,"   9750.0000"

20180712,," 1250.0000"

Total,36,99483.6

If this helps well and good, if not still good.I'll just have to keep searching through google to crack it.

Thanks for the help!
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

Sum values of specific column in multiple files, considering ranges defined in another file

I have a file (let say file B) like this: File B: A1 3 5 A1 7 9 A2 2 5 A3 1 3 The first column defines a filename and the other two define a range in that specific file. In the same directory, I have also three more files (File A1, A2 and A3). Here is 10 sample lines... (3 Replies)
Discussion started by: Bastami
3 Replies

3. Shell Programming and Scripting

Add sum of columns and max as new row

Hi, I am a new bie i need some help with respect to shell onliner; I have data in following format Name FromDate UntilDate Active Changed Touched Test 28-03-2013 28-03-2013 1 0.6667 100 Test2 28-03-2013 03-04-2013 ... (1 Reply)
Discussion started by: gangaraju6
1 Replies

4. UNIX for Dummies Questions & Answers

Unique values in a row sum the next column in UNIX

Hi would like to ask you guys any advise regarding my problem I have this kind of data file.txt 111111111,20 111111111,50 222222222,70 333333333,40 444444444,10 444444444,20 I need to get this file1.txt 111111111,70 222222222,70 333333333,40 444444444,30 using this code I can... (6 Replies)
Discussion started by: reks
6 Replies

5. Shell Programming and Scripting

Sum specified values (columns) per row

Hello out there, file.txt: comp51820_c1_seq1 42 N 0:0:0:0:0:0 1:0:0:0:0:0 0:0:0:0:0:0 3:0:0:0:0:0 0:0:0:0:0:0 comp51820_c1_seq1 43 N 0:0:0:0:0:0 0:1:0:0:0:0 0:0:0:0:0:0 0:3:0:0:0:0 0:0:0:0:0:0 comp51820_c1_seq1 44 N 0:0:4:0:3:1 0:0:1:9:0:0 10:0:0:0:0:0 0:3:3:2:2:6 2:2:2:5:60:3... (16 Replies)
Discussion started by: pathunkathunk
16 Replies

6. UNIX for Dummies Questions & Answers

Count on grep for more than two values in a row of fixed length file

I want to get count on number of records in a few folders by running grep command for more than two columns in a row of fixed length file. suppose if i have a fixed length file has 5 columns and I want to see the record counts for country =can and province = bc and time stamp <= 12 feb 2013... (14 Replies)
Discussion started by: princetd001
14 Replies

7. Shell Programming and Scripting

Copy values from columns matching in those in second file.

Hi All, I have two sets of files. Set 1: 100 text files with extension .txt with names like 1.txt, 2.txt, 3.txt until 100.txt Set 2: One big file with extension .dat The text files have some records in columns like this: 0.7316431 82628 0.7248189 82577 0.7248182 81369 0.7222999... (1 Reply)
Discussion started by: shoaibjameel123
1 Replies

8. Shell Programming and Scripting

Sum of values coming in a row

Hi, my requirement is to sum values in a row. eg: input is: sum,value1,value2,value3,.....,value N Required Output: sum,<summation of N values> Please help me... (5 Replies)
Discussion started by: MrGopal666
5 Replies

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

10. Shell Programming and Scripting

How to check Null values in a file column by column if columns are Not NULLs

Hi All, I have a table with 10 columns. Some columns(2nd,4th,5th,7th,8th and 10th) are Not Null columns. I'll get a tab-delimited file and want to check col by col and generate seperate error code for each col eg:102 if 2nd col value is NULL and 104 if 4th col value is NULL so on... I am a... (7 Replies)
Discussion started by: Mandab
7 Replies
Login or Register to Ask a Question