<jupyter_start><jupyter_text>DwC Events. Keister Zooplankton Hood Canal 2012-13 dataUniversity of Washington Pelagic Hypoxia Hood Canal project, Zooplankton dataset.2022-1-12 Parse the data to define and pull out 3 event "types": `cruise`, `stationVisit` and `sample`. The DwC event table will be populated sequentially for each of those event types, in that order, from the most temporally aggregated (cruise) to the most granular (sample). Columns will be populated differently depending on the event type. Since a cruise is a spatial collection of points, a `footprintWKT` polygon and depth ranges are generated and populated.**Notes about fields to use**- mention why I'm not using `id` columns (see Abby's comments)- consider adding `basisOfRecord`- See additional fields used in the LifeWatch dataset: modified, language, rightsHolder, accessRights, institutionCode, datasetName, country (in addition to id, type, eventID, parentEventID, samplingProtocol, eventDate, locationID, waterBody, countryCode, minimumDepthInMeters, maximumDepthInMeters, decimalLatitude, decimalLongitude)**date-time issues**- Are times in UTC (as claimed by the `time` variable) or PDT?? Based on `day_night`, I think they're actually in PDT, not UTC!- Inconsistencies between `time_start` and `day_night`. I've spotted at least one: sample ("20120611UNDm3_200") labelled as "Day" while `time_start` is "23:10". Clearly one of them is wrong. Test for other obvious inconsistencies by setting a time window for Day vs Night and comparing it to `time_start`.- Missing `time_start` values. There are a few such cases, but apparently no corresponding missing `date` values. Still, they lead to `NaT` `time` values. I need to replace the `NaT`'s with a valid datetime, and the only option is to use the `date` and an artificial time value. Start with a fixed value (12:00), then refine it by using 12:00 when `day_night` is `Day` and 23:00 when it's `Night`.- Times capture only the *start* of the sampling event. So, using times as the event end in an interval range would be misleading.**Other comments**- Look up US `countryCode`- Add "Day sampling" / "Night sampling" to `eventRemarks` for sample events (or stationVisit, or both?), based on `day_night` column- Parsing `sample_code` for distinctive information  - Example `sample_code`: "20120611UNDm2_200". Dataset description entry for `sample_code`: "PI issued sample ID; sampling date + Station + D (day) or N (night) + Net code (e.g. m1) \_mesh"  - The upper case letter character before the "m" is D or N (Day or Night). **In a few cases there's an additional character found before the D/N character, but its meaning is not described in the `sample_code` description**- These were monthly cruises lasting about 4 days, in June-October 2012 and 2013 (ie, 8 cruises).<jupyter_code>from datetime import datetime, timedelta, timezone
import json
from pathlib import Path

import numpy as np
import pandas as pd
import geopandas as gpd
data_pth = Path(".")<jupyter_output><empty_output><jupyter_text>Process JSON file containing common mappings and strings<jupyter_code>with open(data_pth / 'common_mappings.json') as f:
    common_mappings = json.load(f)
DatasetCode = common_mappings['datasetcode']
cruises = common_mappings['cruises']
stations = common_mappings['stations']
net_tow = common_mappings['net_tow']

iso8601_format = common_mappings['iso8601_format']
CRS = common_mappings['CRS']<jupyter_output><empty_output><jupyter_text>Pre-process data from csv for Event table  Read the csv file<jupyter_code>sourcecsvdata_pth = data_pth / "sourcedata" / "bcodmo_dataset_682074_data.csv"<jupyter_output><empty_output><jupyter_text>`usecols` defines the columns that will be kept and the order in which they'll be organized<jupyter_code>usecols = [
    'sample_code', 'mesh_size', 'FWC_DS', 
    'station', 'latitude', 'longitude', 
    'date', 'time_start', 'time', 'day_night', 
    'depth_min', 'depth_max'
]

eventsource_df = pd.read_csv(
    sourcecsvdata_pth, 
    skiprows=[1], 
    parse_dates=['time'],
    usecols=usecols
)[usecols]
len(eventsource_df)
eventsource_df.head()<jupyter_output><empty_output><jupyter_text>Some `time` entries are missing (NaT). It looks like it's because the time component is missing, while the date component is available.  Remove duplicates and address `time` gaps<jupyter_code>eventsource_df = eventsource_df.drop_duplicates().sort_values(by='sample_code').reset_index(drop=True)
len(eventsource_df)
eventsource_df[eventsource_df.time.isnull()]<jupyter_output><empty_output><jupyter_text>Use `time` filtering on `NaT` (null) values to assign `time` based on `date` plus an artificial time value (12:00)<jupyter_code># eventsource_df['time'].fillna(value='1999-01-01T00:0000Z', inplace=True)
eventsource_df.loc[eventsource_df['time'].isnull(), 'time'] = eventsource_df['date'].astype(str).apply(
    lambda datestr: datetime(
        int(datestr[:4]), int(datestr[4:6]), int(datestr[-2:]), 
        12, 0, tzinfo=timezone.utc
    )
)

