def get_grouped_how_participate():
df = pd.DataFrame(get_feedback_table_data())
df = df[df['Age'] != 'Undisclosed']
df = df[df['Gender'] != 'Undisclosed']
df = df[df['Participate Label'].astype(str).str.isnumeric()]
df = df.groupby(['Gender', 'Age', 'Participate Label']).size()
df = df.reset_index().rename(columns = {0: 'Count'})
df = df.astype({'Gender': 'object', 'Age': 'object', 'Participate Label': 'int64', 'Count': 'int64'})
female_df = df[df['Gender'] == 'Female']
male_df = df[df['Gender'] == 'Male']
Age = list(df.Age.unique())
Cluster = list(df['Participate Label'].unique())
all_gender_df = pd.DataFrame(list(product(Age, Cluster)), columns = ['Age', 'Participate Label'])
male_df = all_gender_df.merge(male_df, how = 'left', on = ['Age', 'Participate Label'])
male_df = male_df[['Age', 'Participate Label', 'Count']]
male_df = male_df.fillna(0)
male_df.sort_values(by = 'Age', inplace = True)
female_df = all_gender_df.merge(female_df, how='left', on=['Age', 'Participate Label'])
female_df = female_df[['Age', 'Participate Label', 'Count']]
female_df = female_df.fillna(0)
female_df.sort_values(by = 'Age', inplace = True)
return (male_df.copy(), female_df.copy())
def download_excel():
# creating the dataframe
df = pd.DataFrame(get_feedback_table_data())
# convert to excel
buf = io.BytesIO()
excel_writer = pd.ExcelWriter(buf, engine = "xlsxwriter")
df.to_excel(excel_writer, sheet_name = "sheet1", index = False)
excel_writer.save()
buf.seek(0)
return send_file(
buf,
mimetype = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
attachment_filename = "kelowna_community_culture_feedback.xlsx",
as_attachment = True,
cache_timeout = 0
)
def feedback_facility_map_callback(n_clicks):
df = pd.DataFrame(get_feedback_table_data())
df = df[df['Facility Sentiment'].apply(
lambda x: isinstance(x, (float, int)))]
# colourscale
scl = [[0.0, '#E12A0A'], [0.1, '#E12A0A'], [0.2, '#E09006'],
[0.3, '#E09006'], [0.4, '#E0D806'], [0.5, '#E0D806'],
[0.6, '#89F972'], [0.7, '#89F972'], [0.8, '#27BC14'],
[0.9, '#27BC14'], [1.0, '#27BC14']]
tick_vals = [
-0.96, -0.75, -0.60, -0.45, -0.3, -0.15, 0, 0.15, 0.30, 0.45, 0.60,
0.75, 0.96
]
tick_labels = [
"<b>Negative</b>", "-0.75", "-0.60", "-0.45", "-0.30", "-0.15", "0",
"0.15", "0.30", "0.45", "0.60", "0.75", "<b>Positive</b>"
]
data = [
go.Scattermapbox(
lat = df['Latitude'],
lon = df['Longitude'],
mode = 'markers',
marker = dict(
size = 12,
opacity = 1,
colorscale = scl,
autocolorscale = False,
color = df['Facility Sentiment'],
colorbar = dict(title = 'Satisfaction',
tickmode = "array",
tickvals = tick_vals,
ticktext = tick_labels),
cmin = -1.0,
cmax = 1.0
),
hoverinfo = 'text',
text = 'Sentiment: ' + df['Facility Sentiment'].apply(get_sentiment) + '<br>Score: ' + df['Facility Sentiment'].apply(lambda x: round(x, 3)).astype(str) + \
'<br>Postal Code: ' + df['Postal Code'] + '<br>Gender: ' + df['Gender'] + '<br>Age Group: ' + df['Age'] + '<br><br>' + df['Status of Facilities'].apply(format_hover_text),
showlegend = False
)
]
layout = go.Layout(
autosize=True,
hovermode='closest',
title=go.layout.Title(
text='How satisfied is the community with cultural facilities?'),
mapbox=go.layout.Mapbox(accesstoken=mapbox_access_token,
center=go.layout.mapbox.Center(
lat=49.8866,
lon=-119.4788,
),
style='light',
pitch=0,
zoom=10,
bearing=0),
margin=dict(t=40, l=0, r=0, b=0))
return go.Figure(data=data, layout=layout)
def feedback_table_rows_callback(activated_tab, n_clicks, existing_state):
if activated_tab == 'tab-4':
return get_feedback_table_data()
else:
return existing_state