Extract data from a nested dataframe into the same record

user_17849018 0
Viewed 143

I have this main dataframe:

testdataframe
       id     sensors_data
       <chr>  <list>
    1  AA     <data.frame [6 × 4]>              
    2  BB     <data.frame [6 × 4]>

and every dataframe of sensors_data looks like this:

         id type  value           status
      <chr><chr>  <dbl>           <int>
1   SN01TP  a     25.800          1
2   SN01HU  b     40.000          1
3   SN02VD  c     1.146           1
4   SN02C2  d     1270.000        1
5   SY01DS  e     31.000          1
6   TD01TP  f     22.500          1

I would want my main data frame to be, instead of only sensors_data, something like this:

   a      b       c      d         e       f
1  25.800 40.000  1.146  1270.000  31.000  22.500

I've tried unnesting the main data frame but that would create a record for each field. What I'm trying is to mutate the main data frame accessing the data inside sensors_data, but I can't figure out how.

data-manipulation pivot-table dataframe multidimensional-array r
2 Answers

Using purrr:map and tidyr::pivot_wider, you can do this. Use bind_rows if you want one dataframe.

df$pivoted <- df$sensors_data %>% 
  map(~ tidyr::pivot_wider(.[,c("type","value")], names_from = type))

df$pivoted
[[1]]
      a     b     c     d     e     f
1  25.8    40  1.15  1270    31  22.5

[[2]]
      a     b     c     d     e     f
1  25.8    40  1.15  1270    31  22.5

df
# A tibble: 2 x 3
  id    sensors_data pivoted         
  <chr> <list>       <list>          
1 AA    <df [6 x 4]> <tibble [1 x 6]>
2 BB    <df [6 x 4]> <tibble [1 x 6]>

Or, with bind_rows and bind_cols:

df$sensors_data %>% 
  map(~ tidyr::pivot_wider(.[,c("type","value")], names_from = type)) %>% 
  bind_rows() %>% 
  bind_cols(df,.)

# A tibble: 2 x 8
  id    sensors_data     a     b     c     d     e     f
  <chr> <list>       <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 AA    <df [6 x 4]>  25.8    40  1.15  1270    31  22.5
2 BB    <df [6 x 4]>  25.8    40  1.15  1270    31  22.5

Data:

df1 <- read.table(header = T, text="         id type  value           status
1   SN01TP  a     25.800          1
2   SN01HU  b     40.000          1
3   SN02VD  c     1.146           1
4   SN02C2  d     1270.000        1
5   SY01DS  e     31.000          1
6   TD01TP  f     22.500          1")

df <- tibble(id = c("AA", "BB"), sensors_data = list(df1,df1))
> df
  id    sensors_data
1 AA    <df [6 x 4]>
2 BB    <df [6 x 4]>
user_134606020

My favorite answer is already provided by Maël! Here is an alternative using lapply

library(dplyr)
library(tidyr)

sensors_data_sub <- lapply(sensors_data, function(x)x[,2:3])

sensors_data_sub_wide <- lapply(1:length(sensors_data_sub), 
                  function(x) (pivot_wider(sensors_data_sub[[x]], names_from = type, values_from = value)))

bind_rows(sensors_data_sub_wide)

      a     b     c     d     e     f
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1  25.8    40  1.15  1270    31  22.5
2  25.8    40  1.15  1270    31  22.5
user_133216470
Related