def natural_gas_data_to_db(filepath): ''' Sends natural_gas data to a database table named elec_usage. Parameters ---------- filepath: (string) filepath for file containing natural_gas utility data. Returns ------- None ''' assert type(filepath) == str, 'Please provide a file path as a string.' engine = create_mysql_engine(CREDS) ngas_data = preprocess_natural_gas(filepath) data_to_db(ngas_data, 'ngas_usage', engine)
def electricity_data_to_db(filepath): ''' Sends electricity data to a database table named elec_usage. Parameters ---------- filepath: (string) filepath for file containing electricity utility data. Returns ------- None ''' assert type(filepath) == str, 'Please provide a file path as a string.' engine = create_mysql_engine(CREDS) elec_data = preprocess_electricity(filepath) data_to_db(elec_data, 'elec_usage', engine)
def elec_accounts_to_db(filepath): ''' Sends electricity accounts data to database table named elec_accounts. Parameters ---------- filepath: (string) filepath for file containing electricity accounts data. Returns ------- None ''' assert type(filepath) == str, 'Please provide a file path as a string.' engine = create_mysql_engine(CREDS) elec_accounts = read_data(filepath, 'other') elec_accounts = elec_accounts.drop_duplicates() elec_accounts['account_number'] = elec_accounts['account_number'].apply( lambda x: restore_leading_zeros(x, 10)) data_to_db(elec_accounts, 'elec_accounts', engine)
def ngas_accounts_to_db(filepath): ''' Sends natural gas accounts data to database table named ngas_accounts. Parameters ---------- filepath: (string) filepath for file containing natural gas account data. Returns ------- None ''' assert type(filepath) == str, 'Please provide a file path as a string.' engine = create_mysql_engine(CREDS) ngas_accounts = read_data(filepath, 'gas_accounts') ngas_accounts = ngas_accounts.drop_duplicates() ngas_accounts['account_number'] = ngas_accounts['account_number'].apply( lambda x: restore_leading_zeros(x, 13)) ngas_accounts['ert_number'] = ngas_accounts['ert_number'].apply( lambda x: restore_leading_zeros(x, 9)) data_to_db(ngas_accounts, 'ngas_accounts', engine)
def test_data_sends_to_database(): engine = create_mysql_engine('./tests/test_creds.yml') df = pd.DataFrame([['a', 'b'], [1, 2]], columns=['first', 'second']) data_to_db(df, 'sample', engine) result = pd.read_sql(sql='SELECT * FROM sample;', con=engine) assert list(result.columns) == ['first', 'second']
def buildings_data_to_db(filepath): assert type(filepath) == str, 'Please provide a file path as a string.' engine = create_mysql_engine(CREDS) buildings = read_data(filepath, 'buildings') buildings = buildings.replace('NV', math.nan) data_to_db(buildings, 'buildings', engine)
def test_send_elec_data_to_database(): engine = create_mysql_engine('./tests/test_creds.yml') data = preprocess_electricity( '/home/vidal/Projects/cityofchicago/2FM/data/energy/energy.xlsx') data_to_db(data, 'elec_usage', engine)