Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Replace missing row with 0

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replace missing row with 0
# 1  
Old 09-09-2015
Replace missing row with 0
Hi all,

i have a file which contains something as below:

INPUT
Code:
00:00   0
01:00   0
02:00   0
03:00   0
04:00   0
05:00   0
06:00   0
07:00   0
08:00   0
09:00   0
10:00   5
11:00   0
13:00   5
14:00   4
15:00   0
16:00   7
17:00   6
18:00   0
19:00   0
20:00   0
23:00   0

what i wanted to achieved is that there should be a checking if there's a missing field/row on the first column. Desired output is shown below.

DESIRED OUTPUT
Code:
00:00   0
01:00   0
02:00   0
03:00   0
04:00   0
05:00   0
06:00   0
07:00   0
08:00   0
09:00   0
10:00   5
11:00   0
12:00   0
13:00   5
14:00   4
15:00   0
16:00   7
17:00   6
18:00   0
19:00   0
20:00   0
21:00   0
22:00   0
23:00   0

Thanks in advance.
# 2  
Old 09-09-2015
Is this a homework assignment? If so, it must be filed in the Homework and Coursework Forum and a completely filled out Homework template must be included in your post.

If this is not homework, please explain why you are trying to do this and show us what you have tried. Please also tell us what operating system and shell you're using.
# 3  
Old 09-09-2015
Hi Sir,

it's not. We are getting stats based on a table which has 00:00:00 to 23:59:00 logging. and i am getting the total for an hour.

Hope you could help me.

Cheers,

---------- Post updated at 03:11 AM ---------- Previous update was at 03:02 AM ----------

We are using a hadoop nosql as the DB

Please see code below. The code part is more of the function that creates the files.

Code:
function func_formater
{

   file=$1
   outfile=$2

   awk 'BEGIN{
                   OFS="\t";

             }
             {
                   sub(/\|/,X,$NF);
                   A[substr($2,1,2)]+=$NF
             }
        END  {
                for(i in A){
                                print i":00" OFS A[i]
                           }
             }
        ' OFS="\t" $file | sort -n > $outfile

}

function 24Hour
{
        STARTTIME=$1
        ENDTIME=$2

        handlerFile="24HourHandler.dat"

        comp="SELECT time, server, component, sum(a+b) as total FROM <table> where logtime >= '${STARTTIME}' and logtime < '${ENDTIME}'  GROUP BY time, server, component ORDER BY time, server, component ASC;"

		#run in hadoop db
        out=`ssh $user@$host "echo \"$comp\" > ${compFile};dbname run -file=${compFile} address=$host -port=$port"`

        echo $outHandler | cut -d'-' -f13- | sed -e 's/---//g' | xargs -n5 | head -n -1  > 24HourExtract.dat

        ids=$(cat 24HourExtract.dat | awk -F'|' '{print $2}' | sort -u | sed -e 's/\n//g')
        comp=$(cat 24HourExtract.dat | awk -F'|' '{print $3}' | sed -e 's/\n//g' | sort -u)

        for id in `echo $ids`
        do
                for component in `echo $comp`
                do
                        grep -i ${id} 24HourExtract.dat | grep -i ${component} > ${id}_${component}.dat

                        #Call formater function
                        func_formater {id}_${component}.dat ${id}_${component}_final.dat

                        echo "${component}" > ${id}_${component}_final1.DAT
                        cat ${id}_${component}_final.dat >> ${id}_${component}_final1.DAT

                done
        done
}


TIMEEND="2015-09-08 23:59:00.0"
TIMESTART="2015-09-08 00:00:00.0"

24Hour "${TIMESTART}" "${TIMEEND}"

---------- Post updated at 03:12 AM ---------- Previous update was at 03:11 AM ----------

we are using Linux RedHat and bash as the default SHELL
# 4  
Old 09-09-2015
Quote:
Originally Posted by ernesto
Hi all,

i have a file which contains something as below:

