<jupyter_start><jupyter_text>SF BAY AREA REAL ESTATE ANALYSIS                CURRENT FORECLOSURE DATA - ALL COUNTIES - SORTED BY ESTIMATED VALUE<jupyter_code># Using the `read_csv` function and Path module, read `foreclosures` file 
# and create the Pandas DataFrame.
foreclosure_df = pd.read_csv(
    Path('../California_Real_Estate_Analysis/Resources/foreclosures.csv')
).set_index('Address')

# For all the rows in the "Est Value", use the str.replace function 
# to replace the "$" with empty strings ("")
foreclosure_df.loc[:, "Est Value"] = foreclosure_df.loc[:, "Est Value"].str.replace("$", "")

# For all the rows in the "Est Value", use the str.replace function 
# to replace the "," with empty strings ("")
foreclosure_df.loc[:, "Est Value"] = foreclosure_df.loc[:, "Est Value"].str.replace(",", "")

# For all the rows in the "Est Value", use the str.replace function 
#convert Est value to float
foreclosure_df.loc[:, "Est Value"] = foreclosure_df.loc[:, "Est Value"].astype(float)

# For all the rows in the "Est Equity", use the str.replace function 
# to replace the "$" with empty strings ("")
foreclosure_df.loc[:, "Est Equity"] = foreclosure_df.loc[:, "Est Equity"].str.replace("$", "")

# For all the rows in the "Est Equity", use the str.replace function 
# to replace the "," with empty strings ("")
foreclosure_df.loc[:, "Est Equity"] = foreclosure_df.loc[:, "Est Equity"].str.replace(",", "")

# For all the rows in the "Est Equity", use the str.replace function 
#convert Est Equity to float
foreclosure_df.loc[:, "Est Equity"] = foreclosure_df.loc[:, "Est Equity"].astype(float)

#preview new dataframe
#foreclosures_equity_county.head()

#foreclosures_equity_county.dtypes
#sort by estimated value
foreclosure_df_sorted_value = foreclosure_df.sort_values('Est Value').dropna()

#Review the first and last five rows of the DataFrame
display(foreclosure_df_sorted_value.round().head(10))
display(foreclosure_df_sorted_value.round().tail(10))
foreclosure_value_plot_county = foreclosure_df_sorted_value[['County','City','Est Value']].hvplot(
    kind='bar',
    height=500,
    width=1500,
    groupby='County',  
    title='Estimated Foreclosue Value by County',
    y='Est Value',
    ylabel='Estimated Value'
).opts(xformatter = '%.0f',
      invert_axes=True,
      color='orange')

foreclosure_value_plot_county<jupyter_output><empty_output><jupyter_text>CURRENT FORECLOSURE DATA - ALL COUNTIES - SORTED BY ESTIMATED EQUITY<jupyter_code>#sort by estimated Equity
foreclosure_df_sorted_equity = foreclosure_df.sort_values('Est Equity').dropna()

#Review the first and last five rows of the DataFrame
display(foreclosure_df_sorted_equity.head())
display(foreclosure_df_sorted_equity.tail())
foreclosure_equity_plot_county = foreclosure_df_sorted_equity[['County','City','Est Equity']].hvplot(
    kind='bar',
    height=500,
    width=1500,
    groupby='County',  
    title='Estimated Foreclosue Equity by County',
    y='Est Equity',
).opts(xformatter = '%.0f',
      invert_axes=True,
      color='blue')

foreclosure_equity_plot_county
overlay_plot_county =  foreclosure_value_plot_county * foreclosure_equity_plot_county

# Style the operlay plot
overlay_plot_county.opts(
    title="Estimated Value and Equity comparison by County", 
    ylabel="Est Value and Equity", 
    width=1000, 
    height=500,
)
foreclosure_value_plot_city = foreclosure_df_sorted_value[['County','City','Est Value']].hvplot(
    kind='bar',
    height=500,
    width=1500,
    groupby='City',  
    title='Estimated Foreclosue Value by City',
    y='Est Value',
    ylabel='Estimated Value'
).opts(xformatter = '%.0f',
      invert_axes=True,
      color='orange')

foreclosure_value_plot_city
foreclosure_equity_plot_city = foreclosure_df_sorted_equity[['County','City','Est Equity']].hvplot(
    kind='bar',
    height=500,
    width=1500,
    groupby='City',  
    title='Estimated Foreclosue Equity by City',
    y='Est Equity',
).opts(xformatter = '%.0f',
      invert_axes=True,
      color='blue')

foreclosure_equity_plot_city
overlay_plot_city =  foreclosure_value_plot_city * foreclosure_equity_plot_city

