In [23]:
%matplotlib inline
from sqlalchemy import create_engine, text
from datetime import timedelta
import pandas as pd
import numpy as np
import matplotlib.pyplot as pltt
import calmap

# setting up a visual style for PyPlot, much better than the standard'fivethirtyeight')

# Your database url as specified in configuration.yaml
# If using default settings, it's \
# sqlite:///<path to config dir>/home-assistant_v2.db
DB_URL = "sqlite:///./home-assistant_v3.db"
engine = create_engine(DB_URL)

# let's get a list of distinct entity_ids from domain 'device tracker'
entitystmt = text("select distinct entity_id from states where \
entitiesquery = engine.execute(entitystmt)
entitylist = list(entitiesquery.fetchall())

# now let's loop through the entitylist to generate plots for each entity
for entity in entitylist:

    # sql query pulling each state for a given entity, joining in on itself \
    # in order to get the time of next state change
    stmt = text("select distinct case when t1.state='home' then 1 else 0 end as \
    HomeBit, t1.last_changed, (select  last_changed from states t2 where \
    t2.state_id> t1.state_id and entity_id=:entity_filter order by \
    t2.state_id limit 1) as next_change from states t1 where \

    # bind entity parameter to query
    stmt = stmt.bindparams(entity_filter=entity[0])

    # execute the SQL statement
    devicestatesquery = engine.execute(stmt)

    # get rows from query into a pandas dataframe
    devicestatesDF = pd.DataFrame(devicestatesquery.fetchall())

    # name the dataframe rows for usability
    devicestatesDF.columns = ['HomeBit',  'last_changed', 'next_change']

    # create a dataframe with an index ranging from the min to the max\
    # last_changed timestamp from our query
    dates = pd.DataFrame(index=pd.date_range(min(pd.to_datetime(

    # create a HomeTime column with a zero timedelta value
    dates['HomeTime'] = timedelta(days=0)

    # create a last_changed_date column with only the date portion of \
    # last_changed value
    devicestatesDF['last_changed_date'] = pd.to_datetime(devicestatesDF
                                    ['last_changed']).apply(lambda x:

    # grab the index values of our dates dataframe
    dateslist_DATE = pd.to_datetime(dates.index.values.tolist())

    # start processing for each date
    for date in dateslist_DATE:

        # create a filtered dataframe where the last_changed_date column \
        # matches the date currently being processed
        datedf = devicestatesDF.loc[devicestatesDF['last_changed_date'] ==

        # the filtered dataframe will retain the indexes, so let's reset them

        # initialize our "counter" as a 0 timedelta
        datetimehomesum = pd.Timedelta(hours=0, unit='us')

        # start processing the filtered dataframe row by row
        for item in datedf.index:

            # if this is the first row of current day
            if item == 0:

                # and the first row of this day indicates that the device \
                # wasn't home, it must've been home for whatever time since \
                # the beginning of current day
                if datedf.ix[item]['HomeBit'] == 0:
                    datetimehomesum += (pd.to_datetime(datedf.ix[item]

                    # if there's more rows to be processed in this day, \
                    # calculate the home value from first timestamp to second
                    if len(datedf.index) > 1:
                        datetimehomesum += (pd.to_datetime(datedf.ix[item]

                # if the first row of this day indicates that the device was\
                # home, calculatee the home value for the first timestamp as \
                # usual
                    datetimehomesum += (pd.to_datetime(datedf.ix[item]

            # if this is the last row of this day, calculate the home value\
            # until beginning of next day
            elif item == len(datedf.index)-1:
                datetimehomesum += (pd.to_datetime(((pd.to_datetime(datedf.ix
                    [item]['last_changed']))+(timedelta(days=1))).date()) -
                    * datedf.ix[item]['HomeBit']

            # otherwise calculate the home value as usual
                datetimehomesum += (pd.to_datetime(datedf.ix[item]

        # set the value of our dates dataframe at index of current date\
        # to the calculated home value for this day
        dates.loc[date] = datetimehomesum

    # convert index of our dates dataframe to datetime
    dates.index = pd.to_datetime(dates.index, box=True)

    # convert each value in our dates dataframe from an hour timedelta \
    # to a float
    dates = dates.apply(lambda x: x/np.timedelta64(1, 'h'))

    # create a series from our dates dataframe (necessary for calmap)
    datesseries = dates['HomeTime']

    # create a calendar map
    entityplot = calmap.calendarplot(datesseries, cmap='YlGn', fig_kws=dict(figsize=(15, 3)), 

    # set the plot title to match our current entity name
    entityplot[0].suptitle('Heatmap of hours spent home daily by {}'.format(entity[0]))
C:\Users\anton.kireyeu\Downloads\WinPython-64bit-\python-3.4.4.amd64\lib\site-packages\calmap\ FutureWarning: how in .resample() is deprecated
the new syntax is .resample(...).sum()
  by_day = data.resample('D', how=how)