INTRODUCTION

This report analyzes a data on oil spills occurring across the United States, attempting to identify trends in location, time, and type of spills.

Dataset

This database comes from the Department of Transportation’s (DOT) Pipeline and Hazardous Materials Safety Administration. It includes data on every reported leak or spill since 2010, with information on the location, company, environmental costs, among others.The data was accessed via Kaggle: kaggle.com/datasets/usdot/pipeline-accidents.

Findings

The following charts display information regarding spills in the United States

Oil Pipeline Incidents by Month and Year

This chart only includes incidents with environmental remediation costs of over $5,000, as identified by the data. Examining this graph, it is not clear that there are any clear trends or patterns as far as the timing of incidents.

df['Accident Date/Time'] = pd.to_datetime(df['Accident Date/Time'], format='%m/%d/%y %H:%M')

df['Month'] = df['Accident Date/Time'].dt.month
df['Year'] = df['Accident Date/Time'].dt.year

df['Month_name'] = df['Accident Date/Time'].dt.strftime('%b')

monthly_costs = df.groupby(['Year', 'Month'])['Environmental Remediation Costs'].sum().reset_index()


df_filtered = df[df['Environmental Remediation Costs'] > 5000]
result = df_filtered.groupby(['Year', 'Month']).size().reset_index(name='Counts')

plt.figure(figsize=(18, 10))

plt.scatter(result['Month'], result['Year'],
            marker='o',
            cmap='viridis',
            c=result['Counts'],
            s=result['Counts']*100,
            edgecolors='black')

plt.title('Oil Pipeline Incidents with Environmental Remediation Costs over $5,000, by Month and Year', fontsize=20)
plt.xlabel('Month', fontsize=16)
plt.ylabel('Year', fontsize=16)

cbar = plt.colorbar()
cbar.set_label('Count of Incidents Over $5000', rotation=270, fontsize=14, labelpad=30)