# Style the operlay plot
overlay_plot_city.opts(
    title="Estimated Value and Equity Comparison by City", 
    ylabel="Est Value and Equity", 
    width=1000, 
    height=500,
)
foreclosure_equity_plot_city = foreclosure_df_sorted_equity[['County','City','Est Equity', 'Beds']].hvplot(
    kind='bar',
    height=500,
    width=1500,
    groupby='Beds',  
    title='Estimated Foreclosue Equity Value - Grouped by Bedrooms',
    y='Est Equity',
).opts(xformatter = '%.0f',
      invert_axes=True,
      color='blue')

foreclosure_equity_plot_city
foreclosure_equity_plot_city = foreclosure_df_sorted_equity[['County','City','Est Equity', 'Owner']].hvplot(
    kind='bar',
    height=500,
    width=1500,
    groupby='Owner',  
    title='Estimated Foreclosue Equity Value - Grouped by Owner',
    y='Est Equity',
).opts(xformatter = '%.0f',
      invert_axes=True,
      color='blue')

foreclosure_equity_plot_city
#return only rows where latitude isn't ???

#foreclosure_df.loc[:, "Latitude"] = foreclosure_df.loc[:, "Latitude"].str.replace("?", "")
#foreclosure_df.loc[:, "Longitude"] = foreclosure_df.loc[:, "Longitude"].str.replace("?", "")

foreclosure_df = foreclosure_df.query('Latitude != "???"')


#foreclosure_df['Latitude'].dropna()
#foreclosure_df['Longitude'].dropna()


#display(foreclosure_df.head())
#display(foreclosure_df.tail())

#display(foreclosure_df.isnull().sum())       
#foreclosure_df.dtypes
# For all the rows in "latitude", use the astype function 
# to change the data type from Object (string) to float
foreclosure_df.loc[:, "Latitude"] = foreclosure_df.loc[:, "Latitude"].astype(float)
foreclosure_df.loc[:, "Longitude"] = foreclosure_df.loc[:, "Longitude"].astype(float)

#display dataframes
#display(foreclosure_df.head())
#display(foreclosure_df.tail())

#confirm float type
#foreclosure_df.dtypes

# Create the Mapbox Scatter plot based on the most_foreclosures_df DataFrame.
# The color parameter should be based on County
px.scatter_mapbox(
foreclosure_df,
    lat='Latitude',
    lon='Longitude',
    zoom=8,
    title='Foreclosures scatter plot by County',
    color='County',
    width=1500,
    height=500
)
# Create the Mapbox Scatter plot based on the most_foreclosures_df DataFrame.
# The color parameter should be based on city
px.scatter_mapbox(
foreclosure_df,
    lat='Latitude',
    lon='Longitude',
    zoom=8,
    title='Foreclosures Scatter Plot by City',
    color='City',
    width=1500,
    height=500
)
total_equity_by_county = foreclosure_df_sorted_equity.groupby('City').mean().round().sort_values('Est Value')

total_equity_by_county

avg_foreclosure_value_city = (
    foreclosure_df[['Est Value','City']]
    .groupby('City')
    .mean()
    .sort_values('Est Value')
).round()

