Pipeline de agregação: aplicando a lei de Benford aos dados de COVID-19

1
mongodb+srv://readonly:readonly@covid-19.hip2i.mongodb.net/covid19

1
{
2
  _id: ObjectId("608b24d4e7a11f5710a66b05"),
3
  uids: [ 504 ],
4
  confirmed: 19645,
5
  deaths: 305,
6
  country: 'Morocco',
7
  date: 2020-07-25T00:00:00.000Z,
8
  country_iso2s: [ 'MA' ],
9
  country_iso3s: [ 'MAR' ],
10
  country_codes: [ 504 ],
11
  combined_names: [ 'Morocco' ],
12
  population: 36910558,
13
  recovered: 16282,
14
  confirmed_daily: 811,
15
  deaths_daily: 6,
16
  recovered_daily: 182
17
}

1
{
2
  country: 'US',
3
  confirmed_size: 435,
4
  deaths_size: 424,
5
  benford: [
6
    { digit: 1, confirmed: 22.3, deaths: 36.1 },
7
    { digit: 2, confirmed: 21.1, deaths: 14.4 },
8
    { digit: 3, confirmed: 11.5, deaths: 10.6 },
9
    { digit: 4, confirmed: 11.7, deaths: 8 },
10
    { digit: 5, confirmed: 11, deaths: 5 },
11
    { digit: 6, confirmed: 11.7, deaths: 4.7 },
12
    { digit: 7, confirmed: 6.7, deaths: 6.8 },
13
    { digit: 8, confirmed: 2.3, deaths: 6.4 },
14
    { digit: 9, confirmed: 1.6, deaths: 8 }
15
  ]
16
}

1
{ "$substr": [ { "$toString": "$confirmed_daily" }, 0, 1 ] }

1
{
2
  "$group": {
3
    "_id": "$country",
4
    "confirmed": {
5
      "$push": {
6
        "$substr": [
7
          {
8
            "$toString": "$confirmed_daily"
9
          },
10
          0,
11
          1
12
        ]
13
      }
14
    },
15
    "deaths": {
16
      "$push": {
17
        "$substr": [
18
          {
19
            "$toString": "$deaths_daily"
20
          },
21
          0,
22
          1
23
        ]
24
      }
25
    }
26
  }
27
}

1
{
2
  _id: 'Japan',
3
  confirmed: [ '1', '3', '7', [...], '7', '5' ],
4
  deaths: [ '7', '6', '0', [...], '-' , '2' ]
5
}

1
{
2
  "$addFields": {
3
    "confirmed": {
4
      "$filter": {
5
        "input": "$confirmed",
6
        "as": "elem",
7
        "cond": {
8
          "$and": [
9
            {
10
              "$ne": [
11
                "$$elem",
12
                "0"
13
              ]
14
            },
15
            {
16
              "$ne": [
17
                "$$elem",
18
                "-"
19
              ]
20
            }
21
          ]
22
        }
23
      }
24
    },
25
    "deaths": { ... } // same as above with $deaths
26
  }
27
}

1
{
2
  "$addFields": {
3
    "confirmed_size": {
4
      "$size": "$confirmed"
5
    },
6
    "deaths_size": {
7
      "$size": "$deaths"
8
    }
9
  }
10
}

1
{
2
  _id: 'Japan',
3
  confirmed: [ '1', '3', '7', [...], '7', '5' ],
4
  deaths: [ '7', '6', '9', [...], '2' , '1' ],
5
  confirmed_size: 452,
6
  deaths_size: 398
7
}

1
{
2
  _id: 'Solomon Islands',
3
  confirmed: [
4
    '4', '1', '1', '3',
5
    '1', '1', '1', '2',
6
    '1', '5'
7
  ],
8
  deaths: [],
9
  confirmed_size: 10,
10
  deaths_size: 0
11
}

1
{
2
  _id: 'Fiji',
3
  confirmed: [
4
    '1', '1', '1', '2', '2', '1', '6', '2',
5
    '2', '1', '2', '1', '5', '5', '3', '1',
6
    '4', '1', '1', '1', '2', '1', '1', '1',
7
    '1', '2', '4', '1', '1', '3', '1', '4',
8
    '3', '2', '1', '4', '1', '1', '1', '5',
9
    '1', '4', '8', '1', '1', '2'
10
  ],
11
  deaths: [ '1', '1' ],
12
  confirmed_size: 46,
13
  deaths_size: 2
14
}

