Advertisements My table looks like this: |ID|Dates|Is_Active| |—-|—-|—-| |718844 |13/07/2023 18:00 – 13/07/2023 19:00| 0| |718842 |13/07/2023 08:00 – 13/07/2023 15:00| 0| |718844 |13/07/2023 06:00 – 13/07/2023 18:00| 0| |718842 |13/07/2023 06:00 – 13/07/2023 08:00| 0| |718842 |13/07/2023 15:00 – 13/07/2023 17:00| 0| |718844 |13/07/2023 18:00 – 13/07/2023 19:00| 1| |718844 |13/07/2023 06:00 – 13/07/2023… Read More Oracle SQL – PIVOT values without aggregate

Advertisements In my index I have multiple documents having field event.code with different values like below – "event": {"code": "98"}, "event": {"code": "99"}, "event": {"code": "98"}, "event": {"code": "88"}. I want Elasticsearch aggregation query which gives me count of particular event code. For eg: count of event.code 98 as 2.(Basically count for particular value is… Read More Get the count of value for particular field using aggregation in Elasticsearch

Advertisements I want to find how much cost I have in remaining stocks. It should filter out stock with remaining = 0 I have the following data db={ stocks: [ { remaining: 2, costPerItem: 5 }, { remaining: 3, costPerItem: 2 }, { remaining: 0, costPerItem: 3 } ] } I have created Mongo Playground:… Read More Mongodb Aggregation: How to find remaining stocks

Advertisements My requirement is to fetch the last updated document from embedded list of documents. Is there any query or aggregation I can write to get only the document that is last updated from this list of documents? Document Structure { _id: "123", updatedAt: 2023-03-02T14:04:37.925+00:00, embedded : [ { _id:"456", updatedAt: 2023-04-02T14:04:37.925+00:00 }, { _id:"789",… Read More MongoDB – Fetch Document with the latest Timestamp from the list of nested documents

Advertisements Below is a block from my aggregate query, { $addFields: { destinations: { $filter: { input: "$destinations", cond: { $and: [ { $or: [ { "$$this.reported_at": { $exists: false } }, { "$$this.reported_at": { $eq: null } } ], }, { $eq: [ "$$this.type", 1 ] } ] } } } } }, which… Read More how to add a OR condition inside AND in $filter of mongodb aggregate

Advertisements I have these documents: [ { "id": 1, "condition": true }, { "id": 1, "condition": false }, { "id": 2, "condition": true }, ] In this case I want the output to be "id": 1, since it has condition as both true and false ("id": 2 only has condition as true, so it does… Read More Mongodb aggregate to find record that has both value of a boolean

Advertisements I am having a MongoDB aggregate result: { "healthDetails": [ { "problem": "Fever", "note": "normal" }, { "problem": "Allergy", "note": "critical" } ] } I need to convert the above array like this: { "healthDetails": "Fever – normal, Allergy – critical" } Need to remove square brackets and double quotes and concat the values… Read More Convert array of objects to string

Advertisements Let import pandas as pd df = pd.DataFrame( { ‘a’: [‘A’, ‘A’, ‘B’, ‘B’, ‘B’, ‘C’], ‘b’: [True, True, True, False, False, True] } ) print(df) groups = df.groupby(‘a’) # "A", "B", "C" agg_groups = groups.agg({‘b’:lambda x: all(x)}) # "A": True, "B": False, "C": True agg_df = agg_groups.reset_index() filtered_df = agg_df[agg_df["b"]] # "A": True,… Read More In Pandas, how to retrieve the rows which created each group, after aggregation and filtering?

Advertisements I was trying to do update and sum up the column value. await single_sku_db.collection("test").updateOne( { _id: ObjectId(id) }, { $push: { report: { $each: [ { name: name, views, }, ], $position: 0, }, }, $set: { report_status: "Completed", total_views: { $sum: "$report.views"}, }, } I cant sum the report.views like this, will get… Read More Updating data with $set and $sum mongodb

Advertisements I have data like this, it’s output of a groupby: numUsers = df.groupby(["user","isvalid"]).count() count user isvalid 5 0.0 1336 1.0 387 But I need to have count of count_valid and count_invalid columns for each user, like this: count_valid count_invalid user 5 387 1336 How can I do it in optimized way in Pandas? >Solution… Read More Pandas merging value of two rows in columns of a single row