Pubs Geo 6.py

#get the regional Postcode only
pubs['Postcode']=pubs['zip_code'].str.split(' ').str[0]

#Create a Grouped DF by Postcode
pubs_by_postcode=pd.DataFrame(data=pubs['Postcode'].value_counts())
pubs_by_postcode.reset_index(inplace=True)
pubs_by_postcode.columns=['Postcode','Pubs']

#Append Population and Coordinates; calculate Pubs per 1.000 people
pubs_by_postcode=pubs_by_postcode.merge(cities[['Postcode', 'Latitude', 'Longitude','Town/Area','Population']],how='left',on='Postcode')
pubs_by_postcode['ratio']=pubs_by_postcode['Pubs']/pubs_by_postcode['Population']*1000
pubs_by_postcode.sort_values(by='ratio',ascending=False,inplace=True)

#Drop Outliers, top 5% of the ratios
pubs_by_postcode=pubs_by_postcode[pubs_by_postcode['ratio']<3]