def get_correlations_ts():
"""
Flask route which returns timeseries of Pearson correlations of two columns with numeric data
using :meth:`pandas:pandas.DataFrame.corr`
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:param cols: comma-separated string from flask.request.args['cols'] containing names of two columns in dataframe
:param dateCol: string from flask.request.args['dateCol'] with name of date-type column in dateframe for timeseries
:returns: JSON {
data: {:col1:col2: {data: [{corr: 0.99, date: 'YYYY-MM-DD'},...], max: 0.99, min: 0.99}
} or {error: 'Exception message', traceback: 'Exception stacktrace'}
"""
try:
query = get_str_arg(request, 'query')
data = DATA[get_port()]
data = data.query(query) if query is not None else data
cols = get_str_arg(request, 'cols')
cols = cols.split(',')
date_col = get_str_arg(request, 'dateCol')
data = data.groupby(date_col)[list(set(cols))].corr(method='pearson')
data.index.names = ['date', 'column']
data = data.reset_index()
data = data[data.column == cols[0]][['date', cols[1]]]
data.columns = ['date', 'corr']
data = {
k: v
for k, v in _build_timeseries_chart_data('corr', data, ['corr'])
}
return jsonify(dict(data=data))
except BaseException as e:
return jsonify(
dict(error=str(e), traceback=str(traceback.format_exc())))
def describe(column):
"""
Flask route which returns standard details about column data using :meth:`pandas:pandas.DataFrame.describe` to
the front-end as JSON
:param column: required dash separated string "START-END" stating a range of row indexes to be returned
to the screen
:return: JSON {
describe: object representing output from :meth:`pandas:pandas.Series.describe`,
unique_data: array of unique values when data has <= 100 unique values
success: True/False
}
"""
try:
data = DATA[get_port()]
desc = load_describe(data[column])
return_data = dict(describe=desc, success=True)
uniq_vals = data[column].unique()
if 'unique' not in return_data['describe']:
return_data['describe']['unique'] = json_int(len(uniq_vals),
as_string=True)
if len(uniq_vals) <= 100:
uniq_f = find_dtype_formatter(get_dtypes(data)[column])
return_data['uniques'] = [
uniq_f(u, nan_display='N/A') for u in uniq_vals
]
return jsonify(return_data)
except BaseException as e:
return jsonify(
dict(error=str(e), traceback=str(traceback.format_exc())))
def get_histogram(data_id):
"""
:class:`flask:flask.Flask` route which returns output from numpy.histogram to front-end as JSON
:param data_id: integer string identifier for a D-Tale process's data
:type data_id: str
:param col: string from flask.request.args['col'] containing name of a column in your dataframe
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:param bins: the number of bins to display in your histogram, options on the front-end are 5, 10, 20, 50
:returns: JSON {results: DATA, desc: output from pd.DataFrame[col].describe(), success: True/False}
"""
col = get_str_arg(request, 'col', 'values')
query = get_str_arg(request, 'query')
bins = get_int_arg(request, 'bins', 20)
try:
data = DATA[data_id]
if query:
data = data.query(query)
selected_col = find_selected_column(data, col)
data = data[~pd.isnull(data[selected_col])][[selected_col]]
hist = np.histogram(data, bins=bins)
desc = load_describe(data[selected_col])
return jsonify(data=[json_float(h) for h in hist[0]], labels=['{0:.1f}'.format(l) for l in hist[1]], desc=desc)
except BaseException as e:
return jsonify(dict(error=str(e), traceback=str(traceback.format_exc())))
def get_correlations(data_id):
"""
:class:`flask:flask.Flask` route which gathers Pearson correlations against all combinations of columns with
numeric data using :meth:`pandas:pandas.DataFrame.corr`
On large datasets with no :attr:`numpy:numpy.nan` data this code will use :meth:`numpy:numpy.corrcoef`
for speed purposes
:param data_id: integer string identifier for a D-Tale process's data
:type data_id: str
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:returns: JSON {
data: [{column: col1, col1: 1.0, col2: 0.99, colN: 0.45},...,{column: colN, col1: 0.34, col2: 0.88, colN: 1.0}],
} or {error: 'Exception message', traceback: 'Exception stacktrace'}
"""
try:
query = get_str_arg(request, 'query')
data = DATA[data_id]
data = data.query(query) if query is not None else data
valid_corr_cols = []
valid_date_cols = []
rolling = False
for col_info in DTYPES[data_id]:
name, dtype = map(col_info.get, ['name', 'dtype'])
dtype = classify_type(dtype)
if dtype in ['I', 'F']:
valid_corr_cols.append(name)
elif dtype == 'D':
# even if a datetime column exists, we need to make sure that there is enough data for a date
# to warrant a correlation, https://github.com/man-group/dtale/issues/43
date_counts = data[name].dropna().value_counts()
if len(date_counts[date_counts > 1]) > 1:
valid_date_cols.append(name)
elif date_counts.eq(1).all():
valid_date_cols.append(name)
rolling = True
if data[valid_corr_cols].isnull().values.any():
data = data.corr(method='pearson')
else:
# using pandas.corr proved to be quite slow on large datasets so I moved to numpy:
# https://stackoverflow.com/questions/48270953/pandas-corr-and-corrwith-very-slow
data = np.corrcoef(data[valid_corr_cols].values, rowvar=False)
data = pd.DataFrame(data,
columns=valid_corr_cols,
index=valid_corr_cols)
data.index.name = str('column')
data = data.reset_index()
col_types = grid_columns(data)
f = grid_formatter(col_types, nan_display=None)
return jsonify(data=f.format_dicts(data.itertuples()),
dates=valid_date_cols,
rolling=rolling)
except BaseException as e:
return jsonify(
dict(error=str(e), traceback=str(traceback.format_exc())))
def get_scatter():
"""
Flask route which returns data used in correlation of two columns for scatter chart
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:param cols: comma-separated string from flask.request.args['cols'] containing names of two columns in dataframe
:param dateCol: string from flask.request.args['dateCol'] with name of date-type column in dateframe for timeseries
:param date: string from flask.request.args['date'] date value in dateCol to filter dataframe to
:returns: JSON {
data: [{col1: 0.123, col2: 0.123, index: 1},...,{col1: 0.123, col2: 0.123, index: N}],
stats: {
correlated: 50,
only_in_s0: 1,
only_in_s1: 2,
pearson: 0.987,
spearman: 0.879,
}
x: col1,
y: col2
} or {error: 'Exception message', traceback: 'Exception stacktrace'}
"""
cols = get_str_arg(request, 'cols')
cols = cols.split(',')
query = get_str_arg(request, 'query')
date = get_str_arg(request, 'date')
date_col = get_str_arg(request, 'dateCol')
try:
data = DATA[get_port()]
data = data[data[date_col] == date] if date else data
if query:
data = data.query(query)
data = data[list(set(cols))].dropna(how='any')
data[str('index')] = data.index
s0 = data[cols[0]]
s1 = data[cols[1]]
pearson = s0.corr(s1, method='pearson')
spearman = s0.corr(s1, method='spearman')
stats = dict(pearson='N/A' if pd.isnull(pearson) else pearson,
spearman='N/A' if pd.isnull(spearman) else spearman,
correlated=len(data),
only_in_s0=len(data[data[cols[0]].isnull()]),
only_in_s1=len(data[data[cols[1]].isnull()]))
if len(data) > 15000:
return jsonify(
stats=stats,
error=
'Dataset exceeds 15,000 records, cannot render scatter. Please apply filter...'
)
f = grid_formatter(grid_columns(data))
data = f.format_dicts(data.itertuples())
return jsonify(data=data, x=cols[0], y=cols[1], stats=stats)
except BaseException as e:
return jsonify(
dict(error=str(e), traceback=str(traceback.format_exc())))
def get_chart_data(data_id):
"""
:class:`flask:flask.Flask` route which builds data associated with a chart.js chart
:param data_id: integer string identifier for a D-Tale process's data
:type data_id: str
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:param x: string from flask.request.args['x'] column to be used as x-axis of chart
:param y: string from flask.request.args['y'] column to be used as y-axis of chart
:param group: string from flask.request.args['group'] comma-separated string of columns to group chart data by
:param agg: string from flask.request.args['agg'] points to a specific function that can be applied to
:func: pandas.core.groupby.DataFrameGroupBy. Possible values are: count, first, last mean,
median, min, max, std, var, mad, prod, sum
:returns: JSON {
data: {
series1: { x: [x1, x2, ..., xN], y: [y1, y2, ..., yN] },
series2: { x: [x1, x2, ..., xN], y: [y1, y2, ..., yN] },
...,
seriesN: { x: [x1, x2, ..., xN], y: [y1, y2, ..., yN] },
},
min: minY,
max: maxY,
} or {error: 'Exception message', traceback: 'Exception stacktrace'}
"""
try:
query = get_str_arg(request, 'query')
data = DATA[data_id]
if query:
try:
data = data.query(query)
except BaseException as e:
return jsonify(dict(error='Invalid query: {}'.format(str(e))))
if not len(data):
return jsonify(
dict(error='query "{}" found no data, please alter'.format(
query)))
x = get_str_arg(request, 'x')
y = get_json_arg(request, 'y')
group_col = get_json_arg(request, 'group')
agg = get_str_arg(request, 'agg')
allow_duplicates = get_bool_arg(request, 'allowDupes')
window = get_int_arg(request, 'rollingWin')
comp = get_str_arg(request, 'rollingComp')
data = build_chart(data,
x,
y,
group_col,
agg,
allow_duplicates,
rolling_win=window,
rolling_comp=comp)
data['success'] = True
return jsonify(data)
except BaseException as e:
return jsonify(
dict(error=str(e), traceback=str(traceback.format_exc())))
def test_filter():
"""
Flask route which will test out pandas query before it gets applied to DATA and return exception information to the
screen if there is any
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:return: JSON {success: True/False}
"""
try:
query = get_str_arg(request, 'query')
_test_filter(DATA[get_port()], query)
return jsonify(dict(success=True))
except BaseException as e:
return jsonify(
dict(error=str(e), traceback=str(traceback.format_exc())))
def get_correlations():
"""
Flask route which gathers Pearson correlations against all combinations of columns with numeric data
using :meth:`pandas:pandas.DataFrame.corr`
On large datasets with no :attr:`numpy:numpy.nan` data this code will use :meth:`numpy:numpy.corrcoef`
for speed purposes
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:returns: JSON {
data: [{column: col1, col1: 1.0, col2: 0.99, colN: 0.45},...,{column: colN, col1: 0.34, col2: 0.88, colN: 1.0}],
} or {error: 'Exception message', traceback: 'Exception stacktrace'}
"""
try:
query = get_str_arg(request, 'query')
port = get_port()
data = DATA[port]
data = data.query(query) if query is not None else data
valid_corr_cols = []
valid_date_cols = []
for col_info in DTYPES[port]:
name, dtype = map(col_info.get, ['name', 'dtype'])
dtype = classify_type(dtype)
if dtype in ['I', 'F']:
valid_corr_cols.append(name)
elif dtype == 'D' and len(data[name].dropna().unique()) > 1:
valid_date_cols.append(name)
if data[valid_corr_cols].isnull().values.any():
data = data.corr(method='pearson')
else:
# using pandas.corr proved to be quite slow on large datasets so I moved to numpy:
# https://stackoverflow.com/questions/48270953/pandas-corr-and-corrwith-very-slow
data = np.corrcoef(data[valid_corr_cols].values, rowvar=False)
data = pd.DataFrame(data,
columns=valid_corr_cols,
index=valid_corr_cols)
data.index.name = str('column')
data = data.reset_index()
col_types = grid_columns(data)
f = grid_formatter(col_types, nan_display=None)
return jsonify(data=f.format_dicts(data.itertuples()),
dates=valid_date_cols)
except BaseException as e:
return jsonify(
dict(error=str(e), traceback=str(traceback.format_exc())))
def test_filter(data_id):
"""
:class:`flask:flask.Flask` route which will test out pandas query before it gets applied to DATA and return
exception information to the screen if there is any
:param data_id: integer string identifier for a D-Tale process's data
:type data_id: str
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:return: JSON {success: True/False}
"""
try:
query = get_str_arg(request, 'query')
_test_filter(DATA[data_id], query)
return jsonify(dict(success=True))
except BaseException as e:
return jsonify(dict(error=str(e), traceback=str(traceback.format_exc())))
def dtypes():
"""
Flask route which returns a list of column names and dtypes to the front-end as JSON
:return: JSON {
dtypes: [
{index: 1, name: col1, dtype: int64},
...,
{index: N, name: colN, dtype: float64}
],
success: True/False
}
"""
try:
return jsonify(dtypes=DTYPES[get_port()], success=True)
except BaseException as e:
return jsonify(error=str(e), traceback=str(traceback.format_exc()))
def describe(data_id, column):
"""
:class:`flask:flask.Flask` route which returns standard details about column data using
:meth:`pandas:pandas.DataFrame.describe` to the front-end as JSON
:param data_id: integer string identifier for a D-Tale process's data
:type data_id: str
:param column: required dash separated string "START-END" stating a range of row indexes to be returned
to the screen
:return: JSON {
describe: object representing output from :meth:`pandas:pandas.Series.describe`,
unique_data: array of unique values when data has <= 100 unique values
success: True/False
}
"""
try:
data = DATA[data_id][[column]]
additional_aggs = None
dtype = next((dtype_info['dtype'] for dtype_info in DTYPES[data_id]
if dtype_info['name'] == column), None)
if classify_type(dtype) in ['I', 'F']:
additional_aggs = [
'sum', 'median', 'mode', 'var', 'sem', 'skew', 'kurt'
]
desc = load_describe(data[column], additional_aggs=additional_aggs)
return_data = dict(describe=desc, success=True)
uniq_vals = data[column].unique()
if 'unique' not in return_data['describe']:
return_data['describe']['unique'] = json_int(len(uniq_vals),
as_string=True)
uniq_f = find_dtype_formatter(get_dtypes(data)[column])
if len(uniq_vals) <= 100:
return_data['uniques'] = dict(data=[uniq_f(u) for u in uniq_vals],
top=False)
else: # get top 100 most common values
uniq_vals = data[column].value_counts().sort_values(
ascending=False).head(100).index.values
return_data['uniques'] = dict(data=[uniq_f(u) for u in uniq_vals],
top=True)
return jsonify(return_data)
except BaseException as e:
return jsonify(
dict(error=str(e), traceback=str(traceback.format_exc())))
def update_settings(data_id):
"""
:class:`flask:flask.Flask` route which updates global SETTINGS for current port
:param data_id: integer string identifier for a D-Tale process's data
:type data_id: str
:param settings: JSON string from flask.request.args['settings'] which gets decoded and stored in SETTINGS variable
:return: JSON
"""
try:
global SETTINGS
curr_settings = SETTINGS.get(data_id, {})
updated_settings = dict_merge(curr_settings, get_json_arg(request, 'settings', {}))
SETTINGS[data_id] = updated_settings
return jsonify(dict(success=True))
except BaseException as e:
return jsonify(dict(error=str(e), traceback=str(traceback.format_exc())))
def get_correlations_ts(data_id):
"""
:class:`flask:flask.Flask` route which returns timeseries of Pearson correlations of two columns with numeric data
using :meth:`pandas:pandas.DataFrame.corr`
:param data_id: integer string identifier for a D-Tale process's data
:type data_id: str
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:param cols: comma-separated string from flask.request.args['cols'] containing names of two columns in dataframe
:param dateCol: string from flask.request.args['dateCol'] with name of date-type column in dateframe for timeseries
:returns: JSON {
data: {:col1:col2: {data: [{corr: 0.99, date: 'YYYY-MM-DD'},...], max: 0.99, min: 0.99}
} or {error: 'Exception message', traceback: 'Exception stacktrace'}
"""
try:
query = get_str_arg(request, 'query')
data = DATA[data_id]
data = data.query(query) if query is not None else data
cols = get_str_arg(request, 'cols')
cols = json.loads(cols)
date_col = get_str_arg(request, 'dateCol')
rolling_window = get_int_arg(request, 'rollingWindow')
if rolling_window:
[col1, col2] = list(set(cols))
data = data[[date_col, col1, col2]].set_index(date_col)
data = data[[col1,
col2]].rolling(rolling_window).corr().reset_index()
data = data.dropna()
data = data[data['level_1'] == col1][[date_col, col2]]
else:
data = data.groupby(date_col)[list(
set(cols))].corr(method='pearson')
data.index.names = ['date', 'column']
data = data.reset_index()
data = data[data.column == cols[0]][['date', cols[1]]]
data.columns = ['date', 'corr']
return_data = build_chart(data.fillna(0), 'date', 'corr')
return_data['success'] = True
return jsonify(return_data)
except BaseException as e:
return jsonify(
dict(error=str(e), traceback=str(traceback.format_exc())))
def dtypes(data_id):
"""
:class:`flask:flask.Flask` route which returns a list of column names and dtypes to the front-end as JSON
:param data_id: integer string identifier for a D-Tale process's data
:type data_id: str
:return: JSON {
dtypes: [
{index: 1, name: col1, dtype: int64},
...,
{index: N, name: colN, dtype: float64}
],
success: True/False
}
"""
try:
return jsonify(dtypes=DTYPES[data_id], success=True)
except BaseException as e:
return jsonify(error=str(e), traceback=str(traceback.format_exc()))
def update_settings():
"""
Flask route which updates global SETTINGS for current port
:param port: number string from flask.request.environ['SERVER_PORT']
:param settings: JSON string from flask.request.args['settings'] which gets decoded and stored in SETTINGS variable
:return: JSON
"""
try:
global SETTINGS
port = get_port()
curr_settings = SETTINGS.get(port, {})
updated_settings = dict_merge(
curr_settings, json.loads(get_str_arg(request, 'settings', '{}')))
SETTINGS[port] = updated_settings
return jsonify(dict(success=True))
except BaseException as e:
return jsonify(
dict(error=str(e), traceback=str(traceback.format_exc())))
def get_correlations():
"""
Flask route which gathers Pearson correlations against all combinations of columns with numeric data
using pandas.DataFrame.corr
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:returns: JSON {
data: [{column: col1, col1: 1.0, col2: 0.99, colN: 0.45},...,{column: colN, col1: 0.34, col2: 0.88, colN: 1.0}],
} or {error: 'Exception message', traceback: 'Exception stacktrace'}
"""
try:
query = get_str_arg(request, 'query')
data = DATA.query(query) if query is not None else DATA
data = data.corr(method='pearson')
data.index.name = 'column'
data = data.reset_index()
col_types = grid_columns(data)
f = grid_formatter(col_types, nan_display=None)
return jsonify(data=f.format_dicts(data.itertuples()))
except BaseException as e:
return jsonify(
dict(error=str(e), traceback=str(traceback.format_exc())))
def get_processes():
"""
:class:`flask:flask.Flask` route which returns list of running D-Tale processes within current python process
:return: JSON {
data: [
{
port: 1, name: 'name1', rows: 5, columns: 5, names: 'col1,...,col5', start: '2018-04-30 12:36:44',
ts: 1525106204000
},
...,
{
port: N, name: 'nameN', rows: 5, columns: 5, names: 'col1,...,col5', start: '2018-04-30 12:36:44',
ts: 1525106204000
}
],
success: True/False
}
"""
def _load_process(data_id):
data = DATA[data_id]
dtypes = DTYPES[data_id]
mdata = METADATA[data_id]
return dict(
data_id=data_id,
rows=len(data),
columns=len(dtypes),
names=','.join([c['name'] for c in dtypes]),
start=json_date(mdata['start']),
ts=json_timestamp(mdata['start']),
name=mdata['name']
)
try:
processes = sorted([_load_process(data_id) for data_id in DATA], key=lambda p: p['ts'])
return jsonify(dict(data=processes, success=True))
except BaseException as e:
return jsonify(dict(error=str(e), traceback=str(traceback.format_exc())))
def get_data(data_id):
"""
:class:`flask:flask.Flask` route which returns current rows from DATA (based on scrollbar specs and saved settings)
to front-end as JSON
:param data_id: integer string identifier for a D-Tale process's data
:type data_id: str
:param ids: required dash separated string "START-END" stating a range of row indexes to be returned to the screen
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:param sort: JSON string from flask.request.args['sort'] which is applied to DATA using the sort_values() or
sort_index() function. Here is the JSON structure: [col1,dir1],[col2,dir2],....[coln,dirn]
:return: JSON {
results: [
{dtale_index: 1, col1: val1_1, ...,colN: valN_1},
...,
{dtale_index: N2, col1: val1_N2, ...,colN: valN_N2}
],
columns: [{name: col1, dtype: 'int64'},...,{name: colN, dtype: 'datetime'}],
total: N2,
success: True/False
}
"""
try:
global SETTINGS, DATA, DTYPES
data = DATA[data_id]
# this will check for when someone instantiates D-Tale programatically and directly alters the internal
# state of the dataframe (EX: d.data['new_col'] = 'foo')
curr_dtypes = [c['name'] for c in DTYPES[data_id]]
if any(c not in curr_dtypes for c in data.columns):
data, _ = format_data(data)
DATA[data_id] = data
DTYPES[data_id] = build_dtypes_state(data)
params = retrieve_grid_params(request)
ids = get_json_arg(request, 'ids')
if ids is None:
return jsonify({})
col_types = DTYPES[data_id]
f = grid_formatter(col_types)
curr_settings = SETTINGS.get(data_id, {})
if curr_settings.get('sort') != params.get('sort'):
data = sort_df_for_grid(data, params)
DATA[data_id] = data
if params.get('sort') is not None:
curr_settings = dict_merge(curr_settings, dict(sort=params['sort']))
else:
curr_settings = {k: v for k, v in curr_settings.items() if k != 'sort'}
data = filter_df_for_grid(data, params)
if params.get('query') is not None:
curr_settings = dict_merge(curr_settings, dict(query=params['query']))
else:
curr_settings = {k: v for k, v in curr_settings.items() if k != 'query'}
SETTINGS[data_id] = curr_settings
total = len(data)
results = {}
for sub_range in ids:
sub_range = list(map(int, sub_range.split('-')))
if len(sub_range) == 1:
sub_df = data.iloc[sub_range[0]:sub_range[0] + 1]
sub_df = f.format_dicts(sub_df.itertuples())
results[sub_range[0]] = dict_merge({IDX_COL: sub_range[0]}, sub_df[0])
else:
[start, end] = sub_range
sub_df = data.iloc[start:] if end >= len(data) - 1 else data.iloc[start:end + 1]
sub_df = f.format_dicts(sub_df.itertuples())
for i, d in zip(range(start, end + 1), sub_df):
results[i] = dict_merge({IDX_COL: i}, d)
return_data = dict(results=results, columns=[dict(name=IDX_COL, dtype='int64')] + DTYPES[data_id], total=total)
return jsonify(return_data)
except BaseException as e:
return jsonify(dict(error=str(e), traceback=str(traceback.format_exc())))
def get_scatter(data_id):
"""
:class:`flask:flask.Flask` route which returns data used in correlation of two columns for scatter chart
:param data_id: integer string identifier for a D-Tale process's data
:type data_id: str
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:param cols: comma-separated string from flask.request.args['cols'] containing names of two columns in dataframe
:param dateCol: string from flask.request.args['dateCol'] with name of date-type column in dateframe for timeseries
:param date: string from flask.request.args['date'] date value in dateCol to filter dataframe to
:returns: JSON {
data: [{col1: 0.123, col2: 0.123, index: 1},...,{col1: 0.123, col2: 0.123, index: N}],
stats: {
stats: {
correlated: 50,
only_in_s0: 1,
only_in_s1: 2,
pearson: 0.987,
spearman: 0.879,
}
x: col1,
y: col2
} or {error: 'Exception message', traceback: 'Exception stacktrace'}
"""
cols = get_json_arg(request, 'cols')
query = get_str_arg(request, 'query')
date = get_str_arg(request, 'date')
date_col = get_str_arg(request, 'dateCol')
rolling = get_bool_arg(request, 'rolling')
try:
data = DATA[data_id]
if query:
data = data.query(query)
idx_col = str('index')
y_cols = [cols[1], idx_col]
if rolling:
window = get_int_arg(request, 'window')
idx = min(data[data[date_col] == date].index) + 1
data = data.iloc[max(idx - window, 0):idx]
data = data[list(set(cols)) + [date_col]].dropna(how='any')
y_cols.append(date_col)
else:
data = data[data[date_col] == date] if date else data
data = data[list(set(cols))].dropna(how='any')
data[idx_col] = data.index
s0 = data[cols[0]]
s1 = data[cols[1]]
pearson = s0.corr(s1, method='pearson')
spearman = s0.corr(s1, method='spearman')
stats = dict(
pearson='N/A' if pd.isnull(pearson) else pearson,
spearman='N/A' if pd.isnull(spearman) else spearman,
correlated=len(data),
only_in_s0=len(data[data[cols[0]].isnull()]),
only_in_s1=len(data[data[cols[1]].isnull()])
)
if len(data) > 15000:
return jsonify(
stats=stats,
error='Dataset exceeds 15,000 records, cannot render scatter. Please apply filter...'
)
data = build_chart(data, cols[0], y_cols, allow_duplicates=True)
data['x'] = cols[0]
data['y'] = cols[1]
data['stats'] = stats
return jsonify(data)
except BaseException as e:
return jsonify(dict(error=str(e), traceback=str(traceback.format_exc())))
def find_coverage():
"""
Flask route which returns coverage information(counts) for a column grouped by other column(s)
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:param col: string from flask.request.args['col'] containing name of a column in your dataframe
:param filters(deprecated): JSON string from flaks.request.args['filters'] with filtering information from group
drilldown [
{name: col1, prevFreq: Y, freq: Q, date: YYYY-MM-DD},
...
{name: col1, prevFreq: D, freq: W, date: YYYY-MM-DD},
]
:param group: JSON string from flask.request.args['group'] containing grouping logic in this structure [
{name: col1} or {name: date_col1, freq: [D,W,M,Q,Y]}
]
:returns: JSON {
data: {
[col]: [count1,count2,...,countN],
labels: [{group_col1: gc1_v1, group_col2: gc2_v1},...,{group_col1: gc1_vN, group_col2: gc2_vN}],
success: True
} or {error: 'Exception message', traceback: 'Exception stacktrace', success: False}
"""
def filter_data(df, req, groups, query=None):
filters = get_str_arg(req, 'filters')
if not filters:
return df.query(query or 'index == index'), groups, ''
filters = json.loads(filters)
col, prev_freq, freq, end = map(filters[-1].get,
['name', 'prevFreq', 'freq', 'date'])
start = DATE_RANGES[prev_freq](pd.Timestamp(end)).strftime('%Y%m%d')
range_query = "{col} >= '{start}' and {col} <= '{end}'".format(
col=col, start=start, end=end)
logger.info('filtered coverage data to slice: {}'.format(range_query))
updated_groups = [
dict(name=col, freq=freq) if g['name'] == col else g
for g in groups
]
return df.query(
query or
'index == index').query(range_query), updated_groups, range_query
try:
col = get_str_arg(request, 'col')
groups = get_str_arg(request, 'group')
if groups:
groups = json.loads(groups)
data = DATA[get_port()]
data, groups, query = filter_data(data,
request,
groups,
query=get_str_arg(request, 'query'))
grouper = []
for g_cfg in groups:
if 'freq' in g_cfg:
freq_grp = data.set_index([g_cfg['name']]).index.to_period(
g_cfg['freq']).to_timestamp(how='end')
freq_grp.name = g_cfg['name']
grouper.append(freq_grp)
else:
grouper.append(data[g_cfg['name']])
data_groups = data.groupby(grouper)
group_data = data_groups[col].count()
if len(groups) > 1:
unstack_order = enumerate(
zip(group_data.index.names, group_data.index.levels))
unstack_order = sorted([(uo[0], uo[1][0], len(uo[1][1]))
for uo in unstack_order],
key=lambda k: k[2])
for i, n, l in unstack_order[:-1]:
group_data = group_data.unstack(i)
group_data = group_data.fillna(0)
if len(unstack_order[:-1]) > 1:
group_data.columns = [
', '.join([
str(group_data.columns.levels[c2[0]][c2[1]])
for c2 in enumerate(c)
]) for c in zip(*group_data.columns.labels)
]
else:
group_data.columns = map(str, group_data.columns.values)
if len(group_data) > 15000:
return jsonify(
dict(error=(
'Your grouping created {} groups, chart will not render. '
'Try making date columns a higher frequency (W, M, Q, Y)'
).format(len(data_groups))))
if len(groups) == 1:
data = {col: [json_int(v) for v in group_data.values]}
else:
data = dict([(c, [json_int(v) for v in group_data[c].values])
for c in group_data.columns])
labels = pd.DataFrame(group_data.index.values,
columns=group_data.index.names)
labels_f_overrides = {
'D': lambda f, i, c: f.add_date(i, c, fmt='%Y-%m-%d'),
}
labels_f = grid_formatter(grid_columns(labels),
overrides=labels_f_overrides)
labels = labels_f.format_dicts(labels.itertuples())
return jsonify(data=data, labels=labels, success=True)
except BaseException as e:
return jsonify(
dict(error=str(e), traceback=str(traceback.format_exc())))
def get_data():
"""
Flask route which returns current rows from DATA (based on scrollbar specs and saved settings) to front-end as
JSON
:param ids: required dash separated string "START-END" stating a range of row indexes to be returned to the screen
:param query: string from flask.request.args['query'] which is applied to DATA using the query() function
:param sort: JSON string from flask.request.args['sort'] which is applied to DATA using the sort_values() or
sort_index() function. Here is the JSON structure: [col1,dir1],[col2,dir2],....[coln,dirn]
:param port: number string from flask.request.environ['SERVER_PORT'] for retrieving saved settings
:return: JSON {
results: [
{dtale_index: 1, col1: val1_1, ...,colN: valN_1},
...,
{dtale_index: N2, col1: val1_N2, ...,colN: valN_N2}.
],
columns: [{name: col1, dtype: 'int64'},...,{name: colN, dtype: 'datetime'}],
total: N2,
success: True/False
}
"""
try:
global SETTINGS, DATA
params = retrieve_grid_params(request)
ids = get_str_arg(request, 'ids')
if ids:
ids = json.loads(ids)
else:
return jsonify({})
col_types = grid_columns(DATA)
f = grid_formatter(col_types)
curr_settings = SETTINGS.get(
request.environ.get('SERVER_PORT', 'curr'), {})
if curr_settings.get('sort') != params.get('sort'):
DATA = sort_df_for_grid(DATA, params)
df = DATA
if params.get('sort') is not None:
curr_settings = dict_merge(curr_settings,
dict(sort=params['sort']))
else:
curr_settings = {
k: v
for k, v in curr_settings.items() if k != 'sort'
}
df = filter_df_for_grid(df, params)
if params.get('query') is not None:
curr_settings = dict_merge(curr_settings,
dict(query=params['query']))
else:
curr_settings = {
k: v
for k, v in curr_settings.items() if k != 'query'
}
SETTINGS[request.environ.get('SERVER_PORT', 'curr')] = curr_settings
total = len(df)
results = {}
for sub_range in ids:
sub_range = list(map(int, sub_range.split('-')))
if len(sub_range) == 1:
sub_df = df.iloc[sub_range[0]:sub_range[0] + 1]
sub_df = f.format_dicts(sub_df.itertuples())
results[sub_range[0]] = dict_merge(
dict(dtale_index=sub_range[0]), sub_df[0])
else:
[start, end] = sub_range
sub_df = df.iloc[start:] if end >= len(
df) - 1 else df.iloc[start:end + 1]
sub_df = f.format_dicts(sub_df.itertuples())
for i, d in zip(range(start, end + 1), sub_df):
results[i] = dict_merge(dict(dtale_index=i), d)
return_data = dict(results=results,
columns=[dict(name='dtale_index', dtype='int64')] +
col_types,
total=total)
return jsonify(return_data)
except BaseException as e:
return jsonify(
dict(error=str(e), traceback=str(traceback.format_exc())))