1
{
2
  "$match": {
3
    "confirmed_size": {
4
      "$gt": 0
5
    },
6
    "deaths_size": {
7
      "$gt": 0
8
    }
9
  }
10
}

1
{
2
  "$size": {
3
    "$filter": {
4
      "input": "$confirmed",
5
      "as": "elem",
6
      "cond": {
7
        "$eq": [
8
          "$$elem",
9
          "1"
10
        ]
11
      }
12
    }
13
  }
14
}

1
{
2
  "$round": [
3
    {
4
      "$divide": [
5
        { "$multiply": [ 100, X ] },
6
        "$confirmed_size"
7
      ]
8
    },
9
    1
10
  ]
11
}

1
{
2
  country: 'US',
3
  confirmed_size: 435,
4
  deaths_size: 424,
5
  benford: [
6
    { digit: 1, confirmed: 22.3, deaths: 36.1 },
7
    { digit: 2, confirmed: 21.1, deaths: 14.4 },
8
    { digit: 3, confirmed: 11.5, deaths: 10.6 },
9
    { digit: 4, confirmed: 11.7, deaths: 8 },
10
    { digit: 5, confirmed: 11, deaths: 5 },
11
    { digit: 6, confirmed: 11.7, deaths: 4.7 },
12
    { digit: 7, confirmed: 6.7, deaths: 6.8 },
13
    { digit: 8, confirmed: 2.3, deaths: 6.4 },
14
    { digit: 9, confirmed: 1.6, deaths: 8 }
15
  ]
16
}

1
{
2
  "$addFields": {
3
    "benford": [
4
      {
5
        "digit": 1,
6
        "confirmed": {
7
          "$round": [
8
            {
9
              "$divide": [
10
                {
11
                  "$multiply": [
12
                    100,
13
                    {
14
                      "$size": {
15
                        "$filter": {
16
                          "input": "$confirmed",
17
                          "as": "elem",
18
                          "cond": {
19
                            "$eq": [
20
                              "$$elem",
21
                              "1"
22
                            ]
23
                          }
24
                        }
25
                      }
26
                    }
27
                  ]
28
                },
29
                "$confirmed_size"
30
              ]
31
            },
32
            1
33
          ]
34
        },
35
        "deaths": {
36
          "$round": [
37
            {
38
              "$divide": [
39
                {
40
                  "$multiply": [
41
                    100,
42
                    {
43
                      "$size": {
44
                        "$filter": {
45
                          "input": "$deaths",
46
                          "as": "elem",
47
                          "cond": {
48
                            "$eq": [
49
                              "$$elem",
50
                              "1"
51
                            ]
52
                          }
53
                        }
54
                      }
55
                    }
56
                  ]
57
                },
58
                "$deaths_size"
59
              ]
60
            },
61
            1
62
          ]
63
        }
64
      },
65
      {"digit": 2...},
66
      {"digit": 3...},
67
      {"digit": 4...},
68
      {"digit": 5...},
69
      {"digit": 6...},
70
      {"digit": 7...},
71
      {"digit": 8...},
72
      {"digit": 9...}
73
    ]
74
  }
75
}

1
{
2
  _id: 'Luxembourg',
3
  confirmed: [
4
    '1', '5', '2', '1', '1', '4', '3', '1', '2', '5', '8', '4',
5
    '1', '4', '1', '1', '1', '2', '3', '1', '9', '5', '3', '2',
6
    '2', '2', '1', '7', '4', '1', '2', '5', '1', '2', '1', '8',
7
    '9', '6', '8', '1', '1', '3', '7', '8', '6', '6', '4', '2',
8
    '2', '1', '1', '1', '9', '5', '8', '2', '2', '6', '1', '6',
9
    '4', '8', '5', '4', '1', '2', '1', '3', '1', '4', '1', '1',
10
    '3', '3', '2', '1', '2', '2', '3', '2', '1', '1', '1', '3',
11
    '1', '7', '4', '5', '4', '1', '1', '1', '1', '1', '7', '9',
12
    '1', '4', '4', '8',
13
    ... 242 more items
14
  ],
15
  deaths: [
16
    '1', '1', '8', '9', '2', '3', '4', '1', '3', '5', '5', '1',
17
    '3', '4', '2', '5', '2', '7', '1', '1', '5', '1', '2', '2',
18
    '2', '9', '6', '1', '1', '2', '5', '3', '5', '1', '3', '3',
19
    '1', '3', '3', '4', '1', '1', '2', '4', '1', '2', '2', '1',
20
    '4', '4', '1', '3', '6', '5', '8', '1', '3', '2', '7', '1',
21
    '6', '8', '6', '3', '1', '2', '6', '4', '6', '8', '1', '1',
22
    '2', '3', '7', '1', '8', '2', '1', '6', '3', '3', '6', '2',
23
    '2', '2', '3', '3', '3', '2', '6', '3', '1', '3', '2', '1',
24
    '1', '4', '1', '1',
25
    ... 86 more items
26
  ],
27
  confirmed_size: 342,
28
  deaths_size: 186,
29
  benford: [
30
    { digit: 1, confirmed: 36.3, deaths: 32.8 },
31
    { digit: 2, confirmed: 16.4, deaths: 19.9 },
32
    { digit: 3, confirmed: 9.1, deaths: 14.5 },
33
    { digit: 4, confirmed: 8.8, deaths: 7.5 },
34
    { digit: 5, confirmed: 6.4, deaths: 6.5 },
35
    { digit: 6, confirmed: 9.6, deaths: 8.6 },
36
    { digit: 7, confirmed: 5.8, deaths: 3.8 },
37
    { digit: 8, confirmed: 5, deaths: 4.8 },
38
    { digit: 9, confirmed: 2.6, deaths: 1.6 }
39
  ]
40
}

