Calculate average from csv file in shell script

khairilthegreat · 11-21-2007, 09:31 AM

Hi all,

Suppose I have a text file containing some data in csv format:

Code:

7,5,7
1,1,1
1,4,2

Can anybody show me how to process this data using bash shell script to get the average of every row and output it into another text file or append it to existing csv

For example, the result that I want is a file containing

Code:

6.33
1
2.33

or a new file containing

Code:

7,5,7,6.33
1,1,1,1
1,4,2,2.33

where

Code:

6.33 = (7+5+7)/3
1 = (1+1+1)/3
2.33 = (1+4+2)/3

Thank you

crabboy · 11-21-2007, 09:48 AM

Sounds a lot like homework.

crabboy · 11-21-2007, 09:56 AM

In fact 90% of your posts look like homework questions and the tell tale clue is:

Quote:

I'm on campus network so the speed is not so fast. I think I can't just talk to the administrator to speed up the connection.

This problem is trivial, it took no more than 3 minutes to solve in 15 lines. If you ever want to learn how to it yourself, I'd suggest doing your own homework.

khairilthegreat · 11-21-2007, 12:50 PM

Excuse me, its really unfair to judge somebody just like that.

This question is 100% not a homework. I'm using ApacheWorkbench to research something and I came to this problem. I'm really new to bash shell scripting so I ask to this forum. In fact, I dont need the complete answer. What I need just a clue as fantasio did with my other post (http://www.linuxquestions.org/questi...-colum-601381/).

My other post is also 100% is not a homework. I don't have any Linux or networking related homework. I love learning linux, that's why I keep asking to this forum. I know I can google everything in this world but what is the function of this linuxquestions.org if googling is the only way to get information.

But never mind, I solved the problem using more powerfull tools: google.

Code:

n1 = 100
n2 = 200
n3 = 300
n4 = 400
n5 = 500

for count in $n1 $n2 $n3 $n4 $n5
	do
		file="data-$count"
		delimiter=","
		rm rata2-$file
		while read line
		do

			f1=$(echo $line|cut -d$delimiter -f1)
			f2=$(echo $line|cut -d$delimiter -f2)
			f3=$(echo $line|cut -d$delimiter -f3)
			f4=$(echo $line|cut -d$delimiter -f4)
			f5=$(echo $line|cut -d$delimiter -f5)
			f6=$(echo $line|cut -d$delimiter -f6)
		 	f7=$(echo $line|cut -d$delimiter -f7)
		 	f8=$(echo $line|cut -d$delimiter -f8)
			f9=$(echo $line|cut -d$delimiter -f9)
			f10=$(echo $line|cut -d$delimiter -f10)
			
			temp=$(echo "scale=2; $f1 + $f2 + $f3 + $f4 + $f5 + $f6 + $f7 + $f8 + $f9 + $f10" | bc -l)
			
			f11=$(echo "scale=2; $temp / 10" | bc -l)
			echo "Processing..."
			echo $f1,$f2,$f3,$f4,$f5,$f6,$f7,$f8,$f9,$f10,$f11 >> mean-$file
		
		done < $file
echo "Finish"

The data that I'm referring in this question is just a simplification of the real data. The real data before looks like this:

Code:

Server Software:        Apache/2.0.55
Server Hostname:        xxx.xxx.xx.xx
Server Port:            80

Document Path:          /index.html
Document Length:        46 bytes

Concurrency Level:      2
Time taken for tests:   0.30116 seconds
Complete requests:      5
Failed requests:        0
Write errors:           0
Total transferred:      1908 bytes
HTML transferred:       276 bytes
Requests per second:    166.02 [#/sec] (mean)
Time per request:       12.046 [ms] (mean)
Time per request:       6.023 [ms] (mean, across all concurrent requests)
Transfer rate:          33.20 [Kbytes/sec] received

Connection Times (ms)
              min   avg   max
Connect:        0     0    1
Processing:     3    10   14
Total:          3    10   15

I took the data 10 times, and I want to convert the data to something like this so I can export the output to spreadsheet program to analyze it better

Code:

Num,1,2,3,4,5,6,7,8,9,10,mean
Concurrency Level,1,1,1,1,1,1,1,1,1,1,1.00
Time taken for tests (s),0.30727,0.9940,0.16656,0.28809,0.70777,0.12173,0.16168,0.39280,0.9383,0.22678,.43
Complete requests,5,5,5,5,5,5,5,5,5,5,5.00
Failed requests,0,0,0,0,0,0,0,0,0,0,0
Write errors,0,0,0,0,0,0,0,0,0,0,0
Total transferred (B),1590,1590,1590,1590,1590,1590,1590,1590,1590,1590,1590.00
HTML transferred (B),230,230,230,230,230,230,230,230,230,230,230.00
Requests per second,162.72,503.02,300.19,173.56,70.64,410.75,309.25,127.29,532.88,220.48,281.07
Time per request (ms),6.145,1.988,3.331,5.762,14.155,2.435,3.234,7.856,1.877,4.536,5.13
Time per request (KB/s),6.145,1.988,3.331,5.762,14.155,2.435,3.234,7.856,1.877,4.536,5.13
Transfer rate (ms),32.54,100.60,60.04,34.71,14.13,82.15,61.85,25.46,106.58,44.10,56.21
Connect (min)(ms),0,0,0,0,0,0,0,0,0,0,0
Connect (avg)(ms),0,0,0,0,10,0,1,2,0,2,1.50
Connect (max)(ms),0,1,4,0,50,0,8,11,1,7,8.20
Processing (min)(ms),1,1,1,1,1,1,1,1,1,1,1.00
Processing (avg)(ms),3,1,1,4,3,1,1,2,1,2,1.90
Processing (max)(ms),9,2,1,8,1,4,1,1,1,1,2.90
Total (min)(ms),1,1,1,1,1,1,1,1,1,1,1.00
Total (avg)(ms),3,1,1,4,13,1,2,4,1,4,3.40
Total (max)(ms),9,3,5,8,51,4,9,12,2,8,11.10

That's what I want to do.

And have I mentioned that this is not a homework?

b0uncer · 11-21-2007, 12:55 PM

In addition to shell scripts, you could use python. It might or might not suit you, but it's a good tool for doing tasks just like this..

crabboy · 11-21-2007, 12:57 PM

Sorry dude, I was just judging based on your original post and lack of overall detail. My script would have needed quite a bit of modification anyway since you didn't post the original file:

Code:

#!/bin/sh

IFS='
'
for i in `cat file1`; do
   VALUES=`echo $i | tr ',' ' '`
   COUNT=0
   SUM=0
   typeset -i j
   IFS=' '
   for j in $VALUES; do
      SUM=`expr $SUM + $j`
      COUNT=`expr $COUNT + 1`
   done
   AVG=`expr $SUM / $COUNT`
   echo $AVG
done