eventsource_df.head(10)<jupyter_output><empty_output><jupyter_text>Extract net_code and "extra token" from `sample_code`- Retain only ones that are not already found among the existing dataframe columns.- Example: "20120611UNDm2_200". Dataset description entry for `sample_code`: "PI issued sample ID; sampling date + Station + D (day) or N (night) + Net code (e.g. m1) \_mesh"- The upper case letter character before the "m" is D or N (Day or Night). **In a few cases there's an additional character found before the D/N character, but its meaning is not described in the `sample_code` description**- Ultimately, try to pull out or create a profile code and profile depth interval code, if appropriate?<jupyter_code>len(eventsource_df.sample_code.unique())<jupyter_output><empty_output><jupyter_text>Parsing steps:- split the new `sample_code` on the "_" delimiter, create two new columns, `token1` and `mesh_size`- From `token1` extract `token2`, the characters between `station_code` and the "_" delimiter- parse `token2`: split on the letter "m", into `token3` and `net_code`; then create `token4` from `token3` by removing the D/N character. `token4` will be empty in most cases, and will be renamed to `extra_sample_token`<jupyter_code>def split_token2(token2):
    token3, net_code = token2.split('m')
    token4 = token3[:-1]
    
    return pd.Series({
        'net_code': 'm'+net_code,
        'extra_sample_token': token4
    })

eventsource_df['token2'] = eventsource_df['sample_code'].apply(
    lambda cd: cd[10:].split('_')[0]
)
eventsource_df = pd.concat([eventsource_df, eventsource_df['token2'].apply(split_token2)], 
                            axis='columns')
eventsource_df.drop(columns='token2', inplace=True)
eventsource_df.head(20)<jupyter_output><empty_output><jupyter_text>Create empty Event dataframeRecords from each event type will be appended to this dataframe, by "type". The type is encoded in the `eventRemarks` column, not in the DwC `type` column, which is not used here explicitly (the type is `Event`).<jupyter_code>event_cols_dtypes = np.dtype(
    [
        ('eventID', str),
        ('eventRemarks', str), 
        ('parentEventID', str),
        ('eventDate', str), 
        ('locationID', str),
        ('locality', str),
        ('decimalLatitude', float),
        ('decimalLongitude', float),
        ('footprintWKT', str),
        ('geodeticDatum', str),
        ('waterBody', str),
        ('countryCode', str), 
        ('minimumDepthInMeters', float),
        ('maximumDepthInMeters', float),
        ('samplingProtocol', str)
    ]
)
event_df = pd.DataFrame(np.empty(0, dtype=event_cols_dtypes))<jupyter_output><empty_output><jupyter_text>Create cruise events - Assign cruise code from `cruises` based on `date`- Use `group_by` to generate cruise `time` start and end- Come up with cruise `eventID`. Come up with a project/dataset code first- Populating `event_df` with cruise events Add cruise-related columns to `eventsource_df`. `date_yyyymm` will be used to link cruise events to stationVisit events<jupyter_code>eventsource_df['date_yyyymm'] = eventsource_df['time'].apply(lambda dt: dt.strftime("%Y%m"))
eventsource_df['cruise_code'] = eventsource_df['date_yyyymm'].apply(lambda s: cruises[s])
eventsource_df.head()<jupyter_output><empty_output><jupyter_text>Create cruise footprintWKT and centroid points<jupyter_code>cruise_stations_df = eventsource_df[['cruise_code', 'latitude', 'longitude']].drop_duplicates()
cruise_stations_gdf = gpd.GeoDataFrame(
    data=cruise_stations_df,
    geometry=gpd.points_from_xy(cruise_stations_df.longitude, cruise_stations_df.latitude, crs=CRS)
)

