Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Grep the nearest value from a csv file to another

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Grep the nearest value from a csv file to another
# 1  
Old 11-25-2011
Grep the nearest value from a csv file to another
Hello,
I have two csv file.

The first one (a huge file) with 3 columns

X, Y, Z

The second one with 2 columns

A, B

All the values are numbers.

I need to parse every "Z" field of the first file, find the closest "B" value in the second and extract from it the "A" relative value, to obtain a third csv file with a such structure

X, Y, A


Thank you in advance for your help.

--
RjVal
# 2  
Old 11-25-2011
What have you tried so far?

What exactly do you mean by 'closest'?

Some example input & output would be useful.
# 3  
Old 11-25-2011
Could you please let us know the closest value?
# 4  
Old 11-25-2011
Quote:
Originally Posted by CarloM
What have you tried so far?


What exactly do you mean by 'closest'?


Some example input & output would be useful.
I'm trying to use sed and awk but I'm not an expert and it seems I'm groping in the dark...

For "closest" I mean the value with the minimum difference.

Here are the input/output examples

X Y Z file (INPUT)
Code:
584922.210,4492300.990,-170.890
584922.210,4492230.990,-175.370
584922.210,4492160.990,-179.850
584922.210,4492090.990,-184.320
584922.210,4492020.990,-188.800

A B file (INPUT)
Code:
15.5,-166.19
16,-181.01
16.5,-123.78
17,-175.65
17.5,-164.47
18,-171.79

X Y A file (OUTPUT)
Code:
584922.210,4492300.990,18
584922.210,4492230.990,17
584922.210,4492160.990,16
584922.210,4492090.990,16
584922.210,4492020.990,16

Thank you very much for your interest.

--
RjVal

Moderator's Comments:
Mod Comment Please use code tags when posting data and code samples, thank you.
Last edited by Franklin52; 11-25-2011 at 07:25 AM..
# 5  
Old 11-25-2011
This should work. It's not going to be fast though...

Code:
# cat x.awk
function abs (x)
{
   if (x < 0) {
      return x * -1;
   } else {
      return x;
   }
}

NR==FNR {
   comps[$1]=$2;
}
NR!=FNR {
   x="";
   for (i in comps) {
      if ((x=="") || (abs(($3)-comps[i]) < abs(($3)-comps[x]))) {
         x=i;
      }
   }
   printf ("%s,%s,%s\n", $1, $2, x);
}
# awk -F, -f x.awk 2.csv 1.csv
584922.210,4492300.990,18
584922.210,4492230.990,17
584922.210,4492160.990,16
584922.210,4492090.990,16
584922.210,4492020.990,16

# 6  
Old 11-25-2011
MySQL
Great! Simply great!
Smilie

No matter for the time... computers works during the week end too...
... argh! like me Smilie

Thank you very much again

For moderator: sorry for the oversight and thanks for the suggestion.

--
RjVal
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Programming

Python or Shell script to Grep strings from input file and output in csv format

Hi Experts, I am writing a python script to grep string from file and display output in csv file as in attached screenshot https://drive.google.com/file/d/1gfUUdfmQma33tz65NskThYDhkZUGQO0H/view Input file(result_EPFT_config_device) Below is the python script i have prepared as of... (1 Reply)
Discussion started by: as7951
1 Replies

2. Shell Programming and Scripting

Checking File record equal to multiple of 70 or nearest number to multiple of 70

Hello, I have a file with below content - Example 3 6 69 139 210 345 395 418 490 492 I would like the result as - Multiple of 70 or nearest number in the file less than the multiple of 70 69 139 (5 Replies)
Discussion started by: Mannu2525
5 Replies

3. Shell Programming and Scripting

Match and Grep the nearest value in last field

Gents I have this input file file1 (uniq records) 54503207851 170211240 54503207911 170210837 54503208111 170215105 54503208112 170215210 54655210011 170223140 54655210091 170223738 54655210172 170224355 54655210251 170224741 54655210331 170225039 54655210411 170225505 54655210492... (13 Replies)
Discussion started by: jiam912
13 Replies

4. Shell Programming and Scripting

Save output of updated csv file as csv file itself, part 2

Hi, I have another problem. I want to sort another csv file by the first field. result.csv SourceFile,Airspeed,GPSLatitude,GPSLongitude,Temperature,Pressure,Altitude,Roll,Pitch,Yaw /home/intannf/foto5/2015_0313_090651_219.JPG,0.,-7.77223,110.37310,30.75,996.46,148.75,180.94,182.00,63.92 ... (2 Replies)
Discussion started by: refrain
2 Replies

5. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

6. Shell Programming and Scripting

Use grep sed or awk to extract string from log file and put into CSV

I'd like to copy strings from a log file and put them into a CSV. The strings could be on different line numbers, depending on size of log. Example Log File: File = foo.bat Date = 11/11/11 User = Foo Bar Size = 1024 ... CSV should look like: "foo.bat","11/11/11","Foo Bar","1024" (7 Replies)
Discussion started by: chipperuga
7 Replies

7. Shell Programming and Scripting

Script to Grep column 3 from csv file generated yesterday

Hello, Can any one please assist how to scirpt it: Every day a new log file is create and I want to process only the one generated yesterday and get the data of column 3 and 6. For example today's date is 24 then I want to get the data of log file created on 23rd. Log Files in... (7 Replies)
Discussion started by: sureshcisco
7 Replies

8. Shell Programming and Scripting

merge file with nearest range !!!!!!!

I want join 2 files with range of numbers in ascending order according to the value of the numbers the input will be like this INPUT file1 column1 column2 10 14 20 25 56 58 83 90 FILE 2 column1 column 2 1 2 2 8... (15 Replies)
Discussion started by: bogu0001
15 Replies

9. Shell Programming and Scripting

Urgent help on grep the nearest value

Hi, I have a list of data in text files with :- ColA ColB ColC ColD ColE ColF 12 122 2280 30 0.012987 1.776 13 118 2280 30 0.012987 1.954 14 115 2620 30 0.011321 2.131 15 113 2654 30 0.011177 2.309 16 145 1882 25 0.013110 2.487 17 112... (2 Replies)
Discussion started by: ahjiefreak
2 Replies
Login or Register to Ask a Question