MongoDB: Aggregating multidimensional info into a panel data

user_11311795 0
Viewed 40

I am a beginner in MongoDB and I am learning aggregations. I have the following document which is information from Inflation, it looks like this:

{
    "_id": {
        "$oid": "60678f561091100d5dbe2ddf"
    },
    
    "V": 0.21,
    "D1C": "202001",    
    "D2C": 63,    
    "D3C": 7169,
    
},
{
    "_id": {
        "$oid": "60678f561091100d5dbe2de0"
    },    
    "V": 0.39,
    "D1C": "202001",    
    "D2C": 63,    
    "D3C": 7170,
    
},
{
    "_id": {
        "$oid": "60678f561091100d5dbe2de1"
    },    
    "V": 0.2,
    "D1C": "202001",    
    "D2C": 63,    
    "D3C": 7171,
    
},

(...)

{
    "_id": {
        "$oid": "60678f561091100d5dbe2fa8"
    },    
    "V": 0.21,
    "D1C": "202001", 
    "D2C": 69,    
    "D3C": 7169,
    
},
{
    "_id": {
        "$oid": "60678f561091100d5dbe2fa9"
    }
    "V": 0.39,
    "D1C": "202001",
    "D2C": 69,
    "D3C": 7170,
}

"V" is the value of the inflation, "D1C" is the month, "D2C" is the type of index (i.e. % yearly accumulated or % monthly) and "D3C" is the group of inflation (i.e. Food, Industry, Services).

I wanted to aggregate this data in such a way that would be easy to chose an arbitrary value of "D2C" and import the data into a pandas dataframe.

More specifically i wish i had the data in a way I could ask only for D2C=63 and get something like this:

[ { "7169" : {'01/2020' : 0.21, '02/2020' : 0.66, ...},
  { "5555" : {'01/2020' : 0.21, '02/2020' : 0.66, ...},]

In a way that i get all the groups as keys of a dict and the value would be a dict with pairs of the date and values referring to the group.

Thank you everyone, and happy easter!

pymongo mongodb aggregation dictionary python
1 Answers

SOLUTION #1: If you want the output in single document.

  1. Filter out documents with D2C = 63 using $match stage.
  2. $group the results by month/D3C to get an array of format { k: "", v: ""}.
  3. $group it again by null to get an array of format { k: "", v: ""}.
  4. Convert array to object using $arrayToObject.

Try this:

db.Inflation.aggregate([
    { 
        $match: { "D2C": 63 }
    },
    {
        $group: {
            _id: "$D3C",
            array: { 
                $push: { k: "$D1C", v: "$V" }
            }
        }
    },
    {
        $group: {
            _id: null,
            array: {
                $push: {
                    k: { $toString: "$_id" },
                    v: { $arrayToObject: "$array" }
                }
            }
        }
    },
    {
        $replaceRoot: {
            newRoot: { $arrayToObject: "$array" }
        }
    }
])

Output for SOLUTION #1:

{
    "7169" : {
        "202001" : 0.21
    },
    "7170" : {
        "202001" : 0.39
    },
    "7171" : {
        "202001" : 0.2
    }
}

SOLUTION #2: If you want the output as an array of documents.

db.Inflation.aggregate([
    {
        $match: { "D2C": 63 }
    },
    {
        $group: {
            _id: "$D3C",
            array: {
                $push: { k: "$D1C", v: "$V" }
            }
        }
    },
    {
        $replaceRoot: {
            newRoot: {
                $arrayToObject: [
                    [{
                        k: { $toString: "$_id" },
                        v: { $arrayToObject: "$array" }
                    }]
                ]
            }
        }
    }
])

Output for SOLUTION #2:

[
  {
    "7169": { "202001": 0.21 }
  },
  {
    "7170": { "202001": 0.39 }
  },
  {
    "7171": { "202001": 0.2 }
  }
]
user_50704600
Related