This notebook formats a date and a time column for weather data measurements with a unix timestamp. Each measurement is then inserted into a pumilio database.
weather_filepath – path to excel containing weather measurements, each with a unix timestamp
In [ ]:
weather_filepath = ""
In [2]:
import pandas
import pyprind
from datetime import datetime
from Pymilio import database
In [2]:
weather_data = pandas.read_excel(weather_filepath)
In [3]:
weather_data['WeatherDate'] = weather_data['WeatherDate'].astype('str')
weather_data['WeatherTime'] = weather_data['WeatherTime'].astype('str')
In [4]:
for index, row in weather_data.iterrows():
timestamp = row['timestamp']
dt = datetime.fromtimestamp(timestamp)
date = datetime.strftime(dt, "%Y-%m-%d")
time = datetime.strftime(dt, "%H:%M:%S")
weather_data.set_value(index, 'WeatherDate', date)
weather_data.set_value(index, 'WeatherTime', time)
In [5]:
weather_data = weather_data.drop('timestamp', axis=1)
weather_data = weather_data.drop('LightIntensity', axis=1)
In [3]:
db = database.Pymilio_db_connection(user='pumilio',
database='pumilio',
read_default_file='~/.my.cnf.pumilio')
In [8]:
table_name = 'WeatherData'
column_list = [ n for n in weather_data.columns ]
column_names = ", ".join(column_list)
progress_bar = pyprind.ProgBar(len(weather_data), bar_char='█', title='Progress', monitor=True, stream=1, width=50)
for index, row in weather_data.iterrows():
progress_bar.update(item_id=str(index))
value_list = [ str(v) for v in row.as_matrix() ]
value_strings = "'"
value_strings = value_strings + "', '".join(value_list)
value_strings = value_strings + "'"
#value_strings = value_strings.replace('nan', 'NULL')
statement = """INSERT INTO {0} ({1}) VALUES ({2})""".format(table_name, column_names, value_strings)
db = pumilio_db._connect()
c = db.cursor()
c.execute(statement)
c.close()
db.close()
In [73]:
#weather_data.to_csv("~/Desktop/weather_db.csv", index=False, header=False)