display(avg_foreclosure_value_city.hvlpot(title='test')
avg_foreclosure_value_city = (
    foreclosure_df[['Est Value','County']]
    .groupby('County')
    .mean()
    .sort_values('Est Value')
).round()

display(avg_foreclosure_value_city.head(10))
avg_foreclosure_value_county = (
    foreclosure_df[['Est Value']]
    .groupby('Count')
    .mean()
    .sort_values('County')
).round()
display(avg_foreclosure_value_county)
# Using the `read_csv` function and Path module, read sales data 
# and create the Pandas DataFrame.
home_sales_SF = pd.read_csv(
    Path('../California_Real_Estate_Analysis/Resources/San_Francisco.csv'),
)

santa_rosa_sales_df = pd.read_csv(
    Path('../California_Real_Estate_Analysis/Resources/Santa_Rosa.csv'),
)

oakland_sales_data = pd.read_csv(
    Path('../California_Real_Estate_Analysis/Resources/Oakland.csv')
)

San_Jose_Sales_df = pd.read_csv(
    Path('../California_Real_Estate_Analysis/Resources/San_Jose.csv'),
)<jupyter_output><empty_output><jupyter_text>SAN FRANCISCO HOUSING SALES DATA (LAST 3 MONTHS - SORTED BY PRICE)<jupyter_code>#Sort home based on sales date
home_sales_SF_sorted=home_sales_SF.sort_values('price')

#Review the first and last five rows of SF Sales DataFrame
display(home_sales_SF_sorted.head())
display(home_sales_SF_sorted.tail())<jupyter_output><empty_output><jupyter_text>SANTA ROSA HOUSING SALES DATA (LAST 3 MONTHS - SORTED BY PRICE)<jupyter_code>#Review the first and last five rows of Santa Rosa Sales DataFrame
santa_rosa_sales_df_sorted=santa_rosa_sales_df.sort_values('price')

display(santa_rosa_sales_df_sorted.head())
display(santa_rosa_sales_df_sorted.tail())<jupyter_output><empty_output><jupyter_text>SAN JOSE HOUSING SALES DATA (LAST 3 MONTHS - SORTED BY DATE)<jupyter_code>#Review the first and last five rows of San Jose Sales DataFrame
San_Jose_Sales_df_sorted = San_Jose_Sales_df.sort_values('price')

display(San_Jose_Sales_df_sorted.head())
display(San_Jose_Sales_df_sorted.tail())<jupyter_output><empty_output><jupyter_text>OAKLAND HOUSING SALES DATA (LAST 3 MONTHS - SORTED BY DATE)<jupyter_code>#Review the first and last five rows of Oakland Sales DataFrame
oakland_sales_data_sorted = oakland_sales_data.sort_values('price')

display(oakland_sales_data_sorted.head())
display(oakland_sales_data_sorted.tail())
San_Jose_Sales_df[['price', 'redfin_estimate']].hvplot(
    height=500,
    width=1500
)
santa_rosa_sales_df["sold_date"] = pd.to_datetime(santa_rosa_sales_df["sold_date"])
santa_rosa_sales_df.sort_values(by="sold_date")
santa_rosa_sales_df_sorted[['price', 'address','property_type']].hvplot(
    kind='bar',
    height=500,
    width=1500,
    groupby='property_type',
    
)

home_sales_SF[['price', 'address']].hvplot(
    kind='bar',
    height=500,
    width=1500,
)

santa_rosa_sales_df_sorted[['price', 'address','property_type','bedrooms']].hvplot(
    kind='bar',
    height=500,
    width=1500,
    groupby='bedrooms',
    
)
#get summary statistics on SF sales prices

home_sales_SF['price'].describe()
santa_rosa_sales_df['price'].describe()
#slice foreclosures by county and est equity

""""foreclosures_equity_county = (
    foreclosure_df[['County', 'Est Equity']]
)

#preview new datafram
foreclosures_equity_county.head()"""
#convert Est to float
#foreclosures_equity_county.loc[:, "Est Equity"] = foreclosures_equity_county.loc[:, "Est Equity"].astype(float)

total_equity_by_county = foreclosures_equity_county.groupby('County').sum()

total_equity_by_county
total_equity_by_county.hvplot.bar(
    x='County'
)
total_equity_by_county_sorted = total_equity_by_county.sort_values('Est Equity')

total_equity_by_county_sorted.hvplot.bar(
    x='County',
    y='Est Equity',
    rot=90
)
#slice foreclosures by county and est equity

foreclosures_equity_county_value = (
    foreclosure_df[['County', 'Est Value']]
)

#preview new datafram
foreclosures_equity_county_value.head()
# For all the rows in the "Est Equity", use the str.replace function 

# to replace the "$" with empty strings ("")
foreclosures_equity_county_value.loc[:, "Est Value"] = foreclosures_equity_county_value.loc[:, "Est Value"].str.replace("$", "")

# to replace the "," with empty strings ("")
foreclosures_equity_county_value.loc[:, "Est Value"] = foreclosures_equity_county_value.loc[:, "Est Value"].str.replace(",", "")


#preview new datafram
foreclosures_equity_county_value.head()

#foreclosures_equity_county.dtypes
display(oakland_sales_data.head())
display(oakland_sales_data.tail())
bay_area_foreclosure_sliced = foreclosure_df.loc[:, ['Address', 'City', 'Est Value', 'Est Equity', 'Latitude', 'Longitude' ]].dropna
bay_area_foreclosure_sliced.isnull().sum()
display(bay_area_foreclosure_sliced.head())
bay_area_foreclosure_sliced[['Latitude', 'Longitude']].replace('???'," ")
oakland_sales_data_sliced = oakland_sales_data.loc[:, ['address', 'latitude', 'longitude', 'price', 'redfin_estimate', 'property_type',]]
oakland_sales_data_sliced.dtypes
oakland_sales_data_sliced.isnull().sum()
# oakland_sales_data_sliced = oakland_sales_data_sliced.fillna('mean')
oakland_sales_data_sliced = oakland_sales_data_sliced.dropna()
 oakland_sales_data_sliced.isnull().sum()
display(oakland_sales_data_sliced.head())
santa_rosa_sales_sliced = santa_rosa_sales_df.loc[:, ['address', 'latitude', 'longitude', 'price', 'redfin_estimate', 'property_type',]]
santa_rosa_sales_sliced.dtypes
santa_rosa_sales_sliced.isnull().sum()
santa_rosa_sales_sliced = santa_rosa_sales_sliced.dropna()
display(santa_rosa_sales_sliced.head())
bay_area_foreclosure_sliced.dtypes
avg_foreclosure_value_county = (
    bay_area_foreclosure_sliced[['Est Value']]
    .groupby('County')
    .mean()
    .sort_values('County')
).round()
display(avg_foreclosure_value_county)
avg_foreclosure_value_county.hvplot.bar(
    title='Avg Foreclosure Value in the Bay Area By County',
    rot = 90
)
avg_foreclosure_value_city = (
    bay_area_foreclosure_sliced[['Est Value','City', 'Latitude', 'Longitude']]
    .groupby('City')
    .mean()
    .sort_values('City')
).round()

display(avg_foreclosure_value_city.head(10))
avg_foreclosure_value_city.hvplot.bar(
    title='Avg Foreclosure Value in the Bay Area By City',
    rot = 90
)
 alameda_county = bay_area_foreclosure_sliced[bay_area_foreclosure_sliced.index == 'Alameda']
alameda_county.head()
avg_alameda_foreclosure_value = (
    alameda_county[['Est Value','City', 'Latitude', 'Longitude']]
    .groupby('City')
    .mean()
    .sort_values('City')
).round()
avg_alameda_foreclosure_value.hvplot.bar(
    title='Avg Foreclosure Value in Alameda County',
    rot = 90
)
oakland_foreclosure_data = bay_area_foreclosure_sliced[bay_area_foreclosure_sliced.City == 'OAKLAND']
avg_oakland_foreclosure_value = (
    oakland_foreclosure_data[['Est Value', 'Est Equity','City',]]
    .groupby('City')
    .mean()
    .sort_values('City')
).round()
display(avg_oakland_foreclosure_value)
avg_oakland_foreclosure_value.hvplot.bar(
    title='Avg Foreclosure Value in Oakland',
    rot = 90
)
avg_oakland_sale = (
    oakland_sales_data[['price']]
    .mean()
).round()
avg_oakland_sale.hvplot.bar(
    title='Avg Sale price in Oakland',
    rot = 90
)
plot_oakland_foreclosues = avg_oakland_foreclosure_value.hvplot.bar(
    title='Avg Foreclosure Value in Oakland',
    rot = 90
)
plot_oakland_sales = avg_oakland_sale.hvplot.bar(
    title='Avg Foreclosure Value in Oakland',
    rot = 90
)
oakland_overlay_plot = plot_oakland_foreclosues * plot_oakland_sales

oakland_overlay_plot.opts(
    title="Average sale of a home in oakland vs the cost of a forclosed home", 
    ylabel="$$$", 
    width=1000, 
    height=500)
oakland_sales_data_plotted = px.scatter_mapbox(
    oakland_sales_data_sliced,
    lat = 'latitude',
    lon = 'longitude',
    color = 'property_type',
    size = 'price',
    size_max=50,
    zoom=11
)
sr_foreclosure_data = bay_area_foreclosure_sliced[bay_area_foreclosure_sliced.City == 'SANTA ROSA']
avg_sr_foreclosure_value = (
    sr_foreclosure_data[['Est Value', 'Est Equity','City',]]
    .groupby('City')
    .mean()
    .sort_values('City')
).round()
display(avg_sr_foreclosure_value)
avg_sr_foreclosure_value.hvplot.bar(
    title='Avg Foreclosure Value in Santa Rosa',
    rot = 90
)
avg_sr_sale = (
    santa_rosa_sales_df[['price']]
    .mean()
).round()
avg_sr_sale.hvplot.bar(
    title='Avg Sale price in Santa Rosa',
    rot = 90
)
plot_sr_foreclosues = avg_sr_foreclosure_value.hvplot.bar(
    title='Avg Foreclosure Value in Santa Rosa',
    rot = 90
)
plot_sr_sales = avg_oakland_sale.hvplot.bar(
    title='Avg Foreclosure Value in Santa Rosa',
    rot = 90
)
sr_overlay_plot = plot_sr_foreclosues * plot_sr_salessr_overlay_plot.opts(
    title="Average sale of a home in Santa Rosa vs the cost of a forclosed home", 
    ylabel="$$$", 
    width=1000, 
    height=500)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12847244294704724, 'fraction_numerical': 0.01017099993643125, 'mean_word_length': 5.660457239627434, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, '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': '295748', 'license': 'MIT', 'n_tokens_mistral': 5856, 'n_tokens_neox': 5583, 'n_words': 1071, 'path': 'real_estate.ipynb', 'repo_name': 'brianhabana/California_Real_Estate_Analysis', 'size': '15731'}