<jupyter_start><jupyter_text>Data Cleaning<jupyter_code># Dependencies
import pandas as pd
from datetime import datetime as dt

from sqlalchemy import create_engine

import sqlalchemy
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import inspect

from sqlalchemy import Column, Float, Integer, String
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
perth = pd.read_csv("static/data/Perth.csv")
west_perth = pd.read_csv("static/data/WestPerth.csv")
east_perth = pd.read_csv("static/data/EastPerth.csv")
crawley = pd.read_csv("static/data/Crawley.csv")
nedlands = pd.read_csv("static/data/Nedlands.csv")
northbridge = pd.read_csv("static/data/Northbridge.csv")

northbridge
frames = [perth, west_perth, east_perth, crawley, nedlands, northbridge]

cityofperth_df = pd.concat(frames)

cityofperth_df
cityofperth_df = cityofperth_df.dropna()

cityofperth_df
#to remove '$' from price
cityofperth_df['price'] = cityofperth_df['price'].map(lambda x: x.lstrip('$'))

#to remove ',' from price
cityofperth_df['price'] = cityofperth_df['price'].str.replace(',', '')
cityofperth_df['price']
#to remove '$' and 'pw' from rent
cityofperth_df['rent'] = cityofperth_df['rent'].map(lambda x: x.lstrip('$').rstrip('pw'))
cityofperth_df['rent']
cityofperth_df.dtypes
#convert all datatypes to be able to fit in SQLite
cityofperth_df['address'] = cityofperth_df['address'].astype('string')
cityofperth_df['suburb'] = cityofperth_df['suburb'].astype('string')
cityofperth_df['state'] = cityofperth_df['state'].astype('string')
cityofperth_df['full_address'] = cityofperth_df['full_address'].astype('string')
cityofperth_df['type'] = cityofperth_df['type'].astype('string')
cityofperth_df['price'] = cityofperth_df['price'].astype('float64')
cityofperth_df['sold date'] = cityofperth_df['sold date'].astype('datetime64[ns]')
cityofperth_df['Land size'] = cityofperth_df['Land size'].astype('float64')
# cityofperth_df['Year Built'] = cityofperth_df['Year Built'].astype('datetime64[ns]')
cityofperth_df['rent'] = cityofperth_df['rent'].astype('float64')
# cityofperth_df['rent date'] = cityofperth_df['rent date'].astype('datetime64[ns]')
cityofperth_df['agent'] = cityofperth_df['agent'].astype('string')

cityofperth_df.dtypes
cityofperth_df.head(5)
# renaming columns to have the similar formatting
cityofperth_df = cityofperth_df.rename(columns= {'Year Built':'built_date', 'Land size':'land_size',
                                                 'Building size':'building_size', 'sold date':'sold_date' , 
                                                 'car space':'car_space', 'rent date':'rent_date' ,
                                                'type':'property_type'})
cityofperth_df.to_csv('static/data/cityofperth.csv')
cityofperth_df.dtypes
cityofperth_df.columns
cityofperth_df =  cityofperth_df[['full_address', 'price',
       'sold_date', 'property_type', 'bedrooms', 'bathrooms', 'car_space',
       'land_size', 'building_size', 'built_date', 'rent', 'rent_date',
       'agent', 'lat', 'lng', 'address', 'suburb', 'state', 'postcode']]
cityofperth_df.head()
# Change date columns to string
cityofperth_df['sold_date'] = cityofperth_df['sold_date'].dt.strftime('%Y-%m')
cityofperth_df['built_date'] = cityofperth_df['built_date'].dt.strftime('%Y')
cityofperth_df['rent_date'] = cityofperth_df['rent_date'].dt.strftime('%Y-%m')
# Change date columns to string
cityofperth_df['sold_date'] = cityofperth_df['sold_date'].astype('string')
cityofperth_df['built_date'] = cityofperth_df['built_date'].astype('string')
cityofperth_df['rent_date'] = cityofperth_df['rent_date'].astype('string')
cityofperth_df.head()
cityofperth_df.dtypes<jupyter_output><empty_output><jupyter_text>Export DataFrame to SQLite table<jupyter_code># Establish database connection
engine = create_engine('sqlite:///SQL/CityOfPerth.sqlite')
conn = engine.connect()
# Define our perthcity table
class PerthCity(Base):
    __tablename__ = 'perthcity'
    sale_id = Column(Integer, primary_key=True)
    full_address = Column(String)
    price = Column(Float)
    sold_date = Column(String)
    property_type = Column(String)
    bedrooms = Column(Float)
    bathrooms = Column(Float)
    car_space = Column(Float)
    land_size = Column(Float)
    building_size = Column(Float)
    built_date = Column(String)
    rent = Column(Float)
    rent_date = Column(String)
    agent = Column(String)
    lat = Column(Float)
    lng = Column(Float)
    address = Column(String)
    suburb  = Column(String)
    state = Column(String)
    postcode = Column(Integer)
# # Define our perthcity table
# class PerthCity(Base):
#     __tablename__ = 'perthcity'
#     full_address = Column(VARCHAR, primary_key=True)
#     price = Column(Float)
#     sold_date = Column(varchar)
#     property_type = Column(varchar)
#     bedrooms = Column(Float)
#     bathrooms = Column(Float)
#     car_space = Column(Float)
#     land_size = Column(Float)
#     building_size = Column(Float)
#     built_date = Column(varchar)
#     rent = Column(Float)
#     rent_date = Column(varchar)
#     agent = Column(varchar)
#     lat = Column(Float)
#     lng = Column(Float)
#     address = Column(varchar)
#     suburb  = Column(varchar)
#     state = Column(varchar)
#     postcode = Column(Integer)
# Create the table within the database
Base.metadata.create_all(engine)
# Load data into table
cityofperth_df.to_sql('perthcity', engine, if_exists="append", index=False)
# Reflect the database tables
Base = automap_base()
Base.prepare(engine, reflect=True)
# Print all of the classes mapped to the Base
Base.classes.keys()
# Create a session
session = Session(engine)
# Assign the class to related variable
PerthCity = Base.classes.perthcity
# Use the session to query PerthCity table 
for row in session.query(PerthCity.sale_id, PerthCity.full_address, PerthCity.price, PerthCity.sold_date).limit(20).all():
    print(row)
cityofperth_df.columns<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup