I'm working with a data set that returns a data set of information about people; name, address, phone numbers, emails etc. I can work with the flat data pretty well but the phone numbers and emails are lists of data frames within the larger data frame. I'd like to take that list of data frames and collapse it into a single data frame. I have done this successfully with the code below using tidyverse:
library(tidyverse)
... a bunch of code to pull JSON data from an API and combine into a single JSON variable called finalData...
outputTable <- data.frame(finalData, stringsAsFactors = FALSE)
phoneList <- bind_rows(outputTable$phone)
Now all of the data is inside of 'outputTable' and the collapsed list of data frames that include the phone information is copied to 'phoneList'.
outputTable looks something like:
| ID | Name | Phone | Address | |
|---|---|---|---|---|
| 123 | Jane | 2 obs in dataframe | 1 obs in dataframe | 12 Way Road |
| 098 | Bill | 3 obs in dataframe | 2 obs in dataframe | 34 RockCliff |
Here is the phone dataframe for 'Bill':
| Label | Value | Primary |
|---|---|---|
| "" | 1234567890 | TRUE |
| "work" | 5555555555 | FALSE |
| "home" | 5550000000 | FALSE |
phoneList is now a single data frame with all observations (obs) of the phone data all together. The problem I have here is that now I have no idea who each phone data is associated with. What I think I need is to add in the ID field from the outputTable to the respective phone data frame BEFORE I 'bind_rows'. I just can't seem to figure out how to do that. The result I'd like is something like:
| ID | Name | Label | Value | Primary |
|---|---|---|---|---|
| 123 | Jane | "" | 9999999999 | FALSE |
| 123 | Jane | "mobile" | 1111111111 | TRUE |
| 098 | Bill | "" | 1234567890 | TRUE |
| 098 | Bill | "work" | 5555555555 | FALSE |
| 098 | Bill | "home" | 5550000000 | FALSE |
I feel like I've accomplished this before but can't seem to get it figured out today. I'm not married to tidyverse either, I just found that solution on here earlier. Thanks in advance for the help.
####################### ANSWERED #################### I was able to use unnest once I created a new data table with the data I wanted.
...
outputTable <- data.frame(newDocument,stringsAsFactors = FALSE)
idList <- outputTable$id
phoneList <- outputTable$phone
nameList <- outputTable$name
newDT <- cbind(idList, nameList, phoneList)
newDT <- data.frame(newDT,stringsAsFactors = FALSE)
finalDT <- unnest(newDT, phoneList)
This gives me the desired data table