Overwrite specific column in xml file with the specific column from adjacent line


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Overwrite specific column in xml file with the specific column from adjacent line
# 1  
Old 10-19-2015
Overwrite specific column in xml file with the specific column from adjacent line

I have an xml file dumped from rrd file, that I want to "patch" so the xml file doesn't contain any blank hole in the resulting graph of the rrd file.

Here is the file.

Code:
                        <!-- 2015-10-12 14:00:00 WIB / 1444633200 --> <row><v> 4.0419731265e+07 </v><v> 4.5045912770e+06 </v></row>
                        <!-- 2015-10-12 14:30:00 WIB / 1444635000 --> <row><v> 4.0918656141e+07 </v><v> 4.3788889519e+06 </v></row>
                        <!-- 2015-10-12 15:00:00 WIB / 1444636800 --> <row><v> 3.9170320634e+07 </v><v> 4.1575607379e+06 </v></row>
                        <!-- 2015-10-12 15:30:00 WIB / 1444638600 --> <row><v> 4.0042666683e+07 </v><v> NaN </v></row>
                        <!-- 2015-10-12 16:00:00 WIB / 1444640400 --> <row><v> 3.7800259425e+07 </v><v> 3.9889770705e+06 </v></row>
                        <!-- 2015-10-12 16:30:00 WIB / 1444642200 --> <row><v> 4.3428580982e+07 </v><v> 4.6176893128e+06 </v></row>
                        <!-- 2015-10-12 17:00:00 WIB / 1444644000 --> <row><v> NaN </v><v> NaN </v></row>
                        <!-- 2015-10-12 17:30:00 WIB / 1444645800 --> <row><v> 2.8175279532e+07 </v><v> 3.2702479666e+06 </v></row>
                        <!-- 2015-10-12 18:00:00 WIB / 1444647600 --> <row><v> NaN </v><v> NaN </v></row>
                        <!-- 2015-10-12 18:30:00 WIB / 1444649400 --> <row><v> 2.6849054218e+07 </v><v> NaN </v></row>
                        <!-- 2015-10-12 19:00:00 WIB / 1444651200 --> <row><v> 2.7128265520e+07 </v><v> 3.0079522711e+06 </v></row>
                        <!-- 2015-10-12 19:30:00 WIB / 1444653000 --> <row><v> 2.9454945311e+07 </v><v> 3.2169775579e+06 </v></row>
                        <!-- 2015-10-12 20:00:00 WIB / 1444654800 --> <row><v> 3.0942154265e+07 </v><v> 3.5790954829e+06 </v></row>
                        <!-- 2015-10-12 20:30:00 WIB / 1444656600 --> <row><v> 3.4192846130e+07 </v><v> 3.9272012512e+06 </v></row>
                        <!-- 2015-10-12 21:00:00 WIB / 1444658400 --> <row><v> 3.2414867732e+07 </v><v> 3.5966483780e+06 </v></row>
                        <!-- 2015-10-12 21:30:00 WIB / 1444660200 --> <row><v> 3.3603672868e+07 </v><v> 3.7096918887e+06 </v></row>
                        <!-- 2015-10-12 22:00:00 WIB / 1444662000 --> <row><v> 2.9880049291e+07 </v><v> 3.4207987623e+06 </v></row>
                        <!-- 2015-10-12 22:30:00 WIB / 1444663800 --> <row><v> 2.5531159221e+07 </v><v> 2.9017737347e+06 </v></row>
                        <!-- 2015-10-12 23:00:00 WIB / 1444665600 --> <row><v> 2.3767683540e+07 </v><v> 2.5850900701e+06 </v></row>
                        <!-- 2015-10-12 23:30:00 WIB / 1444667400 --> <row><v> 2.0764912116e+07 </v><v> 2.2501507280e+06 </v></row>
                        <!-- 2015-10-13 00:00:00 WIB / 1444669200 --> <row><v> 1.7792667886e+07 </v><v> 1.9121125517e+06 </v></row>
                        <!-- 2015-10-13 00:30:00 WIB / 1444671000 --> <row><v> 1.5628975308e+07 </v><v> 1.6763476712e+06 </v></row>
                        <!-- 2015-10-13 01:00:00 WIB / 1444672800 --> <row><v> 1.2069293261e+07 </v><v> 1.2963697129e+06 </v></row>
                        <!-- 2015-10-13 01:30:00 WIB / 1444674600 --> <row><v> NaN </v><v> NaN </v></row>
                        <!-- 2015-10-13 02:00:00 WIB / 1444676400 --> <row><v> 8.0396180932e+06 </v><v> 8.4440129683e+05 </v></row>
                        <!-- 2015-10-13 02:30:00 WIB / 1444678200 --> <row><v> NaN </v><v> 8.4456790646e+05 </v></row>
                        <!-- 2015-10-13 03:00:00 WIB / 1444680000 --> <row><v> 5.2890435516e+06 </v><v> 6.0423314244e+05 </v></row>
                        <!-- 2015-10-13 03:30:00 WIB / 1444681800 --> <row><v> 3.3841049411e+06 </v><v> 4.1918953556e+05 </v></row>

