The avatar for @observablehq
Observable
The end-to-end solution for building and hosting better data apps, dashboards, and reports.
Published
Edited
Aug 23, 2022
1 fork
14 stars
8
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
// The original CSV files totaled 650MB, these total 36MB
db = DuckDBClient.of({
data2016: FileAttachment("survey_results_public_2016.parquet"),
data2017: FileAttachment("survey_results_public_2017.parquet"),
data2018: FileAttachment("survey_results_public_2018@1.parquet"),
data2019: FileAttachment("survey_results_public_2019.parquet"),
data2020: FileAttachment("survey_results_public_2020.parquet"),
data2021: FileAttachment("survey_results_public_2021.parquet"),
data2022: FileAttachment("survey_results_public_2022.parquet"),
})
Insert cell
Insert cell
languageQuestionLookup = ({
2016: {
'tech_want': '',
'tech_do': ''
},
2017: {
'WantWorkLanguage': '',
'HaveWorkedLanguage': ''
},
2018: {
'WantWorkLanguage': '',
'HaveWorkedLanguage': ''
},
2019: {
'LanguageDesireNextYear': '',
'LanguageWorkedWith': ''
},
2020: {
'LanguageDesireNextYear': '',
'LanguageWorkedWith': ''
},
2021: {
'LanguageWantToWorkWith': '',
'LanguageHaveWorkedWith': ''
},
2022: {
'LanguageWantToWorkWith': '',
'LanguageHaveWorkedWith': ''
}
})
Insert cell
Insert cell
db
SELECT 'want' as type
, 2016::int as year
, tech_want as "language"
, count(*)::int as value
FROM data2016
GROUP BY 1, 2, 3
UNION
SELECT 'do' as type
, 2016::int as year
, tech_do as "language"
, count(*)::int as value
FROM data2016
GROUP BY 1, 2, 3
UNION
SELECT 'want' as type
, 2017::int as year
, WantWorkLanguage as "language"
, count(*)::int as value
FROM data2017
GROUP BY 1, 2, 3
UNION
SELECT 'do' as type
, 2017::int as year
, HaveWorkedLanguage as "language"
, count(*)::int as value
FROM data2017
GROUP BY 1, 2, 3
UNION
SELECT 'want' as type
, 2018::int as year
, LanguageDesireNextYear as "language"
, count(*)::int as value
FROM data2018
GROUP BY 1, 2, 3
UNION
SELECT 'do' as type
, 2018::int as year
, LanguageWorkedWith as "language"
, count(*)::int as value
FROM data2018
GROUP BY 1, 2, 3
UNION
SELECT 'want' as type
, 2019::int as year
, LanguageDesireNextYear as "language"
, count(*)::int as value
FROM data2019
GROUP BY 1, 2, 3
UNION
SELECT 'do' as type
, 2019::int as year
, LanguageWorkedWith as "language"
, count(*)::int as value
FROM data2019
GROUP BY 1, 2, 3
UNION
SELECT 'want' as type
, 2020::int as year
, LanguageDesireNextYear as "language"
, count(*)::int as value
FROM data2020
GROUP BY 1, 2, 3
UNION
SELECT 'do' as type
, 2020::int as year
, LanguageWorkedWith as "language"
, count(*)::int as value
FROM data2020
GROUP BY 1, 2, 3
UNION
SELECT 'want' as type
, 2021::int as year
, LanguageWantToWorkWith as "language"
, count(*)::int as value
FROM data2021
GROUP BY 1, 2, 3
UNION
SELECT 'do' as type
, 2021::int as year
, LanguageHaveWorkedWith as "language"
, count(*)::int as value
FROM data2021
GROUP BY 1, 2, 3
UNION
SELECT 'want' as type
, 2022::int as year
, LanguageWantToWorkWith as "language"
, count(*)::int as value
FROM data2022
GROUP BY 1, 2, 3
UNION
SELECT 'do' as type
, 2022::int as year
, LanguageHaveWorkedWith as "language"
, count(*)::int as value
FROM data2022
GROUP BY 1, 2, 3
Insert cell
Insert cell
db
SELECT 2016 as year
, count(*)::int as value
FROM data2016
GROUP BY 1

UNION
SELECT 2017 as year
, count(*)::int as value
FROM data2017
GROUP BY 1
UNION
SELECT 2018 as year
, count(*)::int as value
FROM data2018
GROUP BY 1
UNION
SELECT 2019 as year
, count(*)::int as value
FROM data2019
GROUP BY 1
UNION
SELECT 2020 as year
, count(*)::int as value
FROM data2020
GROUP BY 1
UNION
SELECT 2021 as year
, count(*)::int as value
FROM data2021
GROUP BY 1
UNION
SELECT 2022 as year
, count(*)::int as value
FROM data2022
GROUP BY 1
Insert cell
Insert cell
normalizeLookup = d3.rollup(responseNormalizeValues, v=> v[0].value, d => d.year)
Insert cell
Insert cell
languageGroups = {

const allResults = d3.merge(languageFlatRaw
.filter(d => d.language != null)
.map(d => {
const languages = Array.from(new Set(d.language.split(';').map(e => e.trim())))
return languages.map(e => ({type: d.type, year: d.year, value: d.value, language: e}))
}))

const groupedByLanguage = d3.rollups(allResults, values => {

const wantValues = d3.rollups(values.filter(d => d.type == 'want'),
v => d3.sum(v, f => f.value) / normalizeLookup.get(v[0].year),
d => d.year);
const doValues = d3.rollup(values.filter(d => d.type == 'do'),
v => d3.sum(v, f => f.value) / normalizeLookup.get(v[0].year),
d => d.year);

return wantValues.map(d => ({
language: values[0].language,
year: d[0],
want_value: d[1],
do_value: doValues.get(d[0])
}))
}, d => d.language)

return groupedByLanguage.filter(d => d[1].length >= 6).filter(d => d[0] != 'NA')

}
Insert cell
Insert cell
Insert cell
Insert cell
data = languageGroups.flatMap(d => d[1])
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell

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.
Learn more