<jupyter_start><jupyter_text>Table of Contents  The idea of this notebook is to propose WRI - AQ floods team, a schema for the database where to store the data and at the same time a simple overview of the schema for the analysis microservice.First of all we are going to check all csv files stored in folder flood data.  Second we are going to understand what the scripts stored in script folder does.  Finally propose the model.<jupyter_code>import pandas as pd
import json
import os
import configparser
from sqlalchemy import *
from sqlalchemy_schemadisplay import create_schema_graph
from IPython.display import Image, display

#### Global varible configuration
workPath = '/Users/alicia/Projects/jupyter-geotools-alpine/work/data/aqueduct/data_source/floods/floods_vizzuality/Flood_Data' 
files = []
for (dirpath, dirnames, filenames) in os.walk(workPath):
    files.extend([{'path':os.path.join(dirpath,file), 'fileName': file} for file in filenames if '.csv' in file])
list_of_dfs = [{'data': pd.read_csv(file['path']), 
                'fileName': file['path'],
                'path':file['path']
               } for file in files]
for dataset in list_of_dfs:
    print(dataset['fileName'].split('/')[-1])
    display(dataset['data'].shape)
    display(dataset['data'].columns)
metadata = MetaData()
lookup_assets = Table('lookup_assets', metadata,
    Column('geo_id', Integer, primary_key=True),
    Column('year', Integer, nullable=False),
    Column('model', String(60), nullable=False),
    Column('geom_type', String(20), nullable=False)
)

precalc_agg = Table('precalc_agg', metadata,
    Column('pref_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),
    Column('pref_name', String(40), nullable=False),
    Column('pref_value', String(100))
)

graph = create_schema_graph(metadata=metadata,
                 show_datatypes=True,
                 show_indexes=True,
                 rankdir='LR',
                 concentrate=False)

##Generate png image                 
graph.write_png('dbschema.png')
Image('dbschema.png')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1177298311444653, 'fraction_numerical': 0.004690431519699813, 'mean_word_length': 4.1521739130434785, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '665986', 'license': 'MIT', 'n_tokens_mistral': 655, 'n_tokens_neox': 636, 'n_words': 177, 'path': 'Aqueduct/lab/AQ3-Floods_tool_data-model.ipynb', 'repo_name': 'resource-watch/notebooks', 'size': '2132'}