<jupyter_start><jupyter_text>3 for loops below were run after we realized our first weather pull didn't get all of the information we needed. We were short on time and didn't get a chance to trouble shoot why the for loops were randomly stopping (i.e., the first one ended at a random winery about 3000 records in)<jupyter_code># Create an empty list to hold the weather data.
winery_weather_data = []
output_filename = 'winery_weather_v3.csv'

# Print the beginning of the logging.
print("Beginning Data Retrieval     ")
print("-----------------------------")

# Create counters.
record_count = 1
set_count = 1
winery_count = 0

# Loop through all the wineries in the list.
for i, winery in enumerate(wineries):

    # Group wineries in sets of 50 for logging purposes.
    if (i % 50 == 0 and i >= 50):
        set_count += 1
        record_count = 1

    # Log the URL, record, and set numbers and the city.
    print(f"Processing Record {record_count} of Set {set_count} | {winery}")
    
    # Add 1 to the record count
    record_count += 1
    
    # Run an API request for each of the wineries.
    try:
        # Parse the JSON and retrieve data.
        avg_temp, avg_pressure, avg_humidity, avg_precip = get_weather_data(winery, winery_count)
               
        # Append the winery information into city_data list.
        winery_weather_data.append({"Winery": wineries[winery_count],
                          "Average Temperature (Kelvin)": avg_temp,
                          "Average Air Pressure (hPa)": avg_pressure,
                          "Average Humidity (%)": avg_humidity,
                          "Average Precipitation (mm)": avg_precip})

    # If an error is experienced, skip the winery.
    except:
        print("Weather not found. Skipping...")
        pass
    
    # Add to winery count
    winery_count += 1
    
    # Print status every 100 addresses
    if len(winery_weather_data) % 100 == 0:
    	print("Completed {} of {} address".format(len(winery_weather_data), len(wineries)))
            
    # Every 500 addresses, save progress to file(in case of a failure so we have something!)
    if len(winery_weather_data) % 500 == 0:
        pd.DataFrame(winery_weather_data).to_csv("{}_bak".format(output_filename))


    # save full results to CSV
    pd.DataFrame(winery_weather_data).to_csv(output_filename)

# Indicate that Data Loading is complete.
print("-----------------------------")
print("Data Retrieval Complete      ")
print("-----------------------------")
wineries[3189]
# Create an empty list to hold the weather data.
winery_weather_data = []
output_filename = 'winery_weather_startatHess.csv'

#RESET COUNTERS
record_count = 1
set_count = 1

# new for loop to pick up where above left off
for i in range (3188,7994):
    
    winery = wineries[i]
    
 # Group wineries in sets of 50 for logging purposes.
    if (i % 50 == 0 and i >= 50):
        set_count += 1
        record_count = 1

    # Log the URL, record, and set numbers and the city.
    print(f"Processing Record {record_count} of Set {set_count} | {winery}")
    
    # Add 1 to the record count
    record_count += 1
    
    # Run an API request for each of the wineries.
    try:
        # Parse the JSON and retrieve data.
        avg_temp, avg_pressure, avg_humidity, avg_precip = get_weather_data(winery, i)
               
        # Append the winery information into city_data list.
        winery_weather_data.append({"Winery": winery,
                          "Average Temperature (Kelvin)": avg_temp,
                          "Average Air Pressure (hPa)": avg_pressure,
                          "Average Humidity (%)": avg_humidity,
                          "Average Precipitation (mm)": avg_precip})

    # If an error is experienced, skip the winery.
    except:
        print("Weather not found. Skipping...")
        pass
    
    # Print status every 100 addresses
    if len(winery_weather_data) % 100 == 0:
    	print("Completed {} of {} address".format(len(winery_weather_data), len(wineries)))
            
    # Every 500 addresses, save progress to file(in case of a failure so we have something!)
    if len(winery_weather_data) % 500 == 0:
        pd.DataFrame(winery_weather_data).to_csv("{}_bak".format(output_filename))


    # save full results to CSV
    pd.DataFrame(winery_weather_data).to_csv(output_filename)

