-
Notifications
You must be signed in to change notification settings - Fork 0
/
routes.py
348 lines (292 loc) · 11 KB
/
routes.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
import base64
import io
from datetime import datetime
from textwrap import fill
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
import pandas as pd
from flask import abort, redirect, render_template, url_for
from matplotlib.backends.backend_agg import FigureCanvasAgg
from src import database
def get_available_state_codes():
"""
Get states' codes and names from DB and return them as a dataframe
"""
with database.db.engine.connect() as connection:
query = '''
SELECT DISTINCT(state_code) state_code,
state
FROM state_contributions
WHERE state_code IN ('AC', 'AL', 'AP', 'AM', 'BA', 'CE', 'DF', 'ES',
'GO', 'MA', 'MT', 'MS', 'MG', 'PR', 'PB', 'PA',
'PE', 'PI', 'RN', 'RS', 'RJ', 'RO', 'RR', 'SC',
'SE', 'SP', 'TO')
ORDER BY state
'''
return pd.read_sql(query, connection, index_col='state_code')
def get_available_years():
"""
Get the years which are present in the DB and return them as a list of
integers.
"""
with database.db.engine.connect() as connection:
query = '''
SELECT DISTINCT(year)
FROM top_by_state_and_year
ORDER BY year
'''
return pd.read_sql(query, connection).year.astype(int).values.tolist()
def get_month_name(month):
"""
Get the month name in Portuguese.
Parameters:
month: (int): The month number from 1 to 12
Returns:
name: (str): The name of the month in Portuguese, or
'todos os meses' (all months) if month is not in the range from 1 to 12
"""
names = {
1: 'janeiro', 2: 'fevereiro', 3: 'março', 4: 'abril',
5: 'maio', 6: 'junho', 7: 'julho', 8: 'agosto',
9: 'setembro', 10: 'outubro', 11: 'novembro', 12: 'dezembro'
}
if month in names:
return names[month]
return 'todos os meses'
def get_month_options(year):
"""
Get the month options for the given year.
For the previous year, there is data available for each individual month.
However, for previous years only the aggregate statistics is available.
Parameters:
year: (int): The year for which the months will be listed.
"""
previous_year = datetime.now().year - 1
if year == previous_year:
return list(range(1, 13))
return []
def get_top_products(kind, state, year, month, count=3, index=None):
"""
Get products with the largest FOB values in USD and return them as a
dataframe.
Parameters:
kind: (str): The kind of trade. One of 'import' or 'export'.
state: (str): The UF code of the state of origin/destiny the trade
year: (str): The year of the trade
count: (int): How many products to get
"""
if month:
table = 'top_by_state_and_month'
period_filter = f't.year=\'{year}\' AND t.month=\'{month}\''
else:
table = 'top_by_state_and_year'
period_filter = f't.year=\'{year}\''
query = f'''
SELECT t.product, t.state, t.total
FROM {table} t
WHERE {period_filter}
AND t.state_code='{state}'
AND t.kind='{kind}'
ORDER BY t.total
LIMIT {count}
'''
with database.db.engine.connect() as connection:
return pd.read_sql(query, connection, index_col=index)\
.sort_values('total')
def get_top_contributions(kind, year, limit=3, index=None):
"""
Get states with largest contributions to the yearly total and return them
as a dataframe.
Parameters:
kind: (str): The kind of trade. One of 'import' or 'export'.
year: (str): The year of the trades
limit: (int): Limit the results to this many states.
"""
limit = f'LIMIT {limit}' if limit is not None else ''
query = f'''
SELECT s.state_code, s.state, s.total, s.percentage
FROM state_contributions s
WHERE year = '{year}'
AND kind = '{kind}'
ORDER BY s.percentage DESC
{limit}
'''
with database.db.engine.connect() as connection:
return pd.read_sql(query, connection, index_col=index)\
.sort_values('total')
def large_num_formatter(num, pos=None):
"""
Format large numbers using appropriate sufixes for powers of 1000
Parameters:
num: (int): The tick value to be formatted
pos: (int): Position of the ticker
"""
for unit in ['', 'mil', 'Mi.', 'Bi.']:
if abs(num) < 1000.0:
return "%3.1f %s" % (num, unit)
num /= 1000.0
return "%.1f %s" % (num, 'Tri.')
def get_data_url(fig):
'''
Generate a data url using the base 64 encoding of the figure
'''
png_image = io.BytesIO()
canvas = FigureCanvasAgg(fig)
canvas.print_png(png_image)
png_image_in_base_64 = 'data:image/png;base64,{}'.format(
base64.b64encode(png_image.getvalue()).decode('utf8')
)
return png_image_in_base_64
def get_contribution_plot(df, state, title=None):
"""
Make a pie chart of the percentage of contribution of each state
Parameters:
df: (pandas.core.frame.DataFrame): Dataframe to be plotted
state: (str): The code of the state
"""
threshold = 3
def pct_format(percent, skip_small_values=True):
if percent <= threshold and skip_small_values:
return ''
return '%1.1f%%' % percent
# Highlight the current state
explode = [0.03 if code != state else 0.2 for code in df.state_code]
colors = ['#CCC' if code != state else '#66F'
for code in df.state_code]
# Hide the percentages of small contributions, unless it is for the
# current state. In that case, show it as part of the label, since it would
# not fit inside its slice.
labels = [name if df['percentage'][name] > threshold else
name + ' ({})'.format(
pct_format(df['percentage'][name],
skip_small_values=False)
) if df['state_code'][name] == state
else '' for name in df.index]
fig, ax = plt.subplots()
ax.pie(df.total, colors=colors, autopct=pct_format, startangle=90,
explode=explode, labels=labels)
if title:
ax.set_title(title, pad=20)
plt.tight_layout()
return get_data_url(fig)
def get_plot(df, ylabel='Produto', title=None):
"""
Make a horizontal bar plot of the dataframe.
Parameters:
df: (pandas.core.frame.DataFrame): Dataframe to be plotted
title: (str): Text to be used as title for the plot
"""
fig, ax = plt.subplots(figsize=(10, len(df)*0.5 + 1.5))
df.plot(kind='barh', ax=ax, legend=False)
for container in ax.containers:
plt.setp(container, height=0.5)
for i, v in enumerate(df['total']):
ax.text(v, i, str(large_num_formatter(v)), color='blue', va='center',
fontweight='bold')
if title:
ax.set_title(title)
ax.set_ylabel(ylabel)
ax.set_xlabel('Valor total anual (US$)')
ax.set_yticklabels([fill(p, 50) for p in df.index])
ax.xaxis.set_major_formatter(ticker.FuncFormatter(large_num_formatter))
plt.tight_layout()
return get_data_url(fig)
def index():
# Use a large state as default
large_state_code = 'SP'
# Use previous year as default
previous_year = datetime.now().year - 1
return redirect(url_for(
'dashboard',
state_code=large_state_code,
year=previous_year
))
def dashboard(state_code, year, month=None):
"""
Show statistics about imports and exports for the state and year provided
in the URL.
Parameters:
state_code: (str): The code of the state of origin/destiny the trade
(default the first state available).
year: (int): The year of the trade (default the first year available).
"""
available_state_codes = get_available_state_codes()
if state_code not in available_state_codes.index:
return abort(404)
state_name = available_state_codes['state'][state_code]
available_years = get_available_years()
if year not in available_years:
return abort(404)
month_options = get_month_options(year)
if month_options and month in month_options:
month_name = get_month_name(month)
else:
month = None
month_name = 'todos os meses'
if month is None:
group = f'({year})'
img_top_importers = get_plot(
get_top_contributions('import', year, index='state'),
ylabel='Estado',
title=f'Maiores importadores {group}'
)
img_top_exporters = get_plot(
get_top_contributions('export', year, index='state'),
ylabel='Estado',
title=f'Maiores exportadores {group}'
)
img_contribution_to_imports = get_contribution_plot(
get_top_contributions('import', year, limit=None, index='state'),
state=state_code,
title='Representatividade das importações do estado no'
+ ' ano\nem relação ao total de importações do país'
)
img_contribution_to_exports = get_contribution_plot(
get_top_contributions('export', year, limit=None, index='state'),
state=state_code,
title='Representatividade das exportações do estado no'
+ ' ano\nem relação ao total de exportações do país'
)
else:
img_top_importers = None
img_top_exporters = None
img_contribution_to_imports = None
img_contribution_to_exports = None
group = f'({state_name}, {month_name} de {year})'
img_top_imports = get_plot(
get_top_products('import', state_code, year, month, index='product'),
ylabel='Produto',
title=f'Produtos mais importados {group}'
)
img_top_exports = get_plot(
get_top_products('export', state_code, year, month, index='product'),
ylabel='Produto',
title=f'Produtos mais exportados {group}'
)
return render_template(
'dashboard.html',
month_options=[None] + month_options,
month=month,
get_month_name=get_month_name,
available_years=available_years,
year=year,
available_state_codes=available_state_codes,
state_code=state_code,
img_top_imports=img_top_imports,
img_top_exports=img_top_exports,
img_top_importers=img_top_importers,
img_top_exporters=img_top_exporters,
img_contribution_to_imports=img_contribution_to_imports,
img_contribution_to_exports=img_contribution_to_exports
)
def page_not_found(e):
return render_template('404.html'), 404
def init_app(app):
app.add_url_rule('/', view_func=index)
app.add_url_rule('/dashboard', view_func=dashboard)
app.add_url_rule('/dashboard/', view_func=index)
app.add_url_rule('/dashboard/<string:state_code>/<int:year>',
view_func=dashboard)
app.add_url_rule('/dashboard/<string:state_code>/<int:year>/<int:month>',
view_func=dashboard)
app.register_error_handler(404, page_not_found)