LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)
-   -   Calculate average from csv file in shell script (https://www.linuxquestions.org/questions/linux-newbie-8/calculate-average-from-csv-file-in-shell-script-601449/)

khairilthegreat 11-21-2007 09:31 AM
Calculate average from csv file in shell script
 
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


All times are GMT -5. The time now is 07:37 PM.