Help with ... Formating the file using awk


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help with ... Formating the file using awk
# 1  
Old 05-28-2018
Help with ... Formating the file using awk

I have a file like below
Code:
position1                   0          7802            7802            0          client1                                                 -                              -

position1                   8          8032            8032            0          client1                                                 -                              -

position1                   4          7761            7761            0          client1                                                                                            -                              -

position1                   9          7766            7766            0          client2                                                 -                              -

position2                   1          7796            7796            0          client3                                                 -                              -

position2                   3          8032            8032            0          client4                                                 -                              -

position2                   2          8123            8123            0          client4                                                 -                              -

position3                   6          7804            7804            0          client5                                                 -                              -

position3                   7          7890            7890            0          client5                                                 -                              -

position3                   5          7801            7801            0          -                                                       -                              -

how could i format this file like below
Code:
unique(column1), count(column2) -->  position1, 4

                                                                     position2, 3

                                                                     position3, 3



Code:
unique(column1), count(column6) -->  position1, 4

                                                                     position2, 3

                                                                     position3, 2



Code:
unique(column1), unique(column6)), count(unique(column6))) -->         position1,client1, 3

                                                                                                                                position1,client1, 1

                                                                                                                                position2,client3, 1

                                                                                                                                position2,client3, 2

                                                                                                                                position3,client5, 2

                                                                                                                                position3,-, 1


Last edited by manas_ranjan; 05-29-2018 at 05:04 AM..
# 2  
Old 05-28-2018
You know this already: Any attempts / ideas / thoughts from your side?


How are the data produced? Can you influence / modify the creator (of those data, of course!)?
# 3  
Old 05-28-2018
Thanks RudiC. I tried this to count the columns but just couldn't relate how to combine multiple columns/rows at one.

Code:
awk -F"[ :\t]+" 'NR > 2 {A[$1]++}END{for(i in A)print i,A[i]}'

Nope I can't modify or influence the input data
# 4  
Old 05-28-2018
It REALLY were helpful IF you could verbally, carefully, detailedly describe your request and not leave it to the people in here to guess what you want from inconsistent samples! There's no position2 with a "-" in field 6, and it's nowhere told that "-" in $6 must not count like "client5" does. And, with the NR > 2 applied to your sample, the "position1" count is one to low.


Try these essays, all based on and adapted from your attempt above, on your problems and report back:
Code:
awk -F"[ :\t]+" 'NF {A[$1]++}END{for(i in A)print i,A[i]}' OFS=, file4
position1,4
position2,3
position3,3
awk -F"[ :\t]+" 'NF && $6 != "-" {A[$1]++}END{for(i in A)print i,A[i]}' OFS=, file4
position1,4
position2,3
position3,2
awk -F"[ :\t]+" 'NF {A[$1,$6]++}END{for(i in A)print i,A[i]}' OFS=, SUBSEP=, file4
position3,-,1
position3,client5,2
position2,client4,2
position2,client3,1
position1,client2,1
position1,client1,3

This User Gave Thanks to RudiC For This Post:
# 5  
Old 05-29-2018
thanks, it was typo that no position2 with a "-" in field 6 ..let me try it out first and next time I will take care of it.

---------- Post updated at 07:30 AM ---------- Previous update was at 02:58 AM ----------

I have tried to change the below format o/p to something like

Code:
awk -F"[ :\t]+" 'NF {A[$1,$6]++}END{for(i in A)print i,A[i]}' OFS=, SUBSEP=, file4
position3,-,1
position3,client5,2
position2,client4,2
position2,client3,1
position1,client2,1
position1,client1,3

this

Code:
positiondetails,position=position3,client=- <space> count=1
positiondetails,position=position3,client=client5<space> count=2
positiondetails,position=position2,client=client4<space> count=2
positiondetails,position=position2,client=client3<space> count=1
positiondetails,position=position1,client=client2<space> count=1
positiondetails,position=position1,client=client1<space> count=3

Code:
awk -F"[ :\t]+" 'NF {A[$1,$6]++}END{for(i in A){count=A[i]; split(i, w, ",") ; for (j in w) printf("positiondetails,position=%s count=%d", w[j], count}}' OFS=, SUBSEP=, file4

this prints everything one after another, but this is not what I would like to print, can anyone guide me thru? I hope this makes sense.

Last edited by manas_ranjan; 05-29-2018 at 05:06 AM..
# 6  
Old 05-29-2018
Try adding an \n to printf's format string.
# 7  
Old 05-29-2018
it's not working after adding \n after printf

