Scoda openrefine-directordata
Download as PPTX, PDF0 likes770 views
The document discusses grabbing director data from JSON results and parsing it into a formatted string with officer IDs, names, positions, start dates, and end dates separated by colons and each director separated by pipes. It recommends renaming the newly created columns and explains that filling down will copy values from preceding rows into blank rows in a column.
![forEach(value.parseJson()['results']['company']['officers'], v
, [v.officer.id, v.officer.name, v.officer.position, v.officer.star
t_date, v.officer.end_date].join('::')).join('||')](https://image.slidesharecdn.com/scoda-openrefine-directordata-131105103146-phpapp01/85/Scoda-openrefine-directordata-9-320.jpg)
Scoda openrefine-directordata
- 9. forEach(value.parseJson()['results']['company']['officers'], v , [v.officer.id, v.officer.name, v.officer.position, v.officer.star t_date, v.officer.end_date].join('::')).join('||')
- 15. (It would probably make sense to rename the newly created columns.)
- 16. Starting with the first row, Fill down will fill blank rows in a column with the value In the preceding row (so we can fill down company names and ID columns for each corresponding director)
- 17. SchoolOfData.org
Editor's Notes
- #2: A recipe for grabbing director information from OpenCorporatesusing OpenRefinegiven an OpenCorporates company ID or OpenCorporates company page URL For more information, contact: schoolOfData.org
- #3: Heres the start of thing were starting with a list of companies
- #4: Heres the sort of thing we want lists of directors associated with each company (where that information is available).
- #5: The first step is to create a web address/URL to call the OpenCorporates API and ask it for data about a particular company. OpenRefine can create a new column populated with the contents of calls made to a URL contained in, or generated from, another column.
- #6: The URLs should take the form:http://api.opencorporates.com/companies/JURISDICTION/COMPANY_IDIf you already have company page URLs in a column, add column based on that column using:value.replace(http://,http://api)If you have JURISDICTION/COMPANY_ID in a column, use the formula:http://api.opencorporates.com/companies/+value
- #7: The data comes back as JSON data, which we will need to process.Each JSON result contains the data for a single company. The data relating to the directors can be found as a list down the path value.parseJson()['results']['company']['officers]
- #8: Lets parse the JSON data an put the directors information into another column
- #9: What we are aiming for is a contrivance based on the form:32866743::SIMON ALAN CONSTANT-GLEMAS::director::2010-04-07::null32866744::KARIN JACQUELINE HAWKINS::director::2006-01-17::2012-02-2232866745::ANDREW WILLIAM LONGDEN::director::2003-11-03::nullwhere we list director ID, name, position, appointment date, termination date.
- #10: This function will parse the data into string with the form:32866743::SIMON ALAN CONSTANT-GLEMAS::director::2010-04-07::null||32866744::KARIN JACQUELINE HAWKINS::director::2006-01-17::2012-02-22||32866745::ANDREW WILLIAM LONGDEN::director::2003-11-03::null||The function reads as follows: for each officer, join their ID, name, position, start date and end data with ::, then join each of these director descriptions using ||.The use of two different and hopefully unique delimiters means we can split the data on each delimiter type separately.
- #11: The parsed data is put into a new column in this combined list form.
- #12: We can then split the data so that we create a new row for each director using the delimiter we defined: ||
- #13: Note that values from the other columns will not be copied into any newly created rows we will have to do that ourselves either now, or later.
- #14: For each director, we now want to split their details out across several columns, one for each data field (ID, name, position, appointment date, termination date).
- #15: We can do this by splitting on the other separator type we used: ::
- #16: The newly created columns are labeled with automatically generated names. It would probably make sense to rename them to something slightly more convenient.
- #17: Finally, we can do a little more tidying. For any columns we want to export, such as company name, or company ID, we can Fill down using the corresponding values from the original row the directors information was pulled from.
- #18: If you want to know more, contact us