Questions tagged [aggregate]

Aggregate refers to the process of summarizing grouped data, commonly used in Statistics.

1
vote
0answers
18 views

new Date() in MongoDB aggregate returns 1970-01-01T00:00:00Z

I'm trying to see if a date is in the past in a Mongo aggregate pipeline, using Meteor / nodeJS. The pipeline has to be completely self-contained; I cannot pre-calculate any values. The first step is ...
0
votes
0answers
13 views

Returning pairs of _id's of duplicate docs found by a specific field on mongoDb

During DB data migration I'm running these days, I've found docs that remain in the collection that should not be there anymore. I succeeded to find duplicates by unitId field, using the following ...
0
votes
0answers
24 views

Aggregate Window

I need to make a daily rank based on a cumulative window of all days before day t. In this case, I must start with a window of 252 days, order the observations, and make a ranking of 20 equally ...
2
votes
1answer
25 views

Grouping by multiple dimensions, summarise and add calculated column

I have this df: boxChange sameCat # C1 > C2 TRUE # C1 > C2 TRUE # A0 > A1 TRUE # A1 > E4 FALSE # C3 > E6 FALSE # E0 > E3 TRUE # ... ... I would ...
1
vote
2answers
29 views

Group and Sum Multiple Columns without Pandas

I have a list that contains multiple columns, and I need to group and sum rows based on two columns. Can I do this without using a Pandas dataframe? I have a dataset in a list like this: User Days ...
1
vote
1answer
26 views

R count group count and delete if missing n NA

data=data.frame("person"=c(1,1,1,2,2,2,2,3,3,3,3,3), "time"=c(1,2,3,1,2,3,4,1,2,3,4,5), "score"=c(NA,100,97,63,NA,NA,51,NA,NA,73,NA,NA), "count"=c(2,2,2,2,2,2,2,1,1,1,1,1)) I aim to generate 'count' ...
0
votes
0answers
13 views

How to sort data by two attribute types in timeAverage function