INPUT
Code:
00:00   0
01:00   0
02:00   0
03:00   0
04:00   0
05:00   0
06:00   0
07:00   0
08:00   0
09:00   0
10:00   5
11:00   0
13:00   5
14:00   4
15:00   0
16:00   7
17:00   6
18:00   0
19:00   0
20:00   0
23:00   0

what i wanted to achieved is that there should be a checking if there's a missing field/row on the first column. Desired output is shown below.

DESIRED OUTPUT
Code:
00:00   0
01:00   0
02:00   0
03:00   0
04:00   0
05:00   0
06:00   0
07:00   0
08:00   0
09:00   0
10:00   5
11:00   0
12:00   0
13:00   5
14:00   4
15:00   0
16:00   7
17:00   6
18:00   0
19:00   0
20:00   0
21:00   0
22:00   0
23:00   0

Thanks in advance.
Hello Ernesto,

Following may help you in same. Let's say our Input_file is as follows.
Code:
 cat test2323
00:00   0
01:00   0
02:00   0
03:00   0
04:00   0
05:00   0
06:00   0
07:00   0
08:00   0
09:00   0
10:00   5
11:00   0
13:00   5
14:00   4
15:00   0
16:00   7
17:00   6
18:00   0
19:00   0
20:00   0
23:00   0
27:00   0
81:00   0

Then following code may help us in same.
Code:
  awk -F":" 'NR==1{print;A=$1}{if($1==A+1){print;A=$1} else {diff=$1-A;while(diff>0){print A+1 FS "00   0";A=A+1;diff--}};}' test2323

Output will be as follows.
Code:
00:00   0
01:00   0
02:00   0
03:00   0
04:00   0
05:00   0
06:00   0
07:00   0
08:00   0
09:00   0
10:00   5
11:00   0
12:00   0
13:00   0
14:00   4
15:00   0
16:00   7
17:00   6
18:00   0
19:00   0
20:00   0
21:00   0
22:00   0
23:00   0
24:00   0
25:00   0
26:00   0
27:00   0
28:00   0
29:00   0
30:00   0
31:00   0
32:00   0
33:00   0
34:00   0
35:00   0
36:00   0
37:00   0
38:00   0
39:00   0
40:00   0
41:00   0
42:00   0
43:00   0
44:00   0
45:00   0
46:00   0
47:00   0
48:00   0
49:00   0
50:00   0
51:00   0
52:00   0
53:00   0
54:00   0
55:00   0
56:00   0
57:00   0
58:00   0
59:00   0
60:00   0
61:00   0
62:00   0
63:00   0
64:00   0
65:00   0
66:00   0
67:00   0
68:00   0
69:00   0
70:00   0
71:00   0
72:00   0
73:00   0
74:00   0
75:00   0
76:00   0
77:00   0
78:00   0
79:00   0
80:00   0
81:00   0

Thanks,
R. Singh
# 5  
Old 09-09-2015
Below code will suffice even if the records are missing from the top or bottom of the list
Code:
awk -F ':' '
BEGIN {
 p = -1; 
 t = "00\t0"
}
{
 p++; 
 if($1 + 0 != p) {
  for(i = p; i < $1+0; i++) {
   printf "%02d:%s\n", p++, t
  }
 };
 print
} 
END {
 for(i = ++p; i <= 23; i++) {
  printf "%02d:%s\n", p++, t
 }
}' file

# 6  
Old 09-09-2015
Try also
Code:
awk '{split($1,N,":"); while (++L < N[1]) printf "%02d:00\t0 <--\n", L; L=N[1]} 1' file
00:00   0
01:00   0
02:00   0
03:00   0
04:00   0
05:00   0
06:00   0
07:00   0
08:00   0
09:00   0
10:00   5
11:00   0
12:00    0 <--
13:00   5
14:00   4
15:00   0
16:00   7
17:00   6
18:00   0
19:00   0
20:00   0
21:00    0 <--
22:00    0 <--
23:00   0

This User Gave Thanks to RudiC For This Post:
ernesto
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Add Row from First Row (Split Row)

