<jupyter_start><jupyter_text>Gateways reached by a Sensor<jupyter_code>from datetime import datetime, timedelta
import subprocess
import pandas as pd
from dateutil.parser import parse
from dateutil import tz
from label_map import dev_id_lbls, gtw_lbls
DEVICES = [
    '3148 E 19th, Anc',
    '3414 E 16th, Anc',
    '3424 E 18th, Anc',
    '122 N Bliss',
    '122 N Bliss Unit',
    '1826 Columbine, Anc',
    'Phil ELT-2 3692',
    'Phil LT22222 436E',
    'Phil CO2 26D8',
]

# Days of Data to Show
DAYS = 4

GATEWAY_FILE = '~/gateways.tsv'
# Make DateTime objects for time period analyze
tz_ak = tz.gettz('US/Alaska')
start_ts = (datetime.now(tz_ak) - timedelta(days=DAYS)).replace(
    tzinfo=None, minute=0, second=0, microsecond=0)
end_ts = datetime.now(tz_ak).replace(
    tzinfo=None, minute=0, second=0, microsecond=0)
start_ts, end_ts
# Pick a device to use for testing the script
device = DEVICES[3]

df = pd.read_csv(GATEWAY_FILE, 
    sep='\t', 
    parse_dates=['ts', 'ts_hour'],
    index_col='ts',
    low_memory=False)
df = df.loc[str(start_ts):]
df['dev_id'] = df.dev_id.map(dev_id_lbls)
df.query('dev_id == @device', inplace=True)

def gtw_map(gtw_eui):
    return gtw_lbls.get(gtw_eui, gtw_eui)

df['gateway'] = df.gateway.map(gtw_map)
df.head()
# Determine the "Any" gateway counts by dropping duplicate readings
df_any = df[['ts_hour', 'counter']].drop_duplicates(subset=['counter'])
df_any_count = df_any.groupby('ts_hour').count()
df_any_count.columns = ['Any' ]
df_any_count
# Determine counts for individual gateways
df_cts = pd.pivot_table(df, index='ts_hour', columns='gateway', values='counter', aggfunc='count')
df_cts
# Combine the two DataFrames, horizontally (combine columns)
df_final = pd.concat([df_any_count, df_cts], axis=1)

# Make a new index that fills in any missing hours
new_ix = pd.date_range(start_ts, end_ts, freq='1H')
df_final = df_final.reindex(new_ix)

# Replace NaN values with zero and then convert values to integers
df_final.fillna(0, inplace=True)
df_final = df_final.astype('int32')

df_final = df_final[:-1]    # drop last hour because likely incomplete

# Convert index into a string so we can change drop the seconds from display
df_final.index = df_final.index.strftime("%Y-%m-%d %H:%M")

df_final
def color_cells(val):
    color_scale = {
        0: '#FF3131',
        1: '#FFFF00',
        2: '#FFD822',
    }    
    color = color_scale.get(val, '#EEEEEE')
    return 'background: %s' % color

s = df_final.style
s.applymap(color_cells)
s.set_properties(**{'width': '70px', 'text-align': 'center'})
styles = [
    dict(selector="td", props=[('padding', "0px")]),
]         
s.set_table_styles(styles)

s<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13793103448275862, 'fraction_numerical': 0.027437893956247683, 'mean_word_length': 4.644351464435147, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '492727', 'license': 'MIT', 'n_tokens_mistral': 1071, 'n_tokens_neox': 1009, 'n_words': 252, 'path': 'sensor-gateway.ipynb', 'repo_name': 'alanmitchell/streamlit', 'size': '2697'}