Data management¶

import sqlite3
import matplotlib.pyplot as plt
import datetime
import numpy as np

conn = sqlite3.connect('Gadgetbridge')
c = conn.cursor()
min_steps_per_minute=00

d=c.execute("select strftime('%Y.%m.%d', datetime(timestamp, 'unixepoch')) as d,sum(STEPS) from MI_BAND_ACTIVITY_SAMPLE where STEPS > ? group by d",(min_steps_per_minute,)).fetchall()
w=c.execute("select strftime('%Y.%W', datetime(timestamp, 'unixepoch')) as d,sum(STEPS) from MI_BAND_ACTIVITY_SAMPLE where STEPS > ? group by d",(min_steps_per_minute,)).fetchall()
m=c.execute("select strftime('%Y.%m', datetime(timestamp, 'unixepoch')) as d,sum(STEPS) from MI_BAND_ACTIVITY_SAMPLE where STEPS > ? group by d",(min_steps_per_minute,)).fetchall()
y=c.execute("select strftime('%Y', datetime(timestamp, 'unixepoch')) as d,sum(STEPS) from MI_BAND_ACTIVITY_SAMPLE where STEPS > ? group by d",(min_steps_per_minute,)).fetchall()
print("all avg:",c.execute("select avg(STEPS) from MI_BAND_ACTIVITY_SAMPLE where STEPS > ? ",(min_steps_per_minute,)).fetchall())

db={x[0]:x[1] for x in d}
wb={x[0]:x[1] for x in w}
mb={x[0]:x[1] for x in m}
yb={x[0]:x[1] for x in y}

fig, ax = plt.subplots(4)

def doit(where,what,color,label):
    where.bar(
        np.arange(len(what)),
        list(what.values()),
        0.3,
        #tick_label=list(what.values()),
        tick_label=list(what.keys()),
        label=label,
        color=color,
    )
    where.legend()
    #where.xticks(rotation=60)

doit(ax[3],yb,"g","steps/year")
doit(ax[2],mb,"b","steps/month")
doit(ax[1],db,"r","steps/day")
doit(ax[0],wb,"g","steps/week")

for ax in fig.axes:
    plt.sca(ax)
    plt.xticks(rotation=65)
plt.show()
c.close()

#!/usr/bin/env python3

import csv
import datetime
import sys
import sqlite3
import random

# import script to get MiFit data into Gadgetbridge database

# what this tool does:
# - it checks if a particular record (based on timestamp) is in database
# - if record does not exist, it is created:
# - steps are added
# - for sleep, separate minute based records are created
# - all records will have heart rate measurements, if available in the data

# what this tool does not:
# - doesn't import activities

# it can damage your data, make plenty of backups to be able to roll back at any point

# 1) get your MiFit data via GDPR data request, URL:
# https://account.xiaomi.com/pass/serviceLogin?callback=https%3A%2F%2Faccount.xiaomi.com%2Fsts%2Foauth%3Fsign%3D7QjKYjTipB1s7OliGXsWt1OL9sE%253D%26followup%3Dhttps%253A%252F%252Faccount.xiaomi.com%252Foauth2%252Fauthorize%253Fskip_confirm%253Dfalse%2526client_id%253D428135909242707968%2526pt%253D1%2526redirect_uri%253Dhttps%25253A%25252F%25252Fapi-mifit-cn.huami.com%25252Fhuami.health.loginview.do_not%2526_locale%253Dde_DE%2526response_type%253Dcode%2526scope%253D1%25252016001%25252020000%2525206000%2526_sas%253Dtrue%26sid%3Doauth2.0&sid=oauth2.0&lsrp_appName=In%20%24%7BMi%20Fit%7D%24%20%C3%BCber%20das%20Mi-Konto%20anmelden&_customDisplay=20&scope=1%206000%2016001&_locale=de_DE&_ssign=2%26V1_oauth2.0%266qHWjGF3kaRWOWCGQdM8gIt6lR8%3D

# 2) make an export of database in Gadgetbridge
# - make an extra backup of this exported database
# 3) unzip received MiFit data and place the .csv files into a single folder (see list of required files below)
# 4) put exported Gadgetbridge database file into the same folder
# 5) put this script into the same folder
# 6) either remove "numbers" from the .csv file names, or rename the xxx_file_name variables below
# 7) you may need to edit device_id and user_id. for most people (with one device) this will remain as is below
# 8) run this script with python3:
#  python import_from_mifit.py
# 9) re-import the updated Gadgetbridge database file to GB

activity_file_name = "ACTIVITY_MINUTE.csv"
hr_file_name1 = "HEARTRATE.csv"
hr_file_name2 = "HEARTRATE_AUTO.csv"
sleep_file_name = "SLEEP.csv"

database = "Gadgetbridge"
device_id = 1
user_id = 1

# do not edit below

conn = sqlite3.connect(database)
cursor = conn.cursor()

# build HR dictionary
hr = {}

data = csv.reader(open(hr_file_name1), delimiter=",")
# 1572088219,81
next(data)  # skip header
for line in data:
    hr[line[0]] = line[1]

data = csv.reader(open(hr_file_name2), delimiter=",")
# 2017-07-14,23:35,54
next(data)  # skip header
for line in data:
    date = "{0},{1}".format(*line)
    dt = datetime.datetime.strptime(date, "%Y-%m-%d,%H:%M")
    # timestamp=dt.timestamp()
    timestamp = dt.replace(tzinfo=datetime.timezone.utc).timestamp()
    hr[timestamp] = line[2]

# steps
data = csv.reader(open(activity_file_name), delimiter=",")
# 2017-07-04,13:18,11
next(data)  # skip header
for line in data:
    # print(line)
    date = "{0},{1}".format(*line)
    dt = datetime.datetime.strptime(date, "%Y-%m-%d,%H:%M")
    w = {}
    # timestamp=dt.timestamp()
    timestamp = dt.replace(tzinfo=datetime.timezone.utc).timestamp()
    w["timestamp"] = timestamp
    r = cursor.execute(
        "SELECT * from MI_BAND_ACTIVITY_SAMPLE where TIMESTAMP=$timestamp", (w)
    ).fetchone()
    if r:
        # print("record exists", r,line[2])
        pass
    else:
        steps = int(line[2])
        heart_rate = hr.get(timestamp, 255)
        raw_intensity = random.randint(10, 130)
        if steps < 80:
            raw_kind = 1  # slow walking
        elif 100 > steps > 80:
            raw_kind = 1  # 3 fast walking, unsupported by GB
        else:
            raw_kind = 4  # 4 running
        print("inserting", steps, heart_rate)
        cursor.execute(
            "INSERT INTO MI_BAND_ACTIVITY_SAMPLE VALUES (?,?,?,?,?,?,?)",
            (timestamp, device_id, user_id, raw_intensity, steps, raw_kind, heart_rate),
        )

# sleep
data = csv.reader(open(sleep_file_name), delimiter=",")
# 2017-07-05,45,348,0,2017-07-05 08:23:00+0000,2017-07-05 08:23:00+0000
next(data)  # skip header
for line in data:
    deep_sleep = int(line[1])
    light_sleep = int(line[2])

    dt = datetime.datetime.strptime(line[4], "%Y-%m-%d %H:%M:%S%z")
    timestamp = dt.replace(tzinfo=datetime.timezone.utc).timestamp()

    dt_to = datetime.datetime.strptime(line[5], "%Y-%m-%d %H:%M:%S%z")
    ts_to = dt_to.replace(tzinfo=datetime.timezone.utc).timestamp()

    # deep sleep
    # timestamp=ts_from
    for i in range(0, deep_sleep):

        w["timestamp"] = timestamp
        r = cursor.execute(
            "SELECT * from MI_BAND_ACTIVITY_SAMPLE where TIMESTAMP=$timestamp", (w)
        ).fetchone()
        if r:
            # print("record exists", r,line[2])
            pass
        else:
            heart_rate = hr.get(timestamp, 255)
            print("inserting sleep", timestamp)
            steps = 0
            raw_kind = 123
            raw_intensity = random.choice([20, 2, 7] + [0] * 20)
            cursor.execute(
                "INSERT INTO MI_BAND_ACTIVITY_SAMPLE VALUES (?,?,?,?,?,?,?)",
                (
                    timestamp,
                    device_id,
                    user_id,
                    raw_intensity,
                    steps,
                    raw_kind,
                    heart_rate,
                ),
            )

        timestamp = timestamp + 60

    for i in range(0, light_sleep):

        w["timestamp"] = timestamp
        r = cursor.execute(
            "SELECT * from MI_BAND_ACTIVITY_SAMPLE where TIMESTAMP=$timestamp", (w)
        ).fetchone()
        if r:
            # print("record exists", r,line[2])
            pass
        else:
            heart_rate = hr.get(timestamp, 255)
            print("inserting sleep", timestamp)
            steps = 0
            raw_kind = 121
            raw_intensity = random.choice([20, 2, 7] + [0] * 20)
            cursor.execute(
                "INSERT INTO MI_BAND_ACTIVITY_SAMPLE VALUES (?,?,?,?,?,?,?)",
                (
                    timestamp,
                    device_id,
                    user_id,
                    raw_intensity,
                    steps,
                    raw_kind,
                    heart_rate,
                ),
            )

        timestamp = timestamp + 60

conn.commit()
conn.close()