def get_demolition_data():
demolitions = pd.read_csv('./data/detroit-demolition-permits.tsv',
sep='\t')
demolitions_clean_date = demolitions[~demolitions['PERMIT_ISSUED'].str.
match('01/01/112414')]
def compute_co_ords(x):
site = x['site_location']
if site is np.nan or not re.search('\({1}-*[0-9].*\){1}', site):
return np.nan
else:
if re.search('[A-Z]|[a-z]', site):
parts = site.split('\n')
lat_lng = parts[len(parts) - 1]
else:
lat_lng = site
return list(
map(
lambda x: float(x),
lat_lng.replace('(',
'').replace(')',
'').replace(' ',
'').split(',')))
def compute_subtype(x):
bld_des = x['DESCRIPTION']
if bld_des is np.nan:
return x['BLD_TYPE_USE']
else:
return bld_des
demolitions_coords = demolitions_clean_date.apply(compute_co_ords, axis=1)
demolitions_clean = demolitions_clean_date[pd.notna(demolitions_coords)]
coords_clean = demolitions_coords[pd.notna(demolitions_coords)]
demolitions_cols = demolitions_clean.loc[:, ['PERMIT_NO']]
demolitions_cols['sub_type'] = demolitions_clean.apply(compute_subtype,
axis=1)
demolitions_cols['lat'] = pd.to_numeric(coords_clean.map(lambda x: x[0]))
demolitions_cols['lng'] = pd.to_numeric(coords_clean.map(lambda x: x[1]))
demolitions_cols['address'] = demolitions_clean[
'site_location'].str.replace('\n', ' ').str.replace(r'\(.*\)', '')
demolitions_cols['incident_date'] = ut.get_date(
pd.to_datetime(demolitions_clean['PERMIT_ISSUED'], format='%m/%d/%y'))
demolitions_cols = demolitions_cols.rename(
columns={'PERMIT_NO': 'incident_id'})
unique_demolitions = demolitions_cols.groupby(['incident_id'
]).count().reset_index()
valid_demolitions = unique_demolitions[
unique_demolitions['address'] <= 1]['incident_id'].to_list()
demolitions_cols = demolitions_cols[demolitions_cols['incident_id'].isin(
valid_demolitions)]
demolitions_cols.insert(1, 'incident_type', 'demolitions')
return demolitions_cols
def update_comment(cursor, edited_comment, comment_id):
new_date = utility.get_date()
cursor.execute(
sql.SQL(
"UPDATE comment SET {message} = %s, {submission_time} = %s WHERE {id}=%s;"
).format(message=sql.Identifier('message'),
submission_time=sql.Identifier('submission_time'),
id=sql.Identifier('id')),
[edited_comment, new_date, comment_id])
def add_question_comment(cursor, question_id, message, user_id):
id = utility.generate_comment_id()
question_id = question_id
message = message
edited_count = 0
submission_time = utility.get_date()
cursor.execute(
sql.SQL("INSERT INTO {table} VALUES(%s, %s,NULL,%s,%s,%s,%s);").format(
table=sql.Identifier('comment')),
[id, question_id, message, submission_time, edited_count, user_id])
def add_comment_ans(cursor, answer_id, message, user_id):
id = utility.generate_comment_id()
answer_id = answer_id
message = message
submission_time = utility.get_date()
edited_count = 0
cursor.execute(
sql.SQL(
"INSERT INTO comment VALUES(%s , NULL, %s , %s, %s, %s, %s);"),
[id, answer_id, message, edited_count, submission_time, user_id])
def add_answers(cursor, question_id, message, user_id):
id = utility.generate_answer_id()
submission_time = utility.get_date()
vote_number = 0
question_id = question_id
message = message
image = ''
cursor.execute(
sql.SQL("INSERT INTO {table} VALUES(%s, %s, %s, %s, %s, %s, %s);").
format(table=sql.Identifier('answer')), [
id, submission_time, vote_number, question_id, message, image,
user_id
])
def add_question(cursor, title, message, user_id):
id = utility.generate_question_id()
submission_time = utility.get_date()
view_number = 0
vote_number = 0
title = title
message = message
image = ''
cursor.execute(
sql.SQL("INSERT INTO {table} VALUES(%s, %s, %s,%s,%s,%s,%s,%s);").
format(table=sql.Identifier('question')), [
id, submission_time, view_number, vote_number, title, message,
image, user_id
])
def insert_user(cursor, username, password, email):
creation_date = utility.get_date()
id = utility.generate_user_id()
reputation = 0
query = sql.SQL("INSERT INTO {table} VALUES(%s, %s, %s, %s, %s, %s );"
).format(table=sql.Identifier('users'))
cursor.execute(query, [
id,
password,
email,
creation_date,
reputation,
username,
])