Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Adding values of a column based on another column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Adding values of a column based on another column
# 1  
Old 11-22-2013
Adding values of a column based on another column
Hello,

I have a data such as this:
Code:
ENSGALG00000000189 329 G A 4 2 0
ENSGALG00000000189 518 T C 5 1 0
ENSGALG00000000189 1104 G A 5 1 0
ENSGALG00000000187 3687 G T 5 1 0
ENSGALG00000000187 4533 A T 4 2 0
ENSGALG00000000233 5811 T C 4 2 0
ENSGALG00000000233 5998 C A 5 1 0

I want to count the total number in the 6th column for each entry in the first column so to have a final output such as this:

Code:
ENSGALG00000000189 4
ENSGALG00000000187 3
ENSGALG00000000233 3

How is this feasible in awk?

Thanks a lot

---------- Post updated at 01:24 PM ---------- Previous update was at 01:22 PM ----------

I have this wrong code, I still don't know well how to use arrays:

Code:
awk 'a[$1]=0 {count[$6]++;} END{for(snp in count){print snp, count(snp);}}' file

# 2  
Old 11-22-2013
Try:
Code:
awk '{a[$1]+=$6}END{for (i in a) print i,a[i]}' file

This User Gave Thanks to bartus11 For This Post:
Homa
# 3  
Old 11-22-2013
Hello Homa,

this following code may help.

Code:
awk '
                        $1 != first_value {
                        print first_value" "total_value;
                        first_value=$1;
                        totat_value=$6;
        total_value=0;
        }
        {
        total_value=total_value+$6
                }' file_name



Thanks,
R. Singh
# 4  
Old 11-23-2013
Quote:
Originally Posted by RavinderSingh13
Hello Homa,

this following code may help.

Code:
awk '
$1 != first_value {
    print first_value" "total_value;
    first_value=$1;
    totat_value=$6;
   total_value=0;
      }
        {
        total_value=total_value+$6
                }' file_name

Thanks,
R. Singh
You missed END block

Code:
....END{print first_value" "total_value}' ....

Your code works along with END block if file is sorted.

for example
Code:
$  awk ' $1 != first_value {
                            print first_value" "total_value;
                            first_value=$1;
                            totat_value=$6;
                            total_value=0;
                           }
                           {
                           total_value=total_value+$6
                           }
                        END{
                            print first_value" "total_value
                           }' file

Code:
$ cat test1
ENSGALG00000000189 329 G A 4 2 0
ENSGALG00000000189 518 T C 5 1 0
ENSGALG00000000189 1104 G A 5 1 0
ENSGALG00000000187 3687 G T 5 1 0
ENSGALG00000000187 4533 A T 4 2 0
ENSGALG00000000233 5811 T C 4 2 0
ENSGALG00000000233 5998 C A 5 1 0

Resulting
Code:
ENSGALG00000000189 4
ENSGALG00000000187 3
ENSGALG00000000233 3

Code:
$ cat test2
ENSGALG00000000189 329 G A 4 2 0
ENSGALG00000000189 518 T C 5 1 0
ENSGALG00000000187 3687 G T 5 1 0
ENSGALG00000000233 5998 C A 5 1 0
ENSGALG00000000189 1104 G A 5 1 0
ENSGALG00000000187 4533 A T 4 2 0
ENSGALG00000000233 5811 T C 4 2 0

Resulting
Code:
ENSGALG00000000189 3
ENSGALG00000000187 1
ENSGALG00000000233 1
ENSGALG00000000189 1
ENSGALG00000000187 2
ENSGALG00000000233 2

Hope you will understand why bartus11 used array in #2 from this example..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Concatenate values in the first column based on the second column.

I have a file (myfile.txt) with contents like this: 1.txt apple is 3.txt apple is 5.txt apple is 2.txt apple is a 7.txt apple is a 8.txt apple is a fruit 4.txt orange not a fruit 6.txt zero isThe above file is already sorted using this command: sort -k2 myfile.txtMy objective is to get... (3 Replies)
Discussion started by: shoaibjameel123
3 Replies