plt.xticks(range(1, 13), ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'], rotation=45, fontsize=14)
plt.yticks(sorted(result['Year'].unique()), fontsize=14)
plt.tight_layout()
plt.grid(True)
plt.show()

Top 20 States by Number of Incidents

It is clear that a handful of states encompass the vast majority of incidents. There are factors that can contribute to this, such as the size of the state (Texas and California), but clearly the spills center around locations where there is considerable oil production, exploration, and refining.

incidents_per_state = df.groupby('Accident State').size().reset_index(name='Incident Count')

top_20_states = incidents_per_state.sort_values(by='Incident Count', ascending=False).head(20).reset_index()

def pick_colors_according_to_mean_count(this_data):
    colors = []
    avg = this_data['Incident Count'].mean()
    for each in this_data['Incident Count']:
        if each > avg * 1.01:
            colors.append('darkred')
        elif each < avg * 0.99:
            colors.append('skyblue')
        else:
            colors.append('black')
    return colors

top_20_states_sorted = top_20_states.sort_values('Incident Count', ascending=True)

my_colors = pick_colors_according_to_mean_count(top_20_states_sorted)

Above = mpatches.Patch(color='darkred', label='Above Average')
At = mpatches.Patch(color='black', label='Within 1% of the Average')
Below = mpatches.Patch(color='skyblue', label='Below Average')

fig = plt.figure(figsize=(18, 12))
ax1 = fig.add_subplot(1, 1, 1)
ax1.barh(top_20_states_sorted['Accident State'], top_20_states_sorted['Incident Count'], color=my_colors)

for row_counter, value_at_row_counter in enumerate(top_20_states_sorted['Incident Count']):
    color = my_colors[row_counter]
    ax1.text(value_at_row_counter + 2, row_counter, f'{value_at_row_counter}', color=color, size=12, fontweight='bold',
             ha='left', va='center', backgroundcolor='white')

plt.xlim(0, top_20_states_sorted['Incident Count'].max() * 1.1)
ax1.legend(handles=[Above, At, Below], fontsize=14, loc='lower right')
plt.axvline(top_20_states_sorted['Incident Count'].mean(), color='black', linestyle='dashed')
ax1.text(top_20_states_sorted['Incident Count'].mean() + 2, 0, 'Mean = {:.2f}'.format(top_20_states_sorted['Incident Count'].mean()), fontsize=14)

ax1.set_title('Top 20 States by Number of Oil Pipeline Incidents', size=20)
ax1.set_xlabel('Incident Count', fontsize=16)
ax1.set_ylabel('State', fontsize=16)
plt.xticks(fontsize=14)
plt.yticks(fontsize=14)
plt.show()

Oil Spill Costs by Hour of the Day

This is another attempt to identify patterns in the timing of spills - by both day of the week and hour of the day. While there is not a huge trend, it does appear that incidents pick up just slightly around 7 am, and stay minorly elevated until the end of the day. This makes sense as it is when shifts change and leaks/spills are often identified.

#Line chart

df['WeekDay'] = df['Accident Date/Time'].dt.day_name()
df['Hour'] = df['Accident Date/Time'].dt.hour

agg_df = df.groupby(['WeekDay', 'Hour'])['All Costs'].sum().reset_index()

agg_df = agg_df.rename(columns={'All Costs': 'TotalFines'})

fig = plt.figure(figsize=(18, 10))
ax = fig.add_subplot(1, 1, 1)

my_colors = {'Monday':'blue', 'Tuesday':'red', 'Wednesday':'green',
             'Thursday':'gray', 'Friday':'purple', 'Saturday':'gold', 'Sunday':'brown'}

for key, grp in agg_df.groupby(['WeekDay']):
    grp.plot(ax=ax, kind='line', x='Hour', y='TotalFines', color=my_colors[key], label=key, marker='8')

plt.title('Total Costs of Oil Spills by Hour of Day', fontsize=18)
ax.set_xlabel('Hour (24 Hour Interval)', fontsize=18)
ax.set_ylabel('Total Costs ($)', fontsize=18, labelpad=20)
ax.tick_params(axis='x', labelsize=14, rotation=0)
ax.tick_params(axis='y', labelsize=14, rotation=0)

ax.set_xticks(np.arange(24))

day_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
handles, labels = ax.get_legend_handles_labels()
handles = [handle for _, handle in sorted(zip(labels, handles), key=lambda x: day_order.index(x[0]))]
labels = sorted(labels, key=lambda x: day_order.index(x))

plt.legend(handles, labels, loc='best', fontsize=14, ncol=1)

ax.yaxis.set_major_formatter(FuncFormatter(lambda x, pos: '$%1.0fM' % (x*1e-6)))

plt.show()

Total Cost of Accidents by State, and Type of Fuel spilled

Looking at the states with the largest number of incidents (California, Texas, and Louisiana), we can identfiy the types of fuel that make up the total cost. In California, nearly all incidents were of Crude Oil, while in Texas and Louisiana, there was considerable refined product, and highly volatile liquid (HVL) spills.

#PIE
df['Liquid Type'] = df['Liquid Type'].replace({
    "REFINED AND/OR PETROLEUM PRODUCT (NON-HVL), LIQUID": "Refined Product",
    "HVL OR OTHER FLAMMABLE OR TOXIC FLUID, GAS": "HVL fluid/gas",
    "CRUDE OIL": "Crude Oil",
    "CO2 (CARBON DIOXIDE)": "CO2"
})

selected_states = ['CA', 'TX', 'LA']
filtered_df = df[df['Accident State'].isin(selected_states)]

outer_group = filtered_df.groupby('Accident State')['All Costs'].sum()

state_labels = {
    'CA': 'California Cost of All Incidents',
    'TX': 'Texas Cost of All Incidents',
    'LA': 'Louisiana Cost of All Incidents'
}
outer_labels = [f"{state_labels.get(index)}\n${value/1e6:.2f}M" for index, value in outer_group.iteritems()]

inner_group = filtered_df.groupby(['Accident State', 'Liquid Type'])['All Costs'].sum().reset_index()

inner_labels = []
for _, row in inner_group.iterrows():
  liquid_type = row['Liquid Type']
  state = row['Accident State']
  if liquid_type.startswith('CRUDE OIL'):
    label = "Crude Oil"
  elif liquid_type.startswith('REFINED AND/OR'):
    label = "Refined Product"
  elif liquid_type.startswith('HVL'):
    label = "HVL"
  else:
    label = liquid_type
  inner_labels.append(f"{label} ({state})")


base_colors = {
    'CA': plt.cm.Blues,
    'TX': plt.cm.Greens,
    'LA': plt.cm.Reds
}

outer_colors = [base_colors[state](0.5) for state in outer_group.index]

inner_colors = []
for state, group in inner_group.groupby('Accident State'):
    num_liquids = len(group)
    gradient = np.linspace(0.5, 0.9, num_liquids)
    inner_colors.extend([base_colors[state](shade) for shade in gradient])

fig, ax = plt.subplots(figsize=(14, 7))


outer_pie = ax.pie(outer_group, radius=1, labels=outer_labels, colors=outer_colors,
                   labeldistance=1.05,
                   wedgeprops=dict(width=0.3, edgecolor='w'), textprops={'fontsize': 14},
                   startangle=90)


sizes = inner_group['All Costs']
inner_pie = ax.pie(sizes, radius=0.7, labels=None,
                   colors=inner_colors, wedgeprops=dict(width=0.3, edgecolor='w'), startangle=90)

label_threshold = 5

for i, (wedge, label) in enumerate(zip(inner_pie[0], inner_labels)):
    if (wedge.theta2 - wedge.theta1) > label_threshold:
        angle = (wedge.theta2 - wedge.theta1) / 2. + wedge.theta1
        x = 0.55 * np.cos(np.deg2rad(angle))
        y = 0.55 * np.sin(np.deg2rad(angle))
        ax.text(x, y, label, ha='center', va='center', color='white', weight='bold',
                fontsize=10, style='italic', bbox=dict(boxstyle="round,pad=0.3",
                                                       fc=(0, 0, 0, 0.5), ec="none"))
hole = plt.Circle((0, 0), 0.4, color='white')
ax.add_artist(hole)
plt.title('Total Cost of Accidents by State and Fuel Type \n California, Texas, Louisiana', fontsize=20)
ax.axis('equal')
plt.tight_layout()

footnote = "HVL = Highly Volatile Liquids"
plt.figtext(0.5, -0.05, footnote, ha="center", fontsize=12, color='black')
plt.show()

Frequency of Incidents by Year and Cause

While the number of accidents does not vary greatly across years, it is clear that the primary cause of most accidents is “Material/Weld/Equipment Failure”.

hm_df = df.groupby(['Accident Year', 'Cause Category']).size().unstack(fill_value=0)

fig = plt.figure(figsize=(18, 10))
ax = fig.add_subplot(1, 1, 1)

comma_fmt = FuncFormatter(lambda x, p: format(int(x), ','))

ax = sns.heatmap(hm_df, linewidth=0.2, annot=True, cmap='coolwarm', fmt=',.0f',
                 square=True, annot_kws={'size': 11},
                 cbar_kws={'format': comma_fmt, 'orientation': 'vertical'})

plt.title('Heatmap of the Frequency of Oil Pipeline Accidents by Year and Cause', fontsize=18, pad=15)
plt.xlabel('Accident Cause', fontsize=18, labelpad=10)
plt.ylabel('Year', fontsize=18, labelpad=10)
plt.yticks(rotation=0, size=14)
plt.xticks(rotation=45, ha="right", size=12)
ax.invert_yaxis()

cbar = ax.collections[0].colorbar

max_count = hm_df.to_numpy().max()
step_size = max_count // 10
my_colorbar_ticks = [*range(0, max_count + step_size, step_size)]
cbar.set_ticks(my_colorbar_ticks)

my_colorbar_tick_labels = ['{:,}'.format(each) for each in my_colorbar_ticks]
cbar.set_ticklabels(my_colorbar_tick_labels)

cbar.set_label('Number of Accidents', rotation=270, fontsize=14, color='black', labelpad=20)
plt.tight_layout()
plt.show()

##Conclusion Upon analyzing this dataset, it is clear that certain locations have far more spills than others. Further, we can identify the types of products encompassing those incidents, as well as the cause of many of them. The data cannot, on the other hand, be used to identify trends in WHEN these incidents occur.