I have a simple data set. The row names are a meaningful index and column 1 has a list of values. What I eventually want is the average of that list for each row name. What it looks like now: row name years 108457 [1200, 1200, 1540, 1890] 237021 [1600, 1270, 1270] What I eventually… Read More Should I be using unnest_wider and rowMeans to get the average of a list column?
I’m trying to unpack json inside BigQuery. The data contains an array with multiple columns and rows. I’m looking for a way to unpack this to have all the columns, but only the first row of each array. The table should be flat in the end. I can solve it like this, but then I… Read More Return only first row after json_extract_array
import json import pandas as pd resp = requests.get(‘https://data.cms.gov/provider-data/api/1/datastore/query/77hc-ibv8/0?offset=0&count=true&results=true&schema=true&keys=true&format=json’) data = resp.json() data = json.dumps(resp_dict,indent=4) print(data) I am trying to unnest the json data into a dataframe using Python. There are a few ways to do it but wasn’t able to fully unnest the json data. What would be the best way to accomplish this?… Read More How to unnest json data to dataframe in using python
My data on the column _idcounts is like the following: 00A=10;500=20;500=3;00e=11;001(ta)=1; As I want to unnest this column I did my query as: SELECT t._idcounts, anotherField from myDataBase CROSS JOIN UNNEST( cast(_idcounts as array<varchar>)) AS t (_idcounts); But I have this as error: Failed to output to file. Query failed: Cannot cast varchar to array(varchar).… Read More Cannot cast to array(varchar) on presto when unnesting a column
I was trying to unnest the the following data frame. df.org <- structure(list(Gene = "ARIH1", Description = "E3 ubiquitin-protein ligase ARIH1", condition2_cellline = list(c("MCF7", "Jurkat")), condition2_activity = list( c(40.8284023668639, 13.26973)), condition2_concentration = list( c("100uM", "100uM")), condition3_cellline = list("Jurkat"), condition3_activity = list(-4.60251), condition3_concentration = list( "100uM")), row.names = c(NA, -1L), class = c("tbl_df", "tbl", "data.frame")) This… Read More How to unnest a data frame containing list of list with varied length?
I’m trying to find the equivalent of tidyr::unnest() for a data.table with multiple nested columns: MT <- as.data.table(mtcars) MT_NEST_MULT <- MT[, .(data1 = .(.SD[, .(mpg, hp)]), data2 = .(.SD[, !c("mpg", "hp")])), by = .(cyl, gear)] cyl gear data1 data2 8 3 <S3: data.table> <S3: data.table> 8 5 <S3: data.table> <S3: data.table> 6 4 <S3: data.table>… Read More Un-nesting multiple columns with data.table in R
I have a following data and I want to split the each row by delimiter into new rows. Sample Data: **Row No. | Data** 1 | abc,pqr,lmn,omr 2 | aaa,bbb,ccc,ddd And the output should be: **Row No. | Data** 1 | abc 2 | pqr 3 | lmn 4 | omr 5 | aaa 6… Read More Split delimiter based rows in to new rows – SQL
I am trying to generate different length time intervals e.g. 0-5am, 10am-1pm, 6-8pm that occur daily, so something like this: +————————–+————————–+ |start |finish | +————————–+————————–+ |2022-05-17 00:00:00.000000|2022-05-17 05:00:00.000000| |2022-05-17 10:00:00.000000|2022-05-17 13:00:00.000000| |2022-05-17 18:00:00.000000|2022-05-17 20:00:00.000000| |2022-05-18 00:00:00.000000|2022-05-18 05:00:00.000000| |2022-05-18 10:00:00.000000|2022-05-18 13:00:00.000000| |2022-05-18 18:00:00.000000|2022-05-18 20:00:00.000000| +————————–+————————–+ This is what I have so far but it isn’t working… Read More Generating multiple different times over different days with generate_series
Given this input: Row id app_date inventor country 1 id_1 01-15-2022 Steve US Ashley US 2 id_2 03-16-2011 Pete US Jamie US Mary FR I need to concatenate inventor strings for each id, like so: Row id app_date inventors 1 id_1 01-15-2022 Steve, Ashley 2 id_2 03-16-2011 Pete, Jamie, Mary Following this example, I managed… Read More BigQuery – concatenate array of strings for each row
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’. Expecting <expression>. What is the correct way to unnest an array and aggregate the unnested values? >Solution :… Read More SQL Unnest- how to use correctly?