Sleep Data

Load sleep data into PostgreSQL collected from the Sleep Cycle phone app

Sleep Cycle

import os
import re
import pandas as pd
import psycopg2
from sqlalchemy import create_engine

sleepdata = "./data/sleepdata.csv"

Database credentials

with open("../../postgres.txt", "r") as f:
    user, pwd = [s.strip() for s in f.readlines()]

Create DataFrame

df = pd.read_csv(sleepdata, delimiter=";")
df.dtypes

Start                           object
End                             object
Sleep Quality                   object
Regularity                      object
Mood                           float64
Heart rate (bpm)                 int64
Steps                            int64
Alarm mode                      object
Air Pressure (Pa)              float64
City                            object
Movements per hour             float64
Time in bed (seconds)          float64
Time asleep (seconds)          float64
Time before sleep (seconds)    float64
Window start                    object
Window stop                     object
Did snore                         bool
Snore time                     float64
Weather temperature (°F)       float64
Weather type                    object
Notes                           object
dtype: object

Create DB Safe Column Names

nonword_pattern = re.compile(r"[^\w]")
spacing_pattern = re.compile(r"[_]{2,}")
ending_pattern = re.compile(r"_$")
df.columns = [
    re.sub(
        ending_pattern,
        "",
        re.sub(spacing_pattern, "_", re.sub(nonword_pattern, "_", col)),
    ).lower()
    for col in df.columns
]
df.columns

Index(['start', 'end', 'sleep_quality', 'regularity', 'mood', 'heart_rate_bpm',
       'steps', 'alarm_mode', 'air_pressure_pa', 'city', 'movements_per_hour',
       'time_in_bed_seconds', 'time_asleep_seconds',
       'time_before_sleep_seconds', 'window_start', 'window_stop', 'did_snore',
       'snore_time', 'weather_temperature_f', 'weather_type', 'notes'],
      dtype='object')

db_host = "localhost"
db_table = "sleepdata"
db_name = "personal"

engine = create_engine(f"postgresql://{user}:{pwd}@{db_host}/{db_name}")

df.to_sql(f"{db_table}", engine, if_exists="replace", index=False)

509