<jupyter_start><jupyter_text>LOAD NOAA HISTORICAL DATA FROM CSV FILE:<jupyter_code>df = pd.read_csv('C:/Users/rv14996/Power-Rangers/data/NOAA_WAS_monthly_weather_2010_2019.csv')<jupyter_output><empty_output><jupyter_text>VISUALLY INSPECT FIELDS TO KEEP VALID, RELEVANT DATA:<jupyter_code>df.head()
header = "DATE,AWND,CLDD,HTDD,SNOW,TAVG,TMAX,TMIN,WDF2,WDF5,WSF2,WSF5"
for col in header.split(","):
    print(col)
df.drop(columns=['STATION','NAME','LATITUDE','LONGITUDE','ELEVATION','AWND_ATTRIBUTES','CDSD','CDSD_ATTRIBUTES','CLDD_ATTRIBUTES','DP01','DP01_ATTRIBUTES','DP10','DP10_ATTRIBUTES','DSND','DSND_ATTRIBUTES','DSNW','DSNW_ATTRIBUTES','DT00','DT00_ATTRIBUTES','DT32','DT32_ATTRIBUTES','DX32','DX32_ATTRIBUTES','DX70','DX70_ATTRIBUTES','DX90','DX90_ATTRIBUTES','EMNT','EMNT_ATTRIBUTES','EMSD','EMSD_ATTRIBUTES','EMSN','EMSN_ATTRIBUTES','EMXP','EMXP_ATTRIBUTES','EMXT','EMXT_ATTRIBUTES','HDSD','HDSD_ATTRIBUTES','HTDD_ATTRIBUTES','PRCP','PRCP_ATTRIBUTES','SNOW_ATTRIBUTES','TAVG_ATTRIBUTES','TMAX_ATTRIBUTES','TMIN_ATTRIBUTES','WDF2_ATTRIBUTES','WDF5_ATTRIBUTES','WSF2_ATTRIBUTES','WSF5_ATTRIBUTES'],inplace=True)
#Add columns 'WeatherYear' and 'Month'
df['WEATHERYEAR']= df['DATE'].str[0:4]
df['MONTH']= df['DATE'].str[5:7]
df.head()
df.to_csv('C:/Users/rv14996/Power-Rangers/data/NOAA_for_db.csv')
ENDPOINT="dc-energy-benchmarking-project.cetnqm9k1imt.us-east-1.rds.amazonaws.com"
PORT="5432"
USR="postgres"
REGION="us-east-1"
DBNAME="dc_energy_data"
def connect():
    
    # Set up a connection to the postgres server.
    conn_string = "host="+ creds.PGHOST +" port="+ "5432" +" dbname="+ creds.PGDATABASE +" user=" + creds.PGUSER \
                  +" password="+ creds.PGPASSWORD
    
    conn = pg.connect(conn_string)
    print("Connected!")

    # Create a cursor object
    cursor = conn.cursor()
    
    return conn, cursor 
conn, cursor = connect()
cursor.execute(""" CREATE TABLE IF NOT EXISTS noaa_data(
ID integer PRIMARY KEY,
DATE varchar(255),
AWND double precision,
CLDD double precision, 
HTDD double precision,
SNOW double precision,
TAVG double precision,
TMAX double precision,
TMIN double precision,
WDF2 double precision,
WDF5 double precision,
WSF2 double precision,
WSF5 double precision,
WEATHERYEAR integer,
MONTH integer);""")

conn.commit()
#noaa_weather.to_sql('noaa_data', conn, if_exists='append', index=False)
f_contents = open('C:/Users/rv14996/Power-Rangers/data/NOAA_for_db.csv', 'r')
cursor.copy_from(f_contents, 'noaa_data', sep=',')
conn.commit()
cursor.execute("""SELECT * FROM noaa_data LIMIT 5""")
query_results = cursor.fetchall()
print(query_results)
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1665407854984894, 'fraction_numerical': 0.03134441087613293, 'mean_word_length': 8.166089965397925, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '22616', 'license': 'MIT', 'n_tokens_mistral': 1086, 'n_tokens_neox': 1024, 'n_words': 146, 'path': 'ingestion_wrangling/load_historical_weather_db.ipynb', 'repo_name': 'georgetown-analytics/Power-Rangers', 'size': '2648'}