<jupyter_start><jupyter_text>**Merge Water_final and cmc_benthic datasets**<jupyter_code># read in cmc_benthic data
benthic = pd.read_csv('/Users/bibor/Hack_the_Bay/Data/Merged_Benthic.csv', low_memory=False)
# read in cbp_benthic data
cbp = pd.read_csv('/Users/bibor/Hack_the_Bay/Data/CBP_Benthic_clean.csv', low_memory=False, parse_dates=[['Date', 'Time']])
# read in water quality data
wq = data = pd.read_csv('~/Hack_the_Bay/Data/Water_FINAL.csv', low_memory=False, parse_dates=[['Date', 'Time']])

#source for parse_dates: https://stackoverflow.com/questions/17978092/combine-date-and-time-columns-using-python-pandas
wq.shape
wq.drop(columns=['Unnamed: 0', 'Unnamed: 0.1'], inplace=True)
# drop columns where non-null count is less than 100
wq.drop(['BiasPC', 'FieldActivityRemark', 'PrecisionPC'], axis=1, inplace=True)
# transform benthic['SAMPLE_DATE'] into datetime object
benthic['SAMPLE_DATE'] = pd.to_datetime(benthic['SAMPLE_DATE'])<jupyter_output><empty_output><jupyter_text>**Merge Water Quality and Benthic data**<jupyter_code># assure that all Latitude and Longitude values are in same decimal format
wq['Longitude'] = wq['Longitude'].round(6)
wq['Latitude'] = wq['Latitude'].round(6)
benthic['Longitude'] = benthic['Longitude'].round(6)
benthic['Latitude'] = benthic['Latitude'].round(6)
# merge wq and benthic dataframes on Latitude, Longitude, and date
# from wq include all rows, from benthic include only rows where date, latitude and longitude corresponds to wq

data = pd.merge(wq,
             benthic,
             how='left',
             left_on=['Date_Time', 'Latitude', 'Longitude'],
             right_on=['SAMPLE_DATE', 'Latitude', 'Longitude'],
             indicator=True
            )

# merging does not work - no common Latitude/ longitude values
data['_merge'].value_counts()<jupyter_output><empty_output><jupyter_text>**Merge Water Quality and CBP Benthic data**<jupyter_code>cbp['Date'] = cbp['Date_Time'].dt.date
wq['Date'] = wq['Date_Time'].dt.date
# merge wq and cbp dataframes on date_time and HUC12_
# from wq include all rows, from benthic include only rows where date_time and HUC12_ correspond to wq

merged_df = pd.merge(wq,
             cbp,
             how='left',
             on=['Date', 'HUC12_'],
             indicator=True
            )
# check the success of the merge
merged_df['_merge'].value_counts()
merged_df.shape<jupyter_output><empty_output><jupyter_text>**Note:** Consider outer join of water_final and cbp_benthic. **Plot water quality and benthic collection stations**Source: [<NAME>: Getting started with geo-spatial data in Python](https://github.com/oceanspace/DatabricksHackathon/blob/master/WiDS_Tutorial/Python_Geospatial_WiDS.ipynb)<jupyter_code># make geo-dataframes
geo_wq = geo.GeoDataFrame(wq, 
                          geometry=geo.points_from_xy(wq.Longitude, wq.Latitude))

geo_benthic = geo.GeoDataFrame(benthic, 
                          geometry=geo.points_from_xy(benthic.Longitude, benthic.Latitude))

geo_cbp = geo.GeoDataFrame(cbp, 
                          geometry=geo.points_from_xy(cbp.Longitude, cbp.Latitude))
# import a map of US states
fid=urllib.request.urlretrieve("https://eric.clst.org/assets/wiki/uploads/Stuff/gz_2010_us_040_00_500k.json",
                               "/tmp/gz_2010_us_040_00_500k.json") 
us_states = geo.read_file(fid[0])

# code copied from https://github.com/oceanspace/DatabricksHackathon/blob/master/WiDS_Tutorial/Python_Geospatial_WiDS.ipynb
# make a map of sampling locations

plt.rcParams.update({'font.size': 16})

ax = us_states.boundary.plot(color='black',figsize=[20,16])

waterstations = geo_wq.plot(ax=ax, color='blue',marker='.')
benthicstations = geo_benthic.plot(ax=ax, color='red',marker='.')
cbpstations = geo_cbp.plot(ax=ax, color='green',marker='.')

minx, miny, maxx, maxy = geo_wq.total_bounds
ax.set_xlim(minx-1, maxx+1)
ax.set_ylim(miny-1, maxy+1)
ax.set_title('CMC sampling locations')
# ax.legend(handles=[waterstations, benthicstations], labels=['Water Quality', 'Benthic'])

plt.show()

# code modified from https://github.com/oceanspace/DatabricksHackathon/blob/master/WiDS_Tutorial/Python_Geospatial_WiDS.ipynb<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13503215051202666, 'fraction_numerical': 0.01428911645629912, 'mean_word_length': 4.8495821727019495, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 18, 'https://': 5, 'lorem ipsum': 0, 'www.': 0, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '523407', 'license': 'MIT', 'n_tokens_mistral': 1568, 'n_tokens_neox': 1501, 'n_words': 278, 'path': '.ipynb_checkpoints/Merge-checkpoint.ipynb', 'repo_name': 'biborsz/Hack_the_bay', 'size': '4202'}