data
accidentTypeLookup
accidentTypeLookup["01"]
causesLookup
causesLookup("T301")
d3.utcYear("2022-01-01")
cnty.view()
causes.view()
causes = aq.from(causesCSV)
causes.dedupe("Title").array("Title").sort()
accidentsOriginal = aq.from(rawAll)
accSelect = accidentsOriginal
.select(
"INCDTNO", // railroad assigned number
"STATE", // FIPS state code
"CNTYCD", // FIPS county code
//"STCNTY", // FIPS State & County code
"Latitude", // Latitude in decimal degrees, explicit decimal, explicit +/- (WGS84)
"Longitud", // Longitude in decimal degrees, explicit decimal, explicit +/- (WGS84)
"RAILROAD", // railroad code (Reporting RR)
"RR2", // railroad code (Other RR involved)
"RR3", // railroad code (RR responsible for track maintenance)
"SIGNAL", // Type of territory – signalization, 1 = signaled 2 = not signaled
"TYPE", // Type of accident: see function accidentTypeLookup(type)
"TONS", // Gross tonnage, excluding power units
"TRKDMG", // Track, signal, way & structure damage in $
"EQPDMG", // Reportable equipment damage in $
"ACCDMG", // Total reportable damage on all reports in $
"CAUSE", // Primary cause of incident (refer to Appendix C) see function causesLookup(cause)
"CAUSE2", // Contributing cause of incident (refer to Appendix C) see function causesLookup(cause)
"DRUG", // # of positive drug tests
"ALCOHOL", // # of positive alcohol tests
"TRNDIR", // Train direction: 1=north 2=south 3=east 4=west
"TRNSPD", // Speed of train in miles per hour: Blank=unknown
"TYPSPD", // Train speed type: E=estimated R=recorded Blank=unknown
"TRKCLAS", // FRA track class: 1-9, X
"TRKDNSTY", // Annual track density-gross tonnage in millions note:(not consistent)
"CARS", // # of cars carrying hazmat
"CARSDMG", // # of hazmat cars damaged or derailed
"CARSHZD", // # of cars that released hazmat
"EVACUATE", // # of persons evacuated
"POSITON1", // Car position in train (first involved)
"POSITON2", // Car position in train (causing)
"LOADF1", // # of loaded freight cars
"LOADF2", // # of derailed loaded freight cars
"EMPTYF1", // # of empty freight cars
"EMPTYF2", // # of derailed empty freight cars
"LOADP1", // # of loaded passenger cars
"LOADP2", // # of derailed loaded passenger cars
"EMPTYP1", // # of empty passenger cars
"EMPTYP2", // # of derailted empty passenger cars
"DAY", // day of incident
"MONTH", // month of incident
"YEAR", // year of incident
"TIMEHR", // hour of incident
"TIMEMIN", // minute of incident
"AMPM", // AM or PM
"VISIBLTY", // Daylight period: 1=dawn 2=day 3=dusk 4=dark
"WEATHER" // Weather conditions: 1=clear 2=cloudy 3=rain 4=fog 5=sleet 6=snow
)
.derive({ TRKDMG: (d) => +d.TRKDMG })
.derive({ EQPDMG: (d) => +d.EQPDMG })
.derive({ ACCDMG: (d) => +d.ACCDMG })
//.derive({ Latitude: (d) => +d.Latitude })
//.derive({ Longitud: (d) => +d.Longitud })
.derive({ TONS: (d) => +d.TONS })
.derive({ STCNTY: (d) => d.STATE + d.CNTYCD }, { after: "CNTYCD" })
.derive({ VISIBLTY: aq.escape((d) => vis[+d.VISIBLTY]) })
.derive({ WEATHER: aq.escape((d) => weather[+d.WEATHER]) })
.derive({ TYPE: (d) => d.TYPE })
.derive(
{ TYPEDESC: aq.escape((d) => accidentTypeLookup[d.TYPE]) },
{ after: "TYPE" }
)
.derive({
DATESIMPLE: aq.escape((d) => new Date("20" + d.YEAR))
})
.derive({
DATETIME: aq.escape((d) =>
parseDateTime(
d.MONTH +
" " +
d.DAY +
" " +
d.YEAR +
" " +
d.TIMEHR +
" " +
d.TIMEMIN +
" " +
d.AMPM
)
)
})
.join_full(cntyLookup, ["STCNTY", "uid"])
.reify()
accSelect.view()
viewof byRailroadAccident = accSelect
.orderby(aq.desc("DATETIME"))
.derive({ index: (d) => op.row_number() - 1 }) // <-- add row indices before grouping
.groupby("RAILROAD")
.rollup({
count: op.count(),
sum: op.sum("ACCDMG"),
avg: op.average("ACCDMG"),
rows: op.array_agg("index") // <-- aggregate row indices
})
.orderby(aq.desc("sum"))
.ungroup()
.view(10)
viewof byAccidentType = accSelect
.orderby(aq.desc("DATETIME"))
.derive({ index: (d) => op.row_number() - 1 }) // <-- add row indices before grouping
.groupby("TYPEDESC")
.rollup({
count: op.count(),
dmg_sum: op.sum("ACCDMG"),
dmg_avg: op.average("ACCDMG"),
rows: op.array_agg("index") // <-- aggregate row indices
})
.orderby(aq.desc("dmg_sum"))
.ungroup()
.view(10)
viewof byAccidentCausebyTitle = accSelect
.filter((d) => d.TYPE == "01")
.orderby(aq.desc("DATETIME"))
.derive({ index: (d) => op.row_number() - 1 }) // <-- add row indices before grouping
.derive({ title: aq.escape((d) => causesLookupType(d.CAUSE, "Title")) })
.groupby("title")
.rollup({
count: op.count(),
dmg_sum: op.sum("ACCDMG"),
dmg_avg: op.average("ACCDMG"),
rows: op.array_agg("index") // <-- aggregate row indices
})
.orderby(aq.desc("count"))
.ungroup()
.view(10)
viewof byAccidentCausebyCategory = accSelect
.filter((d) => d.TYPE == "01")
.orderby(aq.desc("DATETIME"))
.derive({ index: (d) => op.row_number() - 1 }) // <-- add row indices before grouping
.derive({ category: aq.escape((d) => causesLookupType(d.CAUSE, "Category")) })
.groupby("category")
.rollup({
count: op.count(),
dmg_sum: op.sum("ACCDMG"),
dmg_avg: op.average("ACCDMG"),
rows: op.array_agg("index") // <-- aggregate row indices
})
.orderby(aq.desc("count"))
.ungroup()
.view(10)
viewof byAccidentCause = accSelect
.filter((d) => d.TYPE == "01")
.orderby(aq.desc("DATETIME"))
.derive({ index: (d) => op.row_number() - 1 }) // <-- add row indices before grouping
.groupby("CAUSE")
.rollup({
count: op.count(),
dmg_sum: op.sum("ACCDMG"),
dmg_avg: op.average("ACCDMG"),
rows: op.array_agg("index") // <-- aggregate row indices
})
.orderby(aq.desc("count"))
.ungroup()
.view(10)
viewof byAccidentCause1 = accSelect
.filter((d) => d.TYPE == "01")
.orderby(aq.desc("DATETIME"))
.derive({ index: (d) => op.row_number() - 1 }) // <-- add row indices before grouping
.groupby("CAUSE")
.rollup({
count: op.count(),
dmg_sum: op.sum("ACCDMG"),
dmg_avg: op.average("ACCDMG"),
rows: op.array_agg("index") // <-- aggregate row indices
})
.orderby(aq.desc("dmg_sum"))
.ungroup()
.view(10)
viewof byEvacuate = accSelect
.orderby(aq.desc("DATETIME"))
.filter((d) => d.EVACUATE > 0)
.derive({ index: (d) => op.row_number() - 1 }) // <-- add row indices before grouping
.groupby("RAILROAD")
.rollup({
count: op.count(),
dmg_sum: op.sum("ACCDMG"),
dmg_avg: op.average("ACCDMG"),
evacuated_avg: op.average("EVACUATE"),
evacuated: op.sum("EVACUATE"),
rows: op.array_agg("index") // <-- aggregate row indices
})
.orderby(aq.desc("evacuated"))
.ungroup()
.view(10)
viewof byEvacuateAve = accSelect
.orderby(aq.desc("DATETIME"))
.filter((d) => d.EVACUATE > 0)
.derive({ index: (d) => op.row_number() - 1 }) // <-- add row indices before grouping
.groupby("RAILROAD")
.rollup({
count: op.count(),
dmg_sum: op.sum("ACCDMG"),
dmg_avg: op.average("ACCDMG"),
evacuated_avg: op.average("EVACUATE"),
evacuated: op.sum("EVACUATE"),
rows: op.array_agg("index") // <-- aggregate row indices
})
//.filter((d) => d.count > 10)
.orderby(aq.desc("evacuated_avg"))
.ungroup()
.view(10)
viewof byHazardReleased = accSelect
.orderby(aq.desc("DATETIME"))
.derive({ index: (d) => op.row_number() - 1 }) // <-- add row indices before grouping
.groupby("RAILROAD")
.rollup({
count: op.count(),
dmg_sum: op.sum("ACCDMG"),
dmg_avg: op.average("ACCDMG"),
evacuated: op.sum("EVACUATE"),
hazard: op.sum("CARSHZD"),
rows: op.array_agg("index") // <-- aggregate row indices
})
.orderby(aq.desc("hazard"))
.ungroup()
.view(10)
iUP = byRailroadAccident.filter((d) => d.RAILROAD == "UP").get("rows")
iBNSF = byRailroadAccident.filter((d) => d.RAILROAD == "BNSF").get("rows")
iCSX = byRailroadAccident.filter((d) => d.RAILROAD == "CSX").get("rows")
iNS = byRailroadAccident.filter((d) => d.RAILROAD == "NS").get("rows")
iATK = byRailroadAccident.filter((d) => d.RAILROAD == "ATK").get("rows")
data = accSelect.objects()
Plot.plot({
x: { tickFormat: null, type: "band" },
marks: [
Plot.barY(
accSelect,
Plot.binX(
{
y: "count"
},
{ x: "DATETIME", fill: "RAILROAD", transform: customTransform }
)
),
Plot.ruleY([0])
],
color: {
legend: true
}
})
Plot.plot({
x: { tickFormat: "%Y", type: "band" },
marks: [
Plot.barY(
data,
Plot.binX(
{ y: "count" },
{
x: (d) => new Date("20" + d["YEAR"]),
fill: "RAILROAD",
transform: customTransform
}
)
),
Plot.ruleY([0])
],
color: {
legend: true
}
})
viewof byRailroadDerailment = accSelect
.orderby(aq.desc("DATETIME"))
.filter((d) => d.TYPE == "01")
.derive({ index: (d) => op.row_number() - 1 }) // <-- add row indices before grouping
.groupby("RAILROAD")
.rollup({
count: op.count(),
rows: op.array_agg("index") // <-- aggregate row indices
})
.orderby(aq.desc("count"))
.view()
viewof byCountyDerailment = accSelect
.orderby(aq.desc("DATETIME"))
.filter((d) => d.TYPE == "01")
.derive({ index: (d) => op.row_number() - 1 }) // <-- add row indices before grouping
.groupby("label")
.rollup({
count: op.count(),
rows: op.array_agg("index") // <-- aggregate row indices
})
.orderby(aq.desc("count"))
.view()
viewof byOwnershipDerailment = accSelect
.orderby(aq.desc("DATETIME"))
.filter((d) => d.TYPE == "01")
.derive({ index: (d) => op.row_number() - 1 }) // <-- add row indices before grouping
.groupby("RR3")
.rollup({
count: op.count(),
rows: op.array_agg("index") // <-- aggregate row indices
})
.orderby(aq.desc("count"))
viewof byRailroadDerailment1 = accSelect
.orderby(aq.desc("DATETIME"))
.filter((d) => d.TYPE == "11")
.derive({ index: (d) => op.row_number() - 1 }) // <-- add row indices before grouping
.groupby("RAILROAD")
.rollup({
count: op.count(),
rows: op.array_agg("index") // <-- aggregate row indices
})
.orderby(aq.desc("count"))
.view()
viewof byIncdtno = accSelect
.orderby(aq.desc("DATETIME"))
.derive({ index: (d) => op.row_number() - 1 })
.groupby("INCDTNO")
.rollup({
count: op.count(),
rows: op.array_agg("index")
})
.orderby(aq.desc("count"))
.view()
accSelectAntiJoin.view()
aq.agg(accSelect, op.max("TRKDMG"))
aq.agg(accSelect, op.max("EQPDMG"))
aq.agg(accSelect, op.max("ACCDMG"))
dataDailyAccidentFrequency = accidentsOriginal
.derive({
date: aq.escape((d) =>
d3.utcFormat(d.MONTH + "/" + d.DAY + "/20" + d.YEAR)()
)
})
.groupby("date")
.rollup({
accidents: op.count(),
cars: op.sum("CARS"),
carsdmg: op.sum("CARSDMG"),
damage: op.sum("ACCDMG"),
damage_avg: op.average("ACCDMG")
/*
"CARS", // # of cars carrying hazmat
"CARSDMG", // # of hazmat cars damaged or derailed
"CARSHZD", // # of cars that released hazmat
"EVACUATE", // # of persons evacuated
*/
})
.objects()
dataDailyDerailmentFrequency = accidentsOriginal
.filter((d) => d.TYPE == "01")
.derive({
date: aq.escape((d) =>
d3.utcFormat(d.MONTH + "/" + d.DAY + "/20" + d.YEAR)()
)
})
.groupby("date")
.rollup({
accidents: op.count(),
damage: op.sum("ACCDMG"),
damage_avg: op.average("ACCDMG")
})
.objects()
dataCountYearly = accidentsOriginal
.filter((d) => d.TYPE == "01")
.derive({
date: aq.escape((d) => "20" + d.YEAR)
})
.groupby("date")
.rollup({
accidents: op.count(),
damage: op.sum("ACCDMG"),
damage_avg: op.average("ACCDMG")
})
.orderby(aq.desc("date"))
.objects()
dataAccidentFrequencyGreaterThan1M = accidentsOriginal
.filter((d) => d.TYPE == "01")
.derive({
date: aq.escape((d) =>
d3.utcFormat(d.MONTH + "/" + d.DAY + "/20" + d.YEAR)()
)
})
.groupby("date")
.rollup({
accidents: op.count(),
damage: op.sum("ACCDMG"),
damage_avg: op.average("ACCDMG")
})
.filter((d) => d.accidents > 1)
.objects()
hazardOvertime = accidentsOriginal
.derive({
date: (d) => "20" + d.YEAR
})
.groupby("date")
.derive({
percar: aq.escape((d) =>
isFinite(d.CARSDMG / d.CARS) ? d.CARSDMG / d.CARS : 0
)
}) // damaged or derailed vs number of cars carrying hazmat
.derive({
perhaz: aq.escape((d) =>
isFinite(d.CARSDMG / d.CARSHZD) ? d.CARSDMG / d.CARSHZD : 0
)
}) // release of hazmat vs number of cars damaged or hazmat
.derive({
perdmg: aq.escape((d) =>
isFinite(d.CARSHZD / d.CARS) ? d.CARSHZD / d.CARS : 0
)
}) // release of hazmat vs number of cars carrying hazmat
.rollup({
accidents: op.count(),
cars: op.sum("CARS"),
carsdmg: op.sum("CARSDMG"),
avgcarshzd: op.average("percar"),
aveperhaz: op.average("perhaz"),
aveperdmg: op.average("perdmg"),
evacuate: op.sum("EVACUATE"),
damage: op.sum("ACCDMG"),
damage_avg: op.average("ACCDMG"),
evacuate_avg: op.average("EVACUATE")
/*
"CARS", // # of cars carrying hazmat
"CARSDMG", // # of hazmat cars damaged or derailed
"CARSHZD", // # of cars that released hazmat
"EVACUATE", // # of persons evacuated
*/
})
//.impute({ avgcarshzd: () => 0 })
//.impute({ aveperhaz: () => 0 })
//.impute({ aveperdmg: () => 0 })
.reify()
hazardOvertime
Type Table, then Shift-Enter. Ctrl-space for more options.
byEvacuation = accidentsOriginal
.filter((d) => d.EVACUATE > 0)
.derive({
date: (d) => d.YEAR
})
.groupby("date")
.derive({ percar: (d) => +d.CARSDMG / +d.CARS }) // damaged or derailed by number of cars carrying hazmat
.derive({ perhaz: (d) => +d.CARSHZD / +d.CARSDMG }) // release of hazmat by number of cars damaged or hazmat
.derive({ perdmg: (d) => +d.CARSHZD / +d.CARS }) // release of hazmat by number of cars carrying hazmat
.rollup({
accidents: op.count(),
cars: op.sum("CARS"),
carsdmg: op.sum("CARSDMG"),
avgcarshzd: op.average("percar"),
aveperhaz: op.average("perhaz"),
aveperdmg: op.average("perdmg"),
evacuate: op.sum("EVACUATE"),
damage: op.sum("ACCDMG"),
damage_avg: op.average("ACCDMG"),
evacuate_avg: op.average("EVACUATE")
/*
"CARS", // # of cars carrying hazmat
"CARSDMG", // # of hazmat cars damaged or derailed
"CARSHZD", // # of cars that released hazmat
"EVACUATE", // # of persons evacuated
*/
})
hazardOvertime.objects().map((d) => ({ value: d.accidents, date: d.date }))
hazardOvertime.view()
Plot.plot({
height: 300,
y: { label: "Number of accidents" },
x: { tickFormat: "%Y", type: "band" },
marks: [
Plot.ruleY([0]),
Plot.barY(
hazardOvertime
.objects()
.map((d) => ({ value: d.accidents, date: new Date("20" + d.date) })),
{ x: "date", y: "value", fill: "steelblue" }
),
Plot.text(
[`Train accidents where trains were transporting hazardous materials.`],
{
dy: 45,
frameAnchor: "bottom-left"
}
)
],
marginBottom: 75
})
Plot.plot({
height: 300,
y: { label: "People evacuated" },
x: { tickFormat: "%Y", type: "band" },
marks: [
Plot.ruleY([0]),
Plot.barY(
byEvacuation
.objects()
.map((d) => ({ value: d.evacuate, date: new Date("20" + d.date) })),
{ x: "date", y: "value", fill: "steelblue" }
),
Plot.text(
[
`Total number of people evacuated \n due to a reported train accident.`
],
{
dy: 45,
frameAnchor: "bottom-left"
}
)
],
marginBottom: 75
})
Plot.plot({
height: 300,
y: {
label: "Average number of people evacuated per accident"
},
x: { tickFormat: "%Y", type: "band" },
marks: [
Plot.ruleY([0]),
Plot.barY(
byEvacuation
.objects()
.map((d) => ({ value: d.evacuate_avg, date: new Date("20" + d.date) })),
{ x: "date", y: "value", fill: "steelblue" }
),
Plot.text(
[`Average number of people evacuated due to any train accident.`],
{
dy: 45,
frameAnchor: "bottom-left"
}
)
],
marginBottom: 75
})
Plot.plot({
height: 300,
x: { tickFormat: "%Y", type: "band" },
marks: [
Plot.ruleY([0]),
Plot.barY(
dataCountYearly.map((d) => ({
value: d.accidents,
date: new Date("20" + d.date)
})),
{ x: "date", y: "value", fill: "steelblue" }
)
]
})
Plot.plot({
height: 300,
x: { tickFormat: "%Y", type: "band" },
marks: [
Plot.ruleY([0]),
Plot.barY(
hazardOvertime
.objects()
.map((d) => ({ value: d.accidents, date: new Date("20" + d.date) })),
{ x: "date", y: "value", fill: "steelblue" }
)
]
})
Plot.plot({
height: 300,
x: { tickFormat: "%Y", type: "band" },
marks: [
Plot.ruleY([0]),
Plot.barY(
hazardOvertime
.objects()
.map((d) => ({ value: d.cars, date: new Date("20" + d.date) })),
{ x: "date", y: "value", fill: "steelblue" }
)
]
})
Plot.plot({
height: 300,
x: { tickFormat: "%Y", type: "band" },
marks: [
Plot.ruleY([0]),
Plot.barY(
hazardOvertime
.objects()
.map((d) => ({ value: d.carsdmg, date: new Date("20" + d.date) })),
{ x: "date", y: "value", fill: "steelblue" }
)
]
})
Plot.plot({
height: 300,
y: { tickFormat: "%" },
marks: [
Plot.ruleY([0]),
Plot.lineY(
hazardOvertime
.objects()
.map((d) => ({ value: d.avgcarshzd, date: new Date("20" + d.date) })),
{ x: "date", y: "value" }
)
]
})
hazardOvertime.view()
Plot.plot({
height: 300,
//damaged or derailed hazmat vs number of cars carrying hazmat (not leaking)
y: { tickFormat: "%" },
x: { type: "point" },
marks: [
Plot.ruleY([0]),
Plot.lineY(
hazardOvertime
.objects()
.map((d) => ({ value: d.aveperhaz, date: d.date })),
{ x: "date", y: "value" }
//"CARS", # of cars carrying hazmat
//"CARSDMG", # of hazmat cars damaged or derailed
//"CARSHZD", # of cars that released hazmat
//percar: (d) => +d.CARSDMG / +d.CARS }) // damaged or derailed vs number of cars carrying hazmat
//perhaz: (d) => +d.CARSHZD / +d.CARSDMG }) // release of hazmat vs number of cars damaged or hazmat
//perdmg: (d) => +d.CARSHZD / +d.CARS }) // release of hazmat vs number of cars carrying hazmat
//avgcarshzd: op.average("percar"),
//aveperhaz: op.average("perhaz"),
//aveperdmg: op.average("perdmg"),
)
]
})
Plot.plot({
height: 300,
y: { tickFormat: "%" },
marks: [
Plot.ruleY([0]),
Plot.lineY(
hazardOvertime
.objects()
.map((d) => ({ value: d.aveperdmg, date: new Date("20" + d.date) })),
{ x: "date", y: "value" }
)
]
})
Plot.plot({
height: 300,
y: { tickFormat: "~s" },
x: { tickFormat: "%Y", type: "band" },
marks: [
Plot.ruleY([0]),
Plot.barY(
hazardOvertime
.objects()
.map((d) => ({ value: d.damage, date: new Date("20" + d.date) })),
{ x: "date", y: "value", fill: "steelblue" }
)
]
})
Plot.plot({
height: 300,
y: { tickFormat: "~s" },
x: { tickFormat: "%Y", type: "band" },
marks: [
Plot.ruleY([0]),
Plot.barY(
hazardOvertime
.objects()
.map((d) => ({ value: d.damage_avg, date: new Date("20" + d.date) })),
{ x: "date", y: "value", fill: "steelblue" }
)
]
})
/*Calendar(dataAccidentFrequency, {
fill: "damage_avg",
weekNumber: true,
textFill: "none",
title: (d) =>
`${d3.format("($.2~s")(d.damage_avg)} ave dmg,\n${d.accidents} derailments`,
color: {
scheme: "OrRd",
domain: [1e10, 10e3],
type: "log",
reverse: false
}
})*/
/*Plot.plot({
marks: [
Plot.rectY(dataAccidentFrequency, {
x: (d) => new Date(d["date"]),
y: (d) => d.accidents,
fill: "blue",
fillOpacity: 0.5,
thresholds: d3.utcDay
})
]
})*/
cars = aq.from(carsOnline)
import { Calendar } from "@observablehq/calendar-component"
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
