List of list to dataframe

Question

I obtain from an endpoint in an API query a list of 370 lists of 19 elements each. I am trying to convert this into 370 rows with 19 columns in R.

When I try as.dataframe, I get an error message : arguments imply differing number of rows: 1, 0 When I try lapply(datos, ldply), I get the following error : Results must be all atomic, or all data frames

Also tried the flatten list post. but also get the message: arguments imply differing number of rows: 1, 0

PS: I checked previous similar questions on the subject, tried the answer, but I didn't get what I wanted

My data:

[ enter image description here ]

Expected output: 370 rows with 19 columns

Have a look at http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/28481250#28481250 — Frank, May 05 '17 at 19:08
Thanks. Are you suggesting I show my data ? Not sure how I can do that, Shall I use Snipping Tool and paste ? — cyrilmadrid, May 05 '17 at 19:16
It is not as simple as "show my data" -- you need to make a minimal reproducible example along with expected output. The link explains what is needed, but you could also look at [mcve]. And I'm saying this should be edited into the question; data doesn't belong in the comments. — Frank, May 05 '17 at 19:24
Tried to do what I could. But do not know how to insert these grey boxes which I see in many of the questions. — cyrilmadrid, May 05 '17 at 19:50
I think you are overlooking the minimal and reproducible parts. As Brodie said in the first link, it does take some work beyond `str(object)` and the dimensions of the desired output... anyway, I'll leave you to it. — Frank, May 05 '17 at 20:00
I read about the reproducible part, but shall I give out my password to access the API that generates the data so you can replicate it ? That would seem strange. Minimal: i didn't use any code yet, this is the data I have and want to convert it so I can start using it. — cyrilmadrid, May 05 '17 at 20:56
Possible duplicate of [Flatten list and push list key to vector on second level](http://stackoverflow.com/questions/43813369/flatten-list-and-push-list-key-to-vector-on-second-level) — Uwe, May 05 '17 at 22:22
I tried that but get this message: arguments imply differing number of rows: 1, 0. None of the suggestions on this page solved my problem — cyrilmadrid, May 05 '17 at 22:29

Parfait · Answer 1 · 2017-05-06T16:20:47.673

Consider filtering out the NULL entity which is the challenge in casting list elements to dataframe.

Data (recreates visible part of screenshot)

inner <- 
  list(
    loandId = 2,
    websiteLoanId = 2,
    loanName = "Nottingham Student Accodomodation Development Project",
    amount = 1500000.0,
    principalRemaining = "0.00",
    firstDrawDate = "2013-04-16T07:36.08.000z",
    status = "repaid",
    sector = NULL,
    category = "development",
    holdings = list(1,2,3,4,5),
    aftermarketForSale = list("a", "b", "c", "d", "e")
  )

datos= list(loans = lapply(seq(370), function(i) inner))

Dataframe build

# FILTER OUT NULLs
datos$loans <- lapply(datos$loans, function(item) 
  Filter(function(i) length(i) == 1, item)
)

# IF ALL ELEMENTS ARE THE SAME (I.E., NULL DROPS SAME ITEM)
loansdf <- do.call(rbind, lapply(datos$loans, data.frame))

# IF NULL DROPS ABOVE ARE NOT SAME, USE BELOW PACKAGES FOR RBINDING METHOS
loansdf <- plyr::rbind.fill(datos$loans)
loansdf <- dplyr::bind_rows(datos$loans)
loansdf <- data.table::rbindlist(datos$loans)

Output

loansdf
#    loandId websiteLoanId                                              loanName  amount principalRemaining            firstDrawDate status
# 1         2             2 Nottingham Student Accodomodation Development Project 1500000               0.00 2013-04-16T07:36.08.000z repaid
# 2         2             2 Nottingham Student Accodomodation Development Project 1500000               0.00 2013-04-16T07:36.08.000z repaid
# 3         2             2 Nottingham Student Accodomodation Development Project 1500000               0.00 2013-04-16T07:36.08.000z repaid
# 4         2             2 Nottingham Student Accodomodation Development Project 1500000               0.00 2013-04-16T07:36.08.000z repaid
# 5         2             2 Nottingham Student Accodomodation Development Project 1500000               0.00 2013-04-16T07:36.08.000z repaid
# 6         2             2 Nottingham Student Accodomodation Development Project 1500000               0.00 2013-04-16T07:36.08.000z repaid
# 7         2             2 Nottingham Student Accodomodation Development Project 1500000               0.00 2013-04-16T07:36.08.000z repaid
# 8         2             2 Nottingham Student Accodomodation Development Project 1500000               0.00 2013-04-16T07:36.08.000z repaid

Thanks very much for your efforts, Parfait.I tried to replicate the first part of what you suggested, but I cannot fully copy in Inner the first register because some of the fields are also a list which I cannot view -aftermarketForSale and holdings-. And no, the NULLs are not all the same, not all the field is null, ... I posted a more complete view of my data. — cyrilmadrid, May 06 '17 at 08:44
I tried to do Dataframe build without the first phase, Filter Nulls works, but the plyr, dplyr, data.table give me error : loansdf <- plyr::rbind.fill(datos$loans) Error: All inputs to rbind.fill must be data.frames > loansdf <- dplyr::bind_rows(datos$loans) Error in bind_rows_(x, .id) : incompatible sizes (1 != 2) > loansdf <- data.table::rbindlist(datos$loans) Error in data.table::rbindlist(datos$loans) : Item 2 has 16 columns, inconsistent with item 1 which has 13 columns. If instead you need to fill missing columns, use set argument 'fill' to TRUE. — cyrilmadrid, May 06 '17 at 08:58
You should not have to run the *Data* example as I was attempting to recreate your structure which sadly you only present in a screenshot. I actually went out of my way to do this (which you should have done). Do not place the data/code burden on the volunteer answerer. — Parfait, May 06 '17 at 16:18
And the continued issue is those nested lists. Simply change the `Filter` from `>0` to `==1` to retrieve only atomic values, dropping NULLs and nested lists. See edit where I add *holdings* and *aftermarketForSale* embedded lists in *inner* and change the `Filter()` logic. By the way, I'm convinced your API request may be a `json` file. See such R questions on handling this data format. — Parfait, May 06 '17 at 16:21
Sorry for this. If I knew how to post it the way you did, I would have very gladly done so. Screenshot was the only way I found to show what it was. Yes, my data is a json file. I also tried the alternative to import if with jsonlite and rjson, but also ran into problems. — cyrilmadrid, May 06 '17 at 16:43
And yet you show no coding attempt of the json handling. Always do the three for reproducibility: 1) input sample data (so readers can copy and paste); 2) coding attempt; 3) desired output sample. — Parfait, May 06 '17 at 16:47

List of list to dataframe

1 Answers1