CODEWITHSUNDEEP
pythonpandas
andrewr
andrewr

Reputation: 815

Python/Pandas, Assign Data from one frame to Corresponding Data in Another

I have data in one frame (DF1) containing labels for clusters, and their corresponding centroids in latitude and longitude, and I have a second data frame (DF2) containing some data for geolocated social media posts and the labels of the clusters they were assigned to with DBSCAN. I need to assign/map the cluster centroids from DF1 to posts with the corresponding cluster labels in DF2. Using pandas, how can I assign the latitude and longitudes (centroids) of DF1 to match the labels in DF2? I have tried using .join() and .merge() to join this way, but I've received various type and key errors.

Sample Data:

DF1

cluster_label   latitude    longitude   frequency
0               39.18193382 -77.51885109   6
1               39.18       -77.27         46
2               39.17917928 -76.6688633    35
3               39.1782     -77.2617       48
4               39.1765     -77.1927       6
5               39.1762375  -76.8675441    16
6               39.17468    -76.8204499    7
7               39.17457332 -77.2807235    9

DF2

user_id     timestamp       latitude    longitude   cluster_label
3073171535  3/10/2017 11:10 39.18193382 -77.51885109    0
1628115950  3/11/2017 9:04  39.18193382 -77.51885109    0
7.46E+17    3/9/2017 21:52  39.18       -77.27          1
4188084947  3/10/2017 1:53  39.18       -77.27          1
3123690477  3/10/2017 10:44 39.17917928 -76.6688633     2
3063785591  3/10/2017 11:15 39.17917928 -76.6688633     2
2878413353  3/10/2017 14:33 39.1782     -77.2617        3
19410434    3/14/2017 15:32 39.1782     -77.2617        3
7.51E+17    3/9/2017 21:44  39.1765     -77.1927        4
27081288    3/14/2017 12:28 39.1765     -77.1927        4
400535528   3/12/2017 13:30 39.1762375  -76.8675441     5
8.06E+17    3/9/2017 19:45  39.1762375  -76.8675441     5
199324673   3/13/2017 11:12 39.17468    -76.8204499     6
198014146   3/13/2017 11:22 39.17468    -76.8204499     6
195546843   3/13/2017 11:26 39.17468    -76.8204499     6
2344467747  3/10/2017 21:45 39.1746     -77.2807        7
703776919   3/11/2017 20:14 39.1746     -77.2807        7

Example Output

user_id     timestamp cluster_label  cluster_centroid_lat cluster_centroid_long
3073171535  3/10/2017 11:10     0       39.18193382     -77.51885109
7.46E+17    3/9/2017 21:52      1       39.18            -77.27

Upvotes: 0

Views: 60

Answers (1)

Binyamin Even
Binyamin Even

Reputation: 3382

merged=df2.merge(df1,on='cluster_label')

and drop whatever columns you'd like...

Upvotes: 1

Related Questions