Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

awk adding counts together from column

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers awk adding counts together from column
# 1  
Old 08-26-2013
[SOLVED] awk adding counts together from column
Hello

Im new treat me nicely, I have a headache Smilie

I have a script that seemed to work now it doesnt anyway, the last part is adding counts of unique items in a csv file eg

Code:
05492U34         38
05492U34         47


two columns, (many different values like this in file)

i want the output as in another file

Code:
05492U34         85

I have tried this but seem to have issues

Code:
awk -F, '{a[$1]+=$2;}END{for (i in a)print i ";" a[i];}' file.csv > file1.csv

any help appreciated for this dummy

thanks paul
Last edited by aniquebmx; 08-29-2013 at 10:06 AM.. Reason: additional code tags
# 2  
Old 08-26-2013
I notice a couple of things:

1. From your input file, you don't need the field separator.
2. From your output file, you don't need the ";" in the print statement, just replace with a comma.
3. The sum of the amounts in column 2 on your input file don't match the total in your output file.
This User Gave Thanks to mjf For This Post:
aniquebmx
# 3  
Old 08-27-2013
values corrected ie 38 + 47 = 85 sorry got in a mess trying to put in code brackets
# 4  
Old 08-27-2013
Could you explain more on what is the issue? What is the output you are getting?

I see that your input and output are .csv files. Did you copy paste the input data from excel to here?

I used a comma delimiter for input and I am getting below results with the code you posted.

Input:
Code:
05492U34,         38
05492U34,         47

Output:
Code:
05492U34;85

This User Gave Thanks to krishmaths For This Post:
aniquebmx
# 5  
Old 08-27-2013
hi thanks for reply

ok

both input output are csv files with around 1000 different values like below

input

Code:
05492U343805492U3447

output

Code:
05492U3447005497U3432

this is copy paste from csv but its messed up, help

I get lots of 0 in column 3 which I dont want

---------- Post updated at 04:39 AM ---------- Previous update was at 04:29 AM ----------

here is code from script it works till the last line, i thinks its some csv, excel issue??

Code:
file=`grep '<un:UtranCell id="........' ${folder[$j]}/${folder[$j]}*updateNeighbours4_UtranRelation.xml >> countcellrelation.csv`
  
  
    sed -e 's/<un:UtranCell id="//g' -e 's/">//g' /home/nmsadm/XML/Coherence/countcellrelation.csv > /home/nmsadm/XML/Coherence/countcellrelations.csv
    awk -F, '{a[$1]++;}END{for (i in a)print i ";" a[i];}' countcellrelations.csv >> relations_locked_cells.csv
    awk -F, '{a[$1]+=$2;}END{for (i in a)print i ";" a[i];}' relations_locked_cells.csv > total_relations_with_file_4.csv

# 6  
Old 08-27-2013
Using your input file in post#1, a slight modification of your own code snippet will work:
Code:
awk '{a[$1]+=$2} END{for (i in a)print i "\t" a[i];}' file
05492U34    85


EDIT: Looking at your post#5, I'm almost sure the input file will not be what you posted in post#1; the field separator will be ";", won't it?
This User Gave Thanks to RudiC For This Post:
aniquebmx
# 7  
Old 08-28-2013
Hi all

thanks to all of you for replying, most helpful, I have learned something, i will thank all of you

Ive fixed it, couple of things the field seperator (you were right Rudi)

also am using KSH so nawk needed

here is code now

Code:
nawk -F";" '{a[$1]+=$2;}END{for (i in a)print i ";" a[i];}' relations_locked_cells.csv > total_relations_with_file_4.csv

got a small issue still with leading white space in the xml but can fix with notepad++ trying to find sed but thats another story Smilie
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Des/awk for change format and adding integers in a column of data?

Greetings! I need a quick way to change the format in a table of data Here is an example of the input: 10 72 Value=177 VDB=0.0245 Value4=0,0,171,0 10 274 Value=238 VDB=0.0433 Value4=29,0,205,0 10 312 Value=222 VDB=0.0384 Value4=8,0,190,19 10 540 Value=405 VDB=0.0391 Value4=13,30,153,195... (3 Replies)
Discussion started by: Twinklefingers
3 Replies

2. Shell Programming and Scripting

Adding a specified value to a specified column - awk?

Hi everyone! I sometimes need to do some simple arithmetics, like adding a number to a certain column of a file. So I wrote a small function in the .bashrc file, which looks like this shifter() { COL=$1 VAL=$2 FILE=$3 cp $FILE $FILE.shifted awk 'NF==4 {$(( $COL )) = $(( $COL ))... (6 Replies)
Discussion started by: radudownload
6 Replies

3. Shell Programming and Scripting

Filtering lines for column elements based on corresponding counts in another column

Hi, I have a file like this ACC 2 2 21 aaa AC 443 3 22 aaa GCT 76 1 33 xxx TCG 34 2 33 aaa ACGT 33 1 22 ggg TTC 99 3 44 wee CCA 33 2 33 ggg AAC 1 3 55 ddd TTG 10 1 22 ddd TTGC 98 3 22 ddd GCT 23 1 21 sds GTC 23 4 32 sds ACGT 32 2 33 vvv CGT 11 2 33 eee CCC 87 2 44... (1 Reply)
Discussion started by: polsum
1 Replies

4. Linux

Adding a prefix to a column using awk/sed commands

Hello, I am a newbie to linux and struggling to find a better way to append a column in a text file. Here is the file i want to modify: It has 8 columns (and thousands of rows). I want to append the first column by adding "chr" infront of the numbers. Some rows have a string in the first... (4 Replies)
Discussion started by: bjorngill
4 Replies

5. Shell Programming and Scripting

Adding column using awk

Hello everyone, I have a file with the following structure: abc xyz 111 222 agf hjhf 787 799 tht yah 878 898 ... ... ... ... ... ... ... ... ... ... ... ... I want to add a column (with a fixed value of 1000) at the end such that it becomes: abc xyz 111 222 1000 agf hjhf 787... (5 Replies)
Discussion started by: ad23
5 Replies

6. UNIX for Dummies Questions & Answers

how to get distinct counts in a column of a file

If i have a file sample.txt with more than 10 columns and 11th column as following data. would it be possible to get the distinct counts of values in single shot,Thank you. Y Y N N N P P o Expected Result: Value count Y 2 N 3 P 2 (2 Replies)
Discussion started by: Ariean
2 Replies

7. UNIX for Dummies Questions & Answers

Adding a column with the row number using awk

Is there anyway to use awk to add a first column to my data that automatically goes from 1 to n , where n is the numbers of my rows?:confused: (4 Replies)
Discussion started by: cosmologist
4 Replies

8. Shell Programming and Scripting

sed/awk-adding numeric to a column

I have a txt file as follows Code: Oct 1 file1 4144 Oct 1 file23 5170 Oct 2 file5 3434 Oct 21 file56 2343 I need to add a new column by marking the right log file from current directory. For example populate like this. Please not in the second columt for "1" it has... (2 Replies)
Discussion started by: gubbu
2 Replies

9. Shell Programming and Scripting

awk-adding a column to a file

Hello Friends, i used awk to sum up total size of files under a directory (with the help of examples, threads here). ls -l | awk '/^-/ {total += $5} END {printf "%15.0f\n",total}' >> total.txt After each execution of the script total result is appended into a text file: 7010 7794 8890 ... (7 Replies)
Discussion started by: EAGL€
7 Replies

10. 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
Login or Register to Ask a Question