# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3 (ipykernel)
#     language: python
#     name: python3
# ---

# + pycharm={"name": "#%%\n"}
'''
1.1 data_upload_manifest.ipynb

This routine reads in a manifest of files to be uploaded into the database.  This routine can process
xls, xlsx, and csv formatted files.

The column names of the files will be used as the column names for the database.  The table column names will
standardised as follows:

1. all lowercase
2. spaces converted to underscores
3. periods converted to underscores
4. no leading and training spaces

<NAME>
'''

# + pycharm={"name": "#%%\n"}
# set up modules

from library.common import Database, clean_column_names
import pathlib
import pandas as pd
import numpy as np

# set up source data file location and filename
path_to_data = r'data/raw/manifest'
db = Database()
manifest = 'manifest_20211202_2006.xlsx'

# Get the path to raw data files
tgt_path = pathlib.Path.cwd().parent.joinpath(path_to_data)

# Get the path to manifest file to identify all the files to upload
manifest_loc = tgt_path.joinpath(manifest)
files_to_process = pd.read_excel(manifest_loc)

# Iterate through each data file and upload to the database
for i, f in files_to_process.iterrows():

    print(f"Processing {f[0]}")
    filename = f[0]
    table = f[1].lower()
    file_format = f[2]
    skiprows = f[3]
    load_data = f[4]


    # Enforce naming convention and change all spaces and periods to underscores
    table = table.replace(".", "_")
    table = table.replace(" ", "_")
    table = table.strip()
    full_path = tgt_path.joinpath(filename.strip())
    write_to_db = False

    # the data files will have a header on the first line or the fourth line
    # Assume that the header is on the first line
    if file_format == r"csv":
        df = pd.read_csv(full_path, skiprows = skiprows)
        write_to_db = True
    elif file_format == r"xlsx":
        df = pd.read_excel(full_path, skiprows = skiprows)
        write_to_db == True
    elif file_format == r"xls":
        df = pd.read_excel(full_path, skiprows = skiprows)
        write_to_db == True
    else:
        print(f'File {filename} is NOT processed')

    if write_to_db:
        # Remove unamed columns - since we don't know what they represent
        # we cannot use it
        unnamed_col = [s for s in df.columns if "Unnamed" in s]

        if len(unnamed_col)>0:
            df = df.drop(columns = unnamed_col)

        # Clean column names to remove spaces and % and standardise to lower_case
        df.columns = clean_column_names(df)
        df.to_sql(table, db.config, if_exists = 'replace', index = False, method = 'multi')
print("Upload completed")

StarcoderdataJupyterScriptsDedupFiltered