<jupyter_start><jupyter_text>Population forecasting1. Clean population dataframe    - append missing data to population dataframe (24 cities)2. Transform dataframe3. Forecast population using **fb prophet**4. Clean predictions5. Create pickle6. Test  1. Clean population dataframe<jupyter_code>import pandas as pd
import numpy as np
population = pd.read_csv('https://raw.githubusercontent.com/Lambda-School-Labs/PT17_cityspire-a-ds/main/notebooks/datasets/data/population2010-2019/metropop_2010_2019.csv')<jupyter_output><empty_output><jupyter_text>Check population csv<jupyter_code>print(population.shape)
population.head()<jupyter_output>(415, 14)<jupyter_text>Combine city state- use explode to separate combined cities- combine separated city and states<jupyter_code>def explode_str(population, col='Metro-Area', sep='-'):
    s = population[col]
    i = np.arange(len(s)).repeat(s.str.count(sep) +1)
    return population.iloc[i].assign(**{col: sep.join(s).split(sep)})

population = explode_str(population)
population['Metro-Area'] = population['Metro-Area'].str.strip()
def explode_str(population, col='State', sep='-'):
    s = population[col]
    i = np.arange(len(s)).repeat(s.str.count(sep) +1)
    return population.iloc[i].assign(**{col: sep.join(s).split(sep)})

population = explode_str(population)
population['State'] = population['State'].str.strip()
print(population['Metro-Area'].nunique())
population['Metro-Area'].unique()
population = population[population['Metro-Area'] != '']
print(population['State'].nunique())
population['State'].unique()
population['City,State'] = population['Metro-Area'] + ', ' + population['State']
population.shape<jupyter_output><empty_output><jupyter_text>Drop duplicate rows and unused columns<jupyter_code>population[population.duplicated(subset=['City,State'], keep=False)]
population = population.drop_duplicates(subset=['City,State' ], keep='last')
print(population.shape)
population = population.drop(columns = ['Census', 'Estimate Base'])
population = population[['Metro-Area', 'State', 'City,State', '2010', '2011', '2012', '2013', '2014', '2015', '2016', '2017', '2018', '2019']]
population['Metro-Area'] = population['Metro-Area'].str.replace('Urban Honolulu', 'Honolulu')
population['City,State'] = population['City,State'].str.replace('Urban Honolulu, HI', 'Honolulu, HI')
population.loc[population['Metro-Area'] == 'Honolulu']<jupyter_output><empty_output><jupyter_text>Add cities that are missing from main database<jupyter_code>extended = pd.read_excel('csv/Extended_cities_2010_2019.xlsx')
extended = extended.rename(columns = {
                                                 2010: '2010',
                                                 2011: '2011',
                                                 2012: '2012',
                                                 2013: '2013',
                                                 2014: '2014',
                                                 2015: '2015',
                                                 2016: '2016',
                                                 2017: '2017',
                                                 2018: '2018',
                                                 2019: '2019'})
population = population.append(extended, ignore_index=True)
print(population.shape)
population.head()
population.tail()
population.to_csv('csv/population_cleaned.csv', index=False)<jupyter_output><empty_output><jupyter_text>2. Stack dataframe - this is to try the groupby so I don't have to create separate csv's and run each city csv separately- https://stackoverflow.com/questions/64179626/stack-unstack-melt-pivot-transpose-what-is-the-simple-method-to-convert-mul<jupyter_code>population_melt = population[['City,State', '2010', '2011', '2012', '2013', '2014', '2015', '2016', '2017', '2018', '2019']]
population_melt['City,State'] = population_melt['City,State'].astype(str)
population_melt[['2010', '2011', '2012', '2013', '2014', '2015', '2016', '2017', '2018', '2019']] = population_melt[['2010', '2011', '2012', '2013', '2014', '2015', '2016', '2017', '2018', '2019']].astype(float)
population_melt = (population.melt(id_vars=['City,State'],
                    var_name = 'ds',
                    value_name = 'y'
                    ).reset_index(drop=True))
population_melt = population_melt[population_melt['ds'] != 'State']
population_melt = population_melt[population_melt['ds'] != 'Metro-Area']
population_melt.head()
population_melt.to_csv('csv/population_melt.csv', index=False)<jupyter_output><empty_output><jupyter_text>3. Forecast population using **fb prophet**<jupyter_code>from fbprophet import Prophet
from fbprophet.plot import add_changepoints_to_plot
cities_list = list(population['City,State'])
def rnd_series(city):
    subset = population_melt[population_melt['City,State']== city]
    dates = (pd.DataFrame({'ds': pd.to_datetime(population_melt['ds'])}))
    return subset
series = [rnd_series(city) for city in cities_list]
len(series)
series[0]
def run_prophet(series):
    model = Prophet(daily_seasonality=False,
                    weekly_seasonality=False,
                    yearly_seasonality=False)
    model.fit(series)
    forecast = model.make_future_dataframe(periods=10, freq='Y')
    forecast = model.predict(forecast)
    forecast = forecast[['ds', 'yhat', 'yhat_lower', 'yhat_upper']]
    forecast['City,State'] = series['City,State'].iloc[0]
    forecast = forecast[['City,State','ds', 'yhat', 'yhat_lower', 'yhat_upper']]
    forecast['ds'] = pd.DatetimeIndex(forecast['ds']).year
    forecast[['yhat', 'yhat_lower', 'yhat_upper']] = forecast[['yhat', 'yhat_lower', 'yhat_upper']].astype(float)
    forecast[['yhat', 'yhat_lower', 'yhat_upper']] = forecast[['yhat', 'yhat_lower', 'yhat_upper']].apply(np.ceil)
    return forecast
f = run_prophet(series[0])
f.head()
for i in range(len(series)):
    f = run_prophet(series[i])
    f.to_csv('csv/population_prediction.csv', mode='a', index='False')
predictions = pd.read_csv('csv/population_prediction.csv')
predictions.head()<jupyter_output><empty_output><jupyter_text>4. Clean Predictions- clean predictions.df- create pivot table to show in long format<jupyter_code>predictions = predictions.loc[predictions['City,State'] != 'City,State']
predictions[['yhat','yhat_lower', 'yhat_upper']] = predictions[['yhat','yhat_lower', 'yhat_upper']].astype(float) 
predictions['year'] = pd.DatetimeIndex(predictions['ds']).year
predictions = predictions[['City,State', 'year', 'yhat', 'yhat_lower', 'yhat_upper']]
print(predictions.shape)
predictions.head()
predictions.to_csv('csv/population_prediction.csv', index=False)
population_predictions = predictions[['City,State', 'year', 'yhat']]
population_predictions = population_predictions.pivot_table(index = 'City,State',
                                                          columns = 'year',
                                                          values = 'yhat')
print(population_predictions.shape)
population_predictions.head()
population_predictions.to_csv('csv/population_prediction_long.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15671956185928942, 'fraction_numerical': 0.041848055048448254, 'mean_word_length': 4.061833688699361, 'pattern_counts': {'":': 0, '<': 32, '<?xml version=': 0, '>': 32, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 24, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '102399', 'license': 'MIT', 'n_tokens_mistral': 2469, 'n_tokens_neox': 2157, 'n_words': 379, 'path': 'notebooks/model/population2010-2019/population_prediction.ipynb', 'repo_name': 'jiobu1/labspt15-cityspire-g-ds', 'size': '7121'}