Help with sum range of data set together


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help with sum range of data set together
# 1  
Old 01-18-2017
Help with sum range of data set together

Input File:
Code:
2000    3
1998    2
1997    2
1994    1
1991    1
1989    1
1987    2
1986    2
1985    1
1984    1
.
.
10      277256
9       278274
8       282507
7       284837
6       287066
5       292967
4       314583
3       342942
2       441704
1       487042

Desired Output
Code:
2000 110
1900 163
1800 139
.
.
.
200 3557341
100 13454579

Code Try:
Code:
awk '$1>=1901&&$1<=2000{total = total + $2}END{print total}' Input_File
110
awk '$1>=1801&&$1<=1900{total = total + $2}END{print total}' Input_File
163
awk '$1>=1701&&$1<=1800{total = total + $2}END{print total}' Input_File
139
awk '$1>=101&&$1<=200{total = total + $2}END{print total}' Input_File
3557341
awk '$1>=1&&$1<=100{total = total + $2}END{print total}' Input_File
13454579

I plan to sum the column 2 if column 1 is within every 100 group.
eg.
Sum all the column 2 value if column 1 is range within 1 to 100;
Sum all the column 2 value if column 1 is range within 101 to 200;
Sum all the column 2 value if column 1 is range within 201 to 300;

Desired output should have 20 line which column 1 is every 100 and sum its corresponding column 2 data (maximum record in data set is 2000).
The command I try is quite tedious as I need to type the command one by one from 1 to 100 till 1901 to 2000 and sum its corresponding column 2 data.

Thanks for any advice.

Last edited by perl_beginner; 01-18-2017 at 10:18 AM.. Reason: Typo error
# 2  
Old 01-18-2017
Hi,
Can you try:
Code:
awk '{X=sprintf("%i00",($1+99)/100);A[X]+=$2};END{for (i in A){print i,A[i]}}' file

Regards.
This User Gave Thanks to disedorgue For This Post:
# 3  
Old 01-18-2017
Thanks a lot and again, disedorgue.
Your awk command work perfectly.
It given the same result as I run it one by one.

Is below the command if I would like to edit become every 50 and count its corresponding column 2?
Code:
awk '{X=sprintf("%i00",($1+49)/50);A[X]+=$2};END{for (i in A){print i/2,A[i]}}' Input_File

# 4  
Old 01-18-2017
No, work just for power of 10 like 10,100,1000,...
General case could be (example with range 30 by 30):
Code:
awk '{X=sprintf("%1.0d",($1+29)/30);A[X*30]+=$2};END{for (i in A){print i,A[i]}}' Input_File

or with variable:
Code:
awk -vRGE=30 '{X=sprintf("%1.0d",($1+(RGE-1))/RGE);A[X*RGE]+=$2};END{for (i in A){print i,A[i]}}' Input_File

Regards.
This User Gave Thanks to disedorgue For This Post:
# 5  
Old 01-18-2017
Thanks a lot and again, disedorgue.
Really appreciate for your help.

It worked perfect in my case now.
Thanks.
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Cannot subset ranges from another range set

Ca21chr2_C_albicans_SC5314 2159343 2228327 Ca21chr2_C_albicans_SC5314 636587 638608 Ca21chr2_C_albicans_SC5314 5286 50509 Ca21chr2_C_albicans_SC5314 634021 636276 Ca21chr2_C_albicans_SC5314 1886545 1900975 Ca21chr2_C_albicans_SC5314 610758 613544... (9 Replies)
Discussion started by: cryptodice
9 Replies

2. UNIX for Beginners Questions & Answers

How to sum value of a column by range defined in another file awk?

I have two files, file1.table is the count table, and the other is the range condition file2.range. file1.table chr start end count N1 0 48 1 N1 48 181 2 N1 181 193 0 N1 193 326 2 N1 326 457 0 N1 457 471 1 N1 471 590 2 N1 590 604 1 N1 604 752 1 N1 752 875 1 file2.range... (12 Replies)
Discussion started by: yifangt
12 Replies

3. Red Hat

Which is the effective ephemeral port range in Linux 2.6 for this set up?

In my Linux system ephemeral port range is showing different ranges as follows $ cat /proc/sys/net/ipv4/ip_local_port_range 32768 61000  cat /etc/sysctl.conf | grep net.ipv4.ip_local_port_range net.ipv4.ip_local_port_range = 9000 65500 Which will be the effective ephemeral port... (5 Replies)
Discussion started by: steephen
5 Replies

4. Shell Programming and Scripting

How to set end limit while copying files of a range??

I have files being generated in format A20140326.00........ to A20140326.24............. I need to copy these hourly basis from one location to another. Eg. If i copy from 14 to 19 the hour, I use wildcard as A201403226.1*. Requirement is : I need to copy from 06 hour and wil run the script... (1 Reply)
Discussion started by: Saidul
1 Replies

5. Shell Programming and Scripting

Sum of range of rows and columns in matrix

Hi all, I have a large matrix of 720 x 25. I want to get sum of range of rows and columns. Like, I need sum of all columns and row number 2 to 21, then leaving 22nd row, again sum of all columns and row number 23 to 42 again leaving 43rd row and then sum of 44th to 63. Means I want to add all... (4 Replies)
Discussion started by: CAch
4 Replies

6. Shell Programming and Scripting

Help with sum of data set

Input file 2 1159,310, 4 142,199,218,91, 3 91,273,349, Desired output result 2 1469 4 650 3 713 I have long list of input file as shown above. It has a "," delimited to separate between each record in column 2.... (1 Reply)
Discussion started by: perl_beginner
1 Replies

7. UNIX for Dummies Questions & Answers

How to get the set of files size as a sum in a directory.

Hi, Can someone help me to get the complete files size (sum) over a perod time (1 day,2days)in a directory. eg: Directory :ABC I have a1,a2,a3 files are created in last 24 hours so I need to get the some of all these files. I am using the below find command but its giving me the... (1 Reply)
Discussion started by: gaddamja
1 Replies

8. Shell Programming and Scripting

use variable to set the range of a for loop

Hi; For sure there's an easy answer to this one that I am not finding.. I first set a variable, say b1a:] max=5 then I want to use max to set the range for a for loop like so (it should run for i in 1:5) b1a:] for i in {1..$max}; do echo $i; done {1..5} I would like the output... (2 Replies)
Discussion started by: jbr950
2 Replies

9. Shell Programming and Scripting

Extracting specific lines of data from a file and related lines of data based on a grep value range?

Hi, I have one file, say file 1, that has data like below where 19900107 is the date, 19900107 12 144 129 0.7380047 19900108 12 168 129 0.3149017 19900109 12 192 129 3.2766666E-02 ... (3 Replies)
Discussion started by: Wynner
3 Replies
Login or Register to Ask a Question