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, ])