Analyzing River Thames Water Levels
Time series data is everywhere, from watching your stock portfolio to monitoring climate change, and even live-tracking as local cases of a virus become a global pandemic. In this project, you’ll work with a time series that tracks the tide levels of the Thames River. You’ll first load the data and inspect it data visually, and then perform calculations on the dataset to generate some summary statistics. You’ll end by reducing the time series to its component attributes and analyzing them.
The original dataset is available from the British Oceanographic Data Center here and you can read all about this fascinating archival story in this article from the Nature journal.
Here's a map of the locations of the tidal meters along the River Thames in London.
The provided datasets are in the data folder in this workspace. For this project, you will work with one of these files, 10-11_London_Bridge.txt, which contains comma separated values for water levels in the Thames River at the London Bridge. After you've finished the project, you can use your same code to analyze data from the other files (at other spots in the UK where tidal data is collected) if you'd like.
The TXT file contains data for three variables, described in the table below.
| Variable Name | Description | Format |
|---|---|---|
| Date and time | Date and time of measurement to GMT. Note the tide gauge is accurate to one minute. | dd/mm/yyyy hh:mm:ss |
| Water level | High or low water level measured by tide meter. Tide gauges are accurate to 1 centimetre. | metres (Admiralty Chart Datum (CD), Ordnance Datum Newlyn (ODN or Trinity High Water (THW)) |
| Flag | High water flag = 1, low water flag = 0 | Categorical (0 or 1) |
# We've imported your first Python package for you, along with a function you will need called IQR
import pandas as pd
def IQR(column):
""" Calculates the interquartile range (IQR) for a given DataFrame column using the quantile method """
q25, q75 = column.quantile([0.25, 0.75])
return q75-q25
# Begin coding here ...import pandas as pd
import numpy as np
# Load data file into a DataFrame
London_Bridge = pd.read_csv('data/10-11_London_Bridge.txt')
London_Bridge# Take only the first three
df = London_Bridge.iloc[:,:3]
dfdf.columns = ['datetime','water_level','is_high_tide']
dfdf.info()# Convert to datetime
df['datetime'] = pd.to_datetime(df['datetime'])
df.info()# Convert water_level to float
df['water_level'] = df['water_level'].astype('float')
df.info()# Create extra month and year columns for easy access
df['month'] = df['datetime'].dt.month
df['year'] = df['datetime'].dt.year
df.head()# Separate the high-tide and low-tide data in two different DataFrame
tide_high = df[df['is_high_tide'] == 1]['water_level']
print('tide_high:')
print(tide_high.head())
tide_low = df[df['is_high_tide']==0]['water_level']
print('tide_low:')
print(tide_low.head())# Create summary statistics
high_statistics = tide_high.agg(['mean','median',IQR])
print('high_statistics: ')
print(high_statistics)
low_statistics = tide_low.agg(['mean','median',IQR])
print('low_statistics: ')
print(low_statistics)# Calculate ratio of high tide days
all_high_days = df[df['is_high_tide']==1].groupby('year')['water_level'].count()
print('all_high_days')
print(all_high_days.head())
very_high_days = df[(df['water_level'] > tide_high.quantile(0.9)) & (df['is_high_tide']==1)].groupby('year').count()['water_level']
print('very_high_days')
print(very_high_days.head())
very_high_ratio = (very_high_days/all_high_days).reset_index()
print(very_high_ratio.head())# Calculate ratio of low tide days
all_low_days = df[df['is_high_tide']==0].groupby('year')['water_level'].count()
very_low_days = df[(df['is_high_tide']==0) & (df['water_level']<tide_low.quantile(0.1))].groupby('year').count()['water_level']
very_low_ratio = (very_low_days/all_low_days).reset_index()
print(very_low_ratio)# Create a dictionary {solution}
solution = {'high_statistics':high_statistics, 'low_statistics':low_statistics, 'very_high_ratio':very_high_ratio, 'very_low_ratio':very_low_ratio}
print(solution,'\n')