Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

how to include the missing column in the original file using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting how to include the missing column in the original file using awk
# 8  
Old 03-26-2010
Hi Daptal, Kurumi,Rdcwayx,
Thanks for your reply.
All it works.

However, sometimes my raw files will have different missing columns which is not predicatable.Then how to make codes more flexible.

Possible of missing column of the raw files:
Code:
  
date,nomsgsent,nomsgnotdeliver,nomsgdelay
201003251000,1000,1,2
201003251000,900,0,0
201003251000,1450,0,0
201003251000,1230,0,0

-->is the good raw files.
Code:
 
date,nomsgsent,nomsgdelay
201003251000,1000,2
201003251000,900,0
201003251000,1450,0
201003251000,1230,0

--> missing "nomsgnotdeliver"
Code:
 
date,nomsgdelay
201003251000,2
201003251000,0
201003251000,0
201003251000,0

--> missing "nomsgsent,nomsgnotdeliver" columns
Code:
 
nomsgsent,nomsgdelay
1000,2
900,0
1450,0
1230,0

--> missing "date and nomsgnotdeliver" columns

Thanks,
Natalie

---------- Post updated 03-26-10 at 03:56 AM ---------- Previous update was 03-25-10 at 09:15 PM ----------

I have tried:
Code:
nawk 'BEGIN { FS=","
              OFS="," }
{if (NR == 1 && $3=="msgsent") {print $0; if (NR>=1) {print $0}}
else {if (NR == 1) $3="msgsent,"$3; (NR > 1); $3=","$3; print $0}}'

but I just have:
Code:
date,msgsent,msgdeliver,msgdelay
date,msgsent,msgdeliver,msgdelay--> output for the if part

Code:
date,msgsent,,msgdeliver,msgdelay
201003251000,1000,,2
201003251000,900,00
201003251000,1450,,0
201003251000,1230,,00 --> output for the else part.

Anyone have any good idea how to solve this problem?
Last edited by natalie23; 03-26-2010 at 06:02 AM..
# 9  
Old 03-26-2010
Quote:
Originally Posted by natalie23
...Anyone have any good idea how to solve this problem?
Here's a Perl script that implements a general solution:

Code:
##
perl -lne 'BEGIN {%x=qw(date 0 nomsgsent 1 nomsgnotdeliver 2 nomsgdelay 3);
                  %y=qw(0 date 1 nomsgsent 2 nomsgnotdeliver 3 nomsgdelay);
                  $hdr="date,nomsgsent,nomsgnotdeliver,nomsgdelay"}
           chomp; @a = split/,/;
           if ($.==1) {
             foreach $i (@a) {delete $y{$x{$i}} if (defined $x{$i})}
             print $hdr;
           } else {
             foreach $k (keys %y) {$b[$k]=""}
             for ($i=0; $i<=$#a; $i++){
               $m=0;
               while (defined $b[$m]){$m++}
               $b[$m] = $a[$i];
             }
             print join(",",@b);
             @b=();
           }' yourfile

Shown below are some test runs -

Code:
$
$
$ # correct format
$ cat file
date,nomsgsent,nomsgnotdeliver,nomsgdelay
201003251000,1000,1,2
201003251000,900,0,0
201003251000,1450,0,0
201003251000,1230,0,0
$
$ # (1) missing "nomsgnotdeliver"
$ cat file1
date,nomsgsent,nomsgdelay
201003251000,1000,2
201003251000,900,0
201003251000,1450,0
201003251000,1230,0
$
$ # Perl script
$ ##
$ perl -lne 'BEGIN {%x=qw(date 0 nomsgsent 1 nomsgnotdeliver 2 nomsgdelay 3);
>                   %y=qw(0 date 1 nomsgsent 2 nomsgnotdeliver 3 nomsgdelay);
>                   $hdr="date,nomsgsent,nomsgnotdeliver,nomsgdelay"}
>            chomp; @a = split/,/;
>            if ($.==1) {
>              foreach $i (@a) {delete $y{$x{$i}} if (defined $x{$i})}
>              print $hdr;
>            } else {
>              foreach $k (keys %y) {$b[$k]=""}
>              for ($i=0; $i<=$#a; $i++){
>                $m=0;
>                while (defined $b[$m]){$m++}
>                $b[$m] = $a[$i];
>              }
>              print join(",",@b);
>              @b=();
>            }' file1
date,nomsgsent,nomsgnotdeliver,nomsgdelay
201003251000,1000,,2
201003251000,900,,0
201003251000,1450,,0
201003251000,1230,,0
$
$ # (2) missing "nomsgsent", "nomsgnotdeliver"
$ cat file2
date,nomsgdelay
201003251000,2
201003251000,0
201003251000,0
201003251000,0
$
$ # Perl script
$ ##
$ perl -lne 'BEGIN {%x=qw(date 0 nomsgsent 1 nomsgnotdeliver 2 nomsgdelay 3);
>                   %y=qw(0 date 1 nomsgsent 2 nomsgnotdeliver 3 nomsgdelay);
>                   $hdr="date,nomsgsent,nomsgnotdeliver,nomsgdelay"}
>            chomp; @a = split/,/;
>            if ($.==1) {
>              foreach $i (@a) {delete $y{$x{$i}} if (defined $x{$i})}
>              print $hdr;
>            } else {
>              foreach $k (keys %y) {$b[$k]=""}
>              for ($i=0; $i<=$#a; $i++){
>                $m=0;
>                while (defined $b[$m]){$m++}
>                $b[$m] = $a[$i];
>              }
>              print join(",",@b);
>              @b=();
>            }' file2
date,nomsgsent,nomsgnotdeliver,nomsgdelay
201003251000,,,2
201003251000,,,0
201003251000,,,0
201003251000,,,0
$
$
$ # (3) missing "date", "nomsgnotdeliver"
$ cat file3
nomsgsent,nomsgdelay
1000,2
900,0
1450,0
1230,0
$
$ # Perl script
$ ##
$ perl -lne 'BEGIN {%x=qw(date 0 nomsgsent 1 nomsgnotdeliver 2 nomsgdelay 3);
>                   %y=qw(0 date 1 nomsgsent 2 nomsgnotdeliver 3 nomsgdelay);
>                   $hdr="date,nomsgsent,nomsgnotdeliver,nomsgdelay"}
>            chomp; @a = split/,/;
>            if ($.==1) {
>              foreach $i (@a) {delete $y{$x{$i}} if (defined $x{$i})}
>              print $hdr;
>            } else {
>              foreach $k (keys %y) {$b[$k]=""}
>              for ($i=0; $i<=$#a; $i++){
>                $m=0;
>                while (defined $b[$m]){$m++}
>                $b[$m] = $a[$i];
>              }
>              print join(",",@b);
>              @b=();
>            }' file3
date,nomsgsent,nomsgnotdeliver,nomsgdelay
,1000,,2
,900,,0
,1450,,0
,1230,,0
$
$
$