2. Shell Programming and Scripting

Sum column values based in common identifier in 1st column.

Hi, I have a table to be imported for R as matrix or data.frame but I first need to edit it because I've got several lines with the same identifier (1st column), so I want to sum the each column (2nd -nth) of each identifier (1st column) The input is for example, after sorted: K00001 1 1 4 3... (8 Replies)
Discussion started by: sargotrons
8 Replies

3. Shell Programming and Scripting

Adding of two column values

Hi cat /tmp/xx.txt 1 4 1 5 1 6 2 1 2 1 2 1 i want to add the values of 2nd column resepect to 1st column values..for 1 in 1st column i need sum of all the values in 2nd column ..pls tell me hw to do it?? (8 Replies)
Discussion started by: Aditya.Gurgaon
8 Replies

4. Shell Programming and Scripting

Adding Column Values Using Pattern Match

Hi All, I have a file with data as below: A,FILE1_MYFILE_20130309_1038,80,25.60 B,FILE1_MYFILE_20130309_1038,24290,18543.38 C,FILE1_dsc_dlk_MYFILE_20130309_1038,3,10.10 A,FILE2_MYFILE_20130310_1039,85,110.10 B,FILE2_MYFILE_20130310_1039,10,12.10... (10 Replies)
Discussion started by: angshuman
10 Replies

5. Shell Programming and Scripting

Adding column values in a file

Hi, I am having a file in the following format. for aaaa 1111 1234 2222 3434 for bbbb 1111 3434.343 2222 2343 for cccc 3333 2343.343 4444 89000 for dddd 1111 5678.343 2222 890.3 aaaa 2343.343 bbbb 34343.343 (5 Replies)
Discussion started by: jpkumar10
5 Replies

6. UNIX for Dummies Questions & Answers

Adding column with values

Dear all, I need your help for my question please I have without header (space separated) and need to add two colomns at the beginning with values my file look like : rs1 a t 0.6 rs2 a c 0.3 rs3 t g 0.8 I need to a new file like: 1 100 rs1 a t 0.6 1 100 rs2 a c 0.3 1 100 rs3 t g... (3 Replies)
Discussion started by: biopsy
3 Replies

7. Shell Programming and Scripting

How to averaging column based on first column values

Hello I have file that consist of 2 columns of millions of entries timestamp and throughput I want to find the average (throughput ) for each equal timestamp before change it to proper format e.g : i want to average 2 coloumnd fot all 1308154800 values in column 1 and then print... (4 Replies)
Discussion started by: aadel
4 Replies

8. Shell Programming and Scripting

problem while adding column values in awk

Hi, I have a file "input.txt" with the following content : 5312,0,,,1,8,141.2,20090727 3714,0,,,1,8,285.87,20090727 5426,0,,,1,8,3.9,20090727 3871,0,,,1,8,30.4,20090727 9071,0,,,1,8,146.2,20090727 5141,0,,,1,8,2.8,20090727 0460,0,,,1,8,-0.1,20090727 7918,0,,,1,8,-0.1,20090727... (3 Replies)
Discussion started by: valokv
3 Replies

9. Shell Programming and Scripting

How to pick values from column based on key values by usin AWK

Dear Guyz:) I have 2 different input files like this. I would like to pick the values or letters from the inputfile2 based on inputfile1 keys (A,F,N,X,Z). I have done similar task by using awk but in that case the inputfiles are similar like in inputfile2 (all keys in 1st column and values in... (16 Replies)
Discussion started by: repinementer
16 Replies

10. Shell Programming and Scripting

Adding a column to a text based on file name

Dear all, Does anyone know how I could to add a column of numbers (1s, or 2s, or..., or 6s) to two-column text files (tab-delimited), where the specific number to be added varies as a function of the file naming? Currently, each of my text files has two columns, so the column with the... (12 Replies)
Discussion started by: rlapate
12 Replies
Login or Register to Ask a Question