<jupyter_start><jupyter_text>Data Importing  ObjectiveIn this Jupyter Notebook I will be focusing on:- Unzipping provided Data- Exploring the provided Data- Creating a Database to store data for further analysis- Prepare Data for further analysis  Import Libraries<jupyter_code># Import all the necessary libraries
import os #for interacting with path/directory

import numpy as np
import pandas as pd
import sqlite3

from glob import glob<jupyter_output><empty_output><jupyter_text>Load Data into Jupyter Notebook<jupyter_code># Import data into jupyter notebook using glob
csv_files = glob("zippedData/*.csv")

for file in csv_files:
    print(file)
    
tsv_files = glob("zippedData/*.tsv")

for file in tsv_files:
    print(file)<jupyter_output>zippedData/tmdb.movies.csv
zippedData/tn.movie_budgets.csv
zippedData/imdb.name.basics.csv
zippedData/title.akas.csv
zippedData/bom.movie_gross.csv
zippedData/imdb.title.basics.csv
zippedData/title.ratings.csv
zippedData/rt.reviews.tsv
zippedData/rt.movie_info.tsv<jupyter_text>Create dictionary to store Data properly<jupyter_code># Create a dictionary with file names as the Key
# and their contents as the values
# Clean up file names by getting rid or .csv & .tsv and replacing all '.' with '_'
files_dict = {}
for filename in csv_files:
    filename_cleaned = os.path.basename(filename).replace('.csv', '').replace('.', '_')
    filename_df = pd.read_csv(filename, index_col = 0)
    files_dict[filename_cleaned] = filename_df
for filename in tsv_files:
    filename_cleaned = os.path.basename(filename).replace(".tsv", "").replace(".", "_") 
    filename_df = pd.read_csv(filename, index_col=0, delimiter='\t',encoding = 'unicode_escape')
    files_dict[filename_cleaned] = filename_df
# Check Keys to make sure data is stored correctly
for key in files_dict.keys():
    print(key)
# Check Values to make sure data is stored correctly
for value in files_dict.values():
    print(value)<jupyter_output>genre_ids      id original_language  \
0          [12, 14, 10751]   12444                en   
1      [14, 12, 16, 10751]   10191                en   
2            [12, 28, 878]   10138                en   
3          [16, 35, 10751]     862                en   
4            [28, 878, 12]   27205                en   
...                    ...     ...               ...   
26512             [27, 18]  488143                en   
26513             [18, 53]  485975                en   
26514         [14, 28, 12]  381231                en   
26515      [10751, 12, 28]  366854                en   
26516             [53, 27]  309885                en   

                                     original_title  popularity release_date  \
0      <NAME> and the Deathly Hallows: Part 1      33.533   2010-11-19   
1                          How to Train Your Dragon      28.734   2010-03-26   
2                                        Iron Man 2      28.515   2010-05-07   
3      [...]<jupyter_text>Create SQL Database for Data<jupyter_code># this will create a sqlite file in your directory
# add name for sqlite file
db = 'movies_db.sqlite'
# check what we have
if os.path.isfile(db):
    os.remove(db)
    conn = sqlite3.connect(db)
    cur = conn.cursor()
    print("We removed old database with name "+str(db)+" and create new one with the same name")
else: 
    conn = sqlite3.connect(db)
    cur = conn.cursor()
    print("We create a new database "+str(db))
def create_sql_table_from_df(df, name, conn):
    # Use try except
    # it will try to make a table
    # if a table exists the except part of the code will stop the program from making duplicates
    try:
        df.to_sql(name, conn)
        print(f"Created table {name}")
    
    # if the table exists t will tell you, and won't cause an error
    except Exception as e:
        print(f"could not make table {name}")
        print(e)
for name, table in files_dict.items():
    create_sql_table_from_df(table, name, conn)
# Check tables for accuracy
conn.execute('''SELECT name
                FROM sqlite_master 
                WHERE type='table';
                ''').fetchall()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08901790033865506, 'fraction_numerical': 0.04910498306724722, 'mean_word_length': 2.3949096880131364, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 16, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '600440', 'license': 'Apache-2.0', 'n_tokens_mistral': 1424, 'n_tokens_neox': 1258, 'n_words': 407, 'path': 'Importing_Data.ipynb', 'repo_name': 'Princess-1/1_Module_Project', 'size': '4140'}