I want to copy corresponding column in the line before the line that contain 'NaN' value so all the 'NaN' value overwriten with the value of the column of the line before it.

The expected result will be :

Code:
                <!-- 2015-10-12 14:00:00 WIB / 1444633200 --> <row><v> 4.0419731265e+07 </v><v> 4.5045912770e+06 </v></row>
                        <!-- 2015-10-12 14:30:00 WIB / 1444635000 --> <row><v> 4.0918656141e+07 </v><v> 4.3788889519e+06 </v></row>
                        <!-- 2015-10-12 15:00:00 WIB / 1444636800 --> <row><v> 3.9170320634e+07 </v><v> 4.1575607379e+06 </v></row>
                        <!-- 2015-10-12 15:30:00 WIB / 1444638600 --> <row><v> 4.0042666683e+07 </v><v> 4.1575607379e+06 </v></row>
                        <!-- 2015-10-12 16:00:00 WIB / 1444640400 --> <row><v> 3.7800259425e+07 </v><v> 3.9889770705e+06 </v></row>
                        <!-- 2015-10-12 16:30:00 WIB / 1444642200 --> <row><v> 4.3428580982e+07 </v><v> 4.6176893128e+06 </v></row>
                        <!-- 2015-10-12 17:00:00 WIB / 1444644000 --> <row><v> 4.3428580982e+07 </v><v> 4.6176893128e+06 </v></row>
                        <!-- 2015-10-12 17:30:00 WIB / 1444645800 --> <row><v> 2.8175279532e+07 </v><v> 3.2702479666e+06 </v></row>
                        <!-- 2015-10-12 18:00:00 WIB / 1444647600 --> <row><v> 2.8175279532e+07 </v><v> 3.2702479666e+06 </v></row>
                        <!-- 2015-10-12 18:30:00 WIB / 1444649400 --> <row><v> 2.6849054218e+07 </v><v> 3.2702479666e+06 </v></row>
                        <!-- 2015-10-12 19:00:00 WIB / 1444651200 --> <row><v> 2.7128265520e+07 </v><v> 3.0079522711e+06 </v></row>
                        <!-- 2015-10-12 19:30:00 WIB / 1444653000 --> <row><v> 2.9454945311e+07 </v><v> 3.2169775579e+06 </v></row>
                        <!-- 2015-10-12 20:00:00 WIB / 1444654800 --> <row><v> 3.0942154265e+07 </v><v> 3.5790954829e+06 </v></row>
                        <!-- 2015-10-12 20:30:00 WIB / 1444656600 --> <row><v> 3.4192846130e+07 </v><v> 3.9272012512e+06 </v></row>
                        <!-- 2015-10-12 21:00:00 WIB / 1444658400 --> <row><v> 3.2414867732e+07 </v><v> 3.5966483780e+06 </v></row>
                        <!-- 2015-10-12 21:30:00 WIB / 1444660200 --> <row><v> 3.3603672868e+07 </v><v> 3.7096918887e+06 </v></row>
                        <!-- 2015-10-12 22:00:00 WIB / 1444662000 --> <row><v> 2.9880049291e+07 </v><v> 3.4207987623e+06 </v></row>
                        <!-- 2015-10-12 22:30:00 WIB / 1444663800 --> <row><v> 2.5531159221e+07 </v><v> 2.9017737347e+06 </v></row>
                        <!-- 2015-10-12 23:00:00 WIB / 1444665600 --> <row><v> 2.3767683540e+07 </v><v> 2.5850900701e+06 </v></row>
                        <!-- 2015-10-12 23:30:00 WIB / 1444667400 --> <row><v> 2.0764912116e+07 </v><v> 2.2501507280e+06 </v></row>
                        <!-- 2015-10-13 00:00:00 WIB / 1444669200 --> <row><v> 1.7792667886e+07 </v><v> 1.9121125517e+06 </v></row>
                        <!-- 2015-10-13 00:30:00 WIB / 1444671000 --> <row><v> 1.5628975308e+07 </v><v> 1.6763476712e+06 </v></row>
                        <!-- 2015-10-13 01:00:00 WIB / 1444672800 --> <row><v> 1.2069293261e+07 </v><v> 1.2963697129e+06 </v></row>
                        <!-- 2015-10-13 01:30:00 WIB / 1444674600 --> <row><v> 1.2069293261e+07 </v><v> 1.2963697129e+06 </v></row>
                        <!-- 2015-10-13 02:00:00 WIB / 1444676400 --> <row><v> 8.0396180932e+06 </v><v> 8.4440129683e+05 </v></row>
                        <!-- 2015-10-13 02:30:00 WIB / 1444678200 --> <row><v> 8.0396180932e+06 </v><v> 8.4456790646e+05 </v></row>
                        <!-- 2015-10-13 03:00:00 WIB / 1444680000 --> <row><v> 5.2890435516e+06 </v><v> 6.0423314244e+05 </v></row>
                        <!-- 2015-10-13 03:30:00 WIB / 1444681800 --> <row><v> 3.3841049411e+06 </v><v> 4.1918953556e+05 </v></row>

