unnest Questions
2
I am taking samples from a Bayesian statistical model, serializing them with Avro, uploading them to S3, and querying them with Athena.
I need help writing a query that unnests an array in the tab...
Alumina asked 27/1, 2018 at 2:51
2
Solved
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...
3
Solved
I have a dataframe with several columns containing list columns that I want to unnest (or unchop). BUT, they are different lengths, so the resulting error is Error: No common size for...
Here is a...
3
Solved
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 differe...
Cobham asked 24/12, 2020 at 11:2
4
Solved
Assuming we have a data.table with a nested column val
dt <- data.table(
grp = c(1, 2, 1, 3, 4),
val = list("a", c("b", "c"), c("d", "e", &quo...
Caco asked 13/9, 2022 at 12:45
1
Solved
I have a nested list from reading a JSON that stores logging info from a video game. The time element of the list is a simple vector, while inputManagerStates and syncedProperties are lists that ma...
1
Solved
Say I have some data in a table, t.
id, arr
--, ---
1, [1,2,3]
2, [4,5,6]
SQL
SELECT AVG(n) FROM UNNEST(
SELECT arr FROM t AS n) AS avg_arr
This returns the error, 'Mismatched input 'SELECT'. E...
3
Solved
Table: table_name
create table table_name
(
given_dates timestamp,
set_name varchar
);
Insertion of records:
insert into table_name values('2001-01-01'),('2001-01-05'),('2001-01-10'),
('2001-0...
Injured asked 19/1, 2015 at 6:24
3
Solved
Assume I have following tables:
table: followers_arrays
id | array
--------+---------
1 | {3,4,5}
table: small_profiles
id | username | pic
--------+----------+-------
3 | aaaa | abcd
4 ...
Wertheimer asked 1/11, 2015 at 20:25
2
Solved
I converted a JSON file into a data.frame with a a nested list structure, which I would like to unnest and flatten. Some of the values in the list are NULL, which unnest does not accept. If I repla...
2
Solved
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 <- da...
Kallick asked 22/6, 2021 at 8:23
2
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 m...
Petrick asked 3/12, 2019 at 18:29
3
Solved
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 manual...
2
Solved
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
Solved
I'm trying to get rows where a column of type text[] contains a value similar to some user input.
What I've thought and done so far is to use the 'ANY' and 'LIKE' operator like this:
select * fro...
Industry asked 7/1, 2016 at 14:30
5
Solved
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 ...
1
Solved
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(webTRI...
1
I have a data frame that contains lists, like below:
# Load packages
library(dplyr)
# Create data frame
df <- structure(list(ID = 1:3,
A = structure(list(c(9, 8), c(7,6), c(6, 9)), ptype =...
2
Solved
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 va...
1
Solved
#StandardSQL
WITH tableA AS (
SELECT ["T001", "T002", "T003"] AS T_id, [1, 5] AS L_id
UNION ALL
SELECT ["T008", "T009"] AS T_id, NULL AS L_id
)
SELECT * FROM tableA, UNNEST(L_id) AS unnest
When ...
Patchy asked 5/7, 2017 at 6:3
2
Solved
I'm looking for a postgresql function that will do the opposite of string_agg.
I have a movies table where the tags column contains values such as
Action|Adventure|Drama|Horror|Sci-Fi
Action|Ho...
Vitkun asked 11/3, 2017 at 14:2
2
Solved
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 table, th...
Convalescence asked 24/11, 2016 at 18:51
1
Solved
I would like to use multiple arrays within a select clause. The obvious one didn't work and postgresql points to ROWS FROM() ...
select * from unnest(array[1,2], array[3,4]) as (a int, b int);
E...
Windtight asked 10/1, 2016 at 18:11
1
I'm trying to pass data around as a multidimensional array, and I'm getting behavior that seems odd to me. Specifically I'm trying to get a single element out of a 2 dimensional array (so a 1 dimen...
Chewning asked 21/12, 2015 at 20:29
1
Solved
I have table with subnetworks like
cidr | ip
And I want to select subnetworks by list of belonging ips. In postgres I can do it in this way:
select
ips.ip, net.uid, net.cidr
from
TBL_SID_S...
Teri asked 4/2, 2015 at 11:9
1 Next >
© 2022 - 2025 — McMap. All rights reserved.