Hello,
I've put together a time series database from several dataframes:
I've put together a time series database from several dataframes:
import pandas as pd import sqlite3 import quandl from datetime import date, datetime, timezone from functools import reduce import numpy as np api_key = open('quandlapikey.txt', 'r').read() def data_extract(code, column_name): data = quandl.get(code, authtoken=api_key, start_date='2000-01-01', collapse='monthly',) pd.options.display.float_format = '{:,}'.format data['Value'] = data['Value'].round(decimals=0) data.rename(columns={'Value': column_name}, inplace=True) data[column_name + ', Y/Y'] = data[column_name].pct_change(12)*100 data[column_name + ', Y/Y'] = data[column_name + ', Y/Y'].round(decimals=1) return data m2 = data_extract('FRED/M2','M2') m1 = data_extract('FRED/M1','M1') mb = data_extract('FRED/BOGMBASEW', 'Monetary Base') comm_loans = data_extract('FRED/CILDCBM027NBOG', 'Commercial/Industrial Loans') cons_loans = data_extract('FRED/CLSACBW027NBOG', 'Consumer Loans') bank_credit = data_extract('FRED/LOANINVNSA', 'Bank Credit') total_loans = comm_loans['Commercial/Industrial Loans'] + cons_loans['Consumer Loans'] CI_per_total = comm_loans['Commercial/Industrial Loans'] / bank_credit['Bank Credit']*100 df_merged = [mb,m1,m2,comm_loans,cons_loans,bank_credit] df = reduce(lambda left,right: pd.merge(left,right,on='Date', how='outer'), df_merged).fillna('-') df['Total Loans'] = comm_loans['Commercial/Industrial Loans'] + cons_loans['Consumer Loans'] df['Commercial/Industrial Loans % of Total Credit'] = comm_loans['Commercial/Industrial Loans'] / bank_credit['Bank Credit']*100 df['Commercial/Industrial Loans % of Total Credit'] = df['Commercial/Industrial Loans % of Total Credit'].round(decimals=1) conn = sqlite3.connect('Database.db') c = conn.cursor() def data_entry(): df.to_sql('US Money Supply', conn, if_exists='replace', index_label='Date') conn.commit() c.close() conn.close() data_entry()But when the data loads into SQLite, the date index shows in YYYY-MM-DD-HH-MM-SS:
Output:Date
2000-01-31 00:00:00
2000-02-29 00:00:00
2000-03-31 00:00:00
2000-04-30 00:00:00
2000-05-31 00:00:00
2000-06-30 00:00:00
2000-07-31 00:00:00
2000-08-31 00:00:00
I want to change the format for this SQLite index into a regular YYYY-MM-DD but have been unsuccessful so far. I was trying to find a way to do this thinking it would come in the data_entry() function after index_label but had no luck. Any help would be appreciated.