Public
Edited
Feb 10, 2023
prrResponseDissell1312023 = FileAttachment("PRR Response Dissell 1312023.xlsx").xlsx()
prrResponseDissell1312023.sheetNames
lawyerIDs = aq.from(prrResponseDissell1312023.sheet("Sheet1", { headers: true, range: ":G" }))
Inputs.table(lawyerIDs)
Inputs.table(lawyerIDsclean)
lawyerIDsclean = lawyerIDs
.derive({
"Attorney OSC Number": d => aq.padstart(d.AttorneyRegistrationNumber, 7, 0)
})
cuyahogaCountyJuvenileCourtAttorneyFeesMay2021Apri = FileAttachment("Cuyahoga County Juvenile Court Attorney Fees May 2021 April 2022.xlsx").xlsx()
cuyahogaCountyJuvenileCourtAttorneyFeesMay2021Apri.sheetNames
attorneyfees = aq.from(cuyahogaCountyJuvenileCourtAttorneyFeesMay2021Apri.sheet("Original File", { headers: true, range: ":J" }))
Inputs.table(attorneyfees)
cuyahoga2021Delinquency = FileAttachment("Cuyahoga 2021 Delinquency@1.xlsx").xlsx()
cuyahoga2021Delinquency.sheetNames
delinquency2021 = aq.from(await cuyahoga2021Delinquency.sheet("Application Search", { headers: true, range: "A1:N" }))
Inputs.table(delinquency2021)
cuyahoga2022YtdDelinquency = FileAttachment("Cuyahoga 2022 YTD Delinquency.xlsx").xlsx()
cuyahoga2022YtdDelinquency.sheetNames
delinquency2022 = aq.from(await cuyahoga2022YtdDelinquency.sheet("Application Search", { headers: true, range: "A2:N" }))
Inputs.table(delinquency2022)
delinq2122 = delinquency2021.concat(delinquency2022)
Inputs.table(delinq2122)
summary = delinq2122
.groupby('Attorney OSC Number')
.rollup({
cases: op.count(),
fees: op.sum('Attorney Fees'),
expenses: op.sum('Misc Expenses'),
travel: op.sum('Travel Expenses'),
adjusted: op.sum('Adjusted Amount'),
reimbursed: op.sum('Reimbursement Amount')
})
.derive({
percase: d => d.reimbursed/d.cases
})
Inputs.table(summary)
summarywithnames = summary.join_left(lawyerIDsclean, "Attorney OSC Number")
Inputs.table(summarywithnames)
topearnercases = delinq2122
.filter((d) => d["Attorney OSC Number"] === "0077250"||d["Attorney OSC Number"] === "0087408" || d["Attorney OSC Number"] === "0044021" || d["Attorney OSC Number"] === "0072314")
.join(bindovers17to21, ['Case Number', 'CaseNumber'])
.join_left(lawyerIDsclean, "Attorney OSC Number")
.dedupe("Case Number")
Inputs.table(topearnercases)
topearnercases
.groupby('Attorney OSC Number', 'Last_VC30', "Motion Ruling")
.count()
.groupby('Attorney OSC Number')
.derive({
pct: (row) => row.count / aq.op.sum(row.count)
})
.view()
chart = BarChart(summarywithnames, {
x: d => d["Attorney OSC Number"],
y: d => d.reimbursed,
xDomain: d3.groupSort(summarywithnames, ([d]) => -d.reimbursed, d => d["Attorney OSC Number"]), // sort by descending frequency
yFormat: "$",
yLabel: "↑ Frequency",
width,
height: 500,
color: "steelblue"
})
allcases = delinq2122
.join(bindovers17to21, ['Case Number', 'CaseNumber'])
.join_left(lawyerIDsclean, "Attorney OSC Number")
.dedupe("Case Number")
allcases
.groupby('Attorney OSC Number', 'Last_VC30', "Motion Ruling")
.count()
.groupby('Attorney OSC Number')
.derive({
pct: (row) => row.count / aq.op.sum(row.count)
})
.view()
import {aq, op} from "@uwdata/arquero"
import {howto, altplot} from "@d3/example-components"
function BarChart(data, {
x = (d, i) => i, // given d in data, returns the (ordinal) x-value
y = d => d, // given d in data, returns the (quantitative) y-value
title, // given d in data, returns the title text
marginTop = 20, // the top margin, in pixels
marginRight = 0, // the right margin, in pixels
marginBottom = 30, // the bottom margin, in pixels
marginLeft = 40, // the left margin, in pixels
width = 640, // the outer width of the chart, in pixels
height = 400, // the outer height of the chart, in pixels
xDomain, // an array of (ordinal) x-values
xRange = [marginLeft, width - marginRight], // [left, right]
yType = d3.scaleLinear, // y-scale type
yDomain, // [ymin, ymax]
yRange = [height - marginBottom, marginTop], // [bottom, top]
xPadding = 0.1, // amount of x-range to reserve to separate bars
yFormat, // a format specifier string for the y-axis
yLabel, // a label for the y-axis
color = "currentColor" // bar fill color
} = {}) {
// Compute values.
const X = d3.map(data, x);
const Y = d3.map(data, y);
// Compute default domains, and unique the x-domain.
if (xDomain === undefined) xDomain = X;
if (yDomain === undefined) yDomain = [0, d3.max(Y)];
xDomain = new d3.InternSet(xDomain);
// Omit any data not present in the x-domain.
const I = d3.range(X.length).filter(i => xDomain.has(X[i]));
// Construct scales, axes, and formats.
const xScale = d3.scaleBand(xDomain, xRange).padding(xPadding);
const yScale = yType(yDomain, yRange);
const xAxis = d3.axisBottom(xScale).tickSizeOuter(0);
const yAxis = d3.axisLeft(yScale).ticks(height / 40, yFormat);
// Compute titles.
if (title === undefined) {
const formatValue = yScale.tickFormat(100, yFormat);
title = i => `${X[i]}\n${formatValue(Y[i])}`;
} else {
const O = d3.map(data, d => d);
const T = title;
title = i => T(O[i], i, data);
}
const svg = d3.create("svg")
.attr("width", width)
.attr("height", height)
.attr("viewBox", [0, 0, width, height])
.attr("style", "max-width: 100%; height: auto; height: intrinsic;");
svg.append("g")
.attr("transform", `translate(${marginLeft},0)`)
.call(yAxis)
.call(g => g.select(".domain").remove())
.call(g => g.selectAll(".tick line").clone()
.attr("x2", width - marginLeft - marginRight)
.attr("stroke-opacity", 0.1))
.call(g => g.append("text")
.attr("x", -marginLeft)
.attr("y", 10)
.attr("fill", "currentColor")
.attr("text-anchor", "start")
.text(yLabel));
const bar = svg.append("g")
.attr("fill", color)
.selectAll("rect")
.data(I)
.join("rect")
.attr("x", i => xScale(X[i]))
.attr("y", i => yScale(Y[i]))
.attr("height", i => yScale(0) - yScale(Y[i]))
.attr("width", xScale.bandwidth());
if (title) bar.append("title")
.text(title);
svg.append("g")
.attr("transform", `translate(0,${height - marginBottom})`)
.call(xAxis);
return svg.node();
}
bindovers2017Through2021MotionsAndPleadingsWithDob = FileAttachment("Bindovers 2017 through 2021 (Motions and Pleadings) with dob and race.xlsx").xlsx()
bindovers17to21 = aq.from(bindovers2017Through2021MotionsAndPleadingsWithDob.sheet(1, { headers: true, range: ":L" }))
Inputs.table(bindovers17to21)
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.
