Turn list to various columns in a data frame

Question

I have a dataset (columns below), and I am having issues with one of the variables.

Here's a snapshot of the data.

 [1] "id"                    "parent_keywords"       "tag"                   "venue_name"            "normalized_venue_name"
 [6] "journal"               "authors"               "pub_date"              "doi"                   "title" 

The 'authors' variable is a list and I have been trying to flatten it by various means, with no success. I always get a mismatch between the dataset and the resulting rows of the 'flattening'.

data$authors <- rbindlist(data$authors, use.names = TRUE, fill = TRUE)

data$authors <- data.frame(Reduce(rbind, authors))

data$authors <- do.call(rbind.data.frame, authors)

These produce the error:

Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE,  : 
  arguments imply differing number of rows: 1, 0, 2, 4, 6, 3, 8

If i do:

data$authors <- rbindlist(authors, fill = TRUE)

I get:

Error in `$<-.data.frame`(`*tmp*`, authors, value = list(affiliations = list( : 
  replacement has 14655 rows, data has 8000

Originally the data comes from a .json file.

This is the structure of the list.

> data$authors[1:8]
[[1]]
NULL

[[2]]
        affiliations author_id     author_name
1 Punjabi University  780E3459     munish puri
2 Punjabi University  48D92C79 rajesh dhaliwal
3 Punjabi University  7D9BD37C       r s singh

[[3]]
  author_id         author_name
1  7FF872BC barbara eileen ryan

[[4]]
  author_id      author_name
1  0299B8E9 fraser j harbutt

[[5]]
  author_id        author_name
1  7DAB7B72 richard m freeland

[[6]]
NULL

[[7]]
                                                                                                                                                                  affiliations
1 Laboratory Services Division
2 Department of Environmental
3 Department of Environmental
4 Department of Environmental Biology
  author_id    author_name
1  7C1F9807 s a de grandis
2  01F0D46A    j t trevors
3  7C9E67C5     m j blears
4  7E989139  hongjoo j lee

[[8]]
NULL

I believe i am getting the mismatch because not all items of the list have the affiliations part, but I don't know how to resolve this.

Ideally it should be:

[[1]]
NULL
[[2]]
affiliations   id   name
[[3]]
NA             id   name

This way i can do the flattening with no problem.

I would like to turn it into multiple columns of the same dataset to test some author disambiguation algorithms on the data.

Do you guys have any idea how could I accomplish this? Any other logic to prepare for disambiguation would be very welcome.

Adding the dput.

structure(list(id = c("7CB3F2AD", "7AF8EBC3", "7521A721", "7DAEB9A4", 
"7B3236C5"), parent_keywords = list(c("Chromatography", "Quantum mechanics", 
"Particle physics", "Quantum field theory", "Analytical chemistry", 
"Quantum chromodynamics", "Physics", "Mass spectrometry", "Chemistry"
), c("Nuclear medicine", "Psychology", "Hydrology", "Chromatography", 
"X-ray crystallography", "Nuclear fusion", "Medicine", "Fluid dynamics", 
"Thermodynamics", "Physics", "Gas chromatography", "Radiobiology", 
"Engineering", "Organic chemistry", "High-performance liquid chromatography", 
"Chemistry", "Organic synthesis", "Psychotherapist"), c("Social science", 
"Politics", "Sociology", "Law"), c("Superconductivity", "Nuclear fusion", 
"Geology", "Chemistry", "Metallurgy"), c("Political Science", 
"Economics")), tag = list(c("mass spectra", "elementary particles", 
"bound states"), c("flow rate", "operant conditioning", "packed bed reactor", 
"immobilized enzyme", "specific activity"), "social movements", 
    "iron", "foreign policy"), venue_name = c("Physical Review Letters", 
"Journal of Industrial Microbiology & Biotechnology", "The American Historical Review", 
"The American Historical Review", "The American Historical Review"
), normalized_venue_name = c("phys rev lett", "j ind microbiol biotechnol", 
"american historical review", "american historical review", "american historical review"
), journal = c("Physical Review Letters", "Journal of Industrial Microbiology & Biotechnology", 
"The American Historical Review", "The American Historical Review", 
"The American Historical Review"), authors = list(NULL, structure(list(
    affiliations = list("Punjabi University", "Punjabi University", 
        "Punjabi University"), author_id = c("780E3459", "48D92C79", 
    "7D9BD37C"), author_name = c("munish puri", "rajesh dhaliwal", 
    "r s singh")), .Names = c("affiliations", "author_id", "author_name"
), class = "data.frame", row.names = c(NA, 3L)), structure(list(
    author_id = "7FF872BC", author_name = "barbara eileen ryan"), .Names = c("author_id", 
"author_name"), class = "data.frame", row.names = 1L), structure(list(
    author_id = "0299B8E9", author_name = "fraser j harbutt"), .Names = c("author_id", 
"author_name"), class = "data.frame", row.names = 1L), structure(list(
    author_id = "7DAB7B72", author_name = "richard m freeland"), .Names = c("author_id", 
"author_name"), class = "data.frame", row.names = 1L)), pub_date = c("1987-03-02 00:00:00", 
"2008-04-04 00:00:00", "1992-01-01 00:00:00", "1988-01-01 00:00:00", 
"1985-01-01 00:00:00"), doi = c("", "", "", "", ""), title = c("Evidence for a new meson: A quasinuclear NN-bar bound state", 
"Development of a stable continuous flow immobilized enzyme reactor for the hydrolysis of inulin", 
"Feminism and the women's movement : dynamics of change in social movement ideology, and activism", 
"The iron curtain : Churchill, America, and the origins of the Cold War", 
"The Truman Doctrine and the origins of McCarthyism : foreign policy, domestic politics, and internal security, 1946-1948"
)), .Names = c("id", "parent_keywords", "tag", "venue_name", 
"normalized_venue_name", "journal", "authors", "pub_date", "doi", 
"title"), row.names = c(NA, 5L), class = "data.frame")

Answer 1

Without the data, I can only speculate.

I think bind_rows() may the function you would like. It will include as a column if it exists in any item of the list. link.

In your example, it would be as simple as:

bind_rows(data$authors)

If data is provided, I can make sure it works on your example.

EDIT

Okay - so reading through the documents, and trying to figure out what would work on this problem. I have the following solution.

1) We use the a couple helper functions to make this work. This does some rearranging of the underlying data. I put the author ID, and the author name together.

spread_f <- function(df) {
  df %>% 
     select(author_id, author_name) %>% 
     mutate(num_auths = paste('author_', 1:n(), sep = '')) %>% 
     unite(comb, author_id, author_name, sep = ' ') %>% 
     spread(num_auths, comb)
  }

2) We then use a looping structure to perform this operation per element in the list.

 convert_f <- function(list_authors) {
 list <- map(df$authors, 
             function(x) if(is.null(x)) { 
               data.frame(author_id = '', author_name = '') 
               } else { x })


  list <- map(list, function(x) spread_f(x)) 

  return(list)
 }

3) Finally we can wrap this call into bind_rows to produce the correct number of rows for you dataset.

bind_rows(convert_f(df$authors))

It should return the correct information you need (fingers crossed).