0

I have two dataframes:

First one:-

Unnamed: 0  MapDate FIPS    County  State   Nothing D0  D1  D2  D3  D4  ValidStart  ValidEnd
0   0   20000104    1001    Autauga County  AL  0.0 100.0   0.00    0.00    0.0 0.0 2000-01-04  2000-01-10
1   1   20000104    1003    Baldwin County  AL  0.0 100.0   0.00    0.00    0.0 0.0 2000-01-04  2000-01-10
2   2   20000104    1005    Barbour County  AL  0.0 100.0   48.56   13.81   0.0 0.0 2000-01-04  2000-01-10
3   3   20000104    1007    Bibb County AL  0.0 100.0   21.16   0.00    0.0 0.0 2000-01-04  2000-01-10
4   4   20000104    1009    Blount County   AL  0.0 100.0   100.00  35.17   0.0 0.0 2000-01-04  2000-01-10

Second one:

County  lat lng
0   Los Angeles County  34.3209 -118.2247
1   Cook County 41.8401 -87.8168
2   Harris County   29.8578 -95.3936
3   Maricopa County 33.3490 -112.4915
4   San Diego County    33.0343 -116.7350

They have the same values in df['County'] and cnty['County']. i need to merge in a such a manner that I only get the lat and long of the values in df['County'] from cnty dataframe.

I need to to get the values in the format :

df['map_cordinates'] = {"type": "Point", "coordinates": [['Lat'], ['Lng']]}

where map_cordinates is a new column in the dataframe df. Lat and Lng are the latitude and longitude from the cnty dataframe.

I tried using merge but:-

print(pd.merge(df,cnty,on='County'))

This is increasing the number of rows in df from 3221 to 14489 which I don't want.

I tried taking help from: how to merge two data frames based on particular column in pandas python? but it's not giving the result which I want.

Can anyone suggest.

Lalit Joshi
  • 137
  • 10
  • Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) – BeRT2me Jul 04 '22 at 03:46
  • Thanks for the answer, I can see it answers the questions but I am not getting the values correctly. Eg. : first value in county in first dataframe is Autauga County and in second dataframe is Los Angeles County. So, it is picking the values and adding them to dataframe but not correct values. – Lalit Joshi Jul 04 '22 at 08:36
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Jul 04 '22 at 16:46

1 Answers1

0

I expect adding how='left' solves the issue.

pd.merge(df, cnty, on='County', how='left')
René
  • 4,594
  • 5
  • 23
  • 52
  • Thanks fot the answer but this isn't working. It's increasing the values fro 3332 to 14597 which I don't want to. I just need to have the lat and long of the counties mentioneed in the first dataframe and picked from second one without increasing the records. – Lalit Joshi Jul 04 '22 at 08:37
  • Can you provide sample datasets to reproduce the issue? – René Jul 04 '22 at 09:05