All NaN values when trying to fill latitude/longitude columns

Question

I'm trying to interpret latitude/longitude from one DataFrame and put it in another. However, when I try this, I end up getting all NaN values in my column.

I think the best way to show my issue is to show some code!

First, here's my first DataFrame, which is called happiness_data The following code gives the following output:

happiness_data.head()

Next, I have another called country_coord_data. A .head() of that frame gives this output:

Finally, here is the code where I try to change the latitude and longitude columns of the happiness DF.

country_names = happiness_data["Country or region"]
country_coord_data.loc[country_coord_data["name"] == "Finland"]["latitude"]

for country in country_names:
    for country2 in country_coord_data["name"]:
        if country==country2:
            happiness_data.loc[happiness_data["Country or region"] == country, "Latitude"] = country_coord_data.loc[country_coord_data["name"] == country, "latitude"]
            happiness_data.loc[happiness_data["Country or region"] == country, "Longitude"] = country_coord_data.loc[country_coord_data["name"] == country, "longitude"]
happiness.head()

And finally, here is the resulting output:

The way I see it, there are two main problems:

1. The code is not returning the proper latitude/longitudes.
2. This is terribly inefficient, but I can't think of a better way to do it off the top of my head.

Any help would be greatly appreciated!

Answer 1

I would simply perform a left join using merge, as you state, unless for a very specific reason, using for loops with dataframes is discouraged. The code I would use is as follows:

happiness_data = happiness_data.merge(country_cord_data,how='left',left_on='Country or region',right_on='name')

You would need a bit of df manipulation to drop the old columns, but this should suffice.