HI Guys, I have Below Input :- RepigA_hteis522 ReptCfiEtrBsCll_aofe MSL04_MSL2_A25_1A 0 9 MSL04_MSL2_A25_1B 0 9 MSL04_MSL2_A25_1C 0 9 RepigA ReptCfiEtrBsCll hteis522 aofe MSL04_MSL2_A25_1A 0 9 MSL04_MSL2_A25_1B 0 9 MSL04_MSL2_A25_1C 0 9 Split Data in two first row... (2 Replies)
Discussion started by: pareshkp
2 Replies

2. Shell Programming and Scripting

Replace First Column and First Row Data

HI Guys, I just want to replace data for First Column and Row Cell(1,1) Input :- Hello A B C X 1 2 3 Y 4 5 6 Z 7 8 9 Output:- Byee A B C X 1 2 3 Y 4 5 6 Z 7 8 9 From Hello to Byee .....And The Each file have Different String. (3 Replies)
Discussion started by: pareshkp
3 Replies

3. 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

4. UNIX for Dummies Questions & Answers

I want to replace the row with zero value

I have this file 5 9 7 23 0 5 7 78 0 3 7 0 5 44 9 0 i want to remove the row with zero value but i want to replace it with the above + under over 2 like this (the second zero) (78+3)/2 (5 Replies)
Discussion started by: osama ahmed
5 Replies

5. Shell Programming and Scripting

In a row, replace negative sign and find minimum value among four columns

Hi Friends, I have an input file like this chr1 100 200 1 2 3 4 chr1 150 200 4 5 6 7 chr2 300 400 9 6 7 1 chr2 300 410 -10 21 -11 13 chr3 700 900 -21 -22 130 165 Now, my output file is chr1 100 200 1 chr1 150 200 4 chr2 300 400 1 chr2 300 410 10 chr3 700 900 21 Remove... (5 Replies)
Discussion started by: jacobs.smith
5 Replies

6. Shell Programming and Scripting

Replace Second row with First row in a File

Hi, I need to replace first row to second row: Input: A JACK WAS_${HH}_JACK .... Output should be: JACK WAS_A_JACK .... I tried below code.. awk '{TEMP= (NR==1)}; {sub(/${HH}/$TEMP/)}' (2 Replies)
Discussion started by: kmsekhar
2 Replies

7. Shell Programming and Scripting

replace row separated by tab

Dear users, I have this problem, this is the example: 123 (tab) A (tab) B (tab) C (tab) 456 where the (tab) is actually the \t delimiter. I need to replace the A B and C for D E and F, this is: 123 (tab) D (tab) E (tab) F (tab) 456 The thing is that my file is quite long and this... (2 Replies)
Discussion started by: Gery
2 Replies

8. Shell Programming and Scripting

Replace last row of a column in bash/awk/sed

Hi, I've got a file with 3 columns which ends like this: ... 1234 345 1400 5287 733 1400 8472 874 1400 9317 726 1400 I want to replace the last row of the last column with the value 0. So my new file will end: ... 1234 345 1400 5287 733 1400 8472 874 1400 9317 726 ... (5 Replies)
Discussion started by: jhunter87
5 Replies

9. Shell Programming and Scripting

Find and replace duplicate column values in a row

I have file which as 12 columns and values like this 1,2,3,4,5 a,b,c,d,e b,c,a,e,f a,b,e,a,h if you see the first column has duplicate values, I need to identify (print it to console) the duplicate value (which is 'a') and also remove duplicate values like below. I could be in two... (5 Replies)
Discussion started by: nuthalapati
5 Replies

10. Shell Programming and Scripting

Replace missing standard folders from home directories.

Hi, I want to develop a script to replace missing folders from home directories. These may have been deleted by the user. A standard home directory will have these folders in it and nothing else: Desktop, Documents, Downloads, Library, Movies, Music, Pictures, Public, Sites I also want to... (3 Replies)
Discussion started by: z399y
3 Replies
Login or Register to Ask a Question