# Indicate that Data Loading is complete.
print("-----------------------------")
print("Data Retrieval Complete      ")
print("-----------------------------")
wineries[7077]
# Create an empty list to hold the weather data.
winery_weather_data = []
output_filename = 'winery_weather_startatPend.csv'

#RESET COUNTERS
record_count = 1
set_count = 1

# new for loop to pick up where above left off (again)
for i in range (7077,7993):
    
    winery = wineries[i]
    
 # Group wineries in sets of 50 for logging purposes.
    if (i % 50 == 0 and i >= 50):
        set_count += 1
        record_count = 1

    # Log the URL, record, and set numbers and the city.
    print(f"Processing Record {record_count} of Set {set_count} | {winery}")
    
    # Add 1 to the record count
    record_count += 1
    
    # Run an API request for each of the wineries.
    try:
        # Parse the JSON and retrieve data.
        avg_temp, avg_pressure, avg_humidity, avg_precip = get_weather_data(winery, i)
               
        # Append the winery information into city_data list.
        winery_weather_data.append({"Winery": winery,
                          "Average Temperature (Kelvin)": avg_temp,
                          "Average Air Pressure (hPa)": avg_pressure,
                          "Average Humidity (%)": avg_humidity,
                          "Average Precipitation (mm)": avg_precip})

    # If an error is experienced, skip the winery.
    except:
        print("Weather not found. Skipping...")
        pass
    
    # Print status every 100 addresses
    if len(winery_weather_data) % 100 == 0:
    	print("Completed {} of {} address".format(len(winery_weather_data), len(wineries)))
            
    # Every 500 addresses, save progress to file(in case of a failure so we have something!)
    if len(winery_weather_data) % 500 == 0:
        pd.DataFrame(winery_weather_data).to_csv("{}_bak".format(output_filename))


    # save full results to CSV
    pd.DataFrame(winery_weather_data).to_csv(output_filename)

# Indicate that Data Loading is complete.
print("-----------------------------")
print("Data Retrieval Complete      ")
print("-----------------------------")
# combine CSVs
wine_weather_1 = pd.read_csv('winery_weather_v2.csv', low_memory=False)
wine_weather_2 = pd.read_csv('winery_weather_startatHess.csv', low_memory=False)
wine_weather_3 = pd.read_csv('winery_weather_startatPend.csv', low_memory=False)

combined_winery = pd.concat([wine_weather_1, wine_weather_2, wine_weather_3])
combined_winery.rename(columns={'Average Precipitation (mm)': 'Average Daily Precipitation (mm)'}, inplace=True)
combined_winery

combined_winery.to_csv('winery_weather_updated.csv')
#bring in reviews data
#update link
reviewdata = '../wine_reviews_cleaned.csv'
reviews = pd.read_csv(reviewdata, index_col=0)
review_df = reviews.reset_index()
rev_df = review_df.drop(columns='index')
rev_df.set_index('winery', inplace=True)
rev_df
combined_winery.set_index('Winery', inplace=True)
combined_winery.drop(columns=['Unnamed: 0'], inplace=True)
combined_winery
#join with lat long data
latlong_df.set_index('winery_search_term', inplace=True)
latlong_df.drop(columns=['Unnamed: 0'], inplace=True)
latlong_df
combined_winery_full = combined_winery.join(latlong_df, on='Winery')
combined_winery_full.drop(columns=['number_of_results', 'status'], inplace=True)
combined_winery_full
combined_winery_full = combined_winery_full[["latitude", "longitude", "Average Temperature (Kelvin)", "Average Air Pressure (hPa)", "Average Humidity (%)", "Average Daily Precipitation (mm)"]]
combined_winery_full
output = rev_df.join(combined_winery_full, on='winery_search')
output
clean_df = output.dropna().reset_index()
clean_df
clean_df.to_csv('weather_scaled_updated.csv')<jupyter_output><empty_output><jupyter_text>County weather pull<jupyter_code>county_data = 'onecit_zips.csv'
county_latlong_df = pd.read_csv(county_data, low_memory=False)
county_latlong_df
# Create lists of latitudes and longitudes
county_lats = list(county_latlong_df['lat'])
county_lngs = list(county_latlong_df['lng'])

