--- 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 ---

tl;dr

Intro

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.

Load library and fetch files

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"

Glance at our dataset

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

Convert to an R style dataframe

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"

Merge the survey data

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

Data Query

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()

Final Product: Days

### 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

Final Product: Hours

### 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>