len(cruise_stations_gdf)
cruise_stations_gdf.head()
cruise_stations_gdf.plot();
cruise_stations_gdf.cruise_code.value_counts()<jupyter_output><empty_output><jupyter_text>Create one convex hull polygon for each cruise<jupyter_code>cruise_footprints_gdf = gpd.GeoDataFrame(
    cruise_stations_gdf.groupby(['cruise_code'])['geometry'].apply(
        lambda geom: geom.unary_union.convex_hull)
)
cruise_footprints_gdf['footprintWKT'] = cruise_footprints_gdf.geometry.to_wkt()
# Add footprint centroid latitude and longitude
cruise_footprints_gdf['decimalLongitude'] = cruise_footprints_gdf.centroid.x.round(3)
cruise_footprints_gdf['decimalLatitude'] = cruise_footprints_gdf.centroid.y.round(3)
cruise_footprints_gdf.reset_index(drop=False, inplace=True)
cruise_footprints_gdf<jupyter_output><empty_output><jupyter_text>**NOTE:** It would be nice to add the R2R cruise code (eg, "CB1002"; `cruise_code` column) to the event table, ideally as an R2R url (eg, https://www.rvdata.us/search/cruise/CB988). But in what column? `eventRemarks` is already being used for a very specific purpose (event type).<jupyter_code>cruise_df = eventsource_df.groupby(['cruise_code', 'date_yyyymm']).agg({
    'time':['min', 'max'],
    'depth_min':['min'],
    'depth_max':['max'],
})
cruise_df.columns = ["_".join(stat) for stat in cruise_df.columns.ravel()]
cruise_df = (
    cruise_df.
    rename(columns={'depth_min_min':'minimumDepthInMeters', 'depth_max_max':'maximumDepthInMeters'})
    .sort_values(by='date_yyyymm')
    .reset_index(drop=False)
)
cruise_df
# This form is for populating eventDate with an iso8601 interval
#     lambda row: "{}/{}".format(row['time_min'].strftime(iso8601_format), 
#                                row['time_max'].strftime(iso8601_format)),

cruise_df['eventDate'] = cruise_df.apply(
    lambda row: "{}".format(row['time_min'].strftime(iso8601_format)),
    axis=1
)
cruise_df = cruise_df.merge(cruise_footprints_gdf, on='cruise_code')
cruise_df['eventID'] = DatasetCode + "_" + cruise_df['cruise_code']
cruise_df['eventRemarks'] = 'cruise'
cruise_df['geodeticDatum'] = CRS
cruise_df['waterBody'] = 'Hood Canal, Puget Sound'
cruise_df['countryCode'] = 'US'
cruise_df.head()<jupyter_output><empty_output><jupyter_text>Populate (append to) the `event_df` table with the cruise events<jupyter_code>event_df = event_df.append(
    cruise_df[['eventID', 'eventRemarks', 'eventDate', 
               'decimalLatitude', 'decimalLongitude', 'footprintWKT', 'geodeticDatum', 
               'waterBody', 'countryCode', 'minimumDepthInMeters', 'maximumDepthInMeters']], 
    ignore_index=True
)
event_df.head()<jupyter_output><empty_output><jupyter_text>Create stationVisit events- Use cruise `eventID` from `eventsource_df` as stationVisit `parentEventID`- Add `stationvisit_code` to `eventsource_df`, for use by the next event type (sample)<jupyter_code>eventsource_df['stationvisit_code'] = (
    eventsource_df['date'].astype(str) + eventsource_df['station'] + eventsource_df['day_night'].str.get(0)
)
eventsource_df.head()
stationvisit_df = eventsource_df.groupby(
    ['date', 'day_night', 'station', 'latitude', 'longitude', 'date_yyyymm', 'stationvisit_code']
).agg({
    'time':['min', 'max'],
    'depth_min':['min'],
    'depth_max':['max'],
})
stationvisit_df.columns = ["_".join(stat) for stat in stationvisit_df.columns.ravel()]
stationvisit_df = (
    stationvisit_df
    .sort_values(by='time_min')
    .reset_index(drop=False)
)

len(stationvisit_df)
stationvisit_df.head(10)
stationvisit_df = stationvisit_df.merge(
    cruise_df[['date_yyyymm', 'eventID', 'waterBody', 'countryCode']],
    how='left', 
    on='date_yyyymm'
)
stationvisit_df.head()
stationvisit_df.rename(
    columns={
        'station':'locationID',
        'latitude':'decimalLatitude',
        'longitude':'decimalLongitude',
        'eventID':'parentEventID',
        'depth_min_min':'minimumDepthInMeters', 
        'depth_max_max':'maximumDepthInMeters',
    },
    inplace=True
)
# This form is for populating eventDate with an iso8601 interval
# stationvisit_df['eventDate'] = stationvisit_df[['time_min', 'time_max']].apply(
#     lambda row: "{}/{}".format(row['time_min'].strftime(iso8601_format), 
#                                row['time_max'].strftime(iso8601_format)),
#     axis=1
# )