county_zip = list(county_latlong_df['zip'])
# create function for county weather
def get_county_weather_data (county, county_count):
    
    # Create endpoint URL with each county
    county_url = url + "&lat=" + str(county_lats[county_count]) + "&lon=" + str(county_lngs[county_count])
    
    county_temp_data  = []
    county_pressure_data = []
    county_humidity_data =[]
    county_precip_data = []
    
    # Parse the JSON and retrieve data.
    county_weather = requests.get(county_url).json()
    
    for j in range (0, 364):
        temp = county_weather["result"][j]["temp"]["mean"]
        county_temp_data.append(temp)
        pressure = county_weather["result"][j]["pressure"]["mean"]
        county_pressure_data.append(pressure)
        humidity = county_weather["result"][j]["humidity"]["mean"]
        county_humidity_data.append(humidity)
        precip = county_weather["result"][j]["precipitation"]["mean"]
        county_precip_data.append(precip)
                
    # calculate averages
    avg_temp = sum(county_temp_data) / len(county_temp_data)
    avg_pressure = sum(county_pressure_data) / len(county_pressure_data)
    avg_humidity = sum(county_humidity_data) / len(county_humidity_data)
    avg_precip = sum(county_precip_data) / len(county_precip_data)
    
    return avg_temp, avg_pressure, avg_humidity, avg_precip

# Create an empty list to hold the weather data.
county_weather_data = []
output_filename = 'uscounty_weather_v2.csv'

# Print the beginning of the logging.
print("Beginning Data Retrieval     ")
print("-----------------------------")

# Create counters.
record_count = 1
set_count = 1
county_count = 0

# Loop through all the wineries in the list.
for i in range (0, 1796):
    county = county_zip[i]
    
    # Group counties in sets of 50 for logging purposes.
    if (i % 50 == 0 and i >= 50):
        set_count += 1
        record_count = 1

    # Log the URL, record, and set numbers and the city.
    print(f"Processing Record {record_count} of Set {set_count} | {county}")
    
    # Add 1 to the record count
    record_count += 1
    
    # Run an API request for each of the wineries.
    try:
        # Parse the JSON and retrieve data.
        avg_temp, avg_pressure, avg_humidity, avg_precip = get_county_weather_data(county, i)
               
        # Append the winery information into city_data list.
        county_weather_data.append({"zip": county_zip[county_count],
                          "Average Temperature (Kelvin)": avg_temp,
                          "Average Air Pressure (hPa)": avg_pressure,
                          "Average Humidity (%)": avg_humidity,
                          "Average Daily Precipitation (mm)": avg_precip})

    # If an error is experienced, skip the county.
    except:
        print("Weather not found. Skipping...")
        pass
    
    # Add to county count
    county_count += 1
    
    # Every 500 addresses, save progress to file(in case of a failure so we have something!)
    if len(county_weather_data) % 500 == 0:
        pd.DataFrame(county_weather_data).to_csv("{}_bak".format(output_filename))
        
    # save full results to CSV
    pd.DataFrame(county_weather_data).to_csv(output_filename)

# Indicate that Data Loading is complete.
print("-----------------------------")
print("Data Retrieval Complete      ")
print("-----------------------------")


county_weather_data_df = pd.DataFrame(county_weather_data)

county_weather_df = pd.merge(county_latlong_df, county_weather_data_df, on = "zip", how = "left")
pd.DataFrame(county_weather_df).to_csv(output_filename)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1201322860686234, 'fraction_numerical': 0.012815212897891691, 'mean_word_length': 2.9272727272727272, 'pattern_counts': {'":': 20, '<': 9, '<?xml version=': 0, '>': 13, 'https://': 0, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '453728', 'license': 'MIT', 'n_tokens_mistral': 4011, 'n_tokens_neox': 3740, 'n_words': 1031, 'path': 'Resources/cleaning_notebooks/weather_data_pull.ipynb', 'repo_name': 'renatabg2205/growmywine', 'size': '12095'}