Stack Overflow
Stack Overflow

Questions tagged [unnest]

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

456 questions
3
votes
1 answer

R nest/unnest of dataframe results in non-identical objects

My first use of nest/unnest functions in R and I do not understand the result. I nest and immediately unnest and compare the before/after dataframes. Why are the dataframes not identical? > library(tidyverse) > concentration_original <-…
Eric
  • 43
  • 3
3
votes
1 answer

How to iterate over PostgreSQL jsonb array of objects and modify elements?

Given jsonb array and PostgreSQL 12: [{"data":"42","type":"TEMPERATURE"},{"data":"1.1","type":"PRESSURE"}] Need to convert it to: [{"data":"42","type":"temperature"},{"data":"1.1","type":"pressure"}] Is it possible somehow to iterate over jsonb…
y.bregey
  • 1,469
  • 1
  • 12
  • 21
3
votes
3 answers

How to convert data into this form in SQL :

Input: I have to Convert data from input to output. where we trying if for id freq is n then create n rows of that id. Output:
Verma Sushant
  • 45
  • 7
3
votes
4 answers

UNNEST returns no rows for empty array

I am using unnest for more flatten more than one array in Athena query. When the array has some records it returns the correct result. But when the second array is empty it is returning no records. Can someone please let me know how to do unnest to…
somename
  • 978
  • 11
  • 30
3
votes
2 answers

Can I get unique/distinct values of json column in MySQL?

It's a little bit hard to explain so I will explain by giving example, Let say I have table like this (tags is json column) +----+-------------------+--------------------+ | id | occupation | tags …
Kyaw Kyaw Soe
  • 3,258
  • 1
  • 14
  • 25
3
votes
3 answers

Flatten multiple same-sized array columns in BigQuery table

I have a table with several columns where some of them are arrays of the same length. I would like to unnest them to get a result with values from arrays in separate rows. So having table like this one: I would like to get to: This is how it works…
matt525252
  • 642
  • 1
  • 14
  • 21
3
votes
1 answer

Naming columns from a hoisted vector after unnest_wider

Playing around with some of the new functionality of tidyr 1.0 and I've come across a bit of a head scratcher. I've used boxplot.stats to get a vector of boxplot values I'd like to use to plot. I've done this successfully but am convinced there is…
tomasu
  • 1,388
  • 9
  • 11
3
votes
1 answer

R: How to keep names while unnesting doubled nested tibble?

at the moment I'm trying to figure out how to keep the names of an inner and other list nested within a tibble while unnesting. The .id parameter of the unnest function is the closest I found, but it starts to number the values instead of using the…
Someone2
  • 421
  • 2
  • 15
3
votes
1 answer

Tidyverse unnest_tokens does not work inside function

I have a unnest_tokens function that works in the code, but once I put it into a function I cannot get it to work. I don't understand why this happens when I put it inside a function. data: id words 1 why is this function not…
Dennis Loos
  • 113
  • 2
  • 9
3
votes
2 answers

Concatenate array elements on a joined table PostgreSQL

Is it possible to do a 1 on 1 element array concatenation if I have a query like this: EDIT: Arrays not always have the same number of elements. could be that array1 has sometimes 4 elements ans array2 8 elements. drop table if exists a; drop table…
johan855
  • 1,578
  • 4
  • 26
  • 51
3
votes
1 answer

Select from array in postgres using wildcard/Like

So I have a Postgres database where one of the columns is an array of strings If I do the query SELECT count(*) FROM table WHERE column @> ARRAY['string']::varchar[]; I get a certain set of data back, but if I want to query that array with a…
Brian L. Clark
  • 588
  • 5
  • 19
3
votes
2 answers

How to unnest an ARRAY of dates in Postgres?

I want to do an insert on the following table but I am not able to convert an ARRAY of dates. CREATE TABLE schedule ( idschedule serial NOT NULL, idzone integer NOT NULL, "time" timestamp without time zone NOT NULL, automatic boolean NOT…
pablo masri
  • 33
  • 1
  • 4
3
votes
2 answers

Oracle to PostgreSQL query conversion with string_to_array()

I have below query in Oracle: SELECT to_number(a.v_VALUE), b.v_VALUE FROM TABLE(inv_fn_splitondelimiter('12;5;25;10',';')) a JOIN TABLE(inv_fn_splitondelimiter('10;20;;', ';')) b ON a.v_idx = b.v_idx which give me result…
Dhaval Patel
  • 7,471
  • 6
  • 37
  • 70
3
votes
5 answers

Postgres - find min of array

Suppose I have a table like this: link_ids | length ------------+----------- {1,4} | {1,2} {2,5} | {0,1} How can I find the min length for each link_ids? So the final output looks something like: link_ids | …
jaynp
  • 3,275
  • 4
  • 30
  • 43
2
votes
1 answer

How to unnest an object from `spatialsampling`'s `spatial_clustering_cv`

I would like to perform spatial clustering on a sf object and attach the fold IDs to my original dataframe, in a new column. Here's what my input looks like (an sf object with points) # A tibble: 6 × 2 Street geometry …
Nova
  • 5,423
  • 2
  • 42
  • 62
1 2 3
…
30 31