stationvisit_df['eventDate'] = stationvisit_df.apply(
    lambda row: "{}".format(row['time_min'].strftime(iso8601_format)),
    axis=1
)
stationvisit_df['eventID'] = stationvisit_df['parentEventID'] + '-' + stationvisit_df['stationvisit_code']
stationvisit_df['eventRemarks'] = 'stationVisit'
stationvisit_df['locality'] = stationvisit_df['locationID'].apply(lambda cd: stations[cd])
stationvisit_df['geodeticDatum'] = CRS<jupyter_output><empty_output><jupyter_text>Verify that no duplicate station `eventID` values are created<jupyter_code>len(stationvisit_df.eventID.unique()) == len(stationvisit_df)
stationvisit_df.head(5)<jupyter_output><empty_output><jupyter_text>Populate (append to) the `event_df` table with the stationVisit events<jupyter_code>event_df = event_df.append(
    stationvisit_df[['eventID', 'eventRemarks', 'parentEventID', 'eventDate', 
                     'decimalLatitude', 'decimalLongitude', 'geodeticDatum',
                     'locationID', 'locality', 'waterBody', 'countryCode', 
                     'minimumDepthInMeters', 'maximumDepthInMeters']], 
    ignore_index=True
)

len(event_df)
event_df.head(12)<jupyter_output><empty_output><jupyter_text>Create individual "sample" events- Each unique `sample_code` will be an event. `sample_code` will be the eventID, possibly prefixed by the dataset code, `UWPHHCZoop`<jupyter_code>sample_df = eventsource_df.copy()
sample_df.head()
sample_df = sample_df.merge(
    stationvisit_df[['stationvisit_code', 'eventID', 'waterBody', 'countryCode', 
                     'locationID', 'locality', 'geodeticDatum']],
    how='left', 
    on='stationvisit_code'
)
sample_df= (
    sample_df
    .rename(columns={
        'sample_code':'eventID',
        'eventID':'parentEventID',
        'latitude':'decimalLatitude',
        'longitude':'decimalLongitude',
        'depth_min':'minimumDepthInMeters',
        'depth_max':'maximumDepthInMeters',
    })
    .sort_values(by='time')
    .reset_index(drop=False)
)
def samplingProtocol(row):
    return (
        f"{net_tow[row['FWC_DS']]} net tow using 0.25 m2 HydroBios MultiNet Multiple Plankton Sampler,"
        f" net code {row['net_code']}, {row['mesh_size']} micron mesh (qualifier: {row['extra_sample_token']})"
    )

sample_df['eventRemarks'] = 'sample'
sample_df['eventDate'] = sample_df['time'].apply(lambda t: t.strftime(iso8601_format))
sample_df['samplingProtocol'] = sample_df.apply(samplingProtocol, axis=1)
sample_df.head()<jupyter_output><empty_output><jupyter_text>Populate (append to) the `event_df` table with the sample events<jupyter_code>event_df = event_df.append(
    sample_df[['eventID', 'eventRemarks', 'parentEventID', 'eventDate', 
               'locationID', 'locality', 'waterBody', 'countryCode', 
               'decimalLatitude', 'decimalLongitude', 'geodeticDatum',
               'minimumDepthInMeters', 'maximumDepthInMeters', 'samplingProtocol']], 
    ignore_index=True
)

len(event_df)
event_df.tail(10)<jupyter_output><empty_output><jupyter_text>Export `event_df` to csv<jupyter_code>event_df.eventRemarks.value_counts()
event_df.to_csv(data_pth / 'aligned_csvs' / 'DwC_event.csv', index=False)<jupyter_output><empty_output><jupyter_text>Package versions<jupyter_code>print(
    f"{datetime.utcnow()} +00:00\n"
    f"pandas: {pd.__version__}, geopandas: {gpd.__version__}"
)<jupyter_output>2022-01-13 02:43:18.890177 +00:00
pandas: 1.3.5, geopandas: 0.10.2
{'fraction_non_alphanumeric': 0.1259688739700946, 'fraction_numerical': 0.014891669209642966, 'mean_word_length': 5.538707102952913, 'pattern_counts': {'":': 1, '<': 84, '<?xml version=': 0, '>': 84, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, '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': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '482724', 'license': 'MIT', 'n_tokens_mistral': 5661, 'n_tokens_neox': 5360, 'n_words': 1314, 'path': 'Keister-dwcEvent.ipynb', 'repo_name': 'nanoos-pnw/obis-keisterhczoop', 'size': '16385'}