combine two csv file based on condition

Question

I have two csv file lets say A.csv and B.csv A.csv contains (file size is more than 5gb)

64.234     20.342   786
63.231     20.124   765
63.652     20.857    387

B.csv contains (file size is more than 5gb)

  63.231   20.124    234
  63.652   20.857    383
  64.234   20.342    876

I want final file like

    64.234    20.342     786   876
    63.231    20.124     765    234
    63.652     20.857     387    383

Longitude and latitude will be used for condition When longitude and latitude are equal then there band value wll be stored is there any fast solution for this by using sed ,script or any tools...!!!!

Answer 1

There is no fast solution to your problem, since the input files are unsorted. You could do something like this, however:

join -o 0,1.2,1.3,2.3 <(sort A.csv) <(sort B.csv)

Answer 2

Although question is imprecise about what fields should be used for join - it looks like it is field1 and field2 (with typo in A.csv field2=20.875 instead of 20.857).

If you need to join on the first field only, Michaels solution is ok.
If you need to join on first two fields, you can use:

join <(sed -e 's/  */:/' a.csv | sort) <(sed -e 's/  */:/' b.csv | sort)

(you may need to tweak sed regex if fields are not separated by spaces only). Rakesh's solution is quite inefficient as it scans both A.csv and B.csv for each line in A.csv.

Answer 3

With that much data, you should consider importing the data into a database, creating indexes on (latitude,longitude) and then you can get your results with

select a.latitude, a.longitude, a.value, b.value
from a, b
where a.latitude  = b.latitude
  and a.longitude = b.longitude;

Answer 4

Try with this one :

for i in `awk '{print $1}' A.csv`;
 do
         t1=`awk -v a="$i" '$1==a {print $3}' A.csv`;
         awk -F' ' -v a="$i" -v b="$t1" '$1==a {print $1" "$2" "$3" " b }' B.csv;
 done