I have a dataset of bird locations and I would like to take a weekly average of these lat/long values, sorted by bird and by season. Here is an example of the data: datexample <- data.frame( "...
0
votes
0answers
19 views

How do I aggregate data by 10 year intervals and sum the data - R [duplicate]

I have some statistic data that is from 1965-2016 per year: I want to create a new dataframe that contains e.g. the data from this dataframe but in 10 year intervals (1965-1974). This would be per ...
0
votes
1answer
33 views

What is the maximum limit of JSON Length when running SELECT from MySQL

i have a table with 8 columns and 62,000+ rows. i'd like MySQL to convert its table defition to JSON via the select statement which i do by: SELECT CONCAT( '[', GROUP_CONCAT( ...
0
votes
2answers
16 views

R: How to Count Rows with Subsetted Date in Date Formatted Column

I have about 30,000 rows of data with a Date column in date format. I would like to be able to count the number of rows by month/year and year, but when I aggregate with the below code, I get a vector ...
0
votes
1answer
25 views

Access to previous document in MongoDB aggregation result

The question is how I can refer to the previous document from the result set? I have an aggregation with such result: var result = db.collection.aggregate([...]) { "_id" : "2018-01", "month" ...
2
votes
1answer
44 views

Aggregating neighboring rows with partitioning

I have a huge data set on MS SQL 2012 where a special aggregation must be done. Here is an example of dataset. Key PartitionID StartTime Duration Name 1 1 23/05/2019 ...
-1
votes
0answers
74 views

Aggregate and display complex data in C# WPF

I have a series of Shelves(data structures which hold other data structures called Books). These Shelves reside in a Library class which exists solely to control access to these Shelves. This Library ...
0
votes
0answers
34 views

ORA-22993: specified input amount is greater than actual source amount while creating table with WITH DATA CLAUSE

I need to concatenate the job message descriptions by ett_date and create a new table. After this I am going to operate a different step. My problem is that while i am creating table with WITH DATA ...
-1
votes
0answers
17 views

python excel like pivot table

so I have a table like this below: kev jj yu sales class okay 4 1 8 1 A brand1 5 7 7 3 B 0 4 9 7 6 C brand1 4 5 1 5 A ...
-1
votes
0answers
26 views

Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'SIG.frequences.frequence'

So Im trying to do some math in SQL rather than in python to avoid long query time. Now Im hit with this error but I don't understand it as the column frequences.frequence isn't select in my statement....
0
votes
1answer
27 views

MongoDB: How to group by one field, find the max field in the group and output the entire record

I have a user's location added into a document every few seconds, and I need to query and return a list of users' most recent location and time. A document sample record is as follows. (Of course ...
0
votes
1answer
33 views

For each group, for each week, find the sum of the observations in the previous X weeks in R

For each group (individual_id), for each week_id, I want to calculate the number of appearances the individual has made in the previous X weeks in each city. I have experimented with dplyr to no ...
2
votes
3answers
26 views

Add every three rows of data frame and them multiply the new results - R

I have a data frame and I would like to add every three rows of it. Then, to apply cumprod to the rows in order to have a new data.frame with the resulting new rows. At the end I will have only one ...
0
votes
0answers
29 views

I want to write spring boot code to fetch data from mongoDB with condition

I am able to fetch data from MongoDB using the following code but I want to expose that data from CommandLineRunner runner to other class. @Repository public interface ToolsDao extends ...
0
votes
3answers
43 views

How to I get accuracy values by group [duplicate]

I can't get the average accuracies (proportion of TRUE values) in Correct_answers columns for the groups chart type and condition. data structure(list(Element = structure(c(1L, 1L, 1L, 1L, 1L), ....
0
votes
2answers
36 views

How to merge an array field in multiple documents into a single output array in MongoDB and mongoexport to csv

I have a MongoDB collection, Groups. Each group has an array of members, referenced by the member's unique id. A user can be a member of many groups. Group data looks like this: { _id: 1, members: ['...
0
votes
0answers
38 views

Aggregate based on multiple conditions MongoDB

I'm setting up a dashboard where we want to show some KPIs for each months. I have a collection with business, each business can have a subscription, which can hold multiple subscriptions (Array) and ...
0
votes
2answers
44 views

Use dplyr to group-by dataset and summarize mean and SD (standard deviation)

I have some python code that uses .groupby and .agg to convert a dataframe into a summary table, and am having trouble converting into R. My desired output looks like this: Figure 1. Format for ...
0
votes
1answer
38 views

Grouping maps/structs by key, and summing the associated values for an array

I have an array of structs that i've built by getting a word frequency count for a list of sentences. The output from this is the most popular words per sentence. I need it across all sentences Here ...
0
votes
1answer
42 views

How to write “group by” + “sum” in just one clause?

I've got sample data of IEnumerable<Employee> ie; Where Employee contains "Name, Salary, Department_id" I can use 2 clauses to group by Department id and them "Sum" every group in a for loop,...
0
votes
0answers
34 views

Lookup and Merge Objects based on Regex search match between two collections

I need to perform $lookup aggregate and merge objects based on regex match. One collection is having regex match pattern. So based on these patterns, need to perform search on another collection. I ...
0
votes
2answers
51 views

How to aggregate the data with 5-min-interval for each group?

Here is my data. a <- structure(list(timestamp1.x = c("2019-05-31 18:27:34", "2019-05-31 18:28:34", "2019-05-31 18:29:34", "2019-05-31 18:29:59", "2019-05-31 18:35:35", "2019-05-31 18:35:43", "...
0
votes
0answers
15 views

Change aggregate formula, outputting #NUM

I recently received a formula from a user here and needed to modify it because I realize that my data was a bit different from the example I gave. I fixed for the most part, but ran into a new ...
0
votes
0answers
21 views

python, from group by and aggregate to a new data frame [duplicate]

as I keep learning more and more python, I was able to extract some information I needed from a table. for that I have used the following to group by a specific column, id, and then aggregate some ...
0
votes
2answers
44 views

How to aggregate hourly values into 24h-average means without timestamp

I have 'mydata_hourly' with 3 station (actually more) and their hourly temperature values over one year. This gives me 8760 hourly measurements in one year. Now I want to have the same structure but ...
-1
votes
1answer
31 views

Aggregate vector into dataframe

I want to convert a vector to a dataframe. The vector consists of a unique ID which is followed by further fields. The fields are exhaustive, about 30 different ones, all marked withe backslash. \ID ...
0
votes
0answers
15 views

How to find continuous periods of zero value data by date period in T-SQL

I need to find continuous periods greater than 14 days of zero value data in my database table and flag them for attention using SQL I'm using SQL Server 2016. My data comes in date and time periods, ...
1
vote
2answers
73 views

How to group time by every n minutes in R

I have a dataframe with a lot of time series: 1 0:03 B 1 2 0:05 A 1 3 0:05 A 1 4 0:05 B 1 5 0:10 A 1 6 0:10 B 1 7 0:14 B 1 8 0:18 A 1 9 0:20 ...
1
vote
1answer
32 views

How to concatenate two of the values of a JSON-array in a certain order?

I am using PostgreSQL 9.5. I have a table with JSON arrays, that contain JSON-objects in the following format: [] [{animal:cat}, {plant:sunflower}, {car:mercedes}] [{animal:dog}] [{animal:dog}, {car:...
0
votes
1answer
25 views

how to fetch data using aggregate with dynamic collection with array of _id's

i need get data from dynamic collection like if type = category then fetch data from category collection if type = banner the fetch data from banner. Input Data Array: [ { "title": "New Product", ...
0
votes
1answer
42 views

mongodb: sum of difference between two fields [duplicate]

I am trying to build a query returning the difference between 2 fields. Here are the documents of my collection: [ { _id: 1, type: 'in', amount: 10 }, { _id: 2, type: 'in', amount: 5 }, { _id: 3, ...
0
votes
2answers
34 views

Using an aggregate string function with a subquery

This query selects the following: SELECT jobs.JobID, school.School_Name, CONCAT(staff.First_Name, ' ', staff.Last_Name) AS 'Full_Name', staff.Phone_Number, role.Role FROM jobs ...
1
vote
1answer
54 views

Spit column into many columns

data = data.frame("id"=1:40, "group"=1:5, "score"=sample(1:4,10,r=T)) table(data[which(data$group==1),]$score) WANT=data.frame("group"=1:5, "...
1
vote
1answer
27 views

Pyspark aggregate a StructType column as an Array of its elements for each line [duplicate]

I'm trying to do something that seems pretty much straightforward but somehow cannot figure how to do it with pyspark. I have a df with two columns (to simplify) 'id' and 'strcol', with possible ...
0
votes
0answers
27 views

Why I can't aggregate setof jsonb in Postgres?

I am trying to convert jsonb-column into string, but after using jsonb_array_elements I can't find any function what could do anything useful with setof jsonb it returns. SELECT string_agg( ...
0
votes
0answers
29 views

Repository for value or entity types in Domain Driven Design

I'm reading about Repositories in Domain Driven Design and the Microsoft patterns for micro-service architecture and they both agree that I should have one Repository per Aggregate Root. I generally ...
0
votes
1answer
20 views

Efficiently creating code to calculate relative error

The overall purpose of my code is calculate the pixel-scale relative errors after aggregating a fine-resolution map to a coarse-resolution map. To accomplish this, I have already created a data frame (...
0
votes
0answers
13 views

aggregate with multiple functions in r [duplicate]

I have a simple dataframe, and want to aggregate on both mean and count of each. I have three steps: aggregate(x$value1,list(x$col1),mean) aggregate(x$value1,list(x$col1),nrow) merge(d1,d2,by='Group....
0
votes
2answers
60 views

Different rows for results while using the SQL case statement for same value

The result should be in a single row with logged in and not logged in counts. Why it is coming in two different rows for same account? SELECT distinct Account , SUM(CASE Val WHEN status='logged ...
1
vote
2answers
96 views

Is there an R function for identifying 'n' matches in each row?

I am attempting to aggregate my data to find correlations/patterns, and want to discover how and where data may correlate. Specifically, I want to identify how many times an id (here called 'item') ...
2
votes
1answer
67 views

R - aggregated data.table columns differently

I am given a large data-table that needs to be aggregated according to the first column: The problem is the following: For several columns, one just has to form the sum for each category (given in ...
0
votes
0answers
17 views

How increment inside projection, MongoDB

I'm writing a query in MongoDB. I would like to count every grade from every review and if the grade is more than 6 increment Grades_Count and GradesMoreThan6_Count and if not than increment just ...
0
votes
1answer
22 views

Why does it not show the value when it is 0? [duplicate]

I have to use aggregate for my assignment but I'm facing this problem when I try to get the count of credit that are more than 2, it works because the count value is more than 0, but when i try ...
0
votes
2answers
28 views

sum after subtracting first element of particular column with all the elements of another column and repeat with 2 element and so on in R programming

I would like to sum post subtracting all elements of datestamp-1 with 1st element of datestamp-2, and repeat the same for 2nd element of datestamp-2 and so on in R datestamp1 datestamp2 load_percent ...