usCitiesData.py

import pandas as pd
from geopy.distance import geodesic
from itertools import combinations

us = pd.read_csv("data/uscities.csv")
print(us.columns)
print(us.head())
# Index(['city', 'city_ascii', 'state_id', 'state_name', 'county_fips',
#        'county_name', 'lat', 'lng', 'population', 'density', 'source',
#        'military', 'incorporated', 'timezone', 'ranking', 'zips', 'id'],
#       dtype='str')

us = us.rename(columns={
    'city': 'city_name',
    'lat': 'city_lat',
    'lng': 'city_lon',
    'population': 'population'
})
# adjustable number, will use 150000 to (selfishly) include Columbia; Includes 70 more cities so we will keep for now
# original city data
# us = us[us['population'] > 150000]
us = us[us['population'] > 500000]
# print("Cities remaining:", len(us), " vs 200k: ", len(us2))

# Now we need to generate city pairs based on the metrics we determined from before and build the dataset
city_pairs = list(combinations(us.to_dict('records'), 2))
rows = []
for c1, c2 in city_pairs:
    dist = geodesic(
        (c1['city_lat'], c1['city_lon']),
        (c2['city_lat'], c2['city_lon'])
    ).miles

    rows.append({
        'city_a': c1['city_name'],
        'city_b': c2['city_name'],
        'distance_miles': dist,
        'pop_a': c1['population'],
        'pop_b': c2['population'],
        'combined_pop': c1['population'] + c2['population']
    })

us_pairs = pd.DataFrame(rows)
# print(us_pairs.head())
# print("Total pairs:", len(us_pairs))
us_pairs.to_csv("outputData/usCityPairsFull2.csv", index=False)
# Remember, this is just pairs of cities
# now we take this large dictionary and filter it out by distances.
# start with 30-300, but we can raise 30 or 300 if needed
us_pairs_filtered = us_pairs[
    (us_pairs['distance_miles'] >= 30) &
    (us_pairs['distance_miles'] <= 300)]

print("After distance filter:", len(us_pairs_filtered))
us_pairs_filtered.to_csv("outputData/usCityPairsDistanceFilteredMajor.csv", index=False)

us_pairs_final = us_pairs_filtered[
    us_pairs_filtered['combined_pop'] >= 2000000
]
us_pairs_final.to_csv("outputData/usCityPairsFinal.csv", index=False)