Google Sheet to custom Google Map

sheet_to_map.py

import pandas as pd
import requests
import re

def get_final_url(url):
    response = requests.get(url, allow_redirects=True)
    return response.url

# Load the CSV file
file_path = 'sf housing - list.csv'
df = pd.read_csv(file_path)

# Initialize empty lists to store the results
hackerhouse_results = []
coop_results = []

# Loop through the rows of the DataFrame
for index, row in df.iterrows():
    name = row['name']
    website = row['website']
    google_maps_link = row['google maps link']
    housing_type = row['type']  # Assuming you have a 'type' column indicating the housing type
    min_cost = row['min cost per month']
    max_cost = row['max cost per month']

    if pd.notna(google_maps_link):
        try:
            final_url = get_final_url(google_maps_link)
            latitude, longitude = re.findall(r'@([\d.-]+),([\d.-]+),[\d.-]+z', final_url)[0]
            wkt = f'POINT ({longitude} {latitude})'

            # Create a description based on URL and Price
            description = f"URL: {website}"
            description += f"\nRent: {min_cost}-{max_cost}" if pd.notna(website) and pd.notna(min_cost) and pd.notna(max_cost) else ''

            # Create a result row
            result_row = [wkt, name, description]

            # If it's a co-op, append to the co-op results list
            if housing_type == 'co-op':
                coop_results.append(result_row)
            else:
                hackerhouse_results.append(result_row)
            print(result_row)
                
        except:
            print(final_url)

# Create DataFrames for all results and co-op results
hackerhouse_output_df = pd.DataFrame(hackerhouse_results, columns=['WKT', 'name', 'description'])
coop_output_df = pd.DataFrame(coop_results, columns=['WKT', 'name', 'description'])

# Save both DataFrames to CSV files
hackerhouse_output_df.to_csv('output_all.csv', index=False)
coop_output_df.to_csv('output_coop.csv', index=False)