def test_average(self):
new_user = User(id=100, area='area1', tariff='t1')
new_user.save()
Consumption(user_id=100,
consumption=30.0,
datetime='2016-07-15 09:30:00').save()
Consumption(user_id=100,
consumption=20.0,
datetime='2016-07-19 12:30:00').save()
# average consumptions during July must be (20 + 30) / 2 = 50
self.assertEqual(new_user.calculate_monthly_consumptions()[1][6],
50.00)
def import_consumptions(self, consumption_data_dir=None):
if consumption_data_dir is None:
consumption_data_dir = CONSUMPTION_DATA_DIR
if not os.path.exists(consumption_data_dir):
raise CommandError('The following path does not exist: %s' % consumption_data_dir)
Consumption.objects.all().delete()
for user in User.objects.all():
data_path = os.path.join(consumption_data_dir, '%s.csv' % user.pk)
self.stdout.write('Importing consumption data from %s' % data_path)
if not os.path.exists(data_path):
continue
df = pd.read_csv(data_path)
df['datetime'] = pd.to_datetime(df['datetime'], utc=True)
df['user_id'] = user.pk
df = df.drop_duplicates(subset='datetime')
Consumption.objects.bulk_create(
Consumption(**vals) for vals in df.to_dict('records')
)
def handle(self, *args, **options):
# read users list
users = read_csv(f'{DATA_ROOT}/{USERS_FILE}')
for i, user in users.iterrows():
try:
# add user record to db
new_user = User(id=int(user['id']),
area=user['area'],
tariff=user['tariff'])
new_user.save()
print(f'Importing consumptions for user {user["id"]}')
except IntegrityError:
print(f'Skipping already existing user {user["id"]}')
new_user = User.objects.get(id=int(user['id']))
# read user's consumption records
consumptions = read_csv(
f'{DATA_ROOT}/{CONSUMPTIONS_DIR}/{user["id"]}.csv',
parse_dates=['datetime'])
for j, consumption in consumptions.iterrows():
try:
# add user's single consumption to db
Consumption(user=new_user,
datetime=consumption['datetime'],
consumption=float(
consumption['consumption'])).save()
except IntegrityError:
print(
f'Skipping already existing consumption record for user {user["id"]} at'
f' {consumption["datetime"]}')
def import_consumption_data(self, consumption_dir):
'''This method imports consumption data to the consumption table in the
sqlite db.'''
df_list = []
for file_name in os.listdir(consumption_dir):
if not file_name.startswith('.'):
df = pd.read_csv(consumption_dir + file_name, encoding='utf-8')
df['user_data_id'] = int(os.path.splitext(file_name)[0])
# adding id to consumption file before importing
df.datetime = pd.to_datetime(df.datetime)
df_list.append(df[['user_data_id', 'datetime', 'consumption']])
try:
consumption_data = pd.concat(df_list).to_dict('records')
consumption_data_table = [
Consumption(user_data_id=row['user_data_id'],
datetime=row['datetime'],
consumption=row['consumption'])
for row in consumption_data
]
Consumption.objects.bulk_create(consumption_data_table)
self.logger.info('Consumption data imported')
except IntegrityError:
self.logger.info('consumption table data already imported,'
' drop all rows in the table import again.')
except Exception:
self.logger.info('Could not import consumption_data')
def test_consumption(self):
consumption = Consumption(user_data=self.user,
datetime=self.datetime,
consumption=400)
consumption.save()
consumption_db = Consumption.objects.get(datetime=self.datetime)
self.assertEqual(consumption, consumption_db)
def store_consumption_data(self, *args, **kwargs):
if Consumption.objects.exists():
self.logger.info('consumption table data already exist,'
' drop the table import again.')
Consumption.objects.all().delete()
consumptions = []
for i in glob.glob('../data/consumption/*.csv'):
user_id = i.replace('../data/consumption/', '').replace('.csv', '')
user_data = User_data.objects.get(pk=user_id)
with open(i, 'r') as f:
rows = csv.reader(f)
next(rows)
for row in rows:
info = {
'user_data': user_data,
'datetime': row[0],
'consumption': row[1]
}
consumptions.append(Consumption(**info))
# print(consumptions)
self.logger.info('Consumption data imported')
new_instances = Consumption.objects.bulk_create(consumptions)
new_instances
f.close()
def setup(self):
new_user = User(id=100, area='area1', tariff='t1')
new_user.save()
Consumption(user_id=100,
consumption=30.0,
datetime='2016-07-15 09:30:00').save()
Consumption(user_id=100,
consumption=20.0,
datetime='2016-07-19 12:30:00').save()
Consumption(user_id=100,
consumption=10.0,
datetime='2016-07-21 11:30:00').save()
new_user = User(id=101, area='area1', tariff='t1')
new_user.save()
Consumption(user_id=101,
consumption=45.0,
datetime='2016-07-15 09:30:00').save()
Consumption(user_id=101,
consumption=27.0,
datetime='2016-07-21 10:30:00').save()
Consumption(user_id=101,
consumption=21.0,
datetime='2016-07-24 11:30:00').save()
Consumption(user_id=101,
consumption=43.0,
datetime='2016-09-02 06:30:00').save()
def consumption_record_2(testing_user):
from consumption.models import Consumption
return Consumption(user_id=testing_user,
timestamp=datetime(year=2017,
month=6,
day=2,
hour=14,
minute=30,
second=45),
consumption=1750.25)
def import_user_cons(self):
""" Import data/consumption/*.csv to database.
"""
print('Importing user consumption...')
data = []
for cons_path in Path(self.CONSUMPTION_DIR).iterdir():
user = User.objects.get(id=int(cons_path.stem))
with open(str(cons_path), 'r') as file:
reader = csv.reader(file)
next(reader, None)
cons = [
Consumption(user=user,
time_used=self.str2ts(row[0]),
amount=row[1]) for row in reader
]
data.extend(cons)
Consumption.objects.bulk_create(data)
print('Done')
def handle(self, *args, **options):
# Optional argument.
if (options["skip"]): skip = options["skip"]
else: skip = 1
# Load in consumers.
userFile = open("../data/user_data.csv", "r")
consumersData = parseCsv(userFile.readlines())
userFile.close()
consumptionsData = []
# Iterate through consumers and load in the data pertaining to them.
for consumerData in consumersData:
consumptionFile = open(
"../data/consumption/%s.csv" % consumerData["id"], "r")
consumptionsData.append(parseCsv(consumptionFile.readlines()))
consumptionFile.close()
# Iterate through consumers again and save them and the data relating to them
for i, consumerData in enumerate(consumersData):
area = int(consumerData["area"][1])
tariff = int(consumerData["tariff"][1])
consumer = Consumer(user_id=consumerData["id"],
area=area,
tariff=tariff)
consumer.save()
print("Consumer %d out of %d" % (i, len(consumersData)))
for u, consumptionData in enumerate(consumptionsData[i]):
if (u % skip != 0): continue
dateTime = make_aware(
parse_datetime(consumptionData["datetime"]))
magnitude = float(consumptionData["consumption"])
consumption = Consumption(consumer=consumer,
date_time=dateTime,
magnitude=magnitude)
consumption.save()
def handle(self, *args, **options):
"""
command line method for ETL of user and consumption data from csv to default database
(see dashboard/settings.py to configure the engine)
:param args: args
:param options: kwargs
:return: None
"""
stdlogger = logging.getLogger(__name__)
data_dir = os.path.join(os.path.dirname(settings.BASE_DIR), 'data')
user_path = os.path.join(data_dir, 'user_data.csv')
stdlogger.info("Reading file: {}".format(user_path))
with open(user_path, 'r') as user_data:
reader = csv.DictReader(user_data, delimiter=',')
u_counter = 0
for u_counter, record in enumerate(reader):
user = User()
user.id = int(record['id'])
user.area = record['area']
user.tariff = record['tariff']
stdlogger.debug(
"Saving User object: Id: {}, Area: {}, Tariff: {}".format(
user.id, user.area, user.tariff))
user.save()
stdlogger.info("{} User records saved to database".format(u_counter))
consumption_path = os.path.join(data_dir, 'consumption', '*.csv')
consumption_files = glob.glob(consumption_path)
for con_file in consumption_files:
stdlogger.info("Reading file: {}".format(con_file))
with open(con_file, 'r') as con_data:
reader = csv.DictReader(con_data, delimiter=',')
c_counter = 0
for c_counter, record in enumerate(reader):
usage = Consumption()
usage.user_id = User.objects.get(
id=int(os.path.split(con_file)[-1].rstrip('.csv')))
usage.timestamp = datetime.strptime(
record['datetime'], '%Y-%m-%d %H:%M:%S')
usage.consumption = float(record['consumption'])
stdlogger.debug("Saving Consumption object: "
"Id: {}, Area: {}, Tariff: {}".format(
usage.user_id, usage.timestamp,
usage.consumption))
usage.save()
stdlogger.info(
"{} Consumption records saved to database".format(c_counter))
def create_consumption_obj(self, *args):
return Consumption(user=User.objects.get(user_id=args[0][0]),
datetime=args[0][1],
consumption=args[0][2])
import sys
import os
from django.core.wsgi import get_wsgi_application
from decimal import Decimal
import datetime
file_path = '/home/dilmac/development/gastestenv/gas/smap-coding-challenge/dashboard'
print(file_path, ' The path')
sys.path.append(file_path)
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "dashboard.settings")
application = get_wsgi_application()
import django
# django.setup()
from consumption.models import Consumption
data = csv.reader(open(
'/home/dilmac/development/gastestenv/gas/smap-coding-challenge/data/consumption/3000.csv'
),
delimiter=',')
for row in data:
if row[0] != 'create_date_time':
consum = Consumption()
consum.create_date_time = row[0]
consum.consumption = row[1]
consum.save()