0

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

2
  • 4
    At first glance I don't see how you arrived at the output (to be fair I am pretty slow) so I think it would be helpful if you edit your question to say what the condition for combining is in this case – Zanna Feb 6 '17 at 9:39
  • 2
    @Zanna He seems to want a JOIN on the first 2 columns. But I agree it is not as easy to derive. – ka3ak Feb 6 '17 at 11:25
3

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)
10
  • Just a comment that is actually a missing info by OP: This prints only the common lines of both files, which is fine as the OP is written. If one of the files has different entries (not common in both files) join will ignore them. – George Vasiliou Feb 6 '17 at 10:50
  • Adding options -a1 -a2 to join will also print non matched lines of file1 and file2 (just found it!) – George Vasiliou Feb 6 '17 at 11:30
  • @GeorgeVasiliou Good point. I wasn't aware of that option. – Michael Vehrs Feb 6 '17 at 11:56
  • 1
    Since the files are rather large, it may be a good idea to sort them separately and then do the join on the sorted files. This saves time would he want to re-run the join with tweaked parameters. – Kusalananda Feb 6 '17 at 12:00
  • @Kusalananda ..can u tell me how to sort the csv then join them.... it will be very help since other ans are takingto much time – gaurav singh Feb 6 '17 at 12:43
0

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.

0

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;
0

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
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.