<jupyter_start><jupyter_text>Import Packages<jupyter_code># Import public modules
import pandas as pd
import os
import sys

# Import custom modules
from foursquare import foursquare as fsqr
from openstreetmap import openstreetmap as osm
from eurostat import germany as ger

# Add path to local pwd file folder
sys.path.insert(0, os.path.abspath(os.environ['USERPROFILE'] + '/Desktop/my_pwd'))
import foursquare_pwd

# Set foursquare credentials
fsqr_client_id = foursquare_pwd.client_id
fsqr_client_secret = foursquare_pwd.client_secret<jupyter_output><empty_output><jupyter_text>Eurostat<jupyter_code># Population_by_age_groups_and_sex
population_by_age_groups_and_sex = ger.Population_by_age_groups_and_sex()
population_by_age_groups_and_sex.api_query_result.shape
# Population_structure
population_structure = ger.Population_structure()
population_structure.api_query_result.shape
# Population_by_citizenship
population_by_citizenship = ger.Population_by_citizenship()
population_by_citizenship.api_query_result.shape
# Living_conditions
living_conditions = ger.Living_conditions()
living_conditions.api_query_result.shape
# Education
education = ger.Education()
education.api_query_result.shape
# Culture_and_tourism
culture_and_tourism = ger.Culture_and_tourism()
culture_and_tourism.api_query_result.shape
# Labour_market
labour_market = ger.Labour_market()
labour_market.api_query_result.shape
# Transport
transport = ger.Transport()
transport.api_query_result.shape
# Environment
environment = ger.Environment()
environment.api_query_result.shape<jupyter_output><empty_output><jupyter_text>Merge Eurostat data into a single DataFrame<jupyter_code>eurostat_data = population_by_age_groups_and_sex.api_query_result \
                .merge(population_structure.api_query_result, on=['cities', 'time'], how='outer') \
                .merge(population_by_citizenship.api_query_result, on=['cities', 'time'], how='outer') \
                .merge(living_conditions.api_query_result, on=['cities', 'time'], how='outer') \
                .merge(education.api_query_result, on=['cities', 'time'], how='outer') \
                .merge(culture_and_tourism.api_query_result, on=['cities', 'time'], how='outer') \
                .merge(labour_market.api_query_result, on=['cities', 'time'], how='outer') \
                .merge(transport.api_query_result, on=['cities', 'time'], how='outer') \
                .merge(environment.api_query_result, on=['cities', 'time'], how='outer').drop_duplicates()
print(eurostat_data.shape)
eurostat_data.head()<jupyter_output>(60, 152)<jupyter_text>Collect Foursquare data for top 20 cities  Test biergarten fetch for top 20 cities<jupyter_code>osm_fsqr_data = pd.DataFrame()

# Loop through the 20 biggest cities in Germany
for city, city_code in zip(osm.top20_cities.keys(), osm.top20_cities.values()):
    
    # Get biergartens for city from OpenStreetMap
    osm_biergartens = osm.OpenStreetMap(city, city_code)
    osm_biergartens.get_biergartens_for_city()
    
    # Match OpenStreetMap biergartens to Foursquare venues
    venues = fsqr.Venues(fsqr_client_id, fsqr_client_secret)
    venues.get_nearby_venues(osm_biergartens.df_biergartens['city'], osm_biergartens.df_biergartens['latitude'],osm_biergartens.df_biergartens['longitude'])
    
    # Get reviews for venues identified as biergarten
    reviews = fsqr.Reviews(fsqr_client_id, fsqr_client_secret)
    reviews.get_venue_review(venues.venue_df['City'], venues.venue_df['venue_id'])
    
    # Collect results to a single dataframe
    osm_fsqr_data = osm_fsqr_data.append(reviews.review_df)
    
print(osm_fsqr_data.shape)
osm_fsqr_data.head()<jupyter_output>(426, 5)<jupyter_text>Merging Eurostat Data to Biergartens<jupyter_code>biergarten_data = osm_fsqr_data.merge(eurostat_data, left_on='city', right_on='cities')
print(biergarten_data.shape)
biergarten_data.head()<jupyter_output>(1278, 152)<jupyter_text>Store dataset<jupyter_code>biergarten_data.to_csv('Biergarten_data_for_analysis.csv', sep=';', header=True, index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10223642172523961, 'fraction_numerical': 0.006635536986974687, 'mean_word_length': 6.386569872958257, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 22, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '465701', 'license': 'MIT', 'n_tokens_mistral': 1496, 'n_tokens_neox': 1383, 'n_words': 217, 'path': 'Data Preparation.ipynb', 'repo_name': 'Mtale/Coursera_Capstone', 'size': '4069'}