--- title: 'Gathering OSM Profiles for Research ' author: '' date: '2021-05-20' slug: gathering-osm-profiles-for-research categories: - VGI tags: - R - VGI - OSM - openstreetmap - profile subtitle: '' summary: '' authors: [] lastmod: '2021-05-20T14:18:34+01:00' featured: no image: caption: '' focal_point: '' preview_only: no projects: [] output: html_document: code_folding: hide toc: true number_sections: true toc_float: collapsed: false smooth_scroll: false ---
This blog elaborates the dataset published by Zoe Gardner (2020)’s paper Quantifying gendered participation in OpenStreetMap: responding to theories of female (under) representation in crowdsourced mapping.
Zoe has conducted a survey of OSM users to understand the likelihood contribution of under-represented groups. She gathered 315 user’s data with age, gender, nationality, country of residence, and degree. However, the problem with her survey data is that firstly, not all of the users can be browsed from the Name Browser, and secondly, there are plenty of information in the OSM web profile that hasn’t be investigated in her paper. For example:
Since our overall goal is to compare OSM contribution by demographic groups (assuming that there is a low contribution in underrepresented groups), here is a preliminary dataset that we explored. We note that all profile data are accessible as per the OSM policy.
Amongst multiple ways, we use two R packages tidyjson
to read and analyse json files, and tidyverse
for querying and further analysis.
library(tidyverse)
## ── Attaching packages ─────────────────────────────────────── tidyverse 1.3.1 ──
## ✓ ggplot2 3.3.3 ✓ purrr 0.3.4
## ✓ tibble 3.1.2 ✓ dplyr 1.0.6
## ✓ tidyr 1.1.3 ✓ stringr 1.4.0
## ✓ readr 1.4.0 ✓ forcats 0.5.1
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()
library(tidyjson)
##
## Attaching package: 'tidyjson'
## The following object is masked from 'package:stats':
##
## filter
We found 30 files in the Contributors folder all ending with json
. However, we want the files to be piled as a single file.
So we use purrr::map()
to query multiple json files and reduce
with bind_rows()
. We added id just to simplify the usernames.
# this should give you a character vector, with each file name represented by an entry
filenames <- list.files("Contributors/", pattern="*.json", full.names=TRUE)
# Import data
osm_json <-filenames %>%
map(read_json) %>%
reduce(bind_rows) %>%
mutate(document.id = row_number())
Using class()
, we realise that this file works as a tibble (known as rectangular dataframe with columns of info), and a json. But according to the order, this recognises as a json file.
# Check data type
class(osm_json)
## [1] "tbl_json" "tbl_df" "tbl" "data.frame"
With the assistance of tidyjson
package, we can take a look at our data with glimpse()
.
You can see that there are 30 rows (contributors) and 165 columns. The rows can increase as we gather more data.
osm_json %>%
spread_all() %>%
glimpse()
## Rows: 30
## Columns: 165
## $ document.id <int> 1, 2, 3, 4, 5, 6, 7, 8, 9,…
## $ publicprofile <chr> "false", "true", "false", …
## $ contributor.name <chr> "!i!", "<0174", "24dakenlo…
## $ contributor.uid <chr> "25720", "172147", "335421…
## $ contributor.since <chr> "2008-01-30", "2009-09-14"…
## $ contributor.traces <chr> "55", "204", "0", "0", "54…
## $ contributor.blocks <chr> "0", "0", "0", "0", "0", "…
## $ contributor.blocks_active <chr> "0", "0", "0", "0", "0", "…
## $ contributor.img <chr> "https://www.openstreetmap…
## $ contributor.roles <chr> "", "", "", "", "", "", ""…
## $ contributor.blocks_issued <chr> "0", "0", "0", "0", "0", "…
## $ contributor.blocks_active_issued <chr> "0", "0", "0", "0", "0", "…
## $ contributor.organised_editing <chr> "", "", "", "", "", "", ""…
## $ node.f_id <chr> "9832", "2478231", "350971…
## $ node.f_tstamp <chr> "2008-02-01", "2009-09-14"…
## $ node.f_lon <chr> "12.1092374", "15.2053555"…
## $ node.f_lat <chr> "54.073777", "50.90506595"…
## $ node.l_id <chr> "104695696", "104842849", …
## $ node.l_tstamp <chr> "2021-05-14", "2021-05-17"…
## $ node.l_lon <chr> "11.9780652", "13.6855164"…
## $ node.l_lat <chr> "53.8469423", "49.6288994"…
## $ nodes.c <chr> "808233", "72737", "11897"…
## $ nodes.m <chr> "254432", "9264", "4090", …
## $ nodes.d <chr> "29064", "8653", "5777", "…
## $ nodes.lm <chr> "510992", "53003", "11554"…
## $ ways.c <chr> "193820", "8584", "707", "…
## $ ways.m <chr> "123446", "5805", "808", "…
## $ ways.d <chr> "5200", "729", "107", "13"…
## $ ways.lm <chr> "124569", "5895", "521", "…
## $ waytags.c_addr <chr> "40655", "5995", "0", "178…
## $ waytags.m_addr <chr> "161369", "1500", "0", "34…
## $ waytags.c_building <chr> "127589", "3307", "104", "…
## $ waytags.m_building <chr> "107121", "980", "11", "61…
## $ waytags.c_highway <chr> "43818", "4093", "349", "2…
## $ waytags.m_highway <chr> "41793", "4738", "697", "2…
## $ waytags.c_landuse <chr> "9890", "1139", "44", "368…
## $ waytags.m_landuse <chr> "8134", "255", "9", "116",…
## $ waytags.c_name <chr> "7318", "1197", "61", "243…
## $ waytags.m_name <chr> "34538", "2618", "95", "14…
## $ waytags.c_natural <chr> "20163", "159", "186", "14…
## $ waytags.m_natural <chr> "2554", "82", "71", "45", …
## $ waytags.c_waterway <chr> "1777", "63", "0", "5", "1…
## $ waytags.m_waterway <chr> "982", "178", "3", "10", "…
## $ relations.c <chr> "407", "125", "2", "1", "6…
## $ relations.m <chr> "909", "909", "3", "26", "…
## $ relations.d <chr> "46", "13", "0", "1", "21"…
## $ relations.lm <chr> "149", "120", "1", "1", "1…
## $ changesets.no <chr> "6298", "1268", "99", "678…
## $ changesets.changes <chr> "1416423", "106792", "2339…
## $ changesets.max_gap_days <chr> "231", "85", "446", "2065"…
## $ changesets.mapping_days_year <chr> "2008=2;2009=209;2010=197;…
## $ changesets.info <chr> "6064;5690;3816;29.55;28.0…
## $ changesets.editors <chr> "JOSM=5327;OsmAnd=18;Pic4R…
## $ changesets.bboxs <chr> "5742;0.03;0.00", "1204;2.…
## $ changesets.days <chr> "948,736,877,886,874,824,1…
## $ changesets.hours <chr> "16,12,13,8,9,60,229,297,4…
## $ changesets.mapping_days <chr> "2130", "643", NA, "165", …
## $ changesets.comment_wordcount <chr> "details (1869), bing (151…
## $ changesets.hashtag_wordcount <chr> "#pic4review (4)", "", NA,…
## $ countries.countries <chr> "Germany=de=5733=1321809;P…
## $ notes.op <chr> "407", "14", "0", "1", "30…
## $ notes.co <chr> "451", "2", "0", "0", "37"…
## $ notes.cl <chr> "816", "7", "0", "0", "106…
## $ notes.clco <chr> "764", "7", "0", "0", "85"…
## $ discussion.dis_changesets <chr> "24", "3", "0", "2", "4", …
## $ discussion.com_changesets <chr> "26", "5", "0", "1", "61",…
## $ discussion.num_comments <chr> "32", "11", "0", "1", "86"…
## $ discussion.dis_responses <chr> "17", "3", "0", "1", "1", …
## $ changesets. <lgl> NA, NA, NA, NA, NA, NA, NA…
## $ changesets.review_requested <chr> NA, NA, NA, NA, NA, NA, "1…
## $ lastmodifier.node_tags.ref <chr> "52", "304", NA, NA, "24",…
## $ lastmodifier.node_tags.name <chr> "1345", "651", "32", "279"…
## $ lastmodifier.node_tags.shop <chr> "328", "36", NA, "31", "10…
## $ `lastmodifier.node_tags.addr:` <chr> "20060", "165", NA, "869",…
## $ `lastmodifier.node_tags.name:` <chr> "1", "50", "9", "2", "1", …
## $ lastmodifier.node_tags.place <chr> "60", "8", "3", "2", "97",…
## $ lastmodifier.node_tags.power <chr> "1050", "105", NA, NA, "1"…
## $ lastmodifier.node_tags.sport <chr> "62", "2", NA, NA, "4", "8…
## $ lastmodifier.node_tags.amenity <chr> "2726", "247", "73", "212"…
## $ lastmodifier.node_tags.barrier <chr> "775", "76", "1", "1", "60…
## $ lastmodifier.node_tags.highway <chr> "3863", "101", "1", NA, "5…
## $ lastmodifier.node_tags.leisure <chr> "422", "17", "5", NA, "2",…
## $ lastmodifier.node_tags.natural <chr> "3053", "88", "4", "1", "1…
## $ lastmodifier.node_tags.railway <chr> "116", "10", NA, NA, "4", …
## $ lastmodifier.node_tags.surface <chr> "6", NA, NA, NA, "4", "32"…
## $ lastmodifier.node_tags.tourism <chr> "356", "611", "42", "41", …
## $ lastmodifier.node_tags.building <chr> "10", "90", NA, NA, "8", "…
## $ lastmodifier.node_tags.historic <chr> "30", "86", "5", NA, "2", …
## $ lastmodifier.node_tags.man_made <chr> "1637", "16", NA, NA, "1",…
## $ lastmodifier.node_tags.waterway <chr> "13", NA, NA, NA, NA, "9",…
## $ `lastmodifier.node_tags.building:` <chr> "8", NA, NA, NA, NA, NA, "…
## $ lastmodifier.node_tags.emergency <chr> "42", "1", "4", NA, "1", "…
## $ lastmodifier.node_tags.public_transport <chr> "87", "16", NA, NA, "16", …
## $ lastmodifier.way_tags.ref <chr> "448", "136", NA, "1", "20…
## $ lastmodifier.way_tags.name <chr> "3541", "353", "5", "191",…
## $ lastmodifier.way_tags.shop <chr> "160", "5", NA, "19", "57"…
## $ `lastmodifier.way_tags.addr:` <chr> "57077", "25", NA, "405", …
## $ `lastmodifier.way_tags.name:` <chr> "6", "11", "2", "2", NA, N…
## $ lastmodifier.way_tags.place <chr> "12", "2", NA, NA, NA, "5"…
## $ lastmodifier.way_tags.power <chr> "68", "11", "1", NA, NA, "…
## $ lastmodifier.way_tags.sport <chr> "473", "8", "4", "4", "38"…
## $ lastmodifier.way_tags.amenity <chr> "1174", "36", "2", "133", …
## $ lastmodifier.way_tags.barrier <chr> "1463", "27", NA, "6", "68…
## $ lastmodifier.way_tags.highway <chr> "29309", "3330", "245", "1…
## $ lastmodifier.way_tags.landuse <chr> "6352", "635", "28", "220"…
## $ lastmodifier.way_tags.leisure <chr> "929", "22", "9", "35", "8…
## $ lastmodifier.way_tags.natural <chr> "11732", "66", "150", "87"…
## $ lastmodifier.way_tags.railway <chr> "127", "27", NA, NA, "1", …
## $ lastmodifier.way_tags.surface <chr> "3743", "1237", NA, "13", …
## $ lastmodifier.way_tags.tourism <chr> "333", "1", "1", "23", "9"…
## $ lastmodifier.way_tags.boundary <chr> "18", "5", "1", NA, "86", …
## $ lastmodifier.way_tags.building <chr> "68902", "1622", "76", "41…
## $ lastmodifier.way_tags.historic <chr> "20", NA, NA, "2", NA, "1"…
## $ lastmodifier.way_tags.man_made <chr> "1042", "13", NA, "10", "5…
## $ lastmodifier.way_tags.waterway <chr> "1330", "80", "1", "5", "1…
## $ `lastmodifier.way_tags.building:` <chr> "9747", "196", "1", "8", "…
## $ lastmodifier.way_tags.emergency <chr> "25", "3", NA, NA, NA, NA,…
## $ lastmodifier.way_tags.public_transport <chr> "28", NA, NA, NA, NA, NA, …
## $ lastmodifier.relation_tags.ref <chr> "16", "55", NA, NA, "6", "…
## $ lastmodifier.relation_tags.name <chr> "63", "41", NA, NA, "77", …
## $ lastmodifier.relation_tags.shop <chr> "1", NA, NA, NA, NA, NA, "…
## $ `lastmodifier.relation_tags.addr:` <chr> "104", NA, NA, NA, "48", "…
## $ `lastmodifier.relation_tags.name:` <chr> "11", "4", NA, NA, NA, NA,…
## $ lastmodifier.relation_tags.route <chr> "21", "102", NA, NA, "7", …
## $ lastmodifier.relation_tags.amenity <chr> "21", NA, NA, NA, "11", "2…
## $ lastmodifier.relation_tags.highway <chr> "2", NA, NA, NA, NA, "1", …
## $ lastmodifier.relation_tags.landuse <chr> "8", "3", NA, NA, "1", "11…
## $ lastmodifier.relation_tags.natural <chr> "23", NA, "1", NA, "2", "1…
## $ lastmodifier.relation_tags.surface <chr> "1", NA, NA, NA, NA, "24",…
## $ lastmodifier.relation_tags.tourism <chr> "1", NA, NA, NA, NA, NA, "…
## $ lastmodifier.relation_tags.building <chr> "59", NA, NA, "1", "6", "1…
## $ lastmodifier.relation_tags.historic <chr> "1", NA, NA, NA, NA, NA, N…
## $ `lastmodifier.relation_tags.building:` <chr> "39", NA, NA, NA, NA, "4",…
## $ lastmodifier.relation_tags.restriction <chr> "4", "5", NA, NA, "15", "8…
## $ lastmodifier.relation_tags.public_transport <chr> "1", NA, NA, NA, NA, NA, "…
## $ `accounts.OSM Wiki.id` <chr> "!i!", NA, NA, NA, NA, NA,…
## $ `accounts.OSM Wiki.Editcount` <chr> "8782", NA, NA, NA, NA, NA…
## $ `accounts.OSM Help.id` <chr> "4984/iii", NA, NA, NA, NA…
## $ `accounts.OSM Help.Reputation` <chr> "4,892", NA, NA, NA, NA, N…
## $ `accounts.OSM Forum.id` <chr> "4960", NA, NA, NA, NA, NA…
## $ `accounts.OSM Forum.Posts` <chr> "3,311", NA, NA, NA, NA, N…
## $ accounts.Mapillary.id <chr> "8of9", NA, NA, NA, NA, NA…
## $ `accounts.Mapillary.Total Photos` <chr> "42671", NA, NA, NA, NA, N…
## $ `accounts.Mapillary.Total Distance` <chr> "256.98 km", NA, NA, NA, N…
## $ lastmodifier.way_tags.route <chr> NA, "1", NA, NA, NA, NA, "…
## $ lastmodifier.relation_tags.boundary <chr> NA, "6", NA, NA, "4", "116…
## $ lastmodifier.relation_tags.waterway <chr> NA, NA, NA, NA, "2", "2", …
## $ lastmodifier.relation_tags.emergency <chr> NA, NA, NA, NA, "1", NA, "…
## $ lastmodifier.node_tags.landuse <chr> NA, NA, NA, NA, NA, "5", "…
## $ lastmodifier.relation_tags.place <chr> NA, NA, NA, NA, NA, "11", …
## $ lastmodifier.relation_tags.power <chr> NA, NA, NA, NA, NA, "12", …
## $ lastmodifier.relation_tags.sport <chr> NA, NA, NA, NA, NA, "1", "…
## $ lastmodifier.relation_tags.leisure <chr> NA, NA, NA, NA, NA, "18", …
## $ lastmodifier.relation_tags.man_made <chr> NA, NA, NA, NA, NA, "1", "…
## $ lastmodifier.relation_tags.addr <chr> NA, NA, NA, NA, NA, NA, "1…
## $ lastmodifier.relation_tags.barrier <chr> NA, NA, NA, NA, NA, NA, "1…
## $ accounts.OpenStreetCam.id <chr> NA, NA, NA, NA, NA, NA, NA…
## $ `accounts.OpenStreetCam.Total Photos` <chr> NA, NA, NA, NA, NA, NA, NA…
## $ `accounts.OpenStreetCam.Total Distance` <chr> NA, NA, NA, NA, NA, NA, NA…
## $ accounts.Github.id <chr> NA, NA, NA, NA, NA, NA, NA…
## $ accounts.Github.Contributions <chr> NA, NA, NA, NA, NA, NA, NA…
## $ lastmodifier.node_tags.boundary <chr> NA, NA, NA, NA, NA, NA, NA…
## $ lastmodifier.relation_tags.railway <chr> NA, NA, NA, NA, NA, NA, NA…
## $ lastmodifier.way_tags.restriction <chr> NA, NA, NA, NA, NA, NA, NA…
## $ ..JSON <list> ["false", ["!i!", "25720"…
Otherwise, we can query the first hierarchy of data.
# browse Types
osm_json %>%
gather_object %>%
json_types %>%
count(name, type) %>%
print(n = Inf)
## # A tibble: 18 x 3
## name type n
## <chr> <fct> <int>
## 1 accounts object 6
## 2 accounts array 24
## 3 calendar array 30
## 4 changesets object 30
## 5 contributor object 30
## 6 countries object 30
## 7 discussion object 30
## 8 lastmodifier object 30
## 9 node object 30
## 10 nodes object 30
## 11 notes object 30
## 12 publicprofile string 30
## 13 qa array 30
## 14 ranks array 30
## 15 recent array 30
## 16 relations object 30
## 17 ways object 30
## 18 waytags object 30
You can query a few columns of your own. It seems quite messy on the JavaScript when you open it in R, but the package will help you clean them.
# Glance at the first index
osm_json %>%
enter_object('contributor') %>%
gather_object('index.1') %>%
spread_all() %>%
append_values_string()
## Warning in spread_all(.): no JSON records are objects, returning .x
## # A tbl_json: 330 x 4 tibble with a "JSON" attribute
## ..JSON document.id index.1 string
## <chr> <int> <chr> <chr>
## 1 "\"!i!\"" 1 name "!i!"
## 2 "\"25720\"" 1 uid "25720"
## 3 "\"2008-01-3… 1 since "2008-01-30"
## 4 "\"55\"" 1 traces "55"
## 5 "\"0\"" 1 blocks "0"
## 6 "\"0\"" 1 blocks_active "0"
## 7 "\"https://w… 1 img "https://www.openstreetmap.org/rails…
## 8 "\"\"" 1 roles ""
## 9 "\"0\"" 1 blocks_issued "0"
## 10 "\"0\"" 1 blocks_activ… "0"
## # … with 320 more rows
osm_json %>%
as_tbl_json(drop.nulljson = T) %>%
enter_object('changesets') %>%
spread_values(days = jstring(days))
## # A tbl_json: 30 x 3 tibble with a "JSON" attribute
## ..JSON document.id days
## <chr> <int> <chr>
## 1 "{\"no\":\"6298\",\"c..." 1 948,736,877,886,874,824,1153
## 2 "{\"no\":\"1268\",\"c..." 2 303,168,124,188,92,140,253
## 3 "{\"no\":\"99\",\"cha..." 3 23,7,2,2,10,2,53
## 4 "{\"no\":\"678\",\"ch..." 4 130,143,89,131,88,60,37
## 5 "{\"no\":\"919\",\"ch..." 5 136,187,103,140,152,123,78
## 6 "{\"no\":\"1602\",\"c..." 6 93,324,343,337,284,127,94
## 7 "{\"no\":\"5099\",\"c..." 7 866,715,635,678,616,649,940
## 8 "{\"no\":\"686\",\"ch..." 8 96,140,104,79,110,74,83
## 9 "{\"no\":\"574\",\"ch..." 9 36,63,88,100,115,92,80
## 10 "{\"no\":\"15080\",\"..." 10 2406,2317,2068,2220,2110,1849,2110
## # … with 20 more rows
Those who might not want to see the JSON file automatically attached with the dataframe, we will show you how we dropped the json keys by using as_data_frame.tbl_json()
. You can see that “tbl_df” has now become the primary data type.
osm_json %>%
spread_all() %>%
as_data_frame.tbl_json() -> osm_tibble
class(osm_tibble)
## [1] "tbl_df" "tbl" "data.frame"
survey_original <- readxl::read_xlsx("OSM survey data.xlsx") %>% select(-c(`(Found) Username`, ...6, `6.a. If you selected Other, please specify:`))
survey_original %>%
rename(username = `1. What is your OpenStreetMap Username?`,
gender = `2. What gender do you identify as?`,
age = `3. What is your age?`,
country_residence = `4. What is your country of residence?`,
nationality = `5. What is your nationality?`,
education = `6. What is your highest level of education?`
) -> survey
survey %>%
slice(1:30) %>%
mutate(id = row_number()) %>%
select(id, gender, age) -> survey_profile
survey_profile
## # A tibble: 30 x 3
## id gender age
## <int> <chr> <chr>
## 1 1 Male 55-59
## 2 2 Male 30-34
## 3 3 Male 30-34
## 4 4 Male 45-49
## 5 5 Male 30-34
## 6 6 Male 40-44
## 7 7 Male 30-34
## 8 8 Male 35-39
## 9 9 Male 30-34
## 10 10 Male 25-29
## # … with 20 more rows
survey %>%
left_join(osm_tibble, by = c("username" = "contributor.name")) %>%
select(username, gender, age, country_residence, nationality, education,
starts_with("contributor"))
## # A tibble: 315 x 16
## username gender age country_residen… nationality education contributor.uid
## <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 - no ! W… Male 55-59 German German Universi… <NA>
## 2 !i! Male 30-34 Germany german Universi… 25720
## 3 <0174 Male 30-34 Czech Republic Czech Universi… 172147
## 4 24dakenlo Male 45-49 Japan Japan PhD 3354215
## 5 3mg4 Male 30-34 Austria Austria Universi… <NA>
## 6 ACM Male 40-44 UK British Universi… 20372
## 7 ACS1986 Male 30-34 England British Universi… 2018957
## 8 adamfran… Male 35-39 U.S.A. U.S.A. Universi… <NA>
## 9 adrienan… Male 30-34 French Guiana French Universi… <NA>
## 10 AkuAnakT… Male 25-29 Malaysia Malaysian A Levels… 1407839
## # … with 305 more rows, and 9 more variables: contributor.since <chr>,
## # contributor.traces <chr>, contributor.blocks <chr>,
## # contributor.blocks_active <chr>, contributor.img <chr>,
## # contributor.roles <chr>, contributor.blocks_issued <chr>,
## # contributor.blocks_active_issued <chr>, contributor.organised_editing <chr>
survey %>%
left_join(osm_tibble, by = c("username" = "contributor.name")) %>%
select(username, gender, age, country_residence, nationality, education,
starts_with("lastmodifier")) %>%
DT::datatable()
### Days
osm_json %>%
as_tbl_json(drop.nulljson = T) %>%
enter_object('changesets') %>%
spread_values(days = jstring(days)) %>%
as.data.frame() %>%
pull(2)-> days
data.frame(days) %>%
mutate(days = gsub("\\|$","", days)) %>%
separate_rows(days, sep = "[|]") %>%
separate(days, c("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"), ",") -> days_df
survey_profile %>%
bind_cols(days_df)
## # A tibble: 30 x 10
## id gender age Sun Mon Tue Wed Thu Fri Sat
## <int> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 1 Male 55-59 948 736 877 886 874 824 1153
## 2 2 Male 30-34 303 168 124 188 92 140 253
## 3 3 Male 30-34 23 7 2 2 10 2 53
## 4 4 Male 45-49 130 143 89 131 88 60 37
## 5 5 Male 30-34 136 187 103 140 152 123 78
## 6 6 Male 40-44 93 324 343 337 284 127 94
## 7 7 Male 30-34 866 715 635 678 616 649 940
## 8 8 Male 35-39 96 140 104 79 110 74 83
## 9 9 Male 30-34 36 63 88 100 115 92 80
## 10 10 Male 25-29 2406 2317 2068 2220 2110 1849 2110
## # … with 20 more rows
### hours
osm_json %>%
as_tbl_json(drop.nulljson = T) %>%
enter_object('changesets') %>%
spread_values(hours = jstring(hours)) %>%
as.data.frame() %>%
pull(2)-> hours
data.frame(hours) %>%
mutate(hours = gsub("\\|$","", hours)) %>%
separate_rows(hours, sep = "[|]") %>%
separate(hours, c("h01", "h02", "h03", "h04", "h05", "h06",
"h07", "h08", "h09", "h10", "h11", "h12",
"h13", "h14", "h15", "h16", "h17", "h18",
"h19", "h20", "h21", "h22", "h23", "h24"
), ",") -> hours_df
survey_profile %>%
bind_cols(hours_df)
## # A tibble: 30 x 27
## id gender age h01 h02 h03 h04 h05 h06 h07 h08 h09
## <int> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 1 Male 55-59 16 12 13 8 9 60 229 297 400
## 2 2 Male 30-34 0 0 0 0 0 0 19 16 37
## 3 3 Male 30-34 1 7 0 0 0 3 4 7 18
## 4 4 Male 45-49 18 2 0 0 0 0 2 26 32
## 5 5 Male 30-34 32 25 7 1 6 4 8 10 22
## 6 6 Male 40-44 41 56 64 67 37 39 33 13 3
## 7 7 Male 30-34 166 174 236 277 395 336 343 268 178
## 8 8 Male 35-39 1 0 0 0 0 0 3 47 45
## 9 9 Male 30-34 21 21 10 23 64 74 93 95 40
## 10 10 Male 25-29 920 770 651 826 774 575 573 652 727
## # … with 20 more rows, and 15 more variables: h10 <chr>, h11 <chr>, h12 <chr>,
## # h13 <chr>, h14 <chr>, h15 <chr>, h16 <chr>, h17 <chr>, h18 <chr>,
## # h19 <chr>, h20 <chr>, h21 <chr>, h22 <chr>, h23 <chr>, h24 <chr>