def __init__(self, input_stream):
self.source = Sources.SIEMENS
self.db_connection = DatabaseConnection()
self.siemens_data = pd.read_csv(input_stream)
tag_json = open(get_data_resource("csv_descriptions/PointDecoder.json"))
self.tag_dict = json_load(tag_json)
self.points_with_ids = {}
class RoomTagger:
def __init__(self, building_name):
self.db_connection = DatabaseConnection()
self.building_id = self.db_connection.get_building_id(building_name)
self.generate_rooms()
def generate_rooms(self):
points = self.db_connection.get_point_names_in_building(
self.building_id)
for point in points:
name = point[0]
pid = point[1]
rid = point[2]
room_name = self.parse_room_name(name)
if room_name:
room_id = self.db_connection.add_unique_room(
room_name, self.building_id)
if rid == room_id:
continue
self.db_connection.update_point_room(str(pid), str(room_id))
def parse_room_name(self, name):
room_identifier = "RM"
parts = name.split(".")
if len(parts) == 3:
full_name = parts[1]
if room_identifier in full_name:
return full_name.replace(room_identifier, "")
return False
class BuildingDeduplicator:
building_mapper = {'Libe': 5, 'Rec': 9, 'Cassat Hall/James Hall': 40}
def __init__(self):
self.db_connection = DatabaseConnection()
self.raw_buildings = self.db_connection.get_buildings()
self.construct_buildings()
self.deduplicate()
def construct_buildings(self):
self.buildings = {}
for building in self.raw_buildings:
bid = building[0]
name = building[1]
self.buildings[name] = bid
def deduplicate(self):
for building in self.raw_buildings:
bid = building[0]
name = building[1]
new_id = building[0]
cleaned_name = name.strip()
cleaned_name = cleaned_name.title()
if cleaned_name in self.building_mapper:
new_id = self.building_mapper[cleaned_name]
else:
hallified = cleaned_name + " Hall"
if hallified in self.buildings:
new_id = self.buildings[hallified]
if cleaned_name in self.buildings:
new_id = self.buildings[cleaned_name]
if new_id != bid:
self.db_connection.update_room_building_ids(bid, new_id)
def __init__(self, input_stream):
self.source = Sources.LUCID # Use the enumeration for Lucid
self.data = None # Pandas DataFrame for storing CSV
self.point_identities = {}
self.point_values = None
self.db_connection = DatabaseConnection() # Start up connection to DB
self.data = pd.read_csv(input_stream, skiprows=4, dtype=object)
print("Creating Points")
self.add_points() # Create values to insert into Points Table
print("Creating Values")
self.add_point_values(
) # Create values to insert into PointValue Table
def __init__(self, building_name):
self.db_connection = DatabaseConnection()
self.building_id = self.db_connection.get_building_id(building_name)
self.generate_rooms()
class SiemensReader:
def __init__(self, input_stream):
self.source = Sources.SIEMENS
self.db_connection = DatabaseConnection()
self.siemens_data = pd.read_csv(input_stream)
tag_json = open(get_data_resource("csv_descriptions/PointDecoder.json"))
self.tag_dict = json_load(tag_json)
self.points_with_ids = {}
def add_to_db(self):
"""
Adds building, rooms, point types, and point to the database
:return: None
"""
finish_lst = []
cant_finish_lst = []
for point_name in self.siemens_data.columns[2:]:
tags = tagName(point_name, self.tag_dict)
if tags is None:
cant_finish_lst.append("No tags for point " + point_name)
continue
building_id, building_name = self._add_building(tags)
room_id = self._add_room(tags, building_name, building_id)
equipment_id = self._add_equipment_box(tags)
point_type_id, point_type = self._add_point_type(tags)
description = self._make_point_description(tags)
point = Point(point_name, room_id, building_id, self.source, point_type_id, description, equipment_id)
point.point_type = point_type
point_id = self.db_connection.add_unique_point(point)
point.id = point_id
self.points_with_ids[point.name] = point
finish_lst.append("Finished for point "+point_name)
for item in finish_lst:
print(item)
for item in cant_finish_lst:
print(item)
print("Was able to successfully add {} points".format(len(finish_lst)))
print("Was NOT able to add {} points".format(len(cant_finish_lst)))
self._add_point_values()
def _add_building(self, tags):
"""
Add unique building from the building tag of a point
Only adds building if not in DB, adds dummy "Carleton Campus" building if no building tag
:param tags: a dictionary of tags for a point
:return: The building id (int) added to the database
"""
building = "Carleton Campus" # dummy building
if "Building" in tags:
building = tags["Building"][0]
building_id = self.db_connection.add_unique_building(building)
return building_id, building
def _add_room(self, tags, building_name, building_id):
"""
Add unique room -- only adds if room not already in DB
:param tags: a dictionary of tags for a point
:param building_name: Building name of point (string)
:return: Room id (int)
"""
room = "{}_Dummy_Room".format(building_name)
if "ROOM" in tags:
room = tags["ROOM"][0]
room_id = self.db_connection.add_unique_room(room, building_id)
return room_id
def _add_equipment_box(self, tags):
"""
Add unique equipment box -- only adds if not in DB
:param tags: a dictionary of tags for a point
:return: equipment box id (int)
"""
if "Equipment" in tags:
description = self.tag_dict[tags["Equipment"][0]]["descriptor"]
equipment_name = tags["Equipment"][0]
if len(tags["Equipment"]) > 1:
equipment_name += tags["Equipment"][1]
equipment_id = self.db_connection.add_unique_equipment_box(equipment_name, description)
return equipment_id
return None
def _make_point_description(self, tags):
"""
Creates the point description as a concatenation of the descriptions of its tags
:param tags: a dictionary of the tags for a point
:return: a string description
"""
description = ""
if "Measurement" in tags:
description += self.tag_dict[tags["Measurement"][0]]["descriptor"]
if "Set Point" in tags:
description += self.tag_dict[tags["Set Point"][0]]["descriptor"]
if "Equipment" in tags:
description += " in " + self.tag_dict[tags["Equipment"][0]]["descriptor"]
if "Room" in tags:
description += " in Room " + tags["ROOM"][0]
if "Building" in tags:
description += " in " + self.tag_dict[tags["Building"][0]]["descriptor"]
return description
def _get_point_type(self, tags):
"""
Gets the measurement or set point type tag in the dictionary of tags
:param tags: a dictionary of tags for a point
:return: the measurement or set point tag (should only be one)
"""
try:
if "Measurement" in tags:
return tags["Measurement"][0]
else:
return tags["Set Point"][0]
except KeyError:
print("This point does not have a type")
return None
def _add_point_type(self, tags):
"""
Gets point type information from tags and adds to databse
:param tags: a dictionary of tags for a point
:return: point type id (int)
"""
point_type_name = self._get_point_type(tags)
description = self.tag_dict[point_type_name]["descriptor"]
if self.tag_dict[point_type_name]["isEnumerated"] == "True":
point_type = PointType(point_type_name, "enumerated", description=description)
point_type.enumeration_values = self.tag_dict[point_type_name]["units"][5:].split("/")
else:
point_type = PointType(point_type_name, "float", description=description)
point_type.units = self.tag_dict[point_type_name]["units"]
point_type.factor = 5 # No longer getting this information, everything should be less than this
point_type_id = self.db_connection.add_unique_point_type(point_type)
return point_type_id, point_type
def _add_point_values(self):
"""
Loops over all points and all values for points, adds to db
:return: None
"""
df = self.siemens_data
keep_cols = ['Date', 'Time']
point_names = [x for x in list(df.columns.values) if x not in keep_cols]
# first melt the point name headers into the table
# so that point name is now a row variable, not a header
df = pd.melt(df, id_vars=['Date', 'Time'], value_vars=point_names, var_name='pointname', value_name='pointvalue')
print("Melted")
# Next combine the date and time columns into one
df['pointtimestamp'] = df['Date'] + ' ' + df['Time']
print("Timestamps Combined")
# transform pointnames to pointids based on the mapping that we constructed earlier
# get rid of cases where we get a null point id- means we couldn't map a type to the point
df['pointid'] = df['pointname'].apply(self._map_point_names_id)
df.dropna(axis=0, how='any', inplace=True)
print('Null Points Dropped')
df['pointid'] = df['pointid'].astype(int)
print("Points Mapped")
# finally get our formatted point values by inputting the row into format_value
if df.empty:
# Just check to make sure we don't have an empty df- otherwise it errors here if we do
print("Invalid insert- double check that the points are successfully getting inserted")
return
df['pointvalue'] = df.apply(self._format_value, axis=1)
# replace all NaNs with None for sql
print("Values Formatted")
# drop all of the old cols
df.drop(['Date', 'Time', 'pointname'], axis=1, inplace=True)
# Now format it to go into the sql
df = df[['pointvalue', 'pointtimestamp', 'pointid']]
print("COPY FROM FORMATTING FINISHED")
self.db_connection.bulk_add_point_values(df)
def _map_point_names_id(self, name):
try:
return self.points_with_ids[name].id
except KeyError:
return None
def _format_value(self, row):
"""
Makes every given value into an integer for storage in db
:param row: pandas dataframe containing a raw_value and a point_name
:return: Formatted value as an int corresponding to the original data
"""
# TODO error catching if value not type expected, what if there is a problem value not in that list
point_name = row['pointname']
raw_value = row['pointvalue']
point = self.points_with_ids[point_name]
problem_values = ["data loss", "no data", "nan", "null"]
if (isinstance(raw_value, str) and raw_value.lower() in problem_values) or pd.isnull(raw_value):
formatted_value = 'None'
elif point.point_type.return_type == "enumerated":
formatted_value = point.point_type.enumeration_values.index(raw_value)
# TODO if it doesn't have that value???, what if value is 'closed' and we expected 'on'/'off'
elif point.point_type.return_type == "float":
formatted_value = float(raw_value) * 10 ** point.point_type.factor
formatted_value = round(formatted_value)
else: # it's an int!
formatted_value = int(raw_value)
return formatted_value
class LucidReader:
def __init__(self, input_stream):
self.source = Sources.LUCID # Use the enumeration for Lucid
self.data = None # Pandas DataFrame for storing CSV
self.point_identities = {}
self.point_values = None
self.db_connection = DatabaseConnection() # Start up connection to DB
self.data = pd.read_csv(input_stream, skiprows=4, dtype=object)
print("Creating Points")
self.add_points() # Create values to insert into Points Table
print("Creating Values")
self.add_point_values(
) # Create values to insert into PointValue Table
def add_points(self):
"""
Adds the points to the points table.
:return: None
"""
successfully_inserted = []
unsuccessfully_inserted = []
point_names = list(
self.data.columns) # Get row that includes all point name data.
for i in range(1, len(point_names)):
# Get the name, the building name (sometimes the same thing) and the units information
try:
name, building_name, description = point_names[i].split(" - ")
name = name + " - " + "(".join(description.split("(")[:-1])
except ValueError:
building_name, description = point_names[i].split(" - ")
name = building_name + " - " + "(".join(
description.split("(")[:-1]) # remove units information
name = name.strip()
building_name = building_name.strip()
# Clean up the unit information
units = description.split(" ")[-1]
units = units.replace("(", "")
units = units.replace(")", "")
try:
building_id = self.db_connection.add_unique_building(
building_name)
room = "{}_Dummy_Room".format(building_name)
room_id = self.db_connection.add_unique_room(room, building_id)
# Create PointType class for Lucid Data Column
point_type = PointType(name=name,
return_type="float",
units=units,
factor=5)
point_type_id = self.db_connection.add_unique_point_type(
point_type)
# Create Point Object from this column header information.
point = Point(name=name,
room_id=room_id,
building_id=building_id,
source_enum_value=Sources.LUCID,
point_type_id=point_type_id,
description=description,
equipment_box=None)
point_id = self.db_connection.add_unique_point(point)
point.id = point_id
self.point_identities[point_names[i]] = point
successfully_inserted.append("Inserted point " + point.name)
except KeyError as e:
print("Error: " + e)
unsuccessfully_inserted.append("Couldn't insert point: " +
point.name)
for item in successfully_inserted:
print(item)
for item in unsuccessfully_inserted:
print(item)
print("Was able to successfully insert {} points.".format(
len(successfully_inserted)))
print("Was NOT able to successfully insert {} points.".format(
len(unsuccessfully_inserted)))
def add_point_values(self):
"""
Loops over all points and all values for points, adds to db
:return: None
"""
df = self.data
keep_cols = ['Timestamp']
point_names = [
x for x in list(df.columns.values) if x not in keep_cols
]
# first melt the point name headers into the table
# so that point name is now a row variable, not a header
df = pd.melt(df,
id_vars=keep_cols,
value_vars=point_names,
var_name='pointname',
value_name='pointvalue')
print("Melted")
# transform pointnames to pointids based on the mapping that we constructed earlier
# get rid of cases where we get a null point id- means we couldn't map a type to the point
df['pointid'] = df['pointname'].apply(self._map_point_names_id)
df = df.rename(columns={'Timestamp': 'pointtimestamp'})
df.dropna(axis=0, how='any', inplace=True)
print('Null Points Dropped')
df['pointid'] = df['pointid'].astype(int)
print("Points Mapped")
# finally get our formatted point values by inputting the row into format_value
if df.empty:
# Just check to make sure we don't have an empty df- otherwise it errors here if we do
print(
"Invalid insert due to null columns- double check that the points and buildings are successfully getting inserted"
)
return
df['pointvalue'] = df.apply(self._format_value, axis=1).astype(str)
df = df[df['pointvalue'] != "Invalid"]
print('Bad Point Values Dropped')
# drop the old col
df.drop(['pointname'], axis=1, inplace=True)
# replace all NaNs with None for sql
print("Values Formatted")
# Now format it to go into the sql
print("COPY FROM FORMATTING FINISHED")
df = df[['pointvalue', 'pointtimestamp', 'pointid']]
self.db_connection.bulk_add_point_values(df)
def _map_point_names_id(self, name):
try:
return self.point_identities[name].id
except KeyError:
return None
def _format_value(self, row):
try:
point_value = float(row['pointvalue'])
if math.isnan(point_value):
return 'None'
if point_value > 0:
# Round point_value to 5 decimal places.
point_value = round(point_value, 5)
# Multiply point_value by 100000 to get as long int
point_value *= 100000
return int(point_value)
except:
return "Invalid"
def __init__(self):
self.db_connection = DatabaseConnection()
self.raw_buildings = self.db_connection.get_buildings()
self.construct_buildings()
self.deduplicate()