CDOM Final Concatination#
Combining all cdom datasets into single dataset
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
seabass = pd.read_excel(r'C:\Users\gianna.milton\Documents\Python\Coastal_chl_final\SB_cdom_na.xlsx')
seabass = seabass.rename(columns={'identifier_product_doi':'DOI_url'})
seabass=seabass[['datetime', 'lon', 'lat', 'DOI_url', 'affiliations','investigators', 'experiment', 'cruise', 'station', 'depth','cdom']]
seabass['source']='SeaBASS'
---------------------------------------------------------------------------
KeyboardInterrupt Traceback (most recent call last)
Cell In[2], line 1
----> 1 seabass = pd.read_excel(r'C:\Users\gianna.milton\Documents\Python\Coastal_chl_final\SB_cdom_na.xlsx')
2 seabass = seabass.rename(columns={'identifier_product_doi':'DOI_url'})
3 seabass=seabass[['datetime', 'lon', 'lat', 'DOI_url', 'affiliations','investigators', 'experiment', 'cruise', 'station', 'depth','cdom']]
File ~\AppData\Local\anaconda3\Lib\site-packages\pandas\io\excel\_base.py:508, 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)
502 raise ValueError(
503 "Engine should not be specified when passing "
504 "an ExcelFile - ExcelFile already has the engine set"
505 )
507 try:
--> 508 data = io.parse(
509 sheet_name=sheet_name,
510 header=header,
511 names=names,
512 index_col=index_col,
513 usecols=usecols,
514 dtype=dtype,
515 converters=converters,
516 true_values=true_values,
517 false_values=false_values,
518 skiprows=skiprows,
519 nrows=nrows,
520 na_values=na_values,
521 keep_default_na=keep_default_na,
522 na_filter=na_filter,
523 verbose=verbose,
524 parse_dates=parse_dates,
525 date_parser=date_parser,
526 date_format=date_format,
527 thousands=thousands,
528 decimal=decimal,
529 comment=comment,
530 skipfooter=skipfooter,
531 dtype_backend=dtype_backend,
532 )
533 finally:
534 # make sure to close opened file handles
535 if should_close:
File ~\AppData\Local\anaconda3\Lib\site-packages\pandas\io\excel\_base.py:1616, in ExcelFile.parse(self, sheet_name, header, names, index_col, usecols, converters, true_values, false_values, skiprows, nrows, na_values, parse_dates, date_parser, date_format, thousands, comment, skipfooter, dtype_backend, **kwds)
1576 def parse(
1577 self,
1578 sheet_name: str | int | list[int] | list[str] | None = 0,
(...)
1596 **kwds,
1597 ) -> DataFrame | dict[str, DataFrame] | dict[int, DataFrame]:
1598 """
1599 Parse specified sheet(s) into a DataFrame.
1600
(...)
1614 >>> file.parse() # doctest: +SKIP
1615 """
-> 1616 return self._reader.parse(
1617 sheet_name=sheet_name,
1618 header=header,
1619 names=names,
1620 index_col=index_col,
1621 usecols=usecols,
1622 converters=converters,
1623 true_values=true_values,
1624 false_values=false_values,
1625 skiprows=skiprows,
1626 nrows=nrows,
1627 na_values=na_values,
1628 parse_dates=parse_dates,
1629 date_parser=date_parser,
1630 date_format=date_format,
1631 thousands=thousands,
1632 comment=comment,
1633 skipfooter=skipfooter,
1634 dtype_backend=dtype_backend,
1635 **kwds,
1636 )
File ~\AppData\Local\anaconda3\Lib\site-packages\pandas\io\excel\_base.py:778, in BaseExcelReader.parse(self, sheet_name, header, names, index_col, usecols, dtype, true_values, false_values, skiprows, nrows, na_values, verbose, parse_dates, date_parser, date_format, thousands, decimal, comment, skipfooter, dtype_backend, **kwds)
775 sheet = self.get_sheet_by_index(asheetname)
777 file_rows_needed = self._calc_rows(header, index_col, skiprows, nrows)
--> 778 data = self.get_sheet_data(sheet, file_rows_needed)
779 if hasattr(sheet, "close"):
780 # pyxlsb opens two TemporaryFiles
781 sheet.close()
File ~\AppData\Local\anaconda3\Lib\site-packages\pandas\io\excel\_openpyxl.py:615, in OpenpyxlReader.get_sheet_data(self, sheet, file_rows_needed)
613 data: list[list[Scalar]] = []
614 last_row_with_data = -1
--> 615 for row_number, row in enumerate(sheet.rows):
616 converted_row = [self._convert_cell(cell) for cell in row]
617 while converted_row and converted_row[-1] == "":
618 # trim trailing empty elements
File ~\AppData\Local\anaconda3\Lib\site-packages\openpyxl\worksheet\_read_only.py:85, in ReadOnlyWorksheet._cells_by_row(self, min_col, min_row, max_col, max_row, values_only)
77 with self._get_source() as src:
78 parser = WorkSheetParser(src,
79 self._shared_strings,
80 data_only=self.parent.data_only,
81 epoch=self.parent.epoch,
82 date_formats=self.parent._date_formats,
83 timedelta_formats=self.parent._timedelta_formats)
---> 85 for idx, row in parser.parse():
86 if max_row is not None and idx > max_row:
87 break
File ~\AppData\Local\anaconda3\Lib\site-packages\openpyxl\worksheet\_reader.py:156, in parse()
File ~\AppData\Local\anaconda3\Lib\xml\etree\ElementTree.py:1238, in iterparse.<locals>.iterator(source)
1236 yield from pullparser.read_events()
1237 # load event buffer
-> 1238 data = source.read(16 * 1024)
1239 if not data:
1240 break
File ~\AppData\Local\anaconda3\Lib\zipfile\__init__.py:989, in ZipExtFile.read(self, n)
987 self._offset = 0
988 while n > 0 and not self._eof:
--> 989 data = self._read1(n)
990 if n < len(data):
991 self._readbuffer = data
File ~\AppData\Local\anaconda3\Lib\zipfile\__init__.py:1065, in ZipExtFile._read1(self, n)
1063 elif self._compress_type == ZIP_DEFLATED:
1064 n = max(n, self.MIN_READ_SIZE)
-> 1065 data = self._decompressor.decompress(data, n)
1066 self._eof = (self._decompressor.eof or
1067 self._compress_left <= 0 and
1068 not self._decompressor.unconsumed_tail)
1069 if self._eof:
KeyboardInterrupt:
ioos = pd.read_excel(r'C:\Users\gianna.milton\Documents\Python\Coastal_chl_final\ioos_cdom_na.xlsx')
ioos = ioos.rename(columns={'url':'DOI_url','Institution':'affiliations','date':'datetime','Dataset ID':'station'})
ioos=ioos[['datetime', 'lat', 'lon', 'cdom', 'depth', 'source', 'affiliations', 'DOI_url', 'experiment','station']]
bcodmo = pd.read_excel(r'C:\Users\gianna.milton\Documents\Python\Coastal_chl_final\bco_dmo_cdom_qc.xlsx')
bcodmo = bcodmo.rename(columns={'url':'DOI_url'})
bcodmo=bcodmo[['datetime', 'lat', 'lon', 'depth', 'cruise', 'cdom', 'experiment', 'source', 'investigators', 'affiliations', 'DOI_url',
'station']]
dfs=[seabass,ioos,bcodmo]
all_cdom = pd.concat(dfs).reset_index(drop=True)
all_cdom = all_cdom.dropna(subset=['cdom'])
all_cdom = all_cdom.drop_duplicates()
All rows should have unique ID sample tags, so those will be made from source, experiment, datetime, lat, lon, and depth
#first, create a temporary column that has the xperiment names but without special charecters (_,-,',(,[)
all_cdom['temp_exp'] = all_cdom['experiment'].str.replace('_', '', regex=False)
all_cdom['temp_exp'] = all_cdom['temp_exp'].str.replace('-', '', regex=False)
all_cdom['temp_exp'] = all_cdom['temp_exp'].str.replace(' ', '', regex=False)
all_cdom['temp_exp'] = all_cdom['temp_exp'].str.replace('(', '', regex=False)
all_cdom['temp_exp'] = all_cdom['temp_exp'].str.replace(')', '', regex=False)
all_cdom['temp_exp'] = all_cdom['temp_exp'].str.replace('[', '', regex=False)
all_cdom['temp_exp'] = all_cdom['temp_exp'].str.replace(']', '', regex=False)
all_cdom['ID_code'] = all_cdom['source'].astype(str) + '_' + all_cdom['temp_exp'].astype(str) + '_' + all_cdom['datetime'].dt.strftime('%Y%m%d-%H%M%S').astype(str) + '_' + all_cdom['lat'].astype(str) + '_' + all_cdom['lon'].astype(str) +'_' + all_cdom['depth'].astype(str)+'m'
#add sequential sample number to repeated id tags
all_cdom['ID_code'] = all_cdom['ID_code'] + '_' + all_cdom.groupby('ID_code').cumcount().astype(str)
all_cdom=all_cdom[['datetime','lon','lat','depth','cdom','DOI_url','affiliations','investigators','experiment', 'cruise', 'station', 'source','ID_code']]
Next, all negative cdom values were removed
all_cdom=all_cdom[all_cdom['cdom']>=0]
#all_cdom.to_excel('all_cdom.xlsx', index = False)
Plots#
cdom = pd.read_excel(r'C:\Users\gianna.milton\Documents\Python\Coastal_chl_final\all_cdom.xlsx')
category_counts = cdom['source'].value_counts()
plt.figure(figsize=(5, 5)) # Optional: set the figure size
category_counts.plot.pie(autopct='%1.1f%%', startangle=90, cmap='tab10')
plt.axis('equal')
plt.show()
year_test=cdom.copy()
year_test['datetime'] = pd.to_datetime(year_test['datetime'])
year_test['year'] = year_test['datetime'].dt.year
grouped = year_test.groupby(['year', 'source']).size().reset_index(name='DataPoints')
# Create bar chart
fig = px.bar(grouped, x='year', y='DataPoints', color='source', title='Yearly distribution all data',
labels={'year': 'Year', 'DataPoints': 'Number of Data Points', 'metadata': 'Metadata'},)
fig.update_xaxes(range=[1999,2027])
fig.update_layout(barmode='stack') # ensures stacking
fig.show()
from matplotlib.colors import LogNorm # Important for high-variance data
fig = plt.figure(figsize=(15, 10))
ax = fig.add_subplot(1, 1, 1, projection=ccrs.PlateCarree())
ax.add_feature(cfeature.LAND)
ax.add_feature(cfeature.OCEAN)
ax.add_feature(cfeature.COASTLINE)
ax.add_feature(cfeature.BORDERS)
ax.add_feature(cfeature.STATES)
hb = ax.hexbin(cdom.lon, cdom.lat, gridsize=(40,12), cmap='inferno_r', mincnt=1, transform=ccrs.PlateCarree(),norm=LogNorm())
cb = plt.colorbar(hb, ax=ax, orientation='vertical', pad=0.02, shrink=0.8)
cb.set_label('Number of Datapoints', fontsize=14)
gl=ax.gridlines(linewidth=0.2,color='grey',alpha=0.7,linestyle='-', draw_labels=True, x_inline= False,y_inline=False)
gl.xformatter=LONGITUDE_FORMATTER
gl.yformatter=LATITUDE_FORMATTER
gl.top_labels = False # Disable top labels
gl.right_labels = False # Disable right labels
ax.set_extent([-125, -60, 26, 45], crs=ccrs.PlateCarree())
ax.set_title('Spatial Data Density', fontsize=18, fontweight='bold')
plt.show()
