unnest is a function from the tidyr package that can expand the list columns.
Questions tagged [unnest]
456 questions
4
votes
1 answer
SQL presto - cross join unnest null value
I have arrays of different sizes and I want each value in the array to be in separate rows.
To do that, I have used the cross join unnest. It is working however, it is deleting null array.
So, I have my column ID with the different arrays and some…
Aude Hamdi
- 53
- 1
- 3
4
votes
1 answer
Split value into multiple rows
The following are two tables I have in the database mavenmovies.
testLocation (TABLE 1)
CREATE TABLE mavenmovies.testLocation (
id INT AUTO_INCREMENT PRIMARY KEY,
State varchar(255),
name varchar(255)
);
INSERT INTO testLocation (State,…
Tony Pendleton
- 358
- 1
- 11
4
votes
2 answers
Get all keys of jsonb object in postgresql table where value is true
I have a postgresql table customers with name and features columns.
features contains jsonb objects like {"featureA": true, "featureB": false, "featureC":true}
What I'd like to get is an array of those keys in features where the value is true for…
dennisdee
- 160
- 10
4
votes
2 answers
How to flatten nested array data into row in bigquery
I am trying to flatten inside_array or sub array of nested array data into table rows.
I am able to flatten array_data which is outside array.
Anybody have any suggestion.Thanks in advance
user_a27
- 91
- 1
- 9
4
votes
3 answers
UNNEST a composite array into rows and columns in Postgres
Postgres 11.7.
I'm trying to unpack an array where each item has multiple elements, and can't seem to get the syntax right. I'm hoping that someone can point out what I'm missing. Here's an example:
select
unnest(array[
('Red Large…
Morris de Oryx
- 1,857
- 10
- 28
4
votes
1 answer
Unable to unnest list data frame with different column types
I'm pulling some road traffic data from an API wrapped in an R package. I'm using a list dataframe to control the download of multiple sets of records.
# install.packages(webTRISr)
library(webTRISr)
library(tidyverse)
sites <- c(5745,…
mark
- 537
- 6
- 25
4
votes
1 answer
tidyr::unnest() with different column types
Since the update to tidyr version 1.0.0 I have started to get an error when unnesting a list of dataframes.
The error comes because some of the data frames in the list contain a column with all NA values (logical), while other of the dataframes…
Steen Harsted
- 1,802
- 2
- 21
- 34
4
votes
2 answers
How to split the GENERATE_DATE_ARRAY in a date for a single row in Google Bigquery to create a date view?
I'm attempting to create a date view using the MIN and MAX of dates from another table in Google Bigquery. I've come across GENERATE_DATE_ARRAY and have been able to create the dates I'm after in a single array. However, splitting them out to a…
jbev
- 83
- 1
- 5
4
votes
0 answers
dplr: unnest a matrix column
I am trying to understand invoke_map. In this case I simulate
30 bivariate random normal observations and assign them to a grouping field
for later use simulating a random intercept and slope.
Everything is straight forward until I have to unnest…
Harlan Nelson
- 1,394
- 1
- 10
- 22
4
votes
3 answers
could not find function "unnest_tokens"
I'm trying to split a column into tokens using the tokenizers package but I keep receiving an error: could not find function "unnest_tokens". I am using R 3.5.3 and have installed and reinstalled dplyr, tidytext, tidyverse, tokenizers, tidyr, but…
GoodbyeJane
- 63
- 1
- 5
4
votes
1 answer
Join against the output of an array unnest without creating a temp table
I have a query in a UDF (shown below) which unnest()s an array of integers and joins against it, I have created a local temp table in my pgplsql UDF since I know this works. Is it possible to use unnest directly in a query to perform a join instead…
Hassan Syed
- 20,075
- 11
- 87
- 171
3
votes
4 answers
Expand nested elements of a list within a data.table in R, according to a second list column
I have a data.table in R that is
> data.table(value="one",list1=list(c(list("one1"),list("one2"),list(c("one3 1","one3 2")))),position=list(c(0,1,2)))
value list1 position
1: one 0,1,2
where the element…
user321627
- 2,350
- 4
- 20
- 43
3
votes
3 answers
How to drop duplicated column names in nested tibbles before unnesting
I have a tibble with a column containing (nested) tibbles. The nested tibbles have duplicated data (same names, same values):
df <- tibble(id = 1:2, data = list(tibble(id = 1, var1 = "a", var2 = "b"), tibble(id = 2, var1 = "c", var2 = "d")))
df
#…
MartenMM
- 113
- 5
3
votes
1 answer
AWS Glue - Can't select fields after unnest or relationalize
In AWS S3 I have json docs that I read-in with AWS Glue's create_dynamic_frame.from_options("s3" ...) and the DynamicFrame.printSchema() shows me this, which matches the schema of the documents:
root
|-- updatedAt: string
|-- json: struct
| |--…
user12166
- 51
- 5
3
votes
3 answers
Unnest a data frame and fill new rows with NAs
Let's say I have a nested df, and I want to unnest the columns:
df <- tibble::tribble(
~x, ~y, ~nestdf,
1, 2, tibble::tibble(a=1:2, b=3:4),
3, 4, tibble::tibble(a=3:5, b=5:7)
)
tidyr::unnest(df, nestdf)
# x y a b
# …
Hong Ooi
- 56,353
- 13
- 134
- 187