Final variable appending and organization#
Now, all needed variables (chl, cdom, and rrs) have been organized and standardized. For the final dataset, the chlorophyll dataset will be matched to the cdom and rrs and any coincidental matchups appended to the row. All values that are not coincidental need to be concatinated onto the dataframe as well.
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import cartopy
import cartopy.crs as ccrs
import cartopy.feature as cfeature
from cartopy.mpl.gridliner import LONGITUDE_FORMATTER, LATITUDE_FORMATTER
import geopandas as gpd
from datetime import datetime
import os
from matplotlib import ticker
import datetime as dt
import plotly.express as px
import cmocean as cm
import cmocean.cm as cmo
import matplotlib.gridspec as gridspec
import time
import matplotlib.ticker as mticker
#load in chlorophyll and cdom data and rename columns
chl = pd.read_excel(r'Coastal_chl_final\all_chl.xlsx')
cdom = pd.read_excel(r'Coastal_chl_final\all_cdom.xlsx')
chl = chl.rename(columns={'chl': 'chlorophyll', 'chl_a': 'chlorophyll_HPLC'})
---------------------------------------------------------------------------
FileNotFoundError Traceback (most recent call last)
Cell In[2], line 2
1 #load in chlorophyll and cdom data and rename columns
----> 2 chl = pd.read_excel(r'Coastal_chl_final\all_chl.xlsx')
3 cdom = pd.read_excel(r'Coastal_chl_final\all_cdom.xlsx')
4 chl = chl.rename(columns={'chl': 'chlorophyll', 'chl_a': 'chlorophyll_HPLC'})
File ~\AppData\Local\anaconda3\Lib\site-packages\pandas\io\excel\_base.py:495, in read_excel(io, sheet_name, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, date_format, thousands, decimal, comment, skipfooter, storage_options, dtype_backend, engine_kwargs)
493 if not isinstance(io, ExcelFile):
494 should_close = True
--> 495 io = ExcelFile(
496 io,
497 storage_options=storage_options,
498 engine=engine,
499 engine_kwargs=engine_kwargs,
500 )
501 elif engine and engine != io.engine:
502 raise ValueError(
503 "Engine should not be specified when passing "
504 "an ExcelFile - ExcelFile already has the engine set"
505 )
File ~\AppData\Local\anaconda3\Lib\site-packages\pandas\io\excel\_base.py:1550, in ExcelFile.__init__(self, path_or_buffer, engine, storage_options, engine_kwargs)
1548 ext = "xls"
1549 else:
-> 1550 ext = inspect_excel_format(
1551 content_or_path=path_or_buffer, storage_options=storage_options
1552 )
1553 if ext is None:
1554 raise ValueError(
1555 "Excel file format cannot be determined, you must specify "
1556 "an engine manually."
1557 )
File ~\AppData\Local\anaconda3\Lib\site-packages\pandas\io\excel\_base.py:1402, in inspect_excel_format(content_or_path, storage_options)
1399 if isinstance(content_or_path, bytes):
1400 content_or_path = BytesIO(content_or_path)
-> 1402 with get_handle(
1403 content_or_path, "rb", storage_options=storage_options, is_text=False
1404 ) as handle:
1405 stream = handle.handle
1406 stream.seek(0)
File ~\AppData\Local\anaconda3\Lib\site-packages\pandas\io\common.py:882, in get_handle(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)
873 handle = open(
874 handle,
875 ioargs.mode,
(...)
878 newline="",
879 )
880 else:
881 # Binary mode
--> 882 handle = open(handle, ioargs.mode)
883 handles.append(handle)
885 # Convert BytesIO or file objects passed with an encoding
FileNotFoundError: [Errno 2] No such file or directory: 'Coastal_chl_final\\all_chl.xlsx'
#do an outer match, and any columns without chl or chl_a, remove and make their own dataframe. then rename columns and append
chl_cdom_pre = pd.merge(chl, cdom, on=['ID_code'], how='outer').reset_index(drop=True) #merge the twp dataframes on ID_code
chl_cdom1 =chl_cdom_pre[['ID_code', 'datetime_x', 'lat_x', 'lon_x', 'chlorophyll', 'chlorophyll_HPLC','cdom', 'depth_x', 'experiment_x', 'station_x',
'affiliations_x', 'investigators_x','contact', 'cruise_x', 'DOI_url_x', 'HPLC', 'triplicate', 'data_type_flag', 'source_x',
'cast']] #retain all chl columns (with preffix _x) as well as cdom values
chl_cdom1 = chl_cdom1.dropna(subset=['chlorophyll', 'chlorophyll_HPLC'], how='all') #only keep rows with chl. These are rows with chl and cdom
chl_cdom1.columns = chl_cdom1.columns.str.replace('_x', '', regex=False)
cdom_2 = chl_cdom_pre[chl_cdom_pre[['chlorophyll', 'chlorophyll_HPLC']].isna().all(axis=1)] #find the rows that have no chlorophyll values, but do have cdom
cdom_2 = cdom_2[['ID_code', 'datetime_y', 'lon_y', 'lat_y','depth_y', 'cdom', 'DOI_url_y', 'affiliations_y', 'investigators_y','experiment_y', 'cruise_y',
'station_y', 'source_y']] #subset to all the cdom columns (with preffix _y)
cdom_2.columns = cdom_2.columns.str.replace('_y', '', regex=False)
#now, append the dataframe with matching ids and the dataframe without matching ids together
chl_cdom_final = pd.concat([chl_cdom1, cdom_2], ignore_index=True)
#RRS to chl and cdom dataset
rrs = pd.read_excel(r'Coastal_chl_final\all_rrs.xlsx')
rrs = rrs[rrs['wavelength'] <=800].reset_index(drop=True) #reduce to more realistic limit of wavelengths
#first, turn rrs data long format so that there is 1 uinique id for each row / group of wavelengths
def long_to_wide(df):
"""
Transforms long format to wide format (rrs_###) without averaging duplicates,
preserving rows even if they have missing metadata (NaNs).
"""
id_vars = ['source', 'datetime', 'lon', 'lat', 'depth', 'experiment', 'DOI_url','affiliations', 'investigators', 'contact', 'cruise', 'station',
'ID_code']
df_temp = df.copy()
# temporarily fill NaNs with a string so pivot_table doesn't drop them
df_temp[id_vars] = df_temp[id_vars].fillna('MISSING_DATA')
#group by the full id_vars list to ensure the counter perfectly aligns
df_temp['temp_counter'] = df_temp.groupby(id_vars + ['wavelength']).cumcount()
df_wide = df_temp.pivot_table(index=id_vars + ['temp_counter'], columns='wavelength', values='rrs')
new_column_names = [f"rrs_{str(col)}" for col in df_wide.columns]
df_wide.columns = new_column_names
df_wide = df_wide.reset_index()
df_wide = df_wide.drop(columns=['temp_counter'])
return df_wide
rrs_long = long_to_wide(rrs)
rrs_long=rrs_long.replace('MISSING_DATA', np.nan)
rrs_columns = [col for col in rrs_long.columns if str(col).startswith('rrs_')] #grab all rrs columns
chl_cdom_rrs_pre = pd.merge(chl_cdom_final, rrs_long, on=['ID_code'], how='outer').reset_index(drop=True) #merge the two dataframes on ID_Code
chl_cdom_rrs1 =chl_cdom_rrs_pre[['ID_code', 'datetime_x', 'lat_x', 'lon_x', 'chlorophyll', 'chlorophyll_HPLC', 'cdom','depth_x', 'experiment_x',
'station_x', 'affiliations_x','investigators_x', 'contact_x', 'cruise_x', 'DOI_url_x', 'HPLC','triplicate',
'data_type_flag', 'source_x', 'cast']+rrs_columns] #subset to all chl+cdom columns plus rrs_columns
chl_cdom_rrs1.dropna(axis=1, how='all', inplace=True) #remove any empty columns
chl_cdom_rrs1 = chl_cdom_rrs1.dropna(subset=['chlorophyll', 'chlorophyll_HPLC', 'cdom'], how='all') #remove rows without chl or cdom. This dataframe contains all coincidental variables
chl_cdom_rrs1.columns = chl_cdom_rrs1.columns.str.replace('_x', '', regex=False)
#subset dataframe of only rrs values without coincidental values
rrs_2 = chl_cdom_rrs_pre[chl_cdom_rrs_pre[['chlorophyll', 'chlorophyll_HPLC', 'cdom']].isna().all(axis=1)] #grab all columns without chl or cdom
rrs_2=rrs_2[['ID_code', 'source_y','datetime_y', 'lon_y', 'lat_y', 'depth_y', 'experiment_y', 'DOI_url_y','affiliations_y', 'investigators_y',
'contact_y', 'cruise_y','station_y']+rrs_columns] #subset columns to rrs columns (with preffix _y)
rrs_2.columns = rrs_2.columns.str.replace('_y', '', regex=False)
#concatinate the coincidental dataframe to the rrs dataframe that is not coincidental.
chl_cdom_rrs_final = pd.concat([chl_cdom_rrs1, rrs_2], ignore_index=True)
C:\Users\gianna.milton\AppData\Local\Temp\ipykernel_21380\2510421331.py:7: SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame
See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
chl_cdom_rrs1.dropna(axis=1, how='all', inplace=True) #remove any empty columns
#reformat column positions and datetime format
chl_cdom_rrs_final=chl_cdom_rrs_final[['ID_code','source','experiment', 'affiliations','investigators', 'contact', 'cruise', 'DOI_url',
'datetime', 'lat', 'lon','depth','station', 'cast', 'chlorophyll', 'chlorophyll_HPLC', 'triplicate','HPLC','data_type_flag', 'cdom']+rrs_columns]
chl_cdom_rrs_final['datetime'] = chl_cdom_rrs_final['datetime'].apply(pd.to_datetime)
And now you have a full dataset twith all coincidental and non-coincidental variables!