1
{
2
  _id: 'BenfordTheory',
3
  benford: [
4
    { digit: 1, confirmed: 30.1, deaths: 30.1 },
5
    { digit: 2, confirmed: 17.6, deaths: 17.6 },
6
    { digit: 3, confirmed: 12.5, deaths: 12.5 },
7
    { digit: 4, confirmed: 9.7, deaths: 9.7 },
8
    { digit: 5, confirmed: 7.9, deaths: 7.9 },
9
    { digit: 6, confirmed: 6.7, deaths: 6.7 },
10
    { digit: 7, confirmed: 5.8, deaths: 5.8 },
11
    { digit: 8, confirmed: 5.1, deaths: 5.1 },
12
    { digit: 9, confirmed: 4.6, deaths: 4.6 }
13
  ],
14
  confirmed_size: 999999,
15
  deaths_size: 999999
16
}

1
{
2
  "$unionWith": {
3
    "coll": "benford"
4
  }
5
}

1
{
2
  "$project": {
3
    "country": "$_id",
4
    "_id": 0,
5
    "benford": 1,
6
    "confirmed_size": 1,
7
    "deaths_size": 1
8
  }
9
}

1
{
2
  confirmed_size: 409,
3
  deaths_size: 378,
4
  benford: [
5
    { digit: 1, confirmed: 32.8, deaths: 33.6 },
6
    { digit: 2, confirmed: 20.5, deaths: 13.8 },
7
    { digit: 3, confirmed: 15.9, deaths: 11.9 },
8
    { digit: 4, confirmed: 10.8, deaths: 11.6 },
9
    { digit: 5, confirmed: 5.9, deaths: 6.9 },
10
    { digit: 6, confirmed: 2.9, deaths: 7.7 },
11
    { digit: 7, confirmed: 4.4, deaths: 4.8 },
12
    { digit: 8, confirmed: 3.2, deaths: 5.6 },
13
    { digit: 9, confirmed: 3.7, deaths: 4.2 }
14
  ],
15
  country: 'Bulgaria'
16
}

1
{
2
  confirmed_size: 2,
3
  deaths_size: 1,
4
  benford: [
5
    { digit: 1, confirmed: 0, deaths: 0 },
6
    { digit: 2, confirmed: 50, deaths: 100 },
7
    { digit: 3, confirmed: 0, deaths: 0 },
8
    { digit: 4, confirmed: 0, deaths: 0 },
9
    { digit: 5, confirmed: 0, deaths: 0 },
10
    { digit: 6, confirmed: 0, deaths: 0 },
11
    { digit: 7, confirmed: 50, deaths: 0 },
12
    { digit: 8, confirmed: 0, deaths: 0 },
13
    { digit: 9, confirmed: 0, deaths: 0 }
14
  ],
15
  country: 'MS Zaandam'
16
}