Code:
awk -F"[ :\t]+" 'NF {A[$1,$6]++}END{for(i in A){count=A[i]; split(i, w, ",") ; for (j in w) printf("positiondetails,position=%s,client=%s count=%d", w[j], count}}' OFS=, SUBSEP=, file4

PS I missed client in my previous statement. sorry again!!

So in-order to print like this

Code:
positiondetails,position=position3,client=client5<space> count=2

below awk is not enough
Code:
awk -F"[ :\t]+" 'NF {A[$1,$6]++}END{for(i in A){count=A[i]; split(i, w, ",") ; for (j in w) printf("positiondetails,position=%s,client=%s count=%d\n", w[j], count}}' OFS=, SUBSEP=, file4 
 -->>>it's complaining ran out for this client=%s count=%d

if I make change
Code:
awk -F"[ :\t]+" 'NF {A[$1,$6]++}END{for(i in A){count=A[i]; split(i, w, ",") ; for (j in w) printf("positiondetails,position=%s count=%d", w[j], count}}' OFS=, SUBSEP=, file4

then output is like this

Code:
positiondetails,position=position3,client=client5<space> count=2 
positiondetails,position=client5<space> count=2

while I wanted to print

Code:
positiondetails,position=position3,client=client5<space> count=2

Any direction?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk HTML Conditional Formating

I am receiving the below output in text format. The output is converted to HTML table using the code mentioned below output in text LogDate DayOfWeek/Hours _0_ _1_ _2_ _3_ _4_ _5_ _6_ _7_ _8_ _9_ _10_ _11_ _12_ _13_ _14_ _15_ _16_ ... (3 Replies)
Discussion started by: Dumpi16
3 Replies

2. Shell Programming and Scripting

File formating

I need to create a fixed width file based on the column lengths. lets assume I have six(this may be dynamic) fields each are of different length column1=6 #size of the column column2=3 column3=2 column4=3 column5=4 column6=5 I tried below code snippet but it is not working echo... (4 Replies)
Discussion started by: gvkumar25
4 Replies

3. UNIX for Beginners Questions & Answers

File formating help

Hi all, I am having the file below I need that as below Thanks, Arun (12 Replies)
Discussion started by: arunkumar_mca
12 Replies

4. UNIX for Beginners Questions & Answers

File formating with lines

Hi All, Need to modify a file basically join the extra line to one I am having a file like below And would like to make it as below . (4 Replies)
Discussion started by: arunkumar_mca
4 Replies

5. Shell Programming and Scripting

formating sql file using awk or sed

Hi, I have a file where I would like to add a prompt type object_name statement before every create commnad create or replace force view test_view_01 ( col1 col2 col3 ) as (select a,b,c from sometable ); create or replace view test_view_02 ( col4 col5 col6 ) as (5 Replies)
Discussion started by: jville
5 Replies

6. Shell Programming and Scripting

Help in formating a txt file

Pls help in formatting a txt file using shell scripting Input file format: Name priya 2010-09-21 10:43:49 TEXT ID 1 hi TEXT ID 2 how TEXT ID 3 r TEXT ID 4 u Output required: name priya hi how r u (4 Replies)
Discussion started by: bha148
4 Replies

7. UNIX for Dummies Questions & Answers

File formating help needed.

I have a file called vm.cfg . content of file. acpi = 1 apic = 1 builder = 'hvm' How would i write a script which will add boot = 'd' at 3rd line. For example. after running the script the file should be like below. acpi = 1 apic = 1 boot = 'd' builder = 'hvm' I dont want to... (3 Replies)
Discussion started by: pinga123
3 Replies

8. Shell Programming and Scripting

file formating in Perl

Hi, I am new to unix , I have a requirement for formating the input file and generate the output file as per the downstream requirement .. My application receiving a text input file having 4 field and my application need to check each field and if some value of a field is blank ..then it need... (1 Reply)
Discussion started by: julirani
1 Replies

9. Shell Programming and Scripting

AWK: formating number without printf

Hello, I wrote a script that does lot of things, and I would like to change the format of a number but without printing it now (so I don't want to use printf as it will print the value immediately). Schematically here is what I have: awk 'BEGIN{number=0.01234567} $1==$2{$3=number}... (5 Replies)
Discussion started by: jolecanard
5 Replies

10. UNIX for Dummies Questions & Answers

Help required on file formating

I have two files file1 and file2 as shown below: file1: name nameabc bcd nameabcdefg file2: age age1111 age2345 age6743 I have pasted one file on the other with the delimiter "|" and the resulttant file is: name|age nameabc|age1111 bcd|age2345 nameabcdefg|age6743 (1 Reply)
Discussion started by: udiptya
1 Replies
Login or Register to Ask a Question