<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 CSE 6040, Fall 2015 [10-B]
Step1:  Input (CSV) filename
Step2:  Determine output (SQLite DB) filename from the input filename
Step3:  Connect to an SQL data source
Step4:  Convert CSV to SQLite DB
Load the CSV, chunk-by-chunk, into a DataFrame
Process each chunk by doing some minimal data normalization and stripping out uninteresting columns
Append each chunk to the SQLite database
<ASSISTANT_TASK:>
Python Code:
from sqlalchemy import create_engine # database connection
import datetime as dt # for timing
import pandas as pd # for data frames
Explanation: CSE 6040, Fall 2015 [10-B]: CSV to DB
This notebook is optional and was not covered in class. It shows how we converted the raw comma-separated values (CSV) file corresponding to the NYC 311 Complaints database into an SQLite database. It is adapted from the code that appears here.
Setup: sqlalchemy and pandas
End of explanation
#CSV_FILE = 'NYC-311-2M.csv'
CSV_FILE = None
Explanation: Input (CSV) filename
End of explanation
assert CSV_FILE
import re
CSV_BASES = re.findall (r'(.*)\.csv$', CSV_FILE, re.I)
assert len (CSV_BASES) >= 1
CSV_BASE = CSV_BASES[0]
DB_FILE = "%s.db" % CSV_BASE
print ("Converting: %s to %s (an SQLite DB) ..." % (CSV_FILE, DB_FILE))
Explanation: Determine output (SQLite DB) filename from the input filename
End of explanation
disk_engine = create_engine ('sqlite:///%s' % DB_FILE)
Explanation: Connect to an SQL data source
End of explanation
# Convert .csv to .db
CHUNKSIZE = 25000 # Number of rows to read at a time
# List of columns to keep
KEEP_COLS = ['Agency', 'CreatedDate', 'ClosedDate', 'ComplaintType',
             'Descriptor', 'CreatedDate', 'ClosedDate',
             'TimeToCompletion', 'City']
start = dt.datetime.now () # start timer
j = 0
index_start = 1
for df in pd.read_csv(CSV_FILE, chunksize=CHUNKSIZE, iterator=True, encoding='utf-8'):
    
    # Remove spaces from columns
    df = df.rename(columns={c: c.replace(' ', '') for c in df.columns})
    # Convert to proper date+timestamps
    df['CreatedDate'] = pd.to_datetime(df['CreatedDate'])
    df['ClosedDate'] = pd.to_datetime(df['ClosedDate'])
    df.index += index_start
    for c in df.columns:
        if c not in KEEP_COLS:
            df = df.drop (c, axis=1)
    
    j += 1
    print ('{} seconds: completed {} rows'.format((dt.datetime.now() - start).seconds, j*chunksize))
    df.to_sql('data', disk_engine, if_exists='append')
    index_start = df.index[-1] + 1
Explanation: Convert CSV to SQLite DB
Load the CSV, chunk-by-chunk, into a DataFrame
Process each chunk by doing some minimal data normalization and stripping out uninteresting columns
Append each chunk to the SQLite database
End of explanation
<END_TASK>