def _get_project_events(project_path):
events_sql = """
SELECT
tblDisturbanceEvents.DisturbanceEventID,
tblDisturbanceEvents.DisturbanceGroupScenarioID,
tblDisturbanceGroupScenario.SPUGroupID,
tblDisturbanceGroupScenario.DistTypeID,
tblDisturbanceType.DefaultDistTypeID,
tblDisturbanceEvents.TimeStepFinish,
tblDisturbanceEvents.Efficiency,
tblDisturbanceEvents.DistArea,
tblDisturbanceEvents.MerchCarbonToDisturb,
tblDisturbanceEvents.PropOfRecordToDisturb
FROM tblDisturbanceType INNER JOIN (
tblDisturbanceGroupScenario INNER JOIN tblDisturbanceEvents ON
tblDisturbanceGroupScenario.DisturbanceGroupScenarioID =
tblDisturbanceEvents.DisturbanceGroupScenarioID) ON
tblDisturbanceType.DistTypeID =
tblDisturbanceGroupScenario.DistTypeID
ORDER BY
tblDisturbanceEvents.TimeStepStart,
tblDisturbanceEvents.DisturbanceGroupScenarioID,
tblDisturbanceEvents.DisturbanceEventID,
tblDisturbanceEvents.TimeStepFinish;
"""
project_events = accessdb.as_data_frame(events_sql, project_path)
return project_events
def load_project_level_data(project_db_path):
"""Loads descriptive/metadata tables from the specified CBM3 MS access
project database path.
Args:
project_db_path (str): Path to a CBM-CFS3 project database
Returns:
namespace: A namespace of descriptive pandas.DataFrames
"""
tblDisturbanceType = accessdb.as_data_frame(
"SELECT DistTypeID, DistTypeName, Description, DefaultDistTypeID "
"FROM tblDisturbanceType", project_db_path)
if not (tblDisturbanceType.DistTypeID == 0).any():
# add disturbance type 0
tblDisturbanceType = pd.DataFrame(
columns=[
"DistTypeID", "DistTypeName", "Description",
"DefaultDistTypeID"
],
data=[[0, "Annual Processes", "Annual Processes",
0]]).append(tblDisturbanceType).reset_index(drop=True)
return SimpleNamespace(
tblEcoBoundary=accessdb.as_data_frame("SELECT * FROM tblEcoBoundary",
project_db_path),
tblAdminBoundary=accessdb.as_data_frame(
"SELECT * FROM tblAdminBoundary", project_db_path),
tblSPU=accessdb.as_data_frame(
"SELECT SPUID, AdminBoundaryID, EcoBoundaryID, DefaultSPUID "
"FROM tblSPU", project_db_path),
tblSPUGroup=accessdb.as_data_frame("SELECT * from tblSPUGroup",
project_db_path),
tblSPUGroupLookup=accessdb.as_data_frame(
"SELECT * from tblSPUGroupLookup", project_db_path),
tblDisturbanceType=tblDisturbanceType,
tblClassifiers=accessdb.as_data_frame(
"SELECT ClassifierID, Name FROM tblClassifiers", project_db_path),
tblClassifierValues=accessdb.as_data_frame(
"SELECT * FROM tblClassifierValues", project_db_path),
tblClassifierSetValues=accessdb.as_data_frame(
"SELECT * FROM tblClassifierSetValues", project_db_path),
tblClassifierAggregates=accessdb.as_data_frame(
"SELECT * FROM tblClassifierAggregate", project_db_path))
def mdb_to_delimited(sit_mdb_path, ext, output_dir):
if ext == ".tab":
sep = "\t"
elif ext == ".csv":
sep = ","
else:
raise ValueError()
for k, v in get_db_table_names().items():
df = accessdb.as_data_frame(f"SELECT * FROM {v}", sit_mdb_path)
df.to_csv(os.path.join(output_dir, f"{v}{ext}"), sep=sep, index=False)
def load_archive_index_data(aidb_path):
"""Loads descriptive/metadata tables from the specified CBM3 MS access
archive index database path.
Args:
aidb_path (str): Path to a CBM-CFS3 archive index database.
Returns:
namespace: A namespace of descriptive pandas.DataFrame
"""
aidb_data = SimpleNamespace(
tblEcoBoundaryDefault=accessdb.as_data_frame(
"SELECT EcoBoundaryID, EcoBoundaryName "
"FROM tblEcoBoundaryDefault", aidb_path),
tblAdminBoundaryDefault=accessdb.as_data_frame(
"SELECT AdminBoundaryID, AdminBoundaryName "
"FROM tblAdminBoundaryDefault", aidb_path),
tblSPUDefault=accessdb.as_data_frame(
"SELECT SPUID, AdminBoundaryID, EcoBoundaryID "
"FROM tblSPUDefault", aidb_path),
tblDisturbanceTypeDefault=accessdb.as_data_frame(
"SELECT DistTypeID, DistTypeName, Description "
"FROM tblDisturbanceTypeDefault", aidb_path),
tblUNFCCCLandClass=_load_substituted(aidb_path, "tblUNFCCCLandClass"),
tblKP3334Flags=_load_substituted(aidb_path, "tblKP3334Flags"))
# note in current build v1.2.7739.338 tblKP3334Flags is missing a row,
# and the following lines compensate for that
if len(aidb_data.tblKP3334Flags.index) == 9:
aidb_data.tblKP3334Flags = pd.DataFrame(
columns=["KP3334ID", "Name", "Description"],
data=[[0, "Undetermined",
"Undetermined"]]).append(aidb_data.tblKP3334Flags)
aidb_data.tblKP3334Flags.KP3334ID = list(range(0, 10))
return aidb_data
def _load_df(sql, results_db):
from cbm3_python.cbm3data import accessdb
if isinstance(results_db, str):
return accessdb.as_data_frame(sql, results_db)
else:
return pd.read_sql(sql, results_db)