def get_user_dri(connection, nutrients_start_with, user_id):
"""
ARGS
connection a pyMysql connection object
user_id id of a user from the user profile table
RETURNS
a series, ordered by nutrient name A-Z, containing the nutrient breakdown of a user's DRI
"""
# Get user record
sql = "SELECT * FROM app.user_profile WHERE id = " + str(user_id)
user = qy.execute_query(connection=connection, sql=sql).fetchone()
# Determine age group
dob = user['dob']
today = date.today()
age = today.year - dob.year - (today.month < dob.month
and today.day < dob.day)
if age <= 3:
age_group = 1
elif age <= 8:
age_group = 2
elif age <= 13:
age_group = 3
elif age <= 18:
age_group = 4
elif age <= 30:
age_group = 5
elif age <= 50:
age_group = 6
elif age <= 70:
age_group = 7
else:
age_group = 8
# Get DRI record
sql = "SELECT * FROM app.dri WHERE sex = %s AND age_group = %s LIMIT 1"
cursor = qy.execute_query(connection=connection,
sql=sql,
insert_data=(user['sex'], age_group))
series_dri = pd.Series(cursor.fetchone())
required_cols = [
key for key in series_dri.keys()
if key.startswith(nutrients_start_with)
]
ordered_dri = series_dri[required_cols].sort_index()
return ordered_dri
def items(query=None, user=LOGGED_IN_USER, username=USER):
"""
Given an LQL query and a library identity, return the items that match.
"""
library = Library.objects.get()
items = Item.objects.all()
query_json = '[]'
if query:
from query import execute_query, parse_query
parsed = parse_query(query)
items = execute_query(items, parsed)
# this json representation of the query that was just used to filter
# the items will be passed back to the template as a json string
# so the front end can render the query widget. A string representation
# is not passed because there is no way to parse LQL in javascript (yet).
query_json = jsonify(parsed)
items = items.all()
all_keys = []
return {
'parsed_query_json': query_json,
'library_items': items,
'items_count': len(items),
'keys': all_keys,
}
def render_documents(navigation):
doc_tree_html = ""
try:
# Get the navigation id
navigation_id = str(navigation.id)
# Get projection
projection = navigation.options['projection']
# get filters from parents
filters = []
while True:
# add filter to the list of filters
if 'filter' in navigation.options and navigation.options['filter'] is not None:
filters.append(navigation.options['filter'])
# if no parent, stops the filter lookup
if navigation.parent is None:
break
else: # if parent, continue the filter lookup at the parent level
navigation = navigation_get(navigation.parent)
# get the documents matching the query
documents = query.execute_query(filters, projection)
for document in documents:
with open(join(TEMPLATES_PATH, 'li_document.html'), 'r') as li_file:
li_content = li_file.read()
li_template = Template(li_content)
branch_name = get_projection(document)
context = {
'branch_id': document['_id'],
"parent_id": navigation_id,
'branch_name': branch_name,
}
doc_tree_html += li_template.render(Context(context))
except Exception, e:
with open(join(TEMPLATES_PATH, 'li_error.html'), 'r') as li_file:
li_content = li_file.read()
li_template = Template(li_content)
context = {
"error_message": e.message
}
doc_tree_html = li_template.render(Context(context))
def render_documents(navigation, template_id):
"""
Build the documents in the navigation tree
:param request:
- navigation root
- template id
:return:
"""
doc_tree_html = ""
doc_and_content = []
global number_of_doc
number_of_doc = 0
try:
# Get the navigation id
navigation_id = str(navigation.id)
# Get projection
projection = navigation.options['projection']
navigation_child = navigation_get(navigation_id)
# get filters from parents
filters = []
while True:
# add filter to the list of filters
if 'filter' in navigation.options and navigation.options[
'filter'] is not None:
filters.append(navigation.options['filter'])
# if no parent, stops the filter lookup
if navigation.parent is None:
break
else: # if parent, continue the filter lookup at the parent level
navigation = navigation_get(navigation.parent)
# get the documents matching the query
documents = query.execute_query(template_id, filters, projection)
#number_of_doc = 0
for document in documents:
with open(join(TEMPLATES_PATH, 'li_document.html'),
'r') as li_file:
li_content = li_file.read()
li_template = Template(li_content)
branch_name = get_projection(document)
context = {
'branch_id': document['_id'],
"parent_id": navigation_id,
'branch_name': branch_name,
}
doc_tree_html += li_template.render(Context(context))
# doc_and_content.append((doc_tree,li_template.render(Context(context))))
global number_of_doc
number_of_doc += 1
except Exception, e:
with open(join(TEMPLATES_PATH, 'li_error.html'), 'r') as li_file:
li_content = li_file.read()
li_template = Template(li_content)
context = {"error_message": e.message}
doc_tree_html = li_template.render(Context(context))
def get_recommended(connection,
return_type,
user_id,
limit=None,
cluster_ids=None):
"""
ARGS
connection a pyMysql connection object
return_type either 'clusters', 'ingredients' or 'history'
user_id id of a user from the user profile table
limit the number of records to return from the head of the data set
cluster_ids list of ids
RETURNS
a data frame containing recommended ingredients/clusters which balance the user's DRI
"""
# Set required nutrients
nutrients_start_with = ('macro', 'vitamin', 'mineral')
# Get the user nutritional history and requirements
ordered_snapshot = sn.get_user_snapshot(
connection=connection,
nutrients_start_with=nutrients_start_with,
user_id=user_id)
ordered_dri = get_user_dri(connection=connection,
nutrients_start_with=nutrients_start_with,
user_id=user_id)
# Get required nutrition vector
dri_diff = get_dri_diff(snapshot=ordered_snapshot,
dri=ordered_dri,
nutrients_start_with=nutrients_start_with)
# Check return_type is valid
if return_type not in ['clusters', 'ingredients', 'history']:
raise ValueError(
"Invalid value for 'return_type'. Use 'clusters', 'ingredient' or 'history'"
)
# Get the data to sort
if return_type == 'history':
df_to_sort = sn.get_user_history(connection, user_id)
sort_ascending = True
else:
if return_type == 'clusters':
sql = "SELECT * FROM app.cluster WHERE method_id = 3"
elif return_type == 'ingredients':
sql = "SELECT * FROM app.ingredients_imputed WHERE 1 AND NOT cluster_id IN (2, 23, 33, 7, 35, 31, 25, 14, 15, 16, 9, 22)"
if cluster_ids:
sql += " AND cluster_id IN (%s)" % ','.join(
str(cluster_id) for cluster_id in cluster_ids)
cursor = qy.execute_query(connection=connection, sql=sql)
df_to_sort = pd.DataFrame(cursor.fetchall())
sort_ascending = False
# Convert the values of the ingredients to nutrition_vector space
for i, col in enumerate(ordered_dri.index):
df_to_sort[col] = df_to_sort[col].mul(1 / ordered_dri[col], axis=0)
# Sort the data
df_sorted = sort_df_by_cosine_similarity(
data_to_sort=df_to_sort,
nutrition_vector=dri_diff,
nutrients_start_with=nutrients_start_with,
sort_ascending=sort_ascending,
limit=limit)
return df_sorted
# CLUSTER DATA
# Get centroids and cluster IDs for each row
df_centroids, cluster_ids_of_rows, headers = cluster(
df_to_cluster=df_query_complete[filter_col],
method=method,
num_clusters=NUM_CLUSTERS,
dri_user=dri_user)
# PUSH DATA TO SERVER
# Push cluster IDs of rows to server
df_query_complete["cluster_id"] = cluster_ids_of_rows
sql = "UPDATE app.ingredients_imputed SET cluster_method = %s, cluster_id = %s WHERE id = %s"
for row in df_query_complete.iterrows():
qy.execute_query(connection=connection,
sql=sql,
insert_data=(METHOD_ID, row[1]['cluster_id'],
row[1]['id']))
connection.commit()
# Push centroids to server
# Clear old data with same method ID
sql = "DELETE FROM app.cluster WHERE method_id = %s"
qy.execute_query(connection=connection, sql=sql, insert_data=(METHOD_ID, ))
# Push new data
sql = "INSERT INTO app.cluster (%s) VALUES (%s)"
columns = list(df_centroids.columns.values)
for row in df_centroids.iterrows():
column_string = 'method_id' + ", "
column_values = str(int(METHOD_ID)) + ", "
for column in columns:
import pandas as pd
import query as qy
import numpy as np
from fancyimpute import KNN
# FETCH DATA
# Get a connection
connection = qy.get_connection(user="******", password="******")
# Get all ingredients
sql = "SELECT * FROM app.ingredients"
cursor = qy.execute_query(connection=connection, sql=sql)
# Make the data frame
df_ingredients = pd.DataFrame(cursor.fetchall())
required_columns = [
col for col in df_ingredients
if col.startswith(('macro', 'vitamin', 'mineral', 'sup'))
]
# IMPUTE VALUES
# Scale the values for the regressions
column_scaling = {}
for column_name in required_columns:
column_std = np.nanstd(df_ingredients[column_name])
column_mean = np.nanmean(df_ingredients[column_name])
column_scaling[column_name] = {'mean': column_mean, 'std': column_std}
df_ingredients['scaled_' + column_name] = (df_ingredients[column_name] -
column_mean) / column_std