HTH,
tyler_durden
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to lowercase the values in a column in awk and include a dynamic counter?

Hi, I am trying to incorporate 2 functions into my `awk` command. I want to lower case Column 2 (which is essentially the same information in Col1, except in Col1 I want to maintain the capitalization) and I want to count from 0-N that begins and ends with the start of certain markers that I... (6 Replies)
Discussion started by: owwow14
6 Replies

2. Programming

Find gaps in time data and replace missing time value and column 2 value by interpolation in awk

Dear all, I am kindly seeking assistance on the following issue. I am working with data that is sampled every 0.05 hours (that is 3 minutes intervals) here is a sample data from the file 5.00000 15.5030 5.05000 15.6680 5.10000 16.0100 5.15000 16.3450 5.20000 16.7120 5.25000... (4 Replies)
Discussion started by: malandisa
4 Replies

3. Shell Programming and Scripting

Mention file include on command line: awk

Hi All: I need your help please. I have a include file *.awk , and I would want to know if it's there a option's awk where I can use this file include, for example: awk -f decodifica_bitmap.awk cadena_hex.txt -e /home/snh/Awk/include/funciones.awk > sal.out it give error: awk:... (1 Reply)
Discussion started by: solaris21
1 Replies

4. Shell Programming and Scripting

Help with awk script to get missing numbers in column 1

Hello to all, I have show below a file separated by commas. In first column has numbers where the last number is 13. 1,4 2,6 3,7 5,2 6,5 7,5 8,65 9,10 11,78 13,2 What I want to know is which numbers are missing from 1 to 13 (in this case 13 is last number in column 1). My real... (17 Replies)
Discussion started by: Ophiuchus
17 Replies

5. Shell Programming and Scripting

How can I modify my script to include or substitute missing data?

Let me start off by saying I am a self taught sometimes scripter so what you will see below won't be pretty. I have created a script to parse through a file with a large amount of data and simply pull out what I need. In doing this I create several files and then paste them together in order to... (2 Replies)
Discussion started by: fsanchez
2 Replies

6. Shell Programming and Scripting

AWK "make a new column that include increasing numbers"

please help!!!!!! I have a file .txt that has only one column like that: 34.1 35.5 35.6 45.6 ... Now, i want to add a column in the left in which the values of this column increase by 0.4 , for example: 0.0 34.1 0.4 35.5 0.8 35.6 1.2 45.6 How can i do with awk instructions??? ... (2 Replies)
Discussion started by: tienete
2 Replies

7. Shell Programming and Scripting

Need to include two more columns in the file using awk

Hi, I have a input file with many records as below: 1J4RR4GG0BC508200 68646 1 N M i want my output file to be like with columns included dgismdh and timestamp : Example: 1J4RR4GG0BC508200 68646 1 N M dgismdh 2012-02-21 07:22:25.98591 How to do it.can we do using awk? Pls help. (6 Replies)
Discussion started by: sonam273
6 Replies

8. Shell Programming and Scripting

Merge CSV files and create a column with the filename from the original file

Hello everyone!! I am not completely new to shell script but I havent been able to find the answer to my problem and I'm sure there are some smart brains here up for the challenge :D. I have several CSV files that I need to combine into one, but I also need to know where each row came from.... (7 Replies)
Discussion started by: fransanchezoria
7 Replies

9. Shell Programming and Scripting

Fix CSV file with column missing quotes

I have a CSV file that is missing quotes around a column that contains text with commas. Example: Column1, Column2, Column3, Column4, Column5, Column6 Data1, Data2, Data3, Data, 4, Data5, Data6 Data1, Data3, Data3, Data, text, 4, Data5, Data6 I think the easiest way for me to fix this is to... (2 Replies)
Discussion started by: EmptyH
2 Replies

10. UNIX for Dummies Questions & Answers

Option in sql script to include column headers when spooling file to .csv format

Can anyone help me how to include COLUMN HEADER when spooling file to .CSV format through SQL statement. Thanks, Akbar (4 Replies)
Discussion started by: s1a2m3
4 Replies
Login or Register to Ask a Question