def get_samples(self, timeframe=None, **kwargs):
"""
Get samples from appliance
:param timeframe: TimeFrame object containing temporal bounds for the query
:param kwargs:
- limit -> limit amount of samples retrieved
- quantity_name -> name of physical quantity to be retrieved, if omitted gets active_power samples
:return:
- dataframe containing measurement and timestamp columns
"""
session = Session()
quantity_name = copy(kwargs['quantity_name'])
hash_dir = self._hash_dir()
query_hash_dir = "/home/morgan/ELoaDS/disaggregation/data_dir/raw/{}_{}.h5" \
.format(hash_dir, reduce(lambda acc, xi: "{}_{}".format(acc, xi), quantity_name))
if timeframe is None:
try:
samples = pd.read_hdf(query_hash_dir, query_hash_dir)
except (FileNotFoundError, KeyError) as e:
print("Cache doesn't exists for this query, building it...")
samples = self._query_samples(quantity_name=quantity_name,
query_hash_dir=query_hash_dir,
session=session)
else:
samples = self._query_samples(quantity_name=quantity_name,
query_hash_dir=query_hash_dir,
session=session,
timeframe=timeframe)
session.close()
return samples
def get_appliances(**kwargs):
"""
Gets appliances by passing parameters that describe them
:param kwargs: possible parameters so far:
- dataset_name: name of origin dataset where such appliance measurements were taken
- appliance_type_name: appliance_type you want to query
- location: place from the dataset were the appliance is located at
:return: A list of appliances
"""
session = Session()
q = session.query(Appliance) \
.join(ApplianceType) \
.join(OriginDataset) \
.options(joinedload(Appliance.appliance_type), joinedload(Appliance.origin_dataset))
if 'location' in kwargs.keys():
q = q.filter(Appliance.location == kwargs['location'])
if 'dataset_name' in kwargs.keys():
q = q.filter(OriginDataset.dataset_name == kwargs['dataset_name'])
if 'appliance_type_name' in kwargs.keys():
q = q.filter(ApplianceType.appliance_type_name ==
kwargs['appliance_type_name'])
if 'description' in kwargs.keys():
q = q.filter(Appliance.description == kwargs['description'])
appliances = q.all()
session.close()
return appliances
def add():
"""Input transaction."""
t = Transaction(
datetime.strptime(request.values['day'],
'%Y-%m-%d').date(), request.values['supplier'],
float(request.values['amount']), request.values['category'])
Session.add(t)
Session.commit()
return get_latest()
def update(transaction_id):
"""Update transaction by id."""
t = Transaction.get_by_id(transaction_id)
t.date = datetime.strptime(request.values['day'], '%Y-%m-%d').date()
t.supplier = request.values['supplier']
t.amount = float(request.values['amount'])
t.category = request.values['category']
t.notes = request.values['notes']
t.updated_at = datetime.now()
Session.add(t)
Session.commit()
return get(transaction_id)
def get_activation_aggregate(activation, appliance, window_size, quantity_name, sampling_period, is_start):
"""
Gets aggregate data period correspondent to some of its appliances activations
:param activation: pd.DataFrame with activations data
:param appliance: Appliance object
:param window_size: window_size in num_samples
:param quantity_name: quantity names of types of samples to retrieve
:param sampling_period: time-series sampling_period
:param is_start: boolean indicating if the activation is the "start" of a full activation with could
be longer than window_size
:return: pd.DataFrame of the aggregate window, padding used to fill the window
"""
session = Session()
length = activation.index.size
start = activation.index[0]
pad_left = 0 if ((window_size - length == 0) or not is_start) else np.random.randint(0, window_size - length)
aggregate = session.query(Aggregate).filter(Aggregate.id == appliance.aggregate_id).all()[0]
bound_start = start - timedelta(seconds=sampling_period * pad_left)
bound_end = bound_start + timedelta(seconds=sampling_period * window_size - 1)
bounds = TimeFrame(start=bound_start, end=bound_end)
df = aggregate.get_samples(quantity_name=quantity_name, timeframe=bounds)
session.commit()
session.close()
df = df.resample("{}S".format(sampling_period)).bfill()
date_series = pd.DataFrame({'sample_dates': pd.date_range(bound_start, bound_end,
freq='{}S'.format(sampling_period))})
df = df.merge(date_series, how='right', left_index=True, right_on='sample_dates')
if df.isna().sum()[0] / window_size > 0.1:
raise ValueError('No aggregate data for such activation')
df = df.fillna(method='bfill').fillna(method='ffill')
df = df.rename(columns={'sample_dates': 'timestamp'})
df = df.set_index(df['timestamp'])
df = df.drop('timestamp', axis=1)
return df, pad_left
def get_aggregate(**kwargs):
"""
Gets appliances by passing parameters that describe them
:param kwargs: possible parameters so far:
- dataset_name: name of origin dataset
- location: place from the dataset
:return: A list of aggregates
"""
session = Session()
q = session.query(Aggregate) \
.join(OriginDataset) \
.options(joinedload(Aggregate.origin_dataset))
if 'location' in kwargs.keys():
q = q.filter(Aggregate.location == kwargs['location'])
if 'dataset_name' in kwargs.keys():
q = q.filter(OriginDataset.dataset_name == kwargs['dataset_name'])
aggregates = q.all()
session.close()
return aggregates
def get_transactions(lim=None,
reverse=False,
begin=None,
end=None,
month=None):
q = Session.query(Transaction)
if month:
q = q.filter(Transaction.month == month).filter(
Transaction.year == datetime.now().year)
else:
if begin and end:
q = q.filter(Transaction.date.between(begin, end))
elif begin:
q = q.filter(Transaction.date >= begin)
elif end:
q = q.filter(Transaction.date <= end)
rows = q.order_by(Transaction.updated_at.desc()).limit(lim).all()
if reverse:
return rows[::-1]
return rows
def get_samples(self, quantity_name, timeframe=None, **kwargs):
"""
Get samples from appliance
:param timeframe: TimeFrame object specifying time bounds for the retrieved samples
:param quantity_name: list of quantity names to be retrieved
:param kwargs:
- limit -> limit amount of samples retrieved
:return:
- dataframe containing measurement and timestamp columns
"""
columns_to_drop, columns_to_rename = None, None
session = Session()
quantity_name = copy(quantity_name)
if 'active_power' in quantity_name and \
self.origin_dataset.dataset_name == 'ukdale':
quantity_name[quantity_name.index('active_power')] = 'apparent_power'
columns_to_rename = {'apparent_power': 'active_power'}
self._compute_apparent_from_power_triangle = self.origin_dataset.dataset_name in ['rae', 'iawe', 'greenant']
if 'apparent_power' in quantity_name and self._compute_apparent_from_power_triangle:
quantity_name = ['active_power'] + quantity_name
quantity_name = ['reactive_power'] + quantity_name
columns_to_drop = ['active_power', 'reactive_power']
hash_dir = self._hash_dir()
query_hash_dir = "/home/morgan/ELoaDS/disaggregation/data_dir/raw/{}_{}.h5" \
.format(hash_dir, reduce(lambda acc, xi: "{}_{}".format(acc, xi), quantity_name))
if timeframe is None:
try:
df = pd.read_hdf(query_hash_dir, query_hash_dir)
except (FileNotFoundError, KeyError) as e:
print("Cache doesn't exist for this query, building it...")
print("Error thrown: {}".format(e))
df = self._query_samples(quantity_name=quantity_name, query_hash_dir=query_hash_dir, session=session,
columns_to_drop=columns_to_drop, columns_to_rename=columns_to_rename)
else:
df = self._query_samples(quantity_name=quantity_name, query_hash_dir=query_hash_dir, session=session,
columns_to_drop=columns_to_drop, columns_to_rename=columns_to_rename,
timeframe=timeframe)
session.commit()
session.close()
return df
def pivot_transactions(year):
q = Session.query(Transaction.month, Transaction.category,
sqlfunc.sum(Transaction.amount))
q = q.filter(Transaction.year == year)
q = q.group_by(Transaction.month, Transaction.category)
return dict([((m, c), a) for m, c, a in q.all()])
def get_years():
q = Session.query(Transaction.year).distinct().order_by(
Transaction.year.desc())
return [r.year for r in q.all()]
def get_suppliers():
return [
r.supplier
for r in Session.query(Transaction.supplier).distinct().all()
]
def get_categories():
return [
r.category
for r in Session.query(Transaction.category).distinct().all()
]
def get_by_id(cls, id):
return Session.query(cls).get(id)
def guess_category(supplier):
q = Session.query(Transaction.category, sqlfunc.count().label('count'))
q = q.filter(Transaction.supplier == supplier)
q = q.group_by(Transaction.category).order_by(desc('count')).limit(1)
return q.scalar() or ''
def delete(transaction_id):
"""Delete transactio by id."""
t = Transaction.get_by_id(transaction_id)
Session.delete(t)
Session.commit()
return get_latest()