Nan value sometimes happen in cacti (cacti.net) because the script generated the rrd file sometimes not fast enough to generate the data.
I want to patch this NaN value so the graph will look nice and smooth without any blank gap.

I know a little about regex , script and perl. I know how to search specific line before/after regex. But this problem exceed my knowledge.

Thank you for any respons.

Last edited by rk4k; 10-20-2015 at 12:13 AM..
# 2  
Old 10-20-2015
Something alomg this line?
Code:
awk '$9=="NaN" {sub ("NaN",last9)} $11=="NaN" {sub ("NaN", last11)} {last9=$9; last11=$11} 1' file

This User Gave Thanks to RudiC For This Post:
# 3  
Old 10-20-2015
Smilie Wow. Yes , exactly solve the problem.
I know awk , but this is new to me .

Thank you .
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 read particular line in file from specific column?

Hi...friends.... I want to create inventory...information for that I need to read some specific row say 2nd row from 1st 3 column and and write data with particular file used, I have some more column also but I need only 3 column data of first entry after header I attached sample file..those... (12 Replies)
Discussion started by: nex_asp
12 Replies

2. Shell Programming and Scripting

How to grep an empty line in a specific column of a file?

Suppose i have the following data : cat file.txt 12431,123334,55353,546646,14342234,4646,35234 123123,3535,123434,132535,1234134,13535,123534 123213,545465,23434,45646,2342345,4656,31243 2355425,2134324,53425,342,35235,23434,234535 3423424,234234,65465,,2344,35436,234524,234... (7 Replies)
Discussion started by: Ravi Tej
7 Replies

