How to flatten a column of lists while keeping list names and values? [r]

Question

I've read through 4-5 similar questions, but their cases are different, and I couldn't figure out how to apply what I read to my case.

I have a (public) data frame where the last column is a list:

Clicking on the magnifying glass shows the following structure:

When I unnest(), I lose the metric Names (i.e., visits, hits, etc.):

How can I flatten that list while keeping the metric names?

Sample data:

structure(list(fullVisitorId = c("2248281639583218707", "8647436381089107732", 
"2055839700856389632", "0750846065342433129", "0573427169410921198"
), date = c("20170801", "20170801", "20170801", "20170801", "20170801"
), visitStartTime = c(1501583974L, 1501616585L, 1501583344L, 
1501573386L, 1501651467L), totals = list(list(visits = 1L, hits = 1L, 
    pageviews = 1L, timeOnSite = NA_integer_, bounces = 1L, transactions = NA_integer_, 
    transactionRevenue = NA_integer_, newVisits = 1L, screenviews = NA_integer_, 
    uniqueScreenviews = NA_integer_, timeOnScreen = NA_integer_, 
    totalTransactionRevenue = NA_integer_, sessionQualityDim = 1L), 
    list(visits = 1L, hits = 1L, pageviews = 1L, timeOnSite = NA_integer_, 
        bounces = 1L, transactions = NA_integer_, transactionRevenue = NA_integer_, 
        newVisits = 1L, screenviews = NA_integer_, uniqueScreenviews = NA_integer_, 
        timeOnScreen = NA_integer_, totalTransactionRevenue = NA_integer_, 
        sessionQualityDim = 1L), list(visits = 1L, hits = 1L, 
        pageviews = 1L, timeOnSite = NA_integer_, bounces = 1L, 
        transactions = NA_integer_, transactionRevenue = NA_integer_, 
        newVisits = 1L, screenviews = NA_integer_, uniqueScreenviews = NA_integer_, 
        timeOnScreen = NA_integer_, totalTransactionRevenue = NA_integer_, 
        sessionQualityDim = 1L), list(visits = 1L, hits = 1L, 
        pageviews = 1L, timeOnSite = NA_integer_, bounces = 1L, 
        transactions = NA_integer_, transactionRevenue = NA_integer_, 
        newVisits = 1L, screenviews = NA_integer_, uniqueScreenviews = NA_integer_, 
        timeOnScreen = NA_integer_, totalTransactionRevenue = NA_integer_, 
        sessionQualityDim = 1L), list(visits = 1L, hits = 1L, 
        pageviews = 1L, timeOnSite = NA_integer_, bounces = 1L, 
        transactions = NA_integer_, transactionRevenue = NA_integer_, 
        newVisits = NA_integer_, screenviews = NA_integer_, uniqueScreenviews = NA_integer_, 
        timeOnScreen = NA_integer_, totalTransactionRevenue = NA_integer_, 
        sessionQualityDim = 1L))), row.names = c(NA, -5L), class = c("tbl_df", 
"tbl", "data.frame"))

What about `cbind(df[-4], do.call(rbind.data.frame, df$totals))`? — Rui Barradas, Nov 21 '19 at 22:39
Thanks, @RuiBarradas! This works well on the sample data, but is there a faster alternative? The actual data has millions of rows =\. — Khashir, Nov 21 '19 at 22:54

score 1 · Answer 1 · answered Nov 21 '19 at 23:01

Is tidyr::unnest_wider() what you want?

tidyr::unnest_wider(sample_data,totals)

# A tibble: 5 x 16
  fullVisitorId date  visitStartTime visits  hits pageviews timeOnSite bounces transactions transactionReve~ newVisits screenviews
  <chr>         <chr>          <int>  <int> <int>     <int>      <int>   <int>        <int>            <int>     <int>       <int>
1 224828163958~ 2017~     1501583974      1     1         1         NA       1           NA               NA         1          NA
2 864743638108~ 2017~     1501616585      1     1         1         NA       1           NA               NA         1          NA
3 205583970085~ 2017~     1501583344      1     1         1         NA       1           NA               NA         1          NA
4 075084606534~ 2017~     1501573386      1     1         1         NA       1           NA               NA         1          NA
5 057342716941~ 2017~     1501651467      1     1         1         NA       1           NA               NA        NA          NA
# ... with 4 more variables: uniqueScreenviews <int>, timeOnScreen <int>, totalTransactionRevenue <int>, sessionQualityDim <int>

How to flatten a column of lists while keeping list names and values? [r]

1 Answers1