R - converting dataframe into json

Question

I want to convert the below dataframe into JSON but I am not sure how to achieve it. I tried to find some help online but looks like no one has done it on a r dataframe.

df<-data.frame(profileKey=c(7,7,7,8,8,8),joinDate=c('2007-11-01','2007-11-
01','2007-11-01','1993-01-04','1993-01-04','1993-01-04'),Year=c('2013','2014','2015','2013','2014','2015'),monthlySalary=c('2000','3251','2015','4355','1112','33223'),event=c(0,0,0,0,0,1))

My desired output is :

{

"7": {
    "Profile_key": 7,
    "Join.Date": "2007-11-01",
    "event": {
        "2013": "0",
        "2014": "0",
        "2015": "0"
    },
    "monthly_salary": {
        "2013": 2000,
        "2014": 3251,
        "2015": 2015
    }
},
"8": {
    "Profile_key": 8,
    "Join.Date": "1993-01-04",
    "event": {
        "2013": "0",
        "2014": "0",
        "2015": "1"
    },
    "monthly_salary": {
        "2013": 4355,
        "2014": 1112,
        "2015": 33223
    }
}
}

NOTE: There is a similar question convert data frame to json but it is not an exact duplicate. My question addresses the issue of having nested jsons , basically how to generate data in form of time series for some variables . This needs additional data wrangling rather than just feeding it into toJson() function.

Any help is appreciated.

"looks like no one has done it on an r dataframe" - incorrect. Search for 'r data.frame to json` and you'll get multiple example. e.g., `jsonlite::toJSON(df, pretty = T)` — SymbolixAU, Mar 11 '18 at 06:17

score 1 · Accepted Answer · answered Mar 11 '18 at 09:01

As suggested by others in the comments in order to convert a R object to JSON, one could use the jsonlite::toJSON function.

In my opinion, your problems are in formatting properly the R object to get the desired output.

Clearly there is some business logic which determines the contents of each object in the json. I have defined a simple function to convert the data frame to a list, which you might want to edit if you want to change if you have more complex logic.

Hope this helps.

df <- data.frame(
  profileKey=c(7,7,7,8,8,8)
  , joinDate=c('2007-11-01','2007-11-01','2007-11-01'
               ,'1993-01-04','1993-01-04','1993-01-04')
  , Year=c('2013','2014','2015','2013','2014','2015')
  , monthlySalary= as.numeric(c('2000','3251','2015','4355','1112','33223'))
  , event=c(0,0,0,0,0,1)
  , stringsAsFactors = FALSE)


convert_groupings <- function(key_df){

  key_df <- as.list(key_df)

  key_df$profileKey <- unique(key_df$profileKey)
  key_df$joinDate <- unique(key_df$joinDate)

  names(key_df$event) <- names(key_df$monthlySalary) <- key_df$Year
  key_df$Year <- NULL

  key_df$event <- as.list(key_df$event)
  key_df$monthlySalary <- as.list(key_df$monthlySalary)

  key_df
}


df_list <- split(df, f = df$profileKey)
df_list <- lapply(df_list, convert_groupings)

cat(
  jsonlite::toJSON(
    df_list
    , auto_unbox = T
    , pretty = T
  )
)

R - converting dataframe into json

1 Answers1