Data Sources
The app uses different data sources and scripts to work on them. They are listed here.
Databases
The following SQL-Databases are found under ./source/database-name.db/.
Database Profiles
This database is called database_profiles.db and contains the following tables:
Table Name |
Description |
|---|---|
devices |
All devices predefined in the app with their properties. |
device_custom |
All power profile which can be added at a specific time. |
device_preset |
All power profile which has a length of one day. |
device_csv_connection |
Connection between power profiles and source csv in the Tracebase dataset. |
The following columns exist in the table devices:
Name |
Datatype |
Primary Key |
Description |
|---|---|---|---|
type |
TEXT |
Yes |
The unique type string of a device |
standard_room |
TEXT |
No |
The standard room of the device, in which room menu it should appear |
name |
TEXT |
No |
Displayed name of the device (german) |
menu_type |
TEXT |
No |
|
icon |
TEXT |
No |
The icon reference from Iconify |
power_options |
TEXT |
No |
String representation of a python dictionary with all power profiles of this device |
The following columns exist in the table device_custom:
Name |
Datatype |
Primary Key |
Description |
|---|---|---|---|
series_id |
TEXT |
No |
The unique string id of a custom profile |
type |
TEXT |
No |
Device type which can use this profile |
standby_power |
FLOAT |
No |
Standby power of device when no in use |
step_0 |
FLOAT |
No |
First timestep of power profile, power in Watts |
… |
… |
… |
One step per minute |
step_1439 |
FLOAT |
No |
Last timestep of power profile, power in Watts |
The following columns exist in the table device_preset:
Name |
Datatype |
Primary Key |
Description |
|---|---|---|---|
series_id |
TEXT |
No |
The unique string id of a preset profile |
type |
TEXT |
No |
Device type which can use this profile |
step_0 |
FLOAT |
No |
First timestep of power profile, power in Watts |
… |
… |
… |
One step per minute |
step_1439 |
FLOAT |
No |
Last timestep of power profile, power in Watts |
The following columns exist in the table device_csv_connection (see Tracebase dataset):
Name |
Datatype |
Primary Key |
Description |
|---|---|---|---|
series_id |
TEXT |
No |
The unique string id of a profile |
csv_filename |
TEXT |
No |
The filename of the source file of the profile |
Database Households
This database is called database_izes_reduced.db and contains the following tables:
Table Name |
Description |
|---|---|
load_1min |
Measured household power profiles with a time resolution of 1min for 1 year |
The following columns exist in the table load_1min:
Name |
Datatype |
Primary Key |
Description |
|---|---|---|---|
month |
INT |
No |
Month in year |
day |
INT |
No |
Day in month |
profile_4 |
INT |
No |
First household profile |
… |
… |
… |
A total of 27 profiles, not each id is exisiting |
profile_73 |
INT |
No |
Last household profile |
The profiles contain one year of power in Watts in 1 minute resolution. They are a selection from a dataset provided by the Institute for Future Energy Systems (IZES). The dataset and its documentation can be found here. The following list of profiles is included in the reduced dataset:
4, 7, 9, 14, 15, 16, 17, 18, 19, 20, 22, 23, 27, 28, 32, 33, 39, 41, 46, 73, 42, 45, 47, 58, 61, 62, 65
Database PV
This database is called database_pv.db and contains the following tables:
Table Name |
Description |
|---|---|
plz_data |
Coordinates of each postcode in Germany |
The following columns exist in the table plz_data:
Name |
Datatype |
Primary Key |
Description |
|---|---|---|---|
loc_id |
INT |
No |
Location id |
postcode |
INT |
No |
German postcode |
lon |
FLOAT |
No |
Longitude of postcode |
lat |
FLOAT |
No |
Latitude of postcode |
city |
TEXT |
No |
Name of city of the postcode |
The source of this data is a data dump of the old “OpenGeoDB”-project (Here on Github).
SQL Modules
sql_modules.py contains all modules to get different kind of data out of different SQL databases.
- sql_modules.check_postcode(postcode, database)
Check whether a given postcode exist in database.
- Parameters:
postcode (int) – Postcode to check
database (str) – Database to connect to
- Returns:
Result of check if postcode exists
- Return type:
bool
- sql_modules.dict_factory(cursor, row)
Define row factory for cursor
- Parameters:
cursor – SQLite cursor
row –
- Returns:
Row factory for cursor
- sql_modules.get_all_devices(database)
Get all devices that are stored in the database given.
- Parameters:
database – Database to fetch from
- Returns:
List of devices
- sql_modules.get_button_dict(database)
Load all devices from table “devices” of database, which have a standard room defined.
- Parameters:
database (str) – path of sql-database
- Returns:
list of devices from database
- Return type:
list
- sql_modules.get_coordinates(plz, database)
Get longitude and latitude coordinates of given postcode. The function also returns the city name.
- Parameters:
plz (int) – postcode
database (str) – path of database
- Returns:
lon, lat, city name
- sql_modules.get_device(database, device_type)
Get device from SQL database
- Parameters:
database – Database to fetch from
device_type – Type of device to fetch
- Returns:
List of devices
- sql_modules.get_household_profile(database, profile_number, date_start, date_stop)
Load a power profile from the given database. The profile-number is provided with the start and end date.
- Parameters:
database (str) – Database to fetch from
profile_number (int) – Number of the profile, out of a predefined selection of profile numbers
date_start (date) – Start date of timeframe to fetch
date_stop (date) – End date of timeframe to fetch
- Returns:
Power profile
- Return type:
list
- sql_modules.get_load_profile(table_name: str, key: str, database: str)
Loads a power profile with a given key from a given database and table. Cuts away null values.
- Parameters:
table_name (str) – Name of the table to get profile from
key (str) – Key of the profile to fetch
database – Database to read from
- Returns:
Load profile
- Return type:
list
Renewables.ninja
The solar power data is fetched from the Renewables.ninja service. This is done with an API key.
Note
The number of requests is limited to 50/hour.
The code for getting the data can be found in modules.save_settings_pv():
1token_rn = 'your-api-token' # Authorization renewables.ninja
2sess = requests.session()
3sess.headers = {'Authorization': 'Token ' + token_rn}
4url = 'https://www.renewables.ninja/api/data/pv'
5query_params = {
6 # Set all parameters in here (see source code)
7}
8response = sess.get(url, params=query_params) # Send the GET request and get the response
Tracebase Dataset
“The tracebase data set is a collection of power consumption traces which can be used in energy analytics research. Traces have been collected from individual electrical appliances, at an average reporting rate of one sample per second.” - Readme of Tracebase
You can find the documentation and data in the 
Tracebase Github repsitory. The complete reference is given here
The resolution of in average one second is way to high for this project. This is why the used profiles were resampled to 1min resolution using pandas.DataFrame.resample. You can find an example for the resampling in house_callbacks.add_new_device().