1
use covid19;
2
3
let groupBy = {
4
  "$group": {
5
    "_id": "$country",
6
    "confirmed": {
7
      "$push": {
8
        "$substr": [{
9
          "$toString": "$confirmed_daily"
10
        }, 0, 1]
11
      }
12
    },
13
    "deaths": {
14
      "$push": {
15
        "$substr": [{
16
          "$toString": "$deaths_daily"
17
        }, 0, 1]
18
      }
19
    }
20
  }
21
};
22
23
let createConfirmedAndDeathsArrays = {
24
  "$addFields": {
25
    "confirmed": {
26
      "$filter": {
27
        "input": "$confirmed",
28
        "as": "elem",
29
        "cond": {
30
          "$and": [{
31
            "$ne": ["$$elem", "0"]
32
          }, {
33
            "$ne": ["$$elem", "-"]
34
          }]
35
        }
36
      }
37
    },
38
    "deaths": {
39
      "$filter": {
40
        "input": "$deaths",
41
        "as": "elem",
42
        "cond": {
43
          "$and": [{
44
            "$ne": ["$$elem", "0"]
45
          }, {
46
            "$ne": ["$$elem", "-"]
47
          }]
48
        }
49
      }
50
    }
51
  }
52
};
53
54
let addArraySizes = {
55
  "$addFields": {
56
    "confirmed_size": {
57
      "$size": "$confirmed"
58
    },
59
    "deaths_size": {
60
      "$size": "$deaths"
61
    }
62
  }
63
};
64
65
let removeCountriesWithoutConfirmedCasesAndDeaths = {
66
  "$match": {
67
    "confirmed_size": {
68
      "$gt": 0
69
    },
70
    "deaths_size": {
71
      "$gt": 0
72
    }
73
  }
74
};
75
76
function calculatePercentage(inputArray, digit, sizeArray) {
77
  return {
78
    "$round": [{
79
      "$divide": [{
80
        "$multiply": [100, {
81
          "$size": {
82
            "$filter": {
83
              "input": inputArray,
84
              "as": "elem",
85
              "cond": {
86
                "$eq": ["$$elem", digit]
87
              }
88
            }
89
          }
90
        }]
91
      }, sizeArray]
92
    }, 1]
93
  }
94
}
95
96
function calculatePercentageConfirmed(digit) {
97
  return calculatePercentage("$confirmed", digit, "$confirmed_size");
98
}
99
100
function calculatePercentageDeaths(digit) {
101
  return calculatePercentage("$deaths", digit, "$deaths_size");
102
}
103
104
let calculateBenfordPercentagesConfirmedAndDeaths = {
105
  "$addFields": {
106
    "benford": [{
107
      "digit": 1,
108
      "confirmed": calculatePercentageConfirmed("1"),
109
      "deaths": calculatePercentageDeaths("1")
110
    }, {
111
      "digit": 2,
112
      "confirmed": calculatePercentageConfirmed("2"),
113
      "deaths": calculatePercentageDeaths("2")
114
    }, {
115
      "digit": 3,
116
      "confirmed": calculatePercentageConfirmed("3"),
117
      "deaths": calculatePercentageDeaths("3")
118
    }, {
119
      "digit": 4,
120
      "confirmed": calculatePercentageConfirmed("4"),
121
      "deaths": calculatePercentageDeaths("4")
122
    }, {
123
      "digit": 5,
124
      "confirmed": calculatePercentageConfirmed("5"),
125
      "deaths": calculatePercentageDeaths("5")
126
    }, {
127
      "digit": 6,
128
      "confirmed": calculatePercentageConfirmed("6"),
129
      "deaths": calculatePercentageDeaths("6")
130
    }, {
131
      "digit": 7,
132
      "confirmed": calculatePercentageConfirmed("7"),
133
      "deaths": calculatePercentageDeaths("7")
134
    }, {
135
      "digit": 8,
136
      "confirmed": calculatePercentageConfirmed("8"),
137
      "deaths": calculatePercentageDeaths("8")
138
    }, {
139
      "digit": 9,
140
      "confirmed": calculatePercentageConfirmed("9"),
141
      "deaths": calculatePercentageDeaths("9")
142
    }]
143
  }
144
};
145
146
let unionBenfordTheoreticalValues = {
147
  "$unionWith": {
148
    "coll": "benford"
149
  }
150
};
151
152
let finalProjection = {
153
  "$project": {
154
    "country": "$_id",
155
    "_id": 0,
156
    "benford": 1,
157
    "confirmed_size": 1,
158
    "deaths_size": 1
159
  }
160
};
161
162
let pipeline = [groupBy,
163
                createConfirmedAndDeathsArrays,
164
                addArraySizes,
165
                removeCountriesWithoutConfirmedCasesAndDeaths,
166
                calculateBenfordPercentagesConfirmedAndDeaths,
167
                unionBenfordTheoreticalValues,
168
                finalProjection];
169
170
let cursor = db.countries_summary.aggregate(pipeline);
171
172
printjson(cursor.next());