Skip to content

Instantly share code, notes, and snippets.

@joewiz

joewiz/youngest-principal-officers-by-position.xq

Created October 21, 2019 19:28
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save joewiz/84dcdc0b5ce6d5b6d38b9c3f8829c35a to your computer and use it in GitHub Desktop.
Save joewiz/84dcdc0b5ce6d5b6d38b9c3f8829c35a to your computer and use it in GitHub Desktop.
Download ZIP
Sort Principal Officers in a position by age at time of appointment
Raw
youngest-principal-officers-by-position.xq
xquery version "3.1";
(: For a given Principal Officer position, calculate the age of the official at the time of
: appointment; sort the results by age. For officials missing birth or appointment dates,
: return the age as 0 to identify these as outliers with incomplete information.
:)
array {
let $principal-id := "assistant-secretary-legislative-affairs"
let $principal-doc := doc("/db/apps/pocom/positions-principals/" || $principal-id || ".xml")
let $principals := $principal-doc//principal
for $principal in $principals
let $person := collection("/db/apps/pocom/people")/person[id = $principal/person-id]
let $start-date := $principal/(appointed | started)/date[. ne ""][1]
let $birth := $person/birth
let $age :=
if ($birth ne "" and $start-date ne "") then
($start-date => substring(1, 4) => xs:integer()) - ($birth cast as xs:integer)
else
0
order by $age
return
map {
"person-id": $person/id/string(),
"age-at-appointment": $age,
"birth-year": $birth/string(),
"started-date": $start-date/string(),
"url": "https://hi
}
}
Raw
youngest-principal-officers-by-position_results.json
[
{
"birth-year": "1893",
"url": "https://history.state.gov/departmenthistory/people/acheson-dean-gooderham",
"person-id": "acheson-dean-gooderham",
"age-at-appointment": 0,
"started-date": null
},
{
"birth-year": "",
"url": "https://history.state.gov/departmenthistory/people/taylor-mary-e",
"person-id": "taylor-mary-e",
"age-at-appointment": 0,
"started-date": "2018-10-12"
},
{
"birth-year": "1921",
"url": "https://history.state.gov/departmenthistory/people/macomber-william-butts-jr",
"person-id": "macomber-william-butts-jr",
"age-at-appointment": 36,
"started-date": "1957-08-15"
},
{
"birth-year": "1942",
"url": "https://history.state.gov/departmenthistory/people/atwood-john-brian",
"person-id": "atwood-john-brian",
"age-at-appointment": 37,
"started-date": "1979-08-02"
},
{
"birth-year": "1948",
"url": "https://history.state.gov/departmenthistory/people/ball-william-lockhart",
"person-id": "ball-william-lockhart",
"age-at-appointment": 37,
"started-date": "1985-03-29"
},
{
"birth-year": "1923",
"url": "https://history.state.gov/departmenthistory/people/dutton-frederick-gary",
"person-id": "dutton-frederick-gary",
"age-at-appointment": 38,
"started-date": "1961-11-29"
},
{
"birth-year": "1948",
"url": "https://history.state.gov/departmenthistory/people/fox-j-edward",
"person-id": "fox-j-edward",
"age-at-appointment": 38,
"started-date": "1986-06-16"
},
{
"birth-year": "1917",
"url": "https://history.state.gov/departmenthistory/people/hill-robert-charles",
"person-id": "hill-robert-charles",
"age-at-appointment": 39,
"started-date": "1956-03-07"
},
{
"birth-year": "1938",
"url": "https://history.state.gov/departmenthistory/people/bennet-douglas-joseph",
"person-id": "bennet-douglas-joseph",
"age-at-appointment": 39,
"started-date": "1977-03-16"
},
{
"birth-year": "1941",
"url": "https://history.state.gov/departmenthistory/people/fairbanks-richard",
"person-id": "fairbanks-richard",
"age-at-appointment": 40,
"started-date": "1981-02-27"
},
{
"birth-year": "1949",
"url": "https://history.state.gov/departmenthistory/people/mullins-janet-gardner",
"person-id": "mullins-janet-gardner",
"age-at-appointment": 40,
"started-date": "1989-03-02"
},
{
"birth-year": "1968",
"url": "https://history.state.gov/departmenthistory/people/verma-richard-rahul",
"person-id": "verma-richard-rahul",
"age-at-appointment": 41,
"started-date": "2009-04-03"
},
{
"birth-year": "1906",
"url": "https://history.state.gov/departmenthistory/people/gross-ernest-arnold",
"person-id": "gross-ernest-arnold",
"age-at-appointment": 43,
"started-date": "1949-03-02"
},
{
"birth-year": "1905",
"url": "https://history.state.gov/departmenthistory/people/mcfall-jack-kirkham",
"person-id": "mcfall-jack-kirkham",
"age-at-appointment": 44,
"started-date": "1949-10-14"
},
{
"birth-year": "1926",
"url": "https://history.state.gov/departmenthistory/people/abshire-david-manker",
"person-id": "abshire-david-manker",
"age-at-appointment": 44,
"started-date": "1970-04-08"
},
{
"birth-year": "1938",
"url": "https://history.state.gov/departmenthistory/people/moore-powell-allen",
"person-id": "moore-powell-allen",
"age-at-appointment": 44,
"started-date": "1982-02-07"
},
{
"birth-year": "1949",
"url": "https://history.state.gov/departmenthistory/people/sherman-wendy-r",
"person-id": "sherman-wendy-r",
"age-at-appointment": 44,
"started-date": "1993-05-12"
},
{
"birth-year": "1964",
"url": "https://history.state.gov/departmenthistory/people/reynolds-matthew-a",
"person-id": "reynolds-matthew-a",
"age-at-appointment": 44,
"started-date": "2008-10-06"
},
{
"birth-year": "1951",
"url": "https://history.state.gov/departmenthistory/people/larkin-barbara-mills",
"person-id": "larkin-barbara-mills",
"age-at-appointment": 45,
"started-date": "1996-07-02"
},
{
"birth-year": "1907",
"url": "https://history.state.gov/departmenthistory/people/morton-thruston-ballard",
"person-id": "morton-thruston-ballard",
"age-at-appointment": 46,
"started-date": "1953-01-29"
},
{
"birth-year": "1921",
"url": "https://history.state.gov/departmenthistory/people/macomber-william-butts-jr",
"person-id": "macomber-william-butts-jr",
"age-at-appointment": 46,
"started-date": "1967-03-02"
},
{
"birth-year": "1926",
"url": "https://history.state.gov/departmenthistory/people/wright-william-marshall",
"person-id": "wright-william-marshall",
"age-at-appointment": 47,
"started-date": "1973-05-24"
},
{
"birth-year": "1961",
"url": "https://history.state.gov/departmenthistory/people/adams-david-s",
"person-id": "adams-david-s",
"age-at-appointment": 50,
"started-date": "2011-08-04"
},
{
"birth-year": "1923",
"url": "https://history.state.gov/departmenthistory/people/holton-abner-linwood",
"person-id": "holton-abner-linwood",
"age-at-appointment": 51,
"started-date": "1974-02-27"
},
{
"birth-year": "1962",
"url": "https://history.state.gov/departmenthistory/people/frifield-julia",
"person-id": "frifield-julia",
"age-at-appointment": 51,
"started-date": "2013-10-18"
},
{
"birth-year": "1922",
"url": "https://history.state.gov/departmenthistory/people/mccloskey-robert-james",
"person-id": "mccloskey-robert-james",
"age-at-appointment": 53,
"started-date": "1975-02-20"
},
{
"birth-year": "1947",
"url": "https://history.state.gov/departmenthistory/people/kelly-paul-vincent",
"person-id": "kelly-paul-vincent",
"age-at-appointment": 54,
"started-date": "2001-05-31"
},
{
"birth-year": "1909",
"url": "https://history.state.gov/departmenthistory/people/macarthur-douglas",
"person-id": "macarthur-douglas",
"age-at-appointment": 56,
"started-date": "1965-03-11"
},
{
"birth-year": "1946",
"url": "https://history.state.gov/departmenthistory/people/bergner-jeffrey-thomas",
"person-id": "bergner-jeffrey-thomas",
"age-at-appointment": 59,
"started-date": "2005-11-08"
},
{
"birth-year": "1958",
"url": "https://history.state.gov/departmenthistory/people/waters-mary-kirtley",
"person-id": "waters-mary-kirtley",
"age-at-appointment": 59,
"started-date": "2017-12-19"
},
{
"birth-year": "1898",
"url": "https://history.state.gov/departmenthistory/people/hays-lawrence-brooks",
"person-id": "hays-lawrence-brooks",
"age-at-appointment": 63,
"started-date": "1961-02-20"
},
{
"birth-year": "1917",
"url": "https://history.state.gov/departmenthistory/people/bennett-william-tapley",
"person-id": "bennett-william-tapley",
"age-at-appointment": 66,
"started-date": "1983-11-14"
}
]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment