940 lines
36 KiB
Python
940 lines
36 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-
|
|
#Nom : : recup_zh_from_medwet.py
|
|
#Description :
|
|
#Copyright : 2021, CEN38
|
|
#Auteur : Colas Geier
|
|
#Version : 1.0
|
|
|
|
import re
|
|
import pandas as pd
|
|
import pandas_access as mdb
|
|
import numpy as np
|
|
from sqlalchemy import create_engine
|
|
from geoalchemy2 import Geometry
|
|
|
|
|
|
isin_bdd = True
|
|
# Parametres bdd OUT
|
|
user_zh = 'postgres'
|
|
pwd_zh = 'tutu'
|
|
adr_zh = '192.168.60.10'
|
|
base_zh = 'bd_cen'
|
|
con_zh = create_engine('postgresql+psycopg2://{0}:{1}@{2}/{3}'.format(user_zh,pwd_zh,adr_zh,base_zh), echo=False)
|
|
|
|
# Read MS access database
|
|
db_file1 = '/home/colas/Documents/5_BDD/ZONES_HUMIDES/MEDWET_v1.mdb'
|
|
db_file2 = '/home/colas/Documents/5_BDD/ZONES_HUMIDES/MEDWET_V2.mdb'
|
|
db_file74 = '/home/colas/Documents/13_CEN74/medwet2gn_ZH/bdd/Inventaires ZHRMCvivante.mdb'
|
|
# df_med1 = mdb.read_table(db_file1, "SITEINFO")
|
|
# df_med2 = mdb.read_table(db_file2, "SITEINFO")
|
|
|
|
|
|
|
|
#####################################
|
|
### Obsrevation de la zone humide ###
|
|
#####################################
|
|
def _aggr_cols(df, lst_col):
|
|
df['aggreg'] = None
|
|
|
|
for col in lst_col:
|
|
iscol = ~df[col].isna()
|
|
isagg = ~df['aggreg'].isna()
|
|
|
|
df.loc[iscol & isagg, 'aggreg'] = df.loc[iscol & isagg, 'aggreg'] + \
|
|
' ; ' + col + ' : ' + df.loc[iscol & isagg, col]
|
|
df.loc[iscol & ~isagg, 'aggreg'] = col + ' : ' + df.loc[iscol & ~isagg, col]
|
|
|
|
return df
|
|
|
|
def _del_na_col(df):
|
|
for col in df.columns:
|
|
tmp = pd.notna(df[col]).unique()
|
|
if len(tmp) == 1 and False in tmp:
|
|
del df[col]
|
|
return df
|
|
|
|
def __get_auteur__(db_file):
|
|
df = mdb.read_table(db_file, 'MWDKC')
|
|
df = _del_na_col(df)
|
|
df.drop(columns=['FAX', 'E_MAIL', 'COUNTRY', 'PHONE', 'ADDRESS', 'CITY'], inplace=True)
|
|
return df
|
|
|
|
def __get_DicGenREGLE__():
|
|
d = {'CODE':['1','2','3','4','5'], 'DESCR':[
|
|
'protection : nationale (PN) / régionale (PR)',
|
|
'livres rouges : nationale (LN) / régionale (LR)',
|
|
'Directives Habitats (annexe II ou annexe IV)',
|
|
'Directive Oiseaux (annexe I)',
|
|
'autres statuts réglementaires (Convention de Bonn, Convention de Bern…)',
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def __get_DicEsp__(db_file, dic, detail=False):
|
|
df = mdb.read_table(db_file, dic)
|
|
df = _del_na_col(df)
|
|
df.drop(columns=[
|
|
'FRANCE', 'ITALY', 'GREECE', 'SPAIN', 'PORTUGAL',
|
|
'ANNEX_II', 'SPCLIV', 'SPCLV', 'SPBCAX1','IUCN_REDL'], inplace=True)
|
|
df_cols = df.columns
|
|
df_cols = df_cols[df_cols.str.startswith(('SPBONN','ANNEX'))]
|
|
if not detail and not df_cols.empty:
|
|
df.drop(columns=df_cols, inplace=True)
|
|
return df
|
|
|
|
def __get_ObsEsp__(db_file, dic):
|
|
df = mdb.read_table(db_file, dic)
|
|
df = _del_na_col(df)
|
|
return df
|
|
|
|
def __get_SiteInfo__(db_file):
|
|
df = mdb.read_table(db_file, 'SITEINFO')
|
|
df = _del_na_col(df)
|
|
df.drop(columns=[
|
|
'COMPLE_COD', 'AREA_EF', 'AREA_COMP'
|
|
], inplace=True)
|
|
for col in ['PROTOCOLE', 'VALEUR_HUMIDITE']:
|
|
if col in df.columns:
|
|
df.drop(columns=[col], inplace=True)
|
|
return df
|
|
|
|
def __insert_type_esp__(df, type_esp_name):
|
|
for col in df.columns:
|
|
if col == 'ORDER': break
|
|
fix_col = col
|
|
tmp = df.loc[:,:fix_col]
|
|
tmp['TYPE_ESP'] = type_esp_name
|
|
return tmp.join(df.loc[:,'ORDER':])
|
|
|
|
def _get_obs(db_file, tab_obs, dic_obs, detail=False):
|
|
df_obs = __get_ObsEsp__(db_file, tab_obs)
|
|
df_dic = __get_DicEsp__(db_file, dic_obs, detail)
|
|
df = pd.merge(df_obs,df_dic,how='left', left_on='SPEC_COD', right_on='ID_COD')
|
|
if tab_obs == 'OBSFLO':
|
|
df.rename(columns={'FLO_MOIS': 'BRIDDATE_M', 'FLO_YEAR': 'BRIDDATE_Y'}, inplace=True)
|
|
if not detail:
|
|
df.drop(columns=['SPEC_COD','ID_COD'], inplace=True)
|
|
return df
|
|
|
|
def get_obs(db_file, type_obs='all', detail=False):
|
|
'''
|
|
:db_file: str. Nom de la base de données access
|
|
:type_obs: str ou list. Code des observations à extraire de
|
|
la bdd access MEDWET : 'amphi', 'bird', 'flore', 'fish',
|
|
'invert', 'mamm', 'rept'. 'all' si tous.
|
|
:detail: bool.
|
|
'''
|
|
obs_amp = pd.DataFrame()
|
|
obs_brd = pd.DataFrame()
|
|
obs_flo = pd.DataFrame()
|
|
obs_fsh = pd.DataFrame()
|
|
obs_inv = pd.DataFrame()
|
|
obs_mam = pd.DataFrame()
|
|
obs_rep = pd.DataFrame()
|
|
if 'all' == type_obs or 'all' in type_obs:
|
|
type_obs = ['amphi', 'bird', 'flore', 'fish', 'invert', 'mamm', 'rept']
|
|
if 'amphi' == type_obs or 'amphi' in type_obs:
|
|
tab_obs = 'OBSAMP'
|
|
dic_obs = 'DICAMP'
|
|
obs_amp = _get_obs(db_file, tab_obs, dic_obs, detail)
|
|
obs_amp = __insert_type_esp__(obs_amp, 'Amphibien')
|
|
if 'bird' == type_obs or 'bird' in type_obs:
|
|
tab_obs = 'OBSBRD'
|
|
dic_obs = 'DICBRD'
|
|
obs_brd = _get_obs(db_file, tab_obs, dic_obs, detail)
|
|
obs_brd = __insert_type_esp__(obs_brd, 'Oiseau')
|
|
if 'flore' == type_obs or 'flore' in type_obs:
|
|
tab_obs = 'OBSFLO'
|
|
dic_obs = 'DICFLO'
|
|
obs_flo = _get_obs(db_file, tab_obs, dic_obs, detail)
|
|
obs_flo = __insert_type_esp__(obs_flo, 'Flore')
|
|
if 'fish' == type_obs or 'fish' in type_obs:
|
|
tab_obs = 'OBSFSH'
|
|
dic_obs = 'DICFSH'
|
|
obs_fsh = _get_obs(db_file, tab_obs, dic_obs, detail)
|
|
obs_fsh = __insert_type_esp__(obs_fsh, 'Poisson')
|
|
if 'invert' == type_obs or 'invert' in type_obs:
|
|
tab_obs = 'OBSINV'
|
|
dic_obs = 'DICINV'
|
|
obs_inv = _get_obs(db_file, tab_obs, dic_obs, detail)
|
|
obs_inv = __insert_type_esp__(obs_inv, 'Invertébré')
|
|
if 'mamm' == type_obs or 'mamm' in type_obs:
|
|
tab_obs = 'OBSMAM'
|
|
dic_obs = 'DICMAM'
|
|
obs_mam = _get_obs(db_file, tab_obs, dic_obs, detail)
|
|
obs_mam = __insert_type_esp__(obs_mam, 'Mammifère')
|
|
if 'rept' == type_obs or 'rept' in type_obs:
|
|
tab_obs = 'OBSREP'
|
|
dic_obs = 'DICREP'
|
|
obs_rep = _get_obs(db_file, tab_obs, dic_obs, detail)
|
|
obs_rep = __insert_type_esp__(obs_rep, 'Reptile')
|
|
|
|
df_obs = pd.concat([obs_amp,obs_brd,obs_flo,obs_fsh,obs_inv,obs_mam,obs_rep])
|
|
df_obs = _del_na_col(df_obs)
|
|
df_rgl = __get_DicGenREGLE__()
|
|
df_aut = __get_auteur__(db_file)
|
|
if 'REGLE_COD' in df_obs.columns:
|
|
df_obs = pd.merge(df_obs,df_rgl,how='left', left_on='REGLE_COD', right_on='CODE')
|
|
df_obs.rename(columns={'DESCR':'NIV_PROTECT', 'CODE': 'CD_PROTECT'}, inplace=True)
|
|
if not detail:
|
|
df_obs.drop(columns=['REGLE_COD', 'CD_PROTECT'], inplace=True)
|
|
df_obs = pd.merge(df_obs,df_aut,how='left', left_on='AUTEUR', right_on='CODE')
|
|
df_obs.rename(columns={'DESCR':'NOM_AUT', 'CODE': 'CD_AUT'}, inplace=True)
|
|
if not detail:
|
|
df_obs.drop(columns=['AUTEUR', 'CD_AUT'], inplace=True)
|
|
return df_obs
|
|
|
|
|
|
|
|
########################################
|
|
### Fonctionnement de la zone humide ###
|
|
########################################
|
|
def __get_DicGenIN1__():
|
|
d = {'CODE':['1','2','3','4','5','6','7','8','9'], 'DESCR':[
|
|
'mer/océan', "cours d'eau",
|
|
'Canaux/fossés','Sources',
|
|
'nappes','précipitations',
|
|
"plans d'eau",'Ruissellement diffus',
|
|
'Eaux de crues',
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def __get_DicGenIN2__():
|
|
d = {'CODE':['1','2','3','4'], 'DESCR':[
|
|
'permanent','saisonnier',
|
|
'temporaire','intermittent',
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def __get_DicGenOUT__():
|
|
d = {'CODE':['1','2','3','4','5','6','7'], 'DESCR':[
|
|
'mer/océan', "cours d'eau",
|
|
'Canaux/fossés','Pompage, drainage',
|
|
'nappes','évaporation',
|
|
"plans d'eau",
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def __get_DicGenOUT2__():
|
|
d = {'CODE':['1','2','3','4','5'], 'DESCR':[
|
|
'aucune','permanent',
|
|
'saisonnier','temporaire',
|
|
'intermittent',
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def __get_DicGenFREQ__():
|
|
d = {'CODE':['1','2','3','4'], 'DESCR':[
|
|
'Jamais submergé','Toujours submergé',
|
|
'Exceptionnellement submergé',
|
|
'Régulièrement submergé',
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def __get_DicGenETEND__():
|
|
d = {'CODE':['1','2'], 'DESCR':[
|
|
'Totalement submergé',
|
|
'Partiellement submergé',
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def __get_DicGenCONNEX__():
|
|
d = {'CODE':['1','2','3','4','5','6'], 'DESCR':[
|
|
'traversée',
|
|
'entrée et sortie',
|
|
'entrée',
|
|
'sortie',
|
|
'passe à coté',
|
|
'aucune connexion',
|
|
], 'NOMFICH':[
|
|
'MWD1.JPG','MWD2.JPG','MWD3.JPG','MWD4.JPG','MWD5.JPG','MWD6.JPG']}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def get_regHydroEntree(db_file, detail=False):
|
|
'''
|
|
:db_file: str. Nom de la base de données access
|
|
:detail: bool.
|
|
'''
|
|
df_in = mdb.read_table(db_file, 'SITEINFL')
|
|
df_dic1= __get_DicGenIN1__()
|
|
df_dic2= __get_DicGenIN2__()
|
|
df_in = pd.merge(df_in,df_dic1, how='left', left_on='INFLOW1', right_on='CODE')
|
|
df_in.rename(columns={'CODE':'CD_ENTREE_EAU', 'DESCR':'ENTREE_EAU'}, inplace=True)
|
|
df_in = pd.merge(df_in,df_dic2, how='left', left_on='INFLOW2', right_on='CODE')
|
|
df_in.rename(columns={'CODE':'CD_PERM', 'DESCR':'PERM_IN'}, inplace=True)
|
|
if not detail:
|
|
df_in.drop(columns=['INFLOW1','INFLOW2','CD_ENTREE_EAU','CD_PERM'], inplace=True)
|
|
return df_in
|
|
|
|
def get_regHydroSortie(db_file, detail=False):
|
|
'''
|
|
:db_file: str. Nom de la base de données access
|
|
:detail: bool.
|
|
'''
|
|
df_out = mdb.read_table(db_file, 'SITEOUTF')
|
|
df_dic1= __get_DicGenOUT__()
|
|
df_dic2= __get_DicGenOUT2__()
|
|
df_out = pd.merge(df_out,df_dic1, how='left', left_on='OUTFLOW', right_on='CODE')
|
|
df_out.rename(columns={'CODE':'CD_SORTIE_EAU', 'DESCR':'SORTIE_EAU'}, inplace=True)
|
|
if not isinstance(df_out.PERMANENCE,str):
|
|
df_out.PERMANENCE.fillna(0, inplace=True)
|
|
df_out.PERMANENCE = df_out.PERMANENCE.astype(int).astype(str)
|
|
df_out = pd.merge(df_out,df_dic2, how='left', left_on='PERMANENCE', right_on='CODE')
|
|
df_out.rename(columns={'PERMANENCE': 'CD_PERM1','CODE':'CD_PERM2', 'DESCR':'PERM_OUT'}, inplace=True)
|
|
if not detail:
|
|
df_out.drop(columns=['OUTFLOW','CD_SORTIE_EAU','CD_PERM1','CD_PERM2'], inplace=True)
|
|
return df_out
|
|
|
|
def get_regSubmersion(db_file, detail=False):
|
|
'''
|
|
:db_file: str. Nom de la base de données access
|
|
:detail: bool.
|
|
'''
|
|
df_inf = __get_SiteInfo__(db_file)
|
|
df = df_inf[['SITE_COD', 'FREQUENCE', 'ORIGINE', 'ETENDUE']]
|
|
# df.rename(columns={'ORIGINE':'SUB_ORIG'}, inplace=True)
|
|
df_dic1 = __get_DicGenFREQ__()
|
|
df_dic2 = __get_DicGenETEND__()
|
|
df = pd.merge(df,df_dic1, how='left', left_on='FREQUENCE', right_on='CODE')
|
|
df.rename(columns={'ORIGINE':'SUB_ORIG','FREQUENCE':'CD_FREQ1' ,'CODE':'CD_FREQ2', 'DESCR':'SUB_FREQ'}, inplace=True)
|
|
df = pd.merge(df,df_dic2, how='left', left_on='ETENDUE', right_on='CODE')
|
|
df.rename(columns={'ETENDUE':'CD_ETEND1' ,'CODE':'CD_ETEND2', 'DESCR':'SUB_ETEND'}, inplace=True)
|
|
if not detail:
|
|
df.drop(columns=['CD_FREQ1','CD_FREQ2','CD_ETEND1','CD_ETEND2'], inplace=True)
|
|
return df
|
|
|
|
def get_connex(db_file, detail=False):
|
|
df_inf = __get_SiteInfo__(db_file)
|
|
df_inf = df_inf[['SITE_COD', 'CONNEX']]
|
|
df_dic = __get_DicGenCONNEX__()
|
|
df = pd.merge(df_inf, df_dic, how='left', left_on='CONNEX', right_on='CODE')
|
|
df.rename(columns={'CODE':'CD_CONNEX', 'DESCR':'CONNEXION'}, inplace=True)
|
|
if not detail:
|
|
df.drop(columns=['CONNEX','CD_CONNEX','NOMFICH'], inplace=True)
|
|
return df
|
|
|
|
def get_fctmt_zh(db_file, detail=False):
|
|
df_in = get_regHydroEntree(db_file, detail)
|
|
if df_in.COORD_X.max() == 0 : df_in.drop(columns=['COORD_X'], inplace=True)
|
|
if df_in.COORD_Y.max() == 0 : df_in.drop(columns=['COORD_Y'], inplace=True)
|
|
df_out = get_regHydroSortie(db_file, detail)
|
|
if df_out.COORD_X.max() == 0 : df_out.drop(columns=['COORD_X'], inplace=True)
|
|
if df_out.COORD_Y.max() == 0 : df_out.drop(columns=['COORD_Y'], inplace=True)
|
|
df_sub = get_regSubmersion(db_file, detail)
|
|
df_con = get_connex(db_file, detail)
|
|
df_sub_con = pd.merge(df_sub,df_con, how='outer', on='SITE_COD')
|
|
lst_df = {
|
|
'entree_eau': df_in,
|
|
'sortie_eau': df_out,
|
|
'sub_connex': df_sub_con,
|
|
}
|
|
return lst_df
|
|
|
|
|
|
|
|
######################################
|
|
### Delimitation de la zone humide ###
|
|
######################################
|
|
def __get_DicGenLIM__():
|
|
d = {'CODE':['1','2','3','4','5','6','7'], 'DESCR': [
|
|
"hydrologie (balancement des eaux, crues, zones d'inondation, fluctuation de la nappe)",
|
|
'présence ou absence de sols hydromorphes',
|
|
"présence ou absence d'une végétation hygrophile",
|
|
'périodicité des inondations ou saturation du sol en eau',
|
|
'occupation des terres (limite entre les espaces naturels et les milieux anthropisés)',
|
|
'répartition et agencement spatial des habitats (types de milieux)',
|
|
'fonctionnement écologique (espace nécessaire à la biologie des espèces : connexions biologiques, relations entre écosystèmes)'
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def __get_DicGenLIM_1__():
|
|
d = {'CODE':['1','2','3','4','5','6','7','8','9','10','11','12'], 'DESCR':[
|
|
'limites du bassin ou sous-bassin versant',
|
|
'limites des zones inondables',
|
|
"bassin d'alimentation souterrain",
|
|
"zone de recharge d'une nappe",
|
|
'occupation du sol',
|
|
'formations végétales, étages de végétation',
|
|
'limites paysagères',
|
|
'répartition et agencement spatial des habitats (types de milieux)',
|
|
"zone nécessaire à la vie d'une espèce",
|
|
'espace de transition entre des zones humides',
|
|
'zone humide altérée en partie ou totalement, restaurable',
|
|
'non déterminé',
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def _get_espFct(db_file, detail=False):
|
|
'''
|
|
:db_file: str. Nom de la base de données access
|
|
:detail: bool.
|
|
'''
|
|
df = mdb.read_table(db_file, 'EFLIM')
|
|
if not isinstance(df.LIM1_COD,str):
|
|
df.LIM1_COD = df.LIM1_COD.astype(str)
|
|
df_dic = __get_DicGenLIM_1__()
|
|
df = pd.merge(df,df_dic, how='left', left_on='LIM1_COD', right_on='CODE')
|
|
df.rename(columns={'SIT_COD':'SITE_COD', 'DESCR':'DEF_ESPACE_FTC'}, inplace=True)
|
|
if not detail:
|
|
df.drop(columns=['LIM1_COD','CODE'], inplace=True)
|
|
return df
|
|
|
|
def _get_delim(db_file, detail=False):
|
|
'''
|
|
:db_file: str. Nom de la base de données access
|
|
:detail: bool.
|
|
'''
|
|
df = mdb.read_table(db_file, 'SITELIM')
|
|
if not isinstance(df.LIM_COD,str):
|
|
df.LIM_COD = df.LIM_COD.astype(str)
|
|
df_dic = __get_DicGenLIM__()
|
|
df = pd.merge(df,df_dic, how='left', left_on='LIM_COD', right_on='CODE')
|
|
df.rename(columns={'SIT_COD':'SITE_COD', 'DESCR':'CRIT_DELIM'}, inplace=True)
|
|
if not detail:
|
|
df.drop(columns=['LIM_COD','CODE'], inplace=True)
|
|
return df
|
|
|
|
def get_Delim_espaceFct(db_file, detail=False):
|
|
df_espFct = _get_espFct(db_file, detail)
|
|
df_delim = _get_delim(db_file, detail)
|
|
df = pd.merge(df_espFct,df_delim, how='left', on='SITE_COD')
|
|
return df
|
|
|
|
|
|
|
|
#####################################
|
|
### Description de la zone humide ###
|
|
#####################################
|
|
def __get_DicGenIMP__():
|
|
d = {'CODE':[
|
|
'0','10','11.0','12.0','13.0','14.0','15.0','16.0','17.0','20','21.0','22.0','23.0','24.0',
|
|
'30','31.0','32.0','33.0','34.0','35.0','36.0','37.0','38.0','40','41.0','42.0','43.0','44.0',
|
|
'45.0','46.0','47.4','48.0','50','51.0','52.0','53.0','54.0','55.0','61.0','62.0','63.0','64.0',
|
|
'70','71.0','72.0','73.0','74.0','75','75.0','76.0','77.0','78.0','79.0','80.0','81.0','82.0',
|
|
'83.0','84.0','85.0 ','86.0','90','91.0','91.1','91.2 ','91.3','91.4','91.5',
|
|
], 'DESCR':[
|
|
'AUCUN',
|
|
"IMPLANTATION, MODIFICATION OU FONCTIONNEMENT D'INFRASTRUCTURES ET AMENAGEMENTS LOURDS",
|
|
'habitats humain, zone urbanisée','zone industrielle ou commerciale',
|
|
'infrastructure linéaire, réseaux de communication',
|
|
'extraction de matériaux','dépôt de matériaux, décharge',
|
|
'équipement sportif et de loisirs','Infrastructure et équipement agricoles',
|
|
'POLLUTIONS ET NUISANCES',
|
|
'rejets substances polluantes dans les eaux','rejets substances polluantes dans les sols',
|
|
"rejets substances polluantes dans l'atmosphère",'nuisances liées à la surfréquentation, au piétinement',
|
|
'PRATIQUES LIÉES À LA GESTION DES EAUX',
|
|
'comblement, assèchement, drainage, poldérisation des zones humides',
|
|
"mise en eau, submersion, création de plan d'eau",'modification des fonds, des courants',
|
|
'création ou modification des berges et des digues, îles et îlots artificiels, remblais et déblais, fossés',
|
|
"entretien rivières, canaux, fossés, plan d'eau",'modification du fonctionnement hydraulique',
|
|
'action sur la végétation immergée, flottante ou amphibie, y compris faucardage et démottage',
|
|
'pêche professionnelle',
|
|
'PRATIQUES AGRICOLES ET PASTORALES',
|
|
'mise en culture, travaux du sol',
|
|
'débroussaillage, suppression haies et bosquets, remembrement et travaux connexes',
|
|
'jachère, abandon provisoire','traitement de fertilisation et pesticides','pâturage',
|
|
'suppression ou entretien de la végétation fauchage et fenaison',
|
|
'abandon de systèmes culturaux et pastoraux, apparition de friches',
|
|
'plantation de haies et de bosquets',
|
|
'PRATIQUES ET TRAVAUX FORESTIERS',
|
|
'coupes, abattages, arrachages et déboisements','taille, élagage',
|
|
'plantation, semis et travaux connexes',
|
|
'entretien liés à la sylviculture, nettoyage, épandage',
|
|
'autre aménagement forestier, accueil du public, création de pistesPRATIQUES LIEES AUX LOISIRS',
|
|
'Sport et loisir de plein air','Chasse','Pêche','Cueillette et ramassage',
|
|
"PRATIQUES DE GESTION OU D'EXPLOITATION DES ESPÈCES ET HABITATS",
|
|
'prélèvement sur la faune ou la flore',
|
|
'introduction, gestion ou limitation des populations',
|
|
"gestion des habitats pour l'accueil et l'information du public",
|
|
"autre (préciser dans l'encart réservé aux remarques)",
|
|
'PRATIQUES AQUACOLES',
|
|
'aménagements liés à la pisciculture ou à la conchyliculture',
|
|
'fertilisation, amendements','alimentation artificielle','rejets de déchets',
|
|
'vidanges',
|
|
'PROCESSUS NATURELS ABIOTIQUES',
|
|
'érosion','atterrissement, envasement, assèchement','submersion',
|
|
'mouvement de terrain','incendie','catastrophe naturelle',
|
|
'PROCESSUS BIOLOGIQUES ET ÉCOLOGIQUES',
|
|
'évolution écologique, appauvrissement, enrichissement',
|
|
'atterrissement','eutrophisation','acidification',"envahissement d'une espèce",
|
|
'fermeture du milieu',
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def __get_DicGenPOS__():
|
|
d = {'CODE':['0','1','2','3'], 'DESCR':[
|
|
'inconnu','au niveau de la zone humide',
|
|
"au niveau de l'espace de fonctionnalité",
|
|
"au niveau de la zone humide et de l'espace de fonctionnalité",
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def __get_DicACT__(db_file):
|
|
return mdb.read_table(db_file, 'DICACT')
|
|
|
|
def __get_DicGenSDA__():
|
|
d = {'CODE':['0','01','02','03','04','05','06','07','08','9','10','11','12','13'], 'DESCR':[
|
|
'None',
|
|
'grands estuaires',
|
|
'baies et estuaires moyens-plats',
|
|
'marais et lagunes côtiers',
|
|
'marais saumâtres aménagés',
|
|
"bordures de cours d'eau",
|
|
'plaines alluviales',
|
|
'zones humides de bas-fond en tête de bassin versant',
|
|
"régions d'étangs",
|
|
"petits plans d'eau et bordures de plans d'eau",
|
|
'marais et landes humides de plaines et plateaux',
|
|
'zones humides ponctuelles',
|
|
'marais aménagés dans un but agricole',
|
|
'zones humides artificielles',
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def _get_SITEACT(db_file, detail=False):
|
|
df = mdb.read_table(db_file, 'SITEACT')
|
|
df = _del_na_col(df)
|
|
df_dic1 = __get_DicGenIMP__()
|
|
df_dic2 = __get_DicGenPOS__()
|
|
df_dic3 = __get_DicACT__(db_file)
|
|
if 'IMPACT_COD' in df.columns:
|
|
if not isinstance(df.IMPACT_COD,str):
|
|
df.IMPACT_COD = df.IMPACT_COD.astype(str)
|
|
df = pd.merge(df,df_dic1, how='left', left_on='IMPACT_COD', right_on='CODE')
|
|
df.rename(columns={'CODE':'CD_IMP', 'DESCR':'IMPACT'}, inplace=True)
|
|
if not detail:
|
|
df.drop(columns=['IMPACT_COD','CD_IMP'],inplace=True)
|
|
df = pd.merge(df,df_dic2, how='left', left_on='POSITION', right_on='CODE')
|
|
df.rename(columns={'CODE':'CD_LOC', 'DESCR':'LOCALISATION'}, inplace=True)
|
|
df = pd.merge(df,df_dic3, how='left', left_on='ACTIV_COD', right_on='CODE')
|
|
df.rename(columns={'CODE':'CD_ACTIV', 'DESCR':'ACTIVITE_HUM'}, inplace=True)
|
|
if not detail:
|
|
df.drop(
|
|
columns=['COORD_X','COORD_Y','POSITION','CD_LOC','ACTIV_COD','CD_ACTIV'],
|
|
inplace=True)
|
|
return df
|
|
|
|
def _get_SITEIMP(db_file, detail=False):
|
|
df = mdb.read_table(db_file, 'SITEIMP')
|
|
df = _del_na_col(df)
|
|
df_dic1 = __get_DicGenIMP__()
|
|
df_dic2 = __get_DicGenPOS__()
|
|
df_dic3 = __get_DicACT__(db_file)
|
|
if 'IMPACT_COD' in df.columns:
|
|
if not isinstance(df.IMPACT_COD,str):
|
|
df.IMPACT_COD = df.IMPACT_COD.astype(str)
|
|
df = pd.merge(df,df_dic1, how='left', left_on='IMPACT_COD', right_on='CODE')
|
|
df.rename(columns={'CODE':'CD_IMP', 'DESCR':'IMPACT'}, inplace=True)
|
|
if not detail:
|
|
df.drop(columns=['IMPACT_COD','CD_IMP'],inplace=True)
|
|
# df = pd.merge(df,df_dic2, how='left', left_on='POSITION', right_on='CODE')
|
|
# df.rename(columns={'CODE':'CD_LOC', 'DESCR':'LOCALISATION'}, inplace=True)
|
|
df = pd.merge(df,df_dic3, how='left', left_on='ACTIV_COD', right_on='CODE')
|
|
df.rename(columns={'CODE':'CD_ACTIV', 'DESCR':'ACTIVITE_HUM'}, inplace=True)
|
|
if not detail:
|
|
df.drop(
|
|
columns=[
|
|
# 'POSITION','CD_LOC',
|
|
'ACTIV_COD','CD_ACTIV'],
|
|
inplace=True)
|
|
return df
|
|
|
|
def get_usage_process(db_file, detail=False):
|
|
df_sitImp = _get_SITEIMP(db_file, detail)
|
|
df_sitAct = _get_SITEACT(db_file, detail)
|
|
if 'IMPACT' not in df_sitAct.columns:
|
|
df_sitAct.merge(df_sitImp, how='left', on=['SITE_COD', 'ACTIVITE_HUM'])
|
|
# Complexe..........
|
|
# df = (pd.concat([df_sitAct,df_sitImp])
|
|
# .sort_values(by=['SITE_COD','ACTIVITE_HUM','IMPACT'], na_position='last')
|
|
# .drop_duplicates(subset=['SITE_COD','ACTIVITE_HUM','IMPACT'], keep='first')
|
|
# .reset_index(drop=True))
|
|
# dup = df[df.duplicated(subset=['SITE_COD','ACTIVITE_HUM'])].index
|
|
# rmq = df[df.index.isin(dup)].REMARKS.dropna().index
|
|
# df.drop(dup).reset_index(drop=True)
|
|
return df_sitAct
|
|
|
|
def get_sdage(db_file, detail=False):
|
|
df_inf = __get_SiteInfo__(db_file)
|
|
df_inf = df_inf[['SITE_COD','SDAGE_COD']]
|
|
df_dic = __get_DicGenSDA__()
|
|
df = pd.merge(df_inf,df_dic, how='left', left_on='SDAGE_COD', right_on='CODE')
|
|
df.rename(columns={'CODE':'CD_SDAGE', 'DESCR':'TYPO_SDAGE'}, inplace=True)
|
|
if not detail:
|
|
df.drop(columns=['CD_SDAGE','SDAGE_COD'],inplace=True)
|
|
return df
|
|
|
|
def get_descrp_zh(db_file, detail=False):
|
|
df_usgP = get_usage_process(db_file, detail)
|
|
df_sdag = get_sdage(db_file, detail)
|
|
df_usgP = df_usgP.merge(df_sdag, how='outer', on=['SITE_COD'])
|
|
return df_usgP
|
|
|
|
|
|
|
|
#######################################
|
|
### Fonctions eclologiques, valeurs ###
|
|
### socio-économiques, interêt ###
|
|
### patrimonial de la zone humide ###
|
|
#######################################
|
|
def __get_DicGenTYPE__():
|
|
d = {'CODE':['1','2','3','4'], 'DESCR':[
|
|
'Fonctions hydrologiques',
|
|
'Fonctions biologiques',
|
|
'Valeurs socio-économiques',
|
|
'Intérêt patrimonial',
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def __get_DicGenFVI__():
|
|
d = {
|
|
'CODE':['01','02','03','04','05','06','07','08','09','10',
|
|
'20','21','22','23','24','25','26','27','30','31',
|
|
'32','33','34','35','36','40','41','42','43','44',
|
|
'50','51','60','61','62','63','64','70'],
|
|
'TYPE': ['3','3','3','3','3','3','3','3','3','4','4','4',
|
|
'4','4','4','4','4','4','4','4','4','4','4','4','4',
|
|
'1','1','1','1','1','1','1','2','2','2','2','2','2'],
|
|
'DESCR':[
|
|
"réservoir pour l'alimentation en eau potable",
|
|
'production biologique (pâturage; fauche; sylviculture; aquaculture; pêche; chasse)',
|
|
'production de matière première (irrigation; granulats; énergie; tourbe; roseaux; etc.)',
|
|
'intérêt pour la valorisation pédagogique/éducation',
|
|
'intérêt paysager','intérêt pour les loisirs/valeurs récréatives',
|
|
'valeur scientifique','valeur culturelle',
|
|
'nuisances sur les conditions de vie des populations humaines résidentes (transmission parasitaire; moustiques; etc.)',
|
|
'HABITATS','FAUNISTIQUES','invertébrés (sauf insectes)','insectes','poissons',
|
|
'amphibiens','reptiles','oiseaux','mammifères','FLORISTIQUES','algues',
|
|
'champignons','lichens','bryophytes','ptéridophytes','phanérogames',
|
|
'FONCTIONS DE REGULATION HYDRAULIQUE',
|
|
'expansion naturelle des crues (contrôle des crues; écrêtement des crues; stockage des eaux de crues; prévention des inondations)',
|
|
'ralentissement du ruissellement',
|
|
"soutien naturel d'étiage (alimentation des nappes phréatiques; émergence des nappes phréatiques; recharge et protection des nappes phréatiques)",
|
|
"fonctions d'épuration (rétention de sédiments et de produits toxiques; recyclage et stockage de matière en suspension; régulation des cycles trophiques par exportation de matière organique; influence sur les cycles du carbone et de l'azote)",
|
|
'FONCTIONS DE PROTECTION DU MILIEU PHYSIQUE',"rôle naturel de protection contre l'érosion",
|
|
"FONCTION D'HABITAT POUR LES POPULATIONS ANIMALES OU VEGETALES",
|
|
"connexions biologiques (continuité avec d'autres milieux naturels), zone d'échanges, zone de passages, corridor écologique (faune, flore)",
|
|
'étapes migratoires, zones de stationnement, dortoirs',
|
|
"zone particulière d'alimentation pour la faune",'zone particulière liée à la reproduction',
|
|
"AUTRE INTERET FONCTIONNEL D'ORDRE ECOLOGIQUE (préciser dans l'encart réservé aux remarques)",
|
|
]}
|
|
return pd.DataFrame(data=d)
|
|
|
|
def _get_siteFVI(db_file, tab_obs):
|
|
return mdb.read_table(db_file,tab_obs)
|
|
|
|
def get_fct_zh(db_file, type_fct='all', detail=False):
|
|
'''
|
|
:db_file: str. Nom de la base de données access
|
|
:type_fct: str ou list. Code des observations à extraire de
|
|
la bdd access MEDWET : 'amphi', 'bird', 'flore', 'fish',
|
|
'invert', 'mamm', 'rept'. 'all' si tous.
|
|
:detail: bool.
|
|
'''
|
|
df_vib = pd.DataFrame()
|
|
df_vih = pd.DataFrame()
|
|
df_vis = pd.DataFrame()
|
|
df_vip = pd.DataFrame()
|
|
if type_fct == 'all' or 'all' in type_fct:
|
|
type_fct = ['hydro', 'bio', 'socio-eco', 'patri']
|
|
if 'bio' == type_fct or 'bio' in type_fct:
|
|
tab_obs = 'SITEFVIB'
|
|
df_vib = _get_siteFVI(db_file, tab_obs)
|
|
if 'hydro' == type_fct or 'hydro' in type_fct:
|
|
tab_obs = 'SITEFVIH'
|
|
df_vih = _get_siteFVI(db_file, tab_obs)
|
|
if 'socio-eco' == type_fct or 'socio-eco' in type_fct:
|
|
tab_obs = 'SITEFVIS'
|
|
df_vis = _get_siteFVI(db_file, tab_obs)
|
|
if 'patri' == type_fct or 'patri' in type_fct:
|
|
tab_obs = 'SITEFVIP'
|
|
df_vip = _get_siteFVI(db_file, tab_obs)
|
|
df = pd.concat([df_vib,df_vih,df_vis,df_vip])
|
|
df_dic1 = __get_DicGenFVI__()
|
|
df_dic2 = __get_DicGenTYPE__()
|
|
df = pd.merge(df,df_dic1, how='left', left_on='FVI_COD', right_on='CODE')
|
|
df.rename(columns={'CODE': 'CD_FVI','DESCR':'FONCTION'}, inplace=True)
|
|
df = pd.merge(df,df_dic2, how='left', left_on='TYPE', right_on='CODE')
|
|
df.rename(columns={'CODE': 'CD_TYPE','DESCR':'TYPE_FCT'}, inplace=True)
|
|
if not detail:
|
|
df.drop(columns=['FVI_COD','CD_FVI','CD_TYPE','TYPE'],inplace=True)
|
|
|
|
cols = df.columns
|
|
lst_memo = cols[cols.str.startswith('MEMO')]
|
|
df = _aggr_cols(df, lst_memo)
|
|
df.rename(columns={'aggreg': 'MEMO'}, inplace=True)
|
|
df.drop(columns=lst_memo, inplace=True)
|
|
return df
|
|
|
|
|
|
|
|
########################################
|
|
### CORINE BIOTOPE de la zone humide ###
|
|
########################################
|
|
def get_cb(db_file, detail=False):
|
|
df_cb = mdb.read_table(db_file, 'CATCHCBiot')
|
|
df_dic = mdb.read_table(db_file, 'DicGen-CBio')
|
|
df = pd.merge(df_cb,df_dic, how='left', left_on='CB_COD', right_on='CODE')
|
|
df.rename(columns={'DESCR':'DESC_CBIOTOPE'}, inplace=True)
|
|
if not detail:
|
|
df.drop(columns=['CODE'],inplace=True)
|
|
return df
|
|
|
|
|
|
#############################################
|
|
### Evaluation générale de la zone humide ###
|
|
#############################################
|
|
# get_eval_glob()
|
|
def get_eval_glob(db_file):
|
|
df_inf = __get_SiteInfo__(db_file)
|
|
df = df_inf[['SITE_COD','SITE_RAP1','SITE_RAP2','SITE_RAP3','SITE_RAP4']]
|
|
df.columns = ['SITE_COD', 'FCT_VALEURS_MAJEURS', 'INT_PATR_MAJEUR','BILAN_MENACES_FACTEURS', 'ORIENT_ACTIONS']
|
|
return df
|
|
|
|
|
|
|
|
#####################################
|
|
### Référence de la zone humide ###
|
|
#####################################
|
|
def __get_MWDREF__(db_file):
|
|
df = mdb.read_table(db_file, 'MWDREF')
|
|
df = _del_na_col(df)
|
|
return df
|
|
|
|
def get_biblio(db_file, detail=False):
|
|
df_ref = mdb.read_table(db_file, 'SITEREF')
|
|
if not isinstance(df_ref.REF_NO,str):
|
|
df_ref.REF_NO = df_ref.REF_NO.astype(str)
|
|
df_dic = __get_MWDREF__(db_file)
|
|
df = pd.merge(df_ref,df_dic, how='left', on='REF_NO')
|
|
if not detail:
|
|
df.drop(columns=['REF_NO'],inplace=True)
|
|
return df
|
|
|
|
|
|
#####################################
|
|
### Information de la zone humide ###
|
|
#####################################
|
|
def __get_CATCHINF__(db_file):
|
|
df = mdb.read_table(db_file, 'CATCHINF')
|
|
df = df[~df.CATCH_NAME.isna()]
|
|
df = df[['CATCH_COD', 'CATCH_NAME']]
|
|
return df
|
|
|
|
def get_SiteInfo(db_file, detail=False):
|
|
"""Récupération des informations génériques
|
|
des zones humides.
|
|
|
|
Paramètres :
|
|
------------
|
|
db_file : string. Chemin d'accès ver la bdd access MEDWET.
|
|
detail : bool. Affichage des relations entre les tables.
|
|
"""
|
|
|
|
# lst_col = [
|
|
# 'SITE_COD','DEPT', 'ORG','NUM','SITE_NAME','OTHER_NAME','COMPILER',
|
|
# 'EDITDATE','UPDDATE','AREA_WET','COORD_X','COORD_Y','SITE_RAP1','SITE_RAP2',
|
|
# 'SITE_RAP2','SITE_RAP3','SITE_RAP4','OTHER3_COD','OTHER4_COD','OTHER_INV',
|
|
# 'HISTORIQ','ZH_REMARKS','MEMO_ACTIV'] ###### NON FINI !! voir df_inf.columns
|
|
df_inf = __get_SiteInfo__(db_file)
|
|
# if 'CATCH_COD' in df_inf.columns:
|
|
# lst_col += ['CATCH_COD']
|
|
# df_inf = df_inf[lst_col]
|
|
df_inf.drop(columns=['CONNEX','FREQUENCE','ORIGINE','ETENDUE','SDAGE_COD',
|
|
'SITE_RAP1','SITE_RAP2','SITE_RAP3','SITE_RAP4'], inplace=True)
|
|
df_inf.rename(columns={
|
|
'OTHER1_COD':'CD_ZNIEFF_1G',
|
|
'OTHER2_COD':'CD_ZNIEFF_2G',
|
|
'OTHER3_COD':'CD_ZICO',
|
|
'OTHER4_COD':'CD_N2000',
|
|
}, inplace=True)
|
|
df_dic1 = __get_auteur__(db_file)
|
|
df_inf = df_inf.merge(df_dic1,how='left', left_on='COMPILER', right_on='CODE')
|
|
df_inf.rename(columns={'DESCR': 'AUTEUR'}, inplace=True)
|
|
if not detail:
|
|
df_inf.drop(columns=['COMPILER','CODE'], inplace=True)
|
|
|
|
if 'CATCH_COD' in df_inf.columns:
|
|
df_dic2 = __get_CATCHINF__(db_file)
|
|
df_inf = df_inf.merge(df_dic2,how='left', on='CATCH_COD')
|
|
if not detail:
|
|
df_inf.drop(columns=['CATCH_COD'],inplace=True)
|
|
return df_inf
|
|
|
|
|
|
|
|
if __name__ == "__main__":
|
|
|
|
# writer = pd.ExcelWriter('~/Documents/9_PROJETS/1_ZH/inventaire_zh_2021.xlsx', engine='xlsxwriter')
|
|
# workbook=writer.book
|
|
NAME_OUT = '/home/colas/Documents/13_CEN74/medwet2gn_ZH/inventaire_zh74_test.xlsx'
|
|
lst_fct = {
|
|
'Infos générales': get_SiteInfo,
|
|
'Corine Biotope' : get_cb,
|
|
'Délimitation de la zh': get_Delim_espaceFct,
|
|
'Description de la zh' : get_descrp_zh,
|
|
'Fonctmt de la zh': get_fctmt_zh,
|
|
'Fonctions de la zh' : get_fct_zh,
|
|
'Evaluation de la zh': get_eval_glob,
|
|
'Observations' : get_obs,
|
|
'Bibliographie': get_biblio
|
|
}
|
|
print('INIT récupération des données ...')
|
|
df = {}
|
|
for f,fonction in enumerate(lst_fct):
|
|
name = fonction
|
|
fct = lst_fct[fonction]
|
|
df1 = fct(db_file74)
|
|
# df2 = fct(db_file2)
|
|
# if f == 0:
|
|
# lst_stcd = df1[df1.SITE_COD.isin(df2.SITE_COD)].SITE_COD
|
|
# if fct == get_SiteInfo:
|
|
# df2.loc[df2.SITE_COD.isin(lst_stcd), 'OTHER_NAME'] = df1.loc[df1.SITE_COD.isin(lst_stcd),'SITE_NAME'].tolist()
|
|
if isinstance(df1, pd.DataFrame) :#and isinstance(df2, pd.DataFrame):
|
|
# df[f] = pd.concat([df1[~df1.SITE_COD.isin(lst_stcd)], df2])
|
|
df[f] = df1
|
|
df[f].name = name
|
|
elif isinstance(df1, dict) :#and isinstance(df2, dict):
|
|
df[f] = {}
|
|
df[f]['title'] = name
|
|
for d in df1:
|
|
# df[f][d] = pd.concat([df1[d][~df1[d].SITE_COD.isin(lst_stcd)], df2[d]])
|
|
df[f][d] = df1[d]
|
|
|
|
print('INIT écriture du fichier ...')
|
|
# Ecriture des données
|
|
with pd.ExcelWriter(NAME_OUT) as writer:
|
|
for d in df:
|
|
DF = df[d]
|
|
if isinstance(DF, pd.DataFrame):
|
|
DF.to_excel(writer,sheet_name=DF.name,startrow=1 , startcol=0, index=False, header=DF.columns)
|
|
ws = writer.book.active
|
|
writer.sheets[DF.name].cell(1,1,value=DF.name)
|
|
writer.save()
|
|
elif isinstance(DF, dict):
|
|
for i,d in enumerate(DF):
|
|
if d == 'title':
|
|
continue
|
|
if i == 1:
|
|
row = 1
|
|
col = 0
|
|
else:
|
|
col = DF[d].shape[1] + col + 3
|
|
DF[d].to_excel(writer,sheet_name=DF['title'],startrow=row , startcol=col, index=False)
|
|
ws = writer.book.active
|
|
writer.sheets[DF['title']].cell(column=col+1,row=row,value=d)
|
|
writer.save()
|
|
|
|
import sys
|
|
sys.exit('END SCRIPT ...')
|
|
|
|
|
|
|
|
# ws.cell(1,1,value=df_cb.name)
|
|
# writer.save()
|
|
# writer.write_cells(df_cb.name,sheet_name='infos_site',startrow=0,startcol=0)
|
|
# worksheet.write_string(df_inf.shape[0] + 4, 0, df2.name)
|
|
# df2.to_excel(writer,sheet_name='infos_site',startrow=df_inf.shape[0] + 5, startcol=0)
|
|
|
|
# df_inf = get_SiteInfo(db_file)
|
|
# df_inf.name = 'Infos générales'
|
|
# df_cb = get_cb(db_file)
|
|
# df_cb.name = 'Corine Biotope'
|
|
# df_evl = get_eval_glob(db_file)
|
|
# df_evl.name = 'Evaluation de la zh'
|
|
# df_dlm = get_Delim_espaceFct(db_file)
|
|
# df_dlm.name = 'Délimitation de la zh'
|
|
# df_dsc = get_descrp_zh(db_file)
|
|
# df_dsc.name = 'Description de la zh'
|
|
# df_ftm = get_fctmt_zh(db_file)
|
|
# df_ftm['title'] = 'Fonctmt de la zh'
|
|
# df_fct = get_fct_zh(db_file)
|
|
# df_fct.name = 'Fonctions de la zh'
|
|
# df_obs = get_obs(db_file)
|
|
# df_obs.name = 'Observations'
|
|
|
|
|
|
lst_df = [df_inf,df_cb,df_evl,df_dlm,df_dsc,df_ftm,df_fct,df_obs]
|
|
with pd.ExcelWriter(NAME_OUT) as writer:
|
|
for df in lst_df:
|
|
if isinstance(df, pd.DataFrame):
|
|
df.to_excel(writer,sheet_name=df.name,startrow=1 , startcol=0, index=False)
|
|
ws = writer.book.active
|
|
writer.sheets[df.name].cell(1,1,value=df.name)
|
|
writer.save()
|
|
elif isinstance(df, dict):
|
|
for i,d in enumerate(df):
|
|
if d == 'title':
|
|
continue
|
|
if i == 0:
|
|
row = 1
|
|
col = 0
|
|
else:
|
|
col = df[d].shape[1] + col + 3
|
|
df[d].to_excel(writer,sheet_name=df['title'],startrow=row , startcol=col, index=False)
|
|
ws = writer.book.active
|
|
writer.sheets[df['title']].cell(column=col+1,row=row,value=d)
|
|
writer.save()
|
|
|
|
import numpy as np
|
|
df1 = get_SiteInfo(db_file1)
|
|
df2 = get_SiteInfo(db_file2)
|
|
# df1 = get_fctmt_zh(db_file1)
|
|
# df2 = get_fctmt_zh(db_file2)
|
|
lst_stcd = df1[df1.SITE_COD.isin(df2.SITE_COD)].SITE_COD
|
|
# Get same columns
|
|
cols1 = df1.columns
|
|
cols2 = df2.columns
|
|
same_cols = cols1[cols1.isin(cols2)]
|
|
# tmp1 = df1.loc[df1.SITE_COD.isin(lst_stcd),same_cols].sort_values('SITE_COD').reset_index(drop=True)
|
|
# tmp2 = df2.loc[df2.SITE_COD.isin(lst_stcd),same_cols].sort_values('SITE_COD').reset_index(drop=True)
|
|
tmp1 = df1.loc[df1.SITE_COD.isin(lst_stcd),same_cols].sort_values('SITE_COD').set_index('SITE_COD',drop=True)
|
|
tmp2 = df2.loc[df2.SITE_COD.isin(lst_stcd),same_cols].sort_values('SITE_COD').set_index('SITE_COD',drop=True)
|
|
ne_stacked = (tmp1 != tmp2).stack()
|
|
changed = ne_stacked[ne_stacked]
|
|
changed.index.names = ['id', 'col']
|
|
difference_locations = np.where(tmp1 != tmp2)
|
|
changed_from = tmp1.values[difference_locations]
|
|
changed_to = tmp2.values[difference_locations]
|
|
pd.DataFrame({'from': changed_from, 'to': changed_to}, index=changed.index)
|
|
|
|
print(df1[df1.SITE_COD.isin(lst_stcd)].sort_values('SITE_COD').iloc[:,:15])
|
|
print(df2[df2.SITE_COD.isin(lst_stcd)].sort_values('SITE_COD').iloc[:,:15])
|
|
|
|
|
|
|
|
|
|
get_regHydroEntree(db_file)
|
|
get_regHydroSortie(db_file)
|
|
get_regSubmersion(db_file)
|
|
|
|
df = mdb.read_table(db_file, 'SITEINFO')
|
|
df = _del_na_col(df)
|
|
|
|
# Trouver un code parmis les tables dictionnaires
|
|
# paramètres
|
|
code = '26CCRV0012'
|
|
db_file = db_file1
|
|
# run
|
|
not_in = [
|
|
'SIG','DicGen','List','Switchboard','Items', # MEDWET2
|
|
'#Save_DicGen-FVI','~TMPCLP308581','#Save_SITEFVIS', # MEDWET1
|
|
'#Save_SITEFVIH','#Save_SITEFVIB','#Save_SITEFVIP', # MEDWET1
|
|
'$$$$1','$$$$2','#Save_SITEACT','$TEMP_ACTIV$', # MEDWET1
|
|
]
|
|
tmp = mdb.list_tables(db_file)
|
|
lst_t = [t for t in tmp if t.startswith('DicGen') and not t.endswith('TEMP')]
|
|
lst_t = [t for t in tmp]
|
|
for t in lst_t:
|
|
if t in not_in:
|
|
continue
|
|
df = mdb.read_table(db_file, t)
|
|
if not df.empty and 'SITE_COD' in df.columns:
|
|
boolean_findings = df.SITE_COD.str.contains(code)
|
|
total_occurence = boolean_findings.sum()
|
|
if(total_occurence > 0):
|
|
print(t)
|
|
elif not df.empty and not 'SITE_COD' in df.columns:
|
|
print('SITE_COD column is not "%s" '%t)
|
|
|