3. Shell Programming and Scripting

Replace column that matches specific pattern, with column data from another file

Can anyone please help with this? I have 2 files as given below. If 2nd column of file1 has pattern foo1@a, find the matching 1st column in file2 & replace 2nd column of file1 with file2's value. file1 abc_1 foo1@a .... abc_1 soo2@a ... def_2 soo2@a .... def_2 foo1@a ........ (7 Replies)
Discussion started by: prashali
7 Replies

4. Shell Programming and Scripting

convert huge .xml file in .csv with specific column.

I have huge xml file in server and i want to convert it to .csv with specific column ... i have search in blog but i didn't get any usefully command. Thanks in advance (1 Reply)
Discussion started by: pareshkp
1 Replies

5. UNIX for Dummies Questions & Answers

Replacing a specific column of a text file with another column

Hi, I have a text file in the following format: Code: 13412 NA06985 0 0 2 46.6432798439 4 4 4 4 13412 NA06991 NA06993 NA06985 2 48.8478948517 4 4 2 4 13412 NA06993 0 0 1 45.8022601455 4 4 2 4 13401 NA06994 0 0 1 48.780669145 4 4 4 4 13401 NA07000 0 0 2 47.7312017846 2 4 4 4 ... (2 Replies)
Discussion started by: evelibertine
2 Replies

6. UNIX for Dummies Questions & Answers

Replacing a specific column of a text file with another column

I have a text file in the following format: 13412 NA06985 0 0 2 46.6432798439 4 4 4 4 13412 NA06991 NA06993 NA06985 2 48.8478948517 4 4 2 4 13412 NA06993 0 0 1 45.8022601455 4 4 2 4 13401 NA06994 0 0 1 48.780669145 4 4 4 4 13401 NA07000 0 0 2 47.7312017846 2 4 4 4 13402 NA07019... (3 Replies)
Discussion started by: evelibertine
3 Replies

7. Shell Programming and Scripting

Assigning a specific format to a specific column in a text file using awk and printf

Hi, I have the following text file: 8 T1mapping_flip02 ok 128 108 30 1 665000-000008-000001.dcm 9 T1mapping_flip05 ok 128 108 30 1 665000-000009-000001.dcm 10 T1mapping_flip10 ok 128 108 30 1 665000-000010-000001.dcm 11 T1mapping_flip15 ok 128 108 30... (2 Replies)
Discussion started by: goodbenito
2 Replies

8. Shell Programming and Scripting

4 column tsv file, output 1 specific column

Hello all siteexplorer.search.yahoo.com can output results in tsv format, when opened in excel I get 4 columns. I would like to wget that file, which I can do. I would then like to pull the 2nd column and output it only. I've searched around and found a few bits and pieces but nothing I've... (6 Replies)
Discussion started by: casphar
6 Replies

9. UNIX for Dummies Questions & Answers

(cont) Retrieve line from a file based on a value in specific column

HI, Your help was great: awk -F":" '$5 ~ /^P/{print }' file I would like to know what changes need to be done to this line code, so that I can put it in a shell script and call it as the example below. example: countries that start with chacater 'P' > country P Result: ... (0 Replies)
Discussion started by: efernandes
0 Replies

10. UNIX for Dummies Questions & Answers

Retrieve line from a file based on a value in specific column

Hi, I have a file that has several values seperated by ":" 2006:John:Student:Football:Portugal:Cinema 2006:James:Engineer:Basket:Poland:Theatre 2007:Lucy:Diver:Gymnastic:England:Music 2007:Smith:Plumber:Basket:Spain:Poker I need make a filter based on the 5th field to find countries that... (1 Reply)
Discussion started by: efernandes
1 Replies
Login or Register to Ask a Question