0

I have a list of of coordinates that have areas mapped out on a map

user_id id  latitude    longitude   requested_at
84  106 13.0472367  77.5022635  12-10-2020 14:59
84  107 13.0472789  77.5024498  12-10-2020 15:00
84  109 13.0478857  77.50110649 12-10-2020 15:01
84  113 13.0472431  77.5022722  12-10-2020 15:02
84  269 13.0473241  77.5022914  12-10-2020 15:58
84  272 13.0472491  77.5022977  12-10-2020 16:02
84  298 13.0472387  77.5021328  12-10-2020 16:13
84  300 13.047189   77.5022587  12-10-2020 16:14
84  361 13.0473381  77.5023609  12-10-2020 17:13
84  1755    13.0473334  77.5023081  14-10-2020 15:53
84  1844    13.0472747  77.502287   14-10-2020 18:01
3883    5374    12.8655367  77.7969649  18-10-2020 11:54
3883    7005    12.865576   77.7971244  19-10-2020 20:04
3883    7094    12.8654815  77.7972272  19-10-2020 21:32
3883    7703    12.8654448  77.7971621  20-10-2020 14:19
3883    8256    12.8654733  77.7970371  21-10-2020 07:59
3883    10022   12.8654014  77.7971733  22-10-2020 18:33
3883    10514   12.8654521  77.7970823  23-10-2020 08:23
3883    24186   12.8655376  77.796956   03-11-2020 10:16
3883    25685   12.8654658  77.7970327  04-11-2020 16:11
3883    29091   14.4539237  75.9065827  07-11-2020 16:33
41802   11757   12.8399959  77.6432516  13-10-2020 21:25
41802   11809   12.8399985  77.6432539  14-10-2020 20:38
41802   11900   12.839994   77.6432571  14-10-2020 20:39
41802   12215   12.8400107  77.6432862  16-10-2020 11:28
41802   27308   12.8419143  77.6434106  19-10-2020 07:49
41802   27309   12.8400799  77.6431911  19-10-2020 07:50
41802   6935    13.21153259 77.6601718  23-10-2020 11:03
41802   6939    13.21157837 77.66084737 23-10-2020 11:04
41802   6941    13.1345632  77.5726076  23-10-2020 18:36
41802   11727   13.134561   77.5726105  23-10-2020 18:44
41802   11736   13.1345605  77.5726143  23-10-2020 18:47
41802   27414   12.8399924  77.6432909  29-10-2020 10:38
41802   27434   12.8399968  77.643295   29-10-2020 10:39
41802   27443   12.8399749  77.6433084  29-10-2020 14:53
41802   27449   12.8399812  77.6432899  29-10-2020 15:16
41802   27461   12.83844757 77.69411675 06-11-2020 07:56
41802   27468   12.83850098 77.69418132 06-11-2020 08:02
41802   27451   12.8400088  77.6432962  07-11-2020 10:43

using these as points I am trying to remove the duplicate latitude and longitude within 500 meters and based on the user_id and the hour of the request

data_gpd = gpd.GeoDataFrame(Raw_trip_1, geometry = gpd.points_from_xy(Raw_trip_1.longitude, Raw_trip_1.latitude), crs={'init':'epsg:4326'})

How do I remove the duplicate and leave the last value like in pandas I would follow these steps

Raw_2 = Raw_1.drop_duplicates(subset=['user_id', 'requested_at'], keep='last')

coords = Raw_trip_2[["latitude", "longitude"]].values



ms_per_radian = 6371.0088
epsilon = 0.005 
db = DBSCAN(eps=epsilon, min_samples=4, algorithm='ball_tree', metric='haversine').fit(np.radians(coords))
cluster_labels = db.labels_
indices = db.core_sample_indices_ 
num_clusters = len(set(cluster_labels))
clusters = pd.Series([coords[cluster_labels == n] for n in range(num_clusters)])
print('Number of clusters: {}'.format(num_clusters))

The ideal result of this would be I get one geo latitude and longitude per hour per user

  • 3
    How did you determine the ideal result? – Kadir Şahbaz Nov 10 at 18:27
  • You want to start by first row, then find all of the following points that are within 500 m of the first point then drop duplicates? Then go to the next row of the remaining point and do the same again? A screenshot or drawing of your data and what you want to do would help – BERA Nov 10 at 18:44
  • I have added on more data here, now gives you more details. each users creates create multiple lat long requests and out of that I want to bring that down to 1 request in a 100~500 meter radius within an hour for my analysis. – Ani Nov 11 at 4:26
  • 1
    @BERA added on the code I am working with – Ani Nov 11 at 8:30

Your Answer

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

Browse other questions tagged or ask your own question.