Stack Overflow
Stack Overflow

Questions tagged [unnest]

unnest is a function from the tidyr package that can expand the list columns.

456 questions
7
votes
3 answers

Unnest an array in AWS Redshift

I have a table with column with lists like this: id [1,2,3,10] [1] [2,3,4,9] The result I would like to have is a table with unlisted values like this: id2 1 2 3 10 1 2 3 4 9 I have tried different solutions that I found on the web, aws…
Makaroni
  • 880
  • 3
  • 15
  • 34
7
votes
2 answers

Returning a tibble: how to vectorize with case_when?

I have a function which returns a tibble. It runs OK, but I want to vectorize it. library(tidyverse) tibTest <- tibble(argX = 1:4, argY = 7:4) square_it <- function(xx, yy) { if(xx >= 4){ tibble(x = NA, y = NA) } else if(xx == 3){ …
David T
  • 1,993
  • 10
  • 18
7
votes
2 answers

How to select a nested field with bigrquery using dplyr syntax?

I'd like to explore a Google Analytics 360 data with bigrquery using dplyr syntax (rather than SQL), if possible. The gist is that I want to understand user journeys—I'm interested in finding the most common sequences of pages at the user level…
Khashir
  • 341
  • 3
  • 20
7
votes
5 answers

pandas: create a long/tidy DataFrame from dictionary when values are sets or lists of variable length

Simple dictionary: d = {'a': set([1,2,3]), 'b': set([3, 4])} (the sets may be turned into lists if it matters) How do I convert it into a long/tidy DataFrame in which each column is a variable and every observation is a row, i.e.: letter value 0…
Giora Simchoni
  • 3,487
  • 3
  • 34
  • 72
7
votes
2 answers

Check differences of various DATE inside one variables R

I want to split the line when the variable contain different YEAR, also split the col : "Price" with evenly divided by the numbers of date appear --> count (" ; ") +1 There is a table with the variable that is not yet be splitted. # Dataset call…
rane
  • 901
  • 4
  • 12
  • 24
7
votes
2 answers

UNNEST expression references column which is neither grouped nor aggregated

Google Analytics BigQuery tables are structured like this (Legacy SQL notations - only relevant fields are shown): visitId: INTEGER hits: RECORD/REPEATED hits.hour: INTEGER On one such…
Jivan
  • 21,522
  • 15
  • 80
  • 131
6
votes
1 answer

NULL emements lost when casting result of unnest()

I stumbled upon very odd behavior with unnest(), when casting after expanding an array. Introduction There are three basic syntax variants to use unnest(): 1) SELECT unnest('{1,NULL,4}'::int[]) AS i; 2) SELECT i FROM unnest('{2,NULL,4}'::int[]) AS…
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
5
votes
2 answers

When using tidyr::unnest_wider(), how to name new columns based on chr vector

I have the following data structure: library(tibble) my_tbl <- tibble::tribble( ~col_x, ~col_y, "a", list(1, 2, 3), "b", list(4, 5, 6), "c", list(7, 8,…
Emman
  • 3,695
  • 2
  • 20
  • 44
5
votes
2 answers

Fast unnest with data.table

I am currently using the tidyr package to unnest list columns. However, I am looking for a faster approach and turned to data.table (where I am a noob). Consider the following example: dt1 <- data.table::data.table( a = c("a1", "a2"), df1 =…
robertdj
  • 909
  • 1
  • 5
  • 10
5
votes
3 answers

tidyr unnest, prefix column names with nested name during unnesting

When running unnest on a data.frame is there a way to add the group name of nested item to the individual columns it contains (either as a suffix or prefix). Or does renaming have to be done manually via rename? This is particularly relevant with…
JWilliman
  • 3,558
  • 32
  • 36
5
votes
1 answer

What is the equivalent of Presto UNNEST function in Hive

Presto has an UNNEST function to explode columns made of arrays. Is there a similar one for Hive? See docs for UNNEST function of Presto here.
ishan3243
  • 1,870
  • 4
  • 30
  • 49
5
votes
1 answer

Array of composite type as stored procedure input

I am creating a stored procedure using plpgsql by passing a type array and do a loop inside the procedure so that I can insert each info type CREATE TYPE info AS( name varchar, email_add varchar, contact_no varchar ); CREATE OR…
megamoth
  • 695
  • 2
  • 12
  • 27
5
votes
2 answers

Convert multi-dimensional array to records

Given: {{1,"a"},{2,"b"},{3,"c"}} Desired: foo | bar -----+------ 1 | a 2 | b 3 | c You can get the intended result with the following query; however, it'd be better to have something that scales with the size of the array. SELECT…
vol7ron
  • 40,809
  • 21
  • 119
  • 172
4
votes
3 answers

How to turn a list of lists into columns of a pandas dataframe?

I would like to ask how I can unnest a list of list and turn it into different columns of a dataframe. Specifically, I have the following dataframe where the Route_set column is a list of lists: Generation Route_set 0…
Anas.S
  • 193
  • 1
  • 11
4
votes
1 answer

Is there a base R version of tidyr's unnest() function?

I've been using tidyverse quite a lot and now I'm interested in the possibilities of base R. Let's take a look at this simple data.frame df <- data.frame(id = 1:4, nested = c("a, b, f", "c, d", "e", "e, f")) Using dplyr, stringr and tidyr we could…
Martin Gal
  • 16,640
  • 5
  • 21
  • 39
1
2
3
…
30 31