Unnest the dataframes which are nest in a column of a list

Hi there,

below I have pasted the structure of real list so that anyone can copy-paste it and play with it.

My intention is to obtain a flat dataframe where each row represents a row from the column-dataframe 'lines' and be able to 'bind_col' the invoice's id and customer's id to that dataframe.

I have been trying with tidyr::unnest() , tidyr::unnest_wider() , tidyr::unnest_longer() , purrr::pluck , but none of them seem to work.

This is the structure from dump:

invoice <-
  structure(list(id = c("in_1000", "in_1001"
  ), customer = c("cus_1000", "cus_1001"), 
  lines = structure(list(object = c("list", "list"), data = list(
    structure(list(id = "in_1000", object = "line_item", 
                   amount = 0L, currency = "eur", description = "Trial period for Food", 
                   discount_amounts = list(list()), discountable = TRUE, 
                   discounts = list(list()), livemode = TRUE, metadata = structure(list(), .Names = character(0), row.names = c(NA, 
                                                                                                                                -1L), class = "data.frame"), period = structure(list(
                                                                                                                                  end = 1605198759L, start = 1601919291L), .Names = c("end", 
                                                                                                                                                                                      "start"), class = "data.frame", row.names = 1L), 
                   plan = structure(list(id = "plan_1000", 
                                         object = "plan", active = TRUE, aggregate_usage = NA, 
                                         amount = 5850L, amount_decimal = "5850", billing_scheme = "per_unit", 
                                         created = 1603405572L, currency = "eur", interval = "day", 
                                         interval_count = 33L, livemode = TRUE, metadata = structure(list(
                                           user_id = "1655", user_email = "iii@gmail.com", 
                                           dog_name = "Nevada"), .Names = c("user_id", 
                                                                            "user_email", "dog_name"), class = "data.frame", row.names = 1L), 
                                         nickname = NA, product = "prod_1000", 
                                         tiers = NA, tiers_mode = NA, transform_usage = NA, 
                                         trial_period_days = NA, usage_type = "licensed"), .Names = c("id", 
                                                                                                      "object", "active", "aggregate_usage", "amount", 
                                                                                                      "amount_decimal", "billing_scheme", "created", "currency", 
                                                                                                      "interval", "interval_count", "livemode", "metadata", 
                                                                                                      "nickname", "product", "tiers", "tiers_mode", "transform_usage", 
                                                                                                      "trial_period_days", "usage_type"), class = "data.frame", row.names = 1L), 
                   price = structure(list(id = "plan_1000", 
                                          object = "price", active = TRUE, billing_scheme = "per_unit", 
                                          created = 1603405572L, currency = "eur", livemode = TRUE, 
                                          lookup_key = NA, metadata = structure(list(user_id = "1655", 
                                                                                     user_email = "iii@gmail.com", dog_name = "Nevada"), .Names = c("user_id", 
                                                                                                                                                    "user_email", "dog_name"), class = "data.frame", row.names = 1L), 
                                          nickname = NA, product = "prod_1000", 
                                          recurring = structure(list(aggregate_usage = NA, 
                                                                     interval = "day", interval_count = 33L, trial_period_days = NA, 
                                                                     usage_type = "licensed"), .Names = c("aggregate_usage", 
                                                                                                          "interval", "interval_count", "trial_period_days", 
                                                                                                          "usage_type"), class = "data.frame", row.names = 1L), 
                                          tiers_mode = NA, transform_quantity = NA, type = "recurring", 
                                          unit_amount = 5850L, unit_amount_decimal = "5850"), .Names = c("id", 
                                                                                                         "object", "active", "billing_scheme", "created", 
                                                                                                         "currency", "livemode", "lookup_key", "metadata", 
                                                                                                         "nickname", "product", "recurring", "tiers_mode", 
                                                                                                         "transform_quantity", "type", "unit_amount", "unit_amount_decimal"
                                          ), class = "data.frame", row.names = 1L), proration = FALSE, 
                   quantity = 1L, subscription = "sub_1000", 
                   subscription_item = "si_1000", tax_amounts = list(
                     list()), tax_rates = list(list()), type = "subscription"), .Names = c("id", 
                                                                                           "object", "amount", "currency", "description", "discount_amounts", 
                                                                                           "discountable", "discounts", "livemode", "metadata", 
                                                                                           "period", "plan", "price", "proration", "quantity", "subscription", 
                                                                                           "subscription_item", "tax_amounts", "tax_rates", "type"
                     ), class = "data.frame", row.names = 1L), structure(list(
                       id = "il_1001", object = "line_item", 
                       amount = 6300L, currency = "eur", description = "1 Ã\u0097 Food (at â\u0082¬63.00 / every 32 days)", 
                       discount_amounts = list(list()), discountable = TRUE, 
                       discounts = list(list()), livemode = TRUE, metadata = structure(list(), .Names = character(0), row.names = c(NA, 
                                                                                                                                    -1L), class = "data.frame"), period = structure(list(
                                                                                                                                      end = 1606161749L, start = 1603396949L), .Names = c("end", 
                                                                                                                                                                                          "start"), class = "data.frame", row.names = 1L), 
                       plan = structure(list(id = "plan_1001", 
                                             object = "plan", active = TRUE, aggregate_usage = NA, 
                                             amount = 6300L, amount_decimal = "6300", billing_scheme = "per_unit", 
                                             created = 1600632148L, currency = "eur", interval = "day", 
                                             interval_count = 32L, livemode = TRUE, metadata = structure(list(
                                               user_id = "1297", user_email = "jjj@gmail.com", 
                                               dog_name = "Jaro"), .Names = c("user_id", "user_email", 
                                                                              "dog_name"), class = "data.frame", row.names = 1L), 
                                             nickname = NA, product = "prod_1000", 
                                             tiers = NA, tiers_mode = NA, transform_usage = NA, 
                                             trial_period_days = NA, usage_type = "licensed"), .Names = c("id", 
                                                                                                          "object", "active", "aggregate_usage", "amount", 
                                                                                                          "amount_decimal", "billing_scheme", "created", "currency", 
                                                                                                          "interval", "interval_count", "livemode", "metadata", 
                                                                                                          "nickname", "product", "tiers", "tiers_mode", "transform_usage", 
                                                                                                          "trial_period_days", "usage_type"), class = "data.frame", row.names = 1L), 
                       price = structure(list(id = "plan_1001", 
                                              object = "price", active = TRUE, billing_scheme = "per_unit", 
                                              created = 1600632148L, currency = "eur", livemode = TRUE, 
                                              lookup_key = NA, metadata = structure(list(user_id = "1297", 
                                                                                         user_email = "jjj@gmail.com", 
                                                                                         dog_name = "Jaro"), .Names = c("user_id", "user_email", 
                                                                                                                        "dog_name"), class = "data.frame", row.names = 1L), 
                                              nickname = NA, product = "prod_1000", 
                                              recurring = structure(list(aggregate_usage = NA, 
                                                                         interval = "day", interval_count = 32L, trial_period_days = NA, 
                                                                         usage_type = "licensed"), .Names = c("aggregate_usage", 
                                                                                                              "interval", "interval_count", "trial_period_days", 
                                                                                                              "usage_type"), class = "data.frame", row.names = 1L), 
                                              tiers_mode = NA, transform_quantity = NA, type = "recurring", 
                                              unit_amount = 6300L, unit_amount_decimal = "6300"), .Names = c("id", 
                                                                                                             "object", "active", "billing_scheme", "created", 
                                                                                                             "currency", "livemode", "lookup_key", "metadata", 
                                                                                                             "nickname", "product", "recurring", "tiers_mode", 
                                                                                                             "transform_quantity", "type", "unit_amount", "unit_amount_decimal"
                                              ), class = "data.frame", row.names = 1L), proration = FALSE, 
                       quantity = 1L, subscription = "sub_1001", 
                       subscription_item = "si_1001", tax_amounts = list(
                         list()), tax_rates = list(list()), type = "subscription"), .Names = c("id", 
                                                                                               "object", "amount", "currency", "description", "discount_amounts", 
                                                                                               "discountable", "discounts", "livemode", "metadata", 
                                                                                               "period", "plan", "price", "proration", "quantity", "subscription", 
                                                                                               "subscription_item", "tax_amounts", "tax_rates", "type"
                         ), class = "data.frame", row.names = 1L)), has_more = c(FALSE, 
                                                                                 FALSE), total_count = c(1L, 1L), url = c("/v1/invoices/in_1000/lines", 
                                                                                                                          "/v1/invoices/in_1001/lines")), .Names = c("object", 
                                                                                                                                                                                         "data", "has_more", "total_count", "url"), class = "data.frame", row.names = 1:2)), .Names = c("id", 
                                                                                                                                                                                                                                                                                        "customer", "lines"), row.names = 1:2, class = "data.frame")

Any help would be appreciated!!

Thanks!!!

(extracted_lines <- bind_rows(invoice$lines$data))

invoice$lines <- NULL

final_invoice <- bind_cols(invoice,extracted_lines)

?

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

Thanks, this is what I was looking for!

Thanks so much!!!