Python Forum
Index Formatting in SQLite
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Index Formatting in SQLite
#1
Hello,

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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  [split] Getting Index Error - list index out of range krishna 2 2,567 Jan-09-2021, 08:29 AM
Last Post: buran
  Getting Index Error - list index out of range RahulSingh 2 6,102 Feb-03-2020, 07:17 AM
Last Post: RahulSingh

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020