I want to add coordinates to this cities, but the dataframe does not have one..
group_metro_manila = metro_manila.groupby(['City'])[['Price (PHP)', 'Floor_area (sqm)', 'Price Per SQM']].mean().sort_values('Price (PHP)')
So I manually created them...
city_location = pd.DataFrame({ 'City': ['Dasmarinas', 'General Trias', 'Imus', 'Las Pinas', 'Makati', 'Mandaluyong','Manila', 'Muntinlupa','Paranaque','Pasay', 'Pasig','Quezon City', 'Silang', 'Taguig'], 'Latitude': [14.2990, 14.3214, 14.4064, 14.4445, 14.5547, 14.5794, 14.5995, 14.4081, 14.4793, 14.5378, 14.5764, 14.6760, 14.2142, 14.5176], 'Longitude': [120.9590, 120.9073, 120.9405, 120.9939, 121.0244, 121.0359, 120.9842, 121.0415, 121.0198, 121.0014, 121.0851, 121.0437, 120.9687, 121.0509] })
I want to add the city_location latitude and longitude to their designated city city_location
So what I do is I join city_location to group_metro_manila
merge_df = group_metro_manila.join(city_location[['City','Latitude','Longitude']].set_index('City'), on='City')
but the values becomes NaN
can someone enlighten me on how to add columns without making the values NaN? I am new to pandas and want to become data analyst, please can someone help me correct my code? Thank you.
CodePudding user response：
You can try concat or merge
pd.concat([group_metro_manila, city_location.set_index('City')], axis=1)
# or
group_metro_manila.merge(city_location, left_index=True, right_on='City', how='left')
CodePudding user response：
It looks like one of your frames has 'City' as a column and other as index. You'll need to either run .set_index('City') on the former or (given a recent enough version of pandas) add on='City' to join() arguments.