Public
Edited
Jun 9
import {render} from "@vega/vega-lite-api-v5"
import {vl} from "@vega/vega-lite-api-v5"
workbook = FileAttachment("Seattle@2.xlsx").xlsx()
workbook.sheetNames
data = workbook.sheet(0, {
headers: true,
// range: "A1:J10"
})
data
Type Table, then Shift-Enter. Ctrl-space for more options.
cleaned_data = data.map(d => ({
Year: new Date(d.Year, 0),
"CPI": +d["CPI"],
"Nominal Mean": +d["Annual mean wage"],
"Real Mean": +d["Real Annual Mean Wage"],
"Nominal Median": +d["Median Annual wage"],
"Real Median": +d["Real Annual Median Wage"]
}))
cleaned_data
Type Table, then Shift-Enter. Ctrl-space for more options.
wage_combo_data = cleaned_data.flatMap(d => {
const yearStr = new Date(d.Year).getFullYear().toString();
return [
{
Year: yearStr,
WageType: "Nominal",
Type: "Mean",
Wage: d["Nominal Mean"],
Difference: d["Nominal Mean"] - d["Real Mean"]
},
{
Year: yearStr,
WageType: "Real",
Type: "Mean",
Wage: d["Real Mean"],
Difference: null
},
{
Year: yearStr,
WageType: "Nominal",
Type: "Median",
Wage: d["Nominal Median"],
Difference: d["Nominal Median"] - d["Real Median"]
},
{
Year: yearStr,
WageType: "Real",
Type: "Median",
Wage: d["Real Median"],
Difference: null
}
];
})
Wage_compare = render({
data: {
values: wage_combo_data
},
width: 700,
height: 400,
resolve: {
scale: { y: "independent" }
},
params: [
{
name: "wageSelector",
value: "Mean",
bind: {
input: "select",
options: ["Mean", "Median"],
name: "Wage Type: "
}
}
],
transform: [
{ filter: "datum.Type === wageSelector" }
],
encoding: {
x: { field: "Year", type: "ordinal", title: "Year"},
xOffset: { field: "WageType" },
color: {
field: "WageType",
type: "nominal",
scale: { range: ["#fbb4ae", "#b3cde3"] },
title: "Wage Type"
}
},
layer: [
// Bar chart
{
mark: "bar",
encoding: {
y: {
field: "Wage",
type: "quantitative",
title: "Wage ($)"
},
tooltip: [
{ field: "Year", type: "ordinal" },
{ field: "WageType", type: "nominal" },
{ field: "Wage", type: "quantitative", title: "Wage ($)" }
]
}
},
// Line chart
{
transform: [
{ filter: "datum.Type === wageSelector && datum.WageType === 'Nominal' && datum.Difference != null" }
],
mark: {
type: "line",
strokeDash: [4, 4],
point: true,
color: "#e41a1c", // bright red
strokeWidth: 2
},
encoding: {
x: { field: "Year", type: "ordinal" },
y: {
field: "Difference",
type: "quantitative",
axis: {
title: "Nominal - Real ($)",
orient: "right"
},
scale: { domain: [0, 30000] }
},
color: {
value: "#e41a1c",
legend: {
title: "Line",
symbolType: "stroke",
labelExpr: "'Difference (Nominal - Real)'"
}
},
tooltip: [
{ field: "Year", type: "ordinal" },
{ field: "Difference", type: "quantitative", title: "Nominal - Real ($)" }
]
}
}
]
})
data_2 = workbook.sheet(2, {
headers: true,
// range: "A1:J10"
})
data_2
Type Table, then Shift-Enter. Ctrl-space for more options.
cpiData = data_2.flatMap(d => [
{ City: "Seattle", Year: new Date(d.observation_date, 0), CPI: +d.Seattle },
{ City: "San Francisco", Year: new Date(d.observation_date, 0), CPI: +d["San Fanscisco"] },
{ City: "New York", Year: new Date(d.observation_date, 0), CPI: +d["New York"] },
{ City: "Chicago", Year: new Date(d.observation_date, 0), CPI: +d.Chicago }
])
cpiData
Type Table, then Shift-Enter. Ctrl-space for more options.
data1
X*
—
Y*
—
Color
—
Size
—
Facet X
—
Facet Y
—
Mark
Auto
Type Chart, then Shift-Enter. Ctrl-space for more options.
CPI_muti = render({
mark: { type: "line", point: true },
data: { values: cpiData },
selection: {
highlight: {
type: "single",
on: "mouseover",
fields: ["City"],
nearest: true,
empty: "all"
}
},
encoding: {
x: { field: "Year", type: "temporal", title: "Year" },
y: {
field: "CPI",
type: "quantitative",
title: "CPI Index",
scale: { domain: [225, 360] },
axis: { tickCount: 6, grid: true }
},
color: {
field: "City",
type: "nominal",
title: "City",
scale: {
range: ["#a6cee3", "#b2df8a", "#fb9a99", "#fdbf6f"]
}
},
opacity: {
condition: { selection: "highlight", value: 1 },
value: 0.3
},
tooltip: [
{ field: "City", type: "nominal" },
{ field: "Year", type: "temporal" },
{ field: "CPI", type: "quantitative", title: "CPI" }
]
},
width: 600,
height: 300
})
CPI_areas = render({
data: { values: cpiData },
facet: {
column: { field: "City", type: "nominal", title: "City" }
},
spec: {
width: 220,
height: 150,
layer: [
// Area chart, 每个城市不同色
{
mark: { type: "area", interpolate: "monotone", opacity: 0.5 },
encoding: {
x: { field: "Year", type: "temporal", axis: { format: "%Y", title: null } },
y: {
field: "CPI", type: "quantitative", title: null,
scale: { domain: [0, 350] }
},
color: {
field: "City",
type: "nominal",
scale: { range: ["#a6cee3", "#b2df8a", "#fb9a99", "#fdbf6f"] }
}
}
},
// 隐形点用于 selection
{
mark: { type: "point", opacity: 0 },
selection: {
hover: {
type: "single",
on: "mouseover",
fields: ["Year"],
nearest: true,
empty: "none",
clear: "mouseout"
}
},
encoding: {
x: { field: "Year", type: "temporal" },
y: { field: "CPI", type: "quantitative" }
}
},
// 竖线，hover 时显示
{
transform: [{ filter: { selection: "hover" } }],
mark: { type: "rule", color: "#666" },
encoding: {
x: { field: "Year", type: "temporal" }
}
},
// 高亮点和 tooltip
{
transform: [{ filter: { selection: "hover" } }],
mark: { type: "point", size: 70, filled: true },
encoding: {
x: { field: "Year", type: "temporal" },
y: { field: "CPI", type: "quantitative" },
color: {
field: "City",
type: "nominal",
scale: { range: ["#a6cee3", "#b2df8a", "#fb9a99", "#fdbf6f"] }
},
tooltip: [
{ field: "City", type: "nominal" },
{ field: "Year", type: "temporal" },
{ field: "CPI", type: "quantitative", format: ".2f", title: "CPI" }
]
}
}
]
},
resolve: { scale: { y: "shared" } } // 统一y轴
})
data_3 = workbook.sheet(1, {
headers: true,
// range: "A1:J10"
})
data_3
Type Table, then Shift-Enter. Ctrl-space for more options.
meanWageData = [
...data.map(d => ({
City: "Seattle",
Year: new Date(d.Year, 0),
Wage: +d["Annual mean wage"]
})),
...data_3.flatMap(d => [
{ City: "San Francisco", Year: new Date(d.Area, 0), Wage: +d.SF },
{ City: "Chicago", Year: new Date(d.Area, 0), Wage: +d.Chi },
{ City: "New York", Year: new Date(d.Area, 0), Wage: +d.NY }
])
]
meanWageData
Type Table, then Shift-Enter. Ctrl-space for more options.
multi_meanWage = render({
mark: { type: "line", point: true },
data: { values: meanWageData },
selection: {
highlight: {
type: "single",
on: "mouseover",
fields: ["City"],
nearest: true,
empty: "all"
}
},
encoding: {
x: { field: "Year", type: "temporal", title: "Year" },
y: {
field: "Wage",
type: "quantitative",
title: "Annual Mean Wage ($)",
scale: { domainMin: 40000 },
axis: { format: "$,.0f", grid: true }
},
color: {
field: "City",
type: "nominal",
title: "City",
scale: {
range: ["#a6cee3", "#fb9a99", "#b2df8a", "#fdbf6f"]
}
},
opacity: {
condition: { selection: "highlight", value: 1 },
value: 0.25
},
tooltip: [
{ field: "City", type: "nominal" },
{ field: "Year", type: "temporal" },
{ field: "Wage", type: "quantitative", title: "Wage ($)" }
]
},
width: 600,
height: 300
})
myWageInteractiveChart = render({
$schema: "https://vega.github.io/schema/vega-lite/v5.json",
title: {
text: "Annual Mean Wage by City, 2015–2024",
fontSize: 22,
font: "sans-serif",
anchor: "start",
dx: 0
},
data: { values: meanWageData },
width: 800,
height: 400,
params: [
{
name: "highlight",
select: {
type: "point",
fields: ["City"],
on: "mouseover",
clear: "mouseout"
},
value: { City: "Seattle" } // 默认高亮哪个城市
},
{
name: "clicked",
select: {
type: "point",
fields: ["City"],
on: "click"
}
}
],
mark: { type: "area", interpolate: "monotone" },
encoding: {
x: {
field: "Year",
type: "temporal",
title: "Year",
axis: { labelAngle: 0 }
},
y: {
field: "Wage",
type: "quantitative",
stack: "zero",
title: "Total Annual Mean Wage ($)",
axis: { format: "$,.0f" }
},
color: {
field: "City",
type: "nominal",
title: "City",
scale: {
range: ["#cce3f6", "#daf5d8","#ffd6d6" , "#ffe6b3"]// Chicago, NY, SF, Seattle
}
},
opacity: {
condition: { param: "highlight", value: 1 },
value: 0.2
},
tooltip: [
{ field: "City", type: "nominal" },
{ field: "Year", type: "temporal", format: "%Y" },
{ field: "Wage", type: "quantitative", title: "Wage ($)", format: ",.0f" }
]
},
title: {
text: { signal: "clicked.City ? clicked.City : 'Annual Mean Wage Over Time'" },
anchor: "start",
offset: 20
}
})
Seattle_CPI_monthly = [
{ observation_date: "2015-02", CPI: 245.496 },
{ observation_date: "2015-04", CPI: 247.611 },
{ observation_date: "2015-06", CPI: 251.622 },
{ observation_date: "2015-08", CPI: 251.617 },
{ observation_date: "2015-10", CPI: 250.831 },
{ observation_date: "2015-12", CPI: 250.385 },
{ observation_date: "2016-02", CPI: 250.942 },
{ observation_date: "2016-04", CPI: 253.815 },
{ observation_date: "2016-06", CPI: 256.098 },
{ observation_date: "2016-08", CPI: 256.907 },
{ observation_date: "2016-10", CPI: 256.941 },
{ observation_date: "2016-12", CPI: 256.821 },
{ observation_date: "2017-02", CPI: 259.503 },
{ observation_date: "2017-04", CPI: 261.560 },
{ observation_date: "2017-06", CPI: 263.756 },
{ observation_date: "2017-08", CPI: 263.333 },
{ observation_date: "2017-10", CPI: 264.653 },
{ observation_date: "2017-12", CPI: 265.850 },
{ observation_date: "2018-02", CPI: 268.031 },
{ observation_date: "2018-04", CPI: 270.309 },
{ observation_date: "2018-06", CPI: 273.753 },
{ observation_date: "2018-08", CPI: 274.421 },
{ observation_date: "2018-10", CPI: 275.415 },
{ observation_date: "2018-12", CPI: 274.346 },
{ observation_date: "2019-02", CPI: 275.770 },
{ observation_date: "2019-04", CPI: 277.837 },
{ observation_date: "2019-06", CPI: 279.497 },
{ observation_date: "2019-08", CPI: 279.496 },
{ observation_date: "2019-10", CPI: 280.157 },
{ observation_date: "2019-12", CPI: 280.786 },
{ observation_date: "2020-02", CPI: 281.926 },
{ observation_date: "2020-04", CPI: 279.355 },
{ observation_date: "2020-06", CPI: 278.208 },
{ observation_date: "2020-08", CPI: 280.087 },
{ observation_date: "2020-10", CPI: 280.925 },
{ observation_date: "2020-12", CPI: 281.195 },
{ observation_date: "2021-02", CPI: 283.368 },
{ observation_date: "2021-04", CPI: 287.598 },
{ observation_date: "2021-06", CPI: 291.826 },
{ observation_date: "2021-08", CPI: 294.897 },
{ observation_date: "2021-10", CPI: 298.263 },
{ observation_date: "2021-12", CPI: 301.899 },
{ observation_date: "2022-02", CPI: 305.426 },
{ observation_date: "2022-04", CPI: 313.599 },
{ observation_date: "2022-06", CPI: 318.578 },
{ observation_date: "2022-08", CPI: 318.138 },
{ observation_date: "2022-10", CPI: 318.588 },
{ observation_date: "2022-12", CPI: 318.142 },
{ observation_date: "2023-02", CPI: 320.841 },
{ observation_date: "2023-04", CPI: 322.842 },
{ observation_date: "2023-06", CPI: 324.373 },
{ observation_date: "2023-08", CPI: 326.495 },
{ observation_date: "2023-10", CPI: 327.456 },
{ observation_date: "2023-12", CPI: 327.947 }
];
Seattle_CPI_Chart = vl.markLine({
point: { filled: true, size: 70, color: "#0072B5" },
strokeWidth: 3,
color: "#0072B5"
})
.data(Seattle_CPI_monthly)
.encode(
vl.x()
.fieldT("observation_date")
.title("Year")
.axis({ labelAngle: -35, format: "%Y-%m", labelFontSize: 13, titleFontSize: 16, tickCount: 10, grid: false }), // No grid
vl.y()
.fieldQ("CPI")
.title("Seattle CPI")
.scale({ domain: [240, 340] })
.axis({ labelFontSize: 14, titleFontSize: 16, grid: false }), // No grid
vl.tooltip([
{ field: "observation_date", type: "temporal", title: "Month" },
{ field: "CPI", type: "quantitative", title: "CPI" }
])
)
.width(780)
.height(420)
.render()
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.
