grap the max value

Hi, I have a text file that has millions lines. The format likes
1	bs1_533	        0	533	
1	bs1_41342	0	41342	
1	bs1_41791	0	41791	
1	bs1_44449	0	44449	
1	rs2462492	0	44539	
1	bs1_44571	0	44571	
1	rs10399749	0	45162
2           rs45677888  0             12345
2           rs45646770  0              23456	

Open in new window

Suppose the first column is a key, the fouth column is a value
You see that the number of the last column is in the asceder order for the the correspond key. I want to
1) grab the max value of it here is 45162 for key 1.
2) We can create <key,value> pair such as <1,45162>
<2,55555> etc..
I want to get the sum of all  values.

Thanks for help. Linux shell script or dos batch are all welcome.
zhshqzycAsked:
Who is Participating?
 
TintinCommented:
Here's a shell script solution

sort -rn -k1 -k4 file.dat | sed "s/\t/     /"| uniq -w6 | awk '{t+=$4} END {print t}'
0
 
oBdACommented:
Just for the fun of it, here's a plain batch script. Probably not very fast, and depending on the number of lines, there might be an overflow, the NT shell can only calculate from -2147483648 to 2147483647. The script will exit with errorlevel 1 in this case (there might be workarounds, but a ballpark figure of the highest expected sum would be nice).
@echo off
setlocal enabledelayedexpansion
set /a Sum = 0
REM Must be set to the first key in the file:
set /a OldKey = 1
for /f "tokens=1,4" %%a in ('type test.txt') do (
  set NewKey=%%a&set Value=%%b
  if not !NewKey!==!OldKey! (
    set /a Sum += Value
    echo ^<!OldKey!,!Value!^> - !Sum!
    if %Sum% LSS 0 (echo Overflow!&exit /b 1)
    set OldKey=!NewKey!
  )
)
set /a Sum += Value
echo ^<%NewKey%,%Value%^> - %Sum%

Open in new window

0
 
zhshqzycAuthor Commented:
The sum is 3021633417 by using other programming code(c#).
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
zhshqzycAuthor Commented:
And the delimter is a tab key.
0
 
oBdACommented:
Scratch that first version, it doesn't even work with sums less than 2147483647, sorry.
This now finally has the same results as Excel, and can work up to 21474836470000. Got stuck with some inexplicably incorrect results which turned out to be caused by the NT shell's specialty of treating strings with a leading 0 as octal numbers ...
@echo off
setlocal enabledelayedexpansion
set /a HiSum = 0
set /a LoSum = 0
REM Must be set to the first key in the file:
set /a OldKey = 1
set /a OldValue = 0
for /f "tokens=1,4" %%a in ('type test.txt') do (
  set NewKey=%%a
  if !NewKey!==!OldKey! (
    set OldValue=%%b
  ) else (
    set /a LoSum = LoSum + OldValue
    set /a HiSum = HiSum + LoSum/10000
    set /a LoSum = LoSum %% 10000
    echo ^<!OldKey!,!OldValue!^> - !HiSum! !LoSum!
    set OldKey=!NewKey!
    set OldValue=%%b
  )
)
set /a LoSum = LoSum + OldValue
set /a HiSum = HiSum + LoSum/10000
set /a LoSum = LoSum %% 10000
echo ^<!OldKey!,!OldValue!^> - !HiSum! !LoSum!
set LoSumLZ=0000!LoSum!
set LoSumLZ=!LoSumLZ:~-4!
echo Sum: !HiSum!!LoSumLZ!

Open in new window

0
 
aavictorCommented:
The biggest bubble floats to the top first.
So, after looping you can find the max.
During recording the new Max in comparison, record the key temporarily.
after looping, you will get only one row.  The potential error is
that there are duplicated values you will miss. You get the Max value but not
all row positions. Perhaps, you find the first or the last  
0
 
zhshqzycAuthor Commented:
Dos batch command is much slow than the other way. One hour passed still no result, using shell script or c# only takes about 2 minutes. But I really appreciate your effort.
0
 
zhshqzycAuthor Commented:
@Tintin,

Could you please explain a little bit about
uniq -w6 

Open in new window

I searched online but no lucky.
0
 
zhshqzycAuthor Commented:
Thanks.
0
 
TintinCommented:
-w6 means only compare the first 6 characters when determining if a line is unique or not.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.