<jupyter_start><jupyter_text>Import Libraries<jupyter_code>import sys
!{sys.executable} -m pip install -r requirements.txt
import numpy as np
import matplotlib.pyplot as plt
from analytics import SQLClient<jupyter_output><empty_output><jupyter_text>Connect to MySQL database<jupyter_code>username = "privateuser"
password = "<PASSWORD>"
port = 7777

client = SQLClient(username, password, port)
sql_tmp = """
    SELECT 
        id
        ,userID
        ,name
        ,type
        ,-priceCNY * count / 6.9 AS price
        ,count
        ,currency
        ,-priceCNY * count AS priceCNY
        ,time 
    FROM items 
    WHERE userID LIKE '%%shawlu%%' 
        AND time BETWEEN '$start_dt$ 00:00:00' AND '$end_dt$ 00:00:00' 
        AND deleted = 0
    ORDER BY time;"""<jupyter_output><empty_output><jupyter_text>Analytics: Nov. 2018<jupyter_code>start_dt = '2018-11-01'
end_dt = '2018-12-01'
df = client.query(sql_tmp.replace('$start_dt$', start_dt).replace("$end_dt$", end_dt))
df = df.groupby(['type']).sum()
total = np.sum(df.price)
df["pct"] = df.price / total
df["category"] = client.categories
df = df.sort_values("pct")[::-1]
df
labels = ["%s: $%.2f"%(df.category.values[i],
             df.price.values[i]) for i in range(len(df))]

plt.figure(figsize=(8, 8))

title = "Total expense %s\n%s-%s"%('$ {:,.2f}'.format(total), start_dt, end_dt)
plt.title(title)

_ = plt.pie(x = df.price.values, 
            labels = labels,
            autopct='%1.1f%%',
            labeldistance = 1.1)

centre_circle = plt.Circle((0,0),0.70,fc='white')
fig = plt.gcf()
fig.gca().add_artist(centre_circle)

plt.savefig("month.png")<jupyter_output><empty_output><jupyter_text>Analytics: Year of 2018<jupyter_code>start_dt = '2018-01-01'
end_dt = '2018-12-01'
df = client.query(sql_tmp.replace('$start_dt$', start_dt).replace("$end_dt$", end_dt))
df[df.type == 'COM']
df = df.groupby(['type']).sum()
total = np.sum(df.price)
df["pct"] = df.price / total
df["category"] = client.categories
df = df.sort_values("pct")[::-1]
df
labels = ["%s: $%.2f"%(df.category.values[i],
             df.price.values[i]) for i in range(len(df))]

plt.figure(figsize=(8, 8))

title = "Total expense %s\n%s-%s"%('$ {:,.2f}'.format(total), start_dt, end_dt)
plt.title(title)

_ = plt.pie(x = df.price.values, 
            labels = labels,
            autopct='%1.1f%%',
            labeldistance = 1.1)

centre_circle = plt.Circle((0,0),0.70,fc='white')
fig = plt.gcf()
fig.gca().add_artist(centre_circle)

plt.savefig("year.png")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1834319526627219, 'fraction_numerical': 0.03431952662721893, 'mean_word_length': 3.749063670411985, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 18, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '1678', 'license': 'MIT', 'n_tokens_mistral': 1053, 'n_tokens_neox': 991, 'n_words': 178, 'path': 'analytics/analytics.ipynb', 'repo_name': 'shawlu95/Grocery_Matter', 'size': '2532'}