import sqlite3
import numpy as np
import pandas as pd
import random
from holoviews.element.tiles import EsriImagery
import hvplot.pandas
import holoviews as hv, pandas as pd, colorcet as cc
import panel as pn
from datashader.utils import lnglat_to_meters
from bokeh.io import output_notebook
from bokeh.plotting import figure, show
from bokeh.models import ColumnDataSource, LogColorMapper
import folium
from folium.plugins import HeatMap
conn = sqlite3.connect('../data/FPA_FOD_20170508.sqlite')
df = pd.read_sql_query("SELECT LATITUDE, LONGITUDE, FIRE_SIZE, STATE, FIRE_YEAR FROM fires", conn)
# Drop states that aren't in continental US
df = df.loc[(df.loc[:,'STATE']!='AK') & (df.loc[:,'STATE']!='HI') & (df.loc[:,'STATE']!='PR')]
df.head()
LATITUDE | LONGITUDE | FIRE_SIZE | STATE | FIRE_YEAR | |
---|---|---|---|---|---|
0 | 40.036944 | -121.005833 | 0.10 | CA | 2005 |
1 | 38.933056 | -120.404444 | 0.25 | CA | 2004 |
2 | 38.984167 | -120.735556 | 0.10 | CA | 2004 |
3 | 38.559167 | -119.913333 | 0.10 | CA | 2004 |
4 | 38.559167 | -119.933056 | 0.10 | CA | 2004 |
# See all of the columns in our DB
cursor = conn.execute('PRAGMA table_info(fires)')
desc = cursor.fetchall()
# getting names using list comprehension
names = [fields[1] for fields in desc]
conn.close()
print(names)
['OBJECTID', 'FOD_ID', 'FPA_ID', 'SOURCE_SYSTEM_TYPE', 'SOURCE_SYSTEM', 'NWCG_REPORTING_AGENCY', 'NWCG_REPORTING_UNIT_ID', 'NWCG_REPORTING_UNIT_NAME', 'SOURCE_REPORTING_UNIT', 'SOURCE_REPORTING_UNIT_NAME', 'LOCAL_FIRE_REPORT_ID', 'LOCAL_INCIDENT_ID', 'FIRE_CODE', 'FIRE_NAME', 'ICS_209_INCIDENT_NUMBER', 'ICS_209_NAME', 'MTBS_ID', 'MTBS_FIRE_NAME', 'COMPLEX_NAME', 'FIRE_YEAR', 'DISCOVERY_DATE', 'DISCOVERY_DOY', 'DISCOVERY_TIME', 'STAT_CAUSE_CODE', 'STAT_CAUSE_DESCR', 'CONT_DATE', 'CONT_DOY', 'CONT_TIME', 'FIRE_SIZE', 'FIRE_SIZE_CLASS', 'LATITUDE', 'LONGITUDE', 'OWNER_CODE', 'OWNER_DESCR', 'STATE', 'COUNTY', 'FIPS_CODE', 'FIPS_NAME', 'Shape']
df.info()
<class 'pandas.core.frame.DataFrame'> Int64Index: 1835646 entries, 0 to 1880464 Data columns (total 5 columns): # Column Dtype --- ------ ----- 0 LATITUDE float64 1 LONGITUDE float64 2 FIRE_SIZE float64 3 STATE object 4 FIRE_YEAR int64 dtypes: float64(3), int64(1), object(1) memory usage: 84.0+ MB
dfCopy = df.copy()
dfCopy.loc[:,'LATITUDE'] = ((dfCopy.loc[:, 'LATITUDE']*10).apply(np.floor))/10
dfCopy.loc[:,'LONGITUDE'] = ((dfCopy.loc[:, 'LONGITUDE']*10).apply(np.floor))/10
dfCopy.loc[:,'LatLonRange'] = dfCopy.loc[:,'LATITUDE'].map(str) + '-' + dfCopy.loc[:, 'LONGITUDE'].map(str)
df_grouped = dfCopy.groupby(['LatLonRange', 'LATITUDE', 'LONGITUDE'])
df_grouped.size()
LatLonRange LATITUDE LONGITUDE 24.5--81.7 24.5 -81.7 1 24.6--81.3 24.6 -81.3 2 24.6--81.4 24.6 -81.4 184 24.6--81.5 24.6 -81.5 43 24.6--81.6 24.6 -81.6 11 ... 48.9--99.9 48.9 -99.9 47 49.0--120.6 49.0 -120.6 1 49.0--122.1 49.0 -122.1 1 49.3--94.9 49.3 -94.9 1 49.3--95.1 49.3 -95.1 1 Length: 64250, dtype: int64
# Find number of fires that occured in each group
fire_count = df_grouped['FIRE_SIZE'].agg(['count']).reset_index()
fire_count.head()
LatLonRange | LATITUDE | LONGITUDE | count | |
---|---|---|---|---|
0 | 24.5--81.7 | 24.5 | -81.7 | 1 |
1 | 24.6--81.3 | 24.6 | -81.3 | 2 |
2 | 24.6--81.4 | 24.6 | -81.4 | 184 |
3 | 24.6--81.5 | 24.6 | -81.5 | 43 |
4 | 24.6--81.6 | 24.6 | -81.6 | 11 |
fire_avgSize = df_grouped['FIRE_SIZE'].agg(['mean']).reset_index()
fire_avgSize.head()
LatLonRange | LATITUDE | LONGITUDE | mean | |
---|---|---|---|---|
0 | 24.5--81.7 | 24.5 | -81.7 | 0.500000 |
1 | 24.6--81.3 | 24.6 | -81.3 | 0.100000 |
2 | 24.6--81.4 | 24.6 | -81.4 | 0.501630 |
3 | 24.6--81.5 | 24.6 | -81.5 | 0.646512 |
4 | 24.6--81.6 | 24.6 | -81.6 | 0.954545 |
source = ColumnDataSource(fire_count)
p1 = figure(title="Mapping the Occurrence of Wildfires from 1992 to 2015 " + \
"(lighter color means more wildfires)",
toolbar_location=None, plot_width=600, plot_height=400)
p1.background_fill_color = "black"
p1.grid.grid_line_color = None
p1.axis.visible = False
color_mapper = LogColorMapper(palette=cc.fire)
glyph = p1.circle('LONGITUDE', 'LATITUDE', source=source,
color={'field': 'count', 'transform' : color_mapper},
size=1)
output_notebook()
show(p1)
source = ColumnDataSource(fire_avgSize)
p2 = figure(title="Average Wildfire Size from 1992 to 2015 " + \
"(lighter color means larger fire)",
toolbar_location=None, plot_width=600, plot_height=400)
p2.background_fill_color = "black"
p2.grid.grid_line_color = None
p2.axis.visible = False
color_mapper = LogColorMapper(palette=cc.fire)
glyph = p2.circle('LONGITUDE', 'LATITUDE', source=source,
color={'field': 'mean', 'transform' : color_mapper},
size=1)
output_notebook()
show(p2)
df_folium = pd.DataFrame({'Lat':fire_count['LATITUDE'],'Long':fire_count['LONGITUDE'],'Count':fire_count['count']})
df_folium['weight'] = df_folium['Count'] / df_folium['Count'].abs().max()
def generateBaseMap(loc, zoom=4, tiles='OpenStreetMap', crs='ESPG2263'):
return folium.Map(location=loc,
control_scale=True,
zoom_start=zoom,
tiles=tiles)
base_map = generateBaseMap([39, -98] )
map_values1 = df_folium[['Lat','Long','weight']]
data = map_values1.values.tolist()
hm = HeatMap(data,gradient={0.1: 'blue', 0.3: 'lime', 0.5: 'yellow', 0.7: 'orange', 1: 'red'},
min_opacity=0.05,
max_opacity=0.9,
radius=25,
use_local_extrema=False)#.add_to(base_map)
base_map.add_child(hm)
df.loc[:, 'x'], df.loc[:, 'y'] = lnglat_to_meters(df.LONGITUDE, df.LATITUDE)
# plot 5 million rows and overlay with a map
map_tiles = EsriImagery().opts(alpha=0.5, width=700, height=480, bgcolor='black')
plot = df.hvplot(
'x',
'y',
kind='scatter',
rasterize=True,
cmap=cc.fire,
cnorm='eq_hist',
colorbar=True).opts(colorbar_position='bottom')
map_tiles * plot