<jupyter_start><jupyter_text>Pull data<jupyter_code>in_path = 'data/winemag-data-geocoded.csv'
df = pd.read_csv(in_path)

red_white_path = 'data/wine_varietal.csv'
red_white_label = pd.read_csv(red_white_path)<jupyter_output><empty_output><jupyter_text>Label red/white wines<jupyter_code>df.groupby('variety').size().sort_values(ascending=False).head()

red = list(red_white_label[red_white_label.Type=='Red'].Name.str.strip().unique()) + ['Verduzzo Friulano ','Tintilia ']
white = list(red_white_label[red_white_label.Type=='White'].Name.str.strip().unique())
df.ix[df.variety.isin(red),'color'] = 'red'
df.ix[df.variety.isin(white),'color'] = 'white'
df.ix[df.color.isna(),'color'] = 'missing'
df.color.unique()
df.groupby(['color']).size().sort_values(ascending=False).to_frame()<jupyter_output><empty_output><jupyter_text>Clean-up province mappings (WIP)<jupyter_code>#clean-up province mappings
print(df.groupby('province')['lat'].mean())
print(df.groupby('province')['long'].mean())<jupyter_output>province
Central Italy         43.176554
Italy Other           43.393173
Lombardy              45.538123
Northeastern Italy    46.088512
Northwestern Italy    44.793867
Piedmont              44.687971
Sicily & Sardinia     38.024212
Southern Italy        41.083497
Tuscany               43.289044
Veneto                45.534886
Name: lat, dtype: float64
province
Central Italy         12.746926
Italy Other           11.205732
Lombardy              10.295514
Northeastern Italy    12.232828
Northwestern Italy     8.255510
Piedmont               8.173752
Sicily & Sardinia     13.358211
Southern Italy        15.348579
Tuscany               11.330808
Veneto                11.528472
Name: long, dtype: float64<jupyter_text>Find number of wineries within 20 miles<jupyter_code>temp = df[~df.long.isna()]
temp = temp.groupby('winery')[['long','lat']].mean().reset_index()
temp.head()
#function computes the distance between two lat/lon
#source: https://community.esri.com/groups/coordinate-reference-systems/blog/2017/10/05/haversine-formula
def geo_dist(coord_1,coord_2):
    # Coordinates in decimal degrees (e.g. 2.89078, 12.79797)
    lon1, lat1 = coord_1
    lon2, lat2 = coord_2

    R = 6371000  # radius of Earth in meters
    phi_1 = math.radians(lat1)
    phi_2 = math.radians(lat2)

    delta_phi = math.radians(lat2 - lat1)
    delta_lambda = math.radians(lon2 - lon1)

    a = math.sin(delta_phi / 2.0) ** 2 + math.cos(phi_1) * math.cos(phi_2) * math.sin(delta_lambda / 2.0) ** 2
    
    c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a))

    km = R * c/1000  # output distance in km
    return km
[temp.lat[0],temp.long[0]]
length = temp.winery.size
cols = ['winery_1','winery_2','dist']
lst = []
for i in range(0,length):
    for j in range(0,int(length)):
        if i < j:
            lst.append([temp.winery[i],temp.winery[j],geo_dist([temp.lat[i],temp.long[i]],[temp.lat[j],temp.long[j]])])
            if i % 100 == 0 and j % 100 == 0:
                print(i,j)

temp_2 = pd.DataFrame(lst,columns=cols)
#export results
out_path = 'data/winery-distances.csv'
temp_2.to_csv(out_path)
#find count within dist_cutoff km
dist_cutoff = 5

dist_table = temp_2[temp_2['dist']<5].groupby('winery_1').size().reset_index()
dist_table['CountWithin5Km'] = dist_table[0]
dist_table = dist_table.drop(columns=0)
dist_table.head()
#add dist to main frame
df = df.merge(right=dist_table,how='left',left_on='winery',right_on='winery_1').drop(columns='winery_1')
df.ix[df.CountWithin5Km.isna(),'CountWithin5Km'] = 0.0
df[df['winery']=='460 Casina Bric']
df_out = df
out_path = 'data/winemag-data-geocoded-clean.csv'
df_out.to_csv(out_path)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15043525571273123, 'fraction_numerical': 0.06964091403699674, 'mean_word_length': 4.036986301369863, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 16, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '541233', 'license': 'Apache-2.0', 'n_tokens_mistral': 1565, 'n_tokens_neox': 1389, 'n_words': 233, 'path': '2_add_red_white_proximity.ipynb', 'repo_name': 'gdc3000/uw-infx562-data-viz', 'size': '3676'}