Skip to content

Instantly share code, notes, and snippets.

@mauforonda

mauforonda/expiracion_vacunas_bolivia.ipynb

Created June 24, 2022 17:44
Show Gist options
  • Save mauforonda/735f4957ab96ea5d5979f0a462472926 to your computer and use it in GitHub Desktop.
Save mauforonda/735f4957ab96ea5d5979f0a462472926 to your computer and use it in GitHub Desktop.
Download ZIP
Cuántas vacunas de covid-19 que llegaron a Bolivia ya expiraron al 24 de Junio de 2022?
Display the source blob
Display the rendered blob
Raw
expiracion_vacunas_bolivia.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 14,
"id": "da049e00-ec94-4f3d-bd78-fdb9a65c69cc",
"metadata": {
"execution": {
"iopub.execute_input": "2022-06-24T16:45:21.336771Z",
"iopub.status.busy": "2022-06-24T16:45:21.335689Z",
"iopub.status.idle": "2022-06-24T16:45:21.342134Z",
"shell.execute_reply": "2022-06-24T16:45:21.340672Z",
"shell.execute_reply.started": "2022-06-24T16:45:21.336720Z"
},
"tags": []
},
"outputs": [],
"source": [
"import pandas as pd\n",
"import datetime as dt"
]
},
{
"cell_type": "markdown",
"id": "c63835c6-60d1-4873-8e33-92a456e32c72",
"metadata": {},
"source": [
"## Cuántas vacunas de covid-19 que llegaron a Bolivia ya expiraron al 24 de Junio de 2022?\n",
"\n",
"Transcribí las fechas y montos de llegada de vacunas a Bolivia desde hace 1 año y el número de meses que deberían durar bajo condiciones ideales de mantenimiento desde el momento de su producción. Estas cifras pueden ser descargadas en [este google sheet](https://docs.google.com/spreadsheets/d/1NNwrMw-dlv5kjI_eGKZFOi6Lk_BNOUzunIg83-5ZoXg/edit?usp=sharing). Cargo estas hojas de cálculo:"
]
},
{
"cell_type": "code",
"execution_count": 51,
"id": "1ad544ed-fd6e-4731-a496-6d7ba553e9c1",
"metadata": {
"execution": {
"iopub.execute_input": "2022-06-24T17:26:27.951582Z",
"iopub.status.busy": "2022-06-24T17:26:27.951242Z",
"iopub.status.idle": "2022-06-24T17:26:27.985947Z",
"shell.execute_reply": "2022-06-24T17:26:27.984486Z",
"shell.execute_reply.started": "2022-06-24T17:26:27.951546Z"
},
"tags": []
},
"outputs": [],
"source": [
"excel = pd.ExcelFile('vacunas.xlsx')\n",
"duraciones, entregas = [pd.read_excel(excel, sheet_name=s) for s in excel.sheet_names]"
]
},
{
"cell_type": "markdown",
"id": "80f97fb5-495f-41a7-b13c-76742fd927b8",
"metadata": {},
"source": [
"Las duraciones por vacuna (en meses) fueron tomadas de reportes de la agemed y del Ministerio de Salud de Argentina\n",
"\n",
"- [Documentos del Ministerio de Salud de Argentina](https://bancos.salud.gob.ar/sites/default/files/2021-10/caracteristicas-de-las-vacunas-contra-la-COVID19-disponibles-en-argentina.pdf)\n",
"- [Boletines de AGEMED](https://www.agemed.gob.bo/#prensa/boletines)\n",
"- Otros reportes de prensa"
]
},
{
"cell_type": "code",
"execution_count": 52,
"id": "353b0300-9343-44a3-8c04-ac16707ab288",
"metadata": {
"execution": {
"iopub.execute_input": "2022-06-24T17:26:28.683383Z",
"iopub.status.busy": "2022-06-24T17:26:28.683035Z",
"iopub.status.idle": "2022-06-24T17:26:28.707292Z",
"shell.execute_reply": "2022-06-24T17:26:28.706390Z",
"shell.execute_reply.started": "2022-06-24T17:26:28.683348Z"
},
"tags": []
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>vacuna</th>\n",
" <th>monto</th>\n",
" <th>duracion</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>sinopharm</td>\n",
" <td>12000000.0</td>\n",
" <td>24.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>pfizer</td>\n",
" <td>3781440.0</td>\n",
" <td>6.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>astrazeneca</td>\n",
" <td>2590500.0</td>\n",
" <td>6.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>sputnikv</td>\n",
" <td>2469960.0</td>\n",
" <td>6.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>moderna</td>\n",
" <td>1965600.0</td>\n",
" <td>7.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>janssen</td>\n",
" <td>1008000.0</td>\n",
" <td>24.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" vacuna monto duracion\n",
"0 sinopharm 12000000.0 24.0\n",
"1 pfizer 3781440.0 6.0\n",
"2 astrazeneca 2590500.0 6.0\n",
"3 sputnikv 2469960.0 6.0\n",
"4 moderna 1965600.0 7.0\n",
"5 janssen 1008000.0 24.0"
]
},
"execution_count": 52,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"duraciones"
]
},
{
"cell_type": "markdown",
"id": "445fb9ee-ea21-4e14-bf10-d32f7f767055",
"metadata": {},
"source": [
"Las entregas de vacunas fueron tomadas de la cuenta de _@bolcuantas_ en twitter:"
]
},
{
"cell_type": "code",
"execution_count": 53,
"id": "b09626e9-b226-4931-be92-6853423884a7",
"metadata": {
"execution": {
"iopub.execute_input": "2022-06-24T17:26:32.365779Z",
"iopub.status.busy": "2022-06-24T17:26:32.364870Z",
"iopub.status.idle": "2022-06-24T17:26:32.386651Z",
"shell.execute_reply": "2022-06-24T17:26:32.385590Z",
"shell.execute_reply.started": "2022-06-24T17:26:32.365719Z"
},
"tags": []
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>fecha</th>\n",
" <th>vacuna</th>\n",
" <th>monto</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2022-02-16</td>\n",
" <td>pfizer</td>\n",
" <td>1310400.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2022-01-01</td>\n",
" <td>moderna</td>\n",
" <td>1965600.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2021-12-26</td>\n",
" <td>sinopharm</td>\n",
" <td>3000000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>2021-12-20</td>\n",
" <td>astrazeneca</td>\n",
" <td>1000000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>2021-12-16</td>\n",
" <td>pfizer</td>\n",
" <td>999180.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>2021-12-06</td>\n",
" <td>astrazeneca</td>\n",
" <td>228000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>2021-11-23</td>\n",
" <td>pfizer</td>\n",
" <td>90090.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>2021-10-23</td>\n",
" <td>pfizer</td>\n",
" <td>1000350.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>2021-10-16</td>\n",
" <td>sputnikv</td>\n",
" <td>144500.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>2021-10-13</td>\n",
" <td>sputnikv</td>\n",
" <td>378000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>2021-09-26</td>\n",
" <td>pfizer</td>\n",
" <td>188370.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>2021-09-19</td>\n",
" <td>astrazeneca</td>\n",
" <td>19200.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>2021-09-19</td>\n",
" <td>sputnikv</td>\n",
" <td>370000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>2021-09-18</td>\n",
" <td>astrazeneca</td>\n",
" <td>661700.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>2021-09-12</td>\n",
" <td>astrazeneca</td>\n",
" <td>150000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>2021-09-06</td>\n",
" <td>sinopharm</td>\n",
" <td>3300000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>2021-09-04</td>\n",
" <td>sputnikv</td>\n",
" <td>30000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>2021-08-14</td>\n",
" <td>sputnikv</td>\n",
" <td>150000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>18</th>\n",
" <td>2021-08-13</td>\n",
" <td>astrazeneca</td>\n",
" <td>153600.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>19</th>\n",
" <td>2021-08-10</td>\n",
" <td>sputnikv</td>\n",
" <td>125460.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>20</th>\n",
" <td>2021-08-06</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>21</th>\n",
" <td>2021-08-02</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>22</th>\n",
" <td>2021-07-30</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>23</th>\n",
" <td>2021-07-23</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>24</th>\n",
" <td>2021-07-17</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25</th>\n",
" <td>2021-07-11</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>26</th>\n",
" <td>2021-07-11</td>\n",
" <td>janssen</td>\n",
" <td>1008000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>27</th>\n",
" <td>2021-07-09</td>\n",
" <td>sputnikv</td>\n",
" <td>527000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>28</th>\n",
" <td>2021-06-24</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>29</th>\n",
" <td>2021-06-22</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>30</th>\n",
" <td>2021-06-13</td>\n",
" <td>astrazeneca</td>\n",
" <td>150000.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" fecha vacuna monto\n",
"0 2022-02-16 pfizer 1310400.0\n",
"1 2022-01-01 moderna 1965600.0\n",
"2 2021-12-26 sinopharm 3000000.0\n",
"3 2021-12-20 astrazeneca 1000000.0\n",
"4 2021-12-16 pfizer 999180.0\n",
"5 2021-12-06 astrazeneca 228000.0\n",
"6 2021-11-23 pfizer 90090.0\n",
"7 2021-10-23 pfizer 1000350.0\n",
"8 2021-10-16 sputnikv 144500.0\n",
"9 2021-10-13 sputnikv 378000.0\n",
"10 2021-09-26 pfizer 188370.0\n",
"11 2021-09-19 astrazeneca 19200.0\n",
"12 2021-09-19 sputnikv 370000.0\n",
"13 2021-09-18 astrazeneca 661700.0\n",
"14 2021-09-12 astrazeneca 150000.0\n",
"15 2021-09-06 sinopharm 3300000.0\n",
"16 2021-09-04 sputnikv 30000.0\n",
"17 2021-08-14 sputnikv 150000.0\n",
"18 2021-08-13 astrazeneca 153600.0\n",
"19 2021-08-10 sputnikv 125460.0\n",
"20 2021-08-06 sinopharm 500000.0\n",
"21 2021-08-02 sinopharm 500000.0\n",
"22 2021-07-30 sinopharm 500000.0\n",
"23 2021-07-23 sinopharm 500000.0\n",
"24 2021-07-17 sinopharm 500000.0\n",
"25 2021-07-11 sinopharm 500000.0\n",
"26 2021-07-11 janssen 1008000.0\n",
"27 2021-07-09 sputnikv 527000.0\n",
"28 2021-06-24 sinopharm 500000.0\n",
"29 2021-06-22 sinopharm 500000.0\n",
"30 2021-06-13 astrazeneca 150000.0"
]
},
"execution_count": 53,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"entregas"
]
},
{
"cell_type": "markdown",
"id": "1ca54f55-fb1e-4f55-a908-b0464bd1bf80",
"metadata": {},
"source": [
"La duración de cada vacuna es una cifra en meses desde la fecha de producción que asume condiciones ideales de mantenimiento, distintas para cada tipo de vacuna. Como no conocemos la fecha de producción de cada lote, sólo podemos estimar el tiempo desde su llegada a Bolivia y estimar si ya pasó el tiempo de duración. Éste es un criterio imperfecto pero práctico para determinar si el lote ya expiró, porque asume que las vacunas fueron producidas inmediatamente antes de llegar al país, lo cual probablemente no es correcto. "
]
},
{
"cell_type": "code",
"execution_count": 54,
"id": "ff17a3b9-4b40-49d8-8d39-78b67a6c2975",
"metadata": {
"execution": {
"iopub.execute_input": "2022-06-24T17:30:36.829945Z",
"iopub.status.busy": "2022-06-24T17:30:36.829467Z",
"iopub.status.idle": "2022-06-24T17:30:36.851995Z",
"shell.execute_reply": "2022-06-24T17:30:36.851091Z",
"shell.execute_reply.started": "2022-06-24T17:30:36.829896Z"
},
"tags": []
},
"outputs": [],
"source": [
"# Interpreto correctamente las fechas\n",
"entregas.fecha = pd.to_datetime(entregas.fecha)\n",
"# Estimo la fecha de expiración de cada lote y si ya pasó\n",
"duracion_dict = duraciones.set_index('vacuna').duracion.to_dict()\n",
"entregas['duracion'] = entregas.vacuna.map(duracion_dict)\n",
"entregas['expiracion'] = [row['fecha'] + dt.timedelta(days=30 * row['duracion']) for i, row in entregas.iterrows()]\n",
"today = dt.datetime.now().date()\n",
"entregas['expirado'] = entregas.expiracion.apply(lambda d: d.date() < today)"
]
},
{
"cell_type": "markdown",
"id": "b1b2cba9-721b-40cc-bcee-4fdaab84eb4e",
"metadata": {
"execution": {
"iopub.execute_input": "2022-06-24T17:34:26.781070Z",
"iopub.status.busy": "2022-06-24T17:34:26.780531Z",
"iopub.status.idle": "2022-06-24T17:34:26.793845Z",
"shell.execute_reply": "2022-06-24T17:34:26.792477Z",
"shell.execute_reply.started": "2022-06-24T17:34:26.781012Z"
},
"tags": []
},
"source": [
"Los resultados para cada entrega son:"
]
},
{
"cell_type": "code",
"execution_count": 55,
"id": "d0feca5e-360d-4c1f-adcc-ebd64f6861d8",
"metadata": {
"execution": {
"iopub.execute_input": "2022-06-24T17:34:18.338111Z",
"iopub.status.busy": "2022-06-24T17:34:18.337757Z",
"iopub.status.idle": "2022-06-24T17:34:18.375893Z",
"shell.execute_reply": "2022-06-24T17:34:18.374616Z",
"shell.execute_reply.started": "2022-06-24T17:34:18.338065Z"
},
"tags": []
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>fecha</th>\n",
" <th>vacuna</th>\n",
" <th>monto</th>\n",
" <th>duracion</th>\n",
" <th>expiracion</th>\n",
" <th>expirado</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2022-02-16</td>\n",
" <td>pfizer</td>\n",
" <td>1310400.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-08-15</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2022-01-01</td>\n",
" <td>moderna</td>\n",
" <td>1965600.0</td>\n",
" <td>7.0</td>\n",
" <td>2022-07-30</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2021-12-26</td>\n",
" <td>sinopharm</td>\n",
" <td>3000000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-12-16</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>2021-12-20</td>\n",
" <td>astrazeneca</td>\n",
" <td>1000000.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-06-18</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>2021-12-16</td>\n",
" <td>pfizer</td>\n",
" <td>999180.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-06-14</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>2021-12-06</td>\n",
" <td>astrazeneca</td>\n",
" <td>228000.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-06-04</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>2021-11-23</td>\n",
" <td>pfizer</td>\n",
" <td>90090.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-05-22</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>2021-10-23</td>\n",
" <td>pfizer</td>\n",
" <td>1000350.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-04-21</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>2021-10-16</td>\n",
" <td>sputnikv</td>\n",
" <td>144500.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-04-14</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>2021-10-13</td>\n",
" <td>sputnikv</td>\n",
" <td>378000.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-04-11</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>2021-09-26</td>\n",
" <td>pfizer</td>\n",
" <td>188370.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-03-25</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>2021-09-19</td>\n",
" <td>astrazeneca</td>\n",
" <td>19200.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-03-18</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>2021-09-19</td>\n",
" <td>sputnikv</td>\n",
" <td>370000.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-03-18</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>2021-09-18</td>\n",
" <td>astrazeneca</td>\n",
" <td>661700.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-03-17</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>2021-09-12</td>\n",
" <td>astrazeneca</td>\n",
" <td>150000.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-03-11</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>2021-09-06</td>\n",
" <td>sinopharm</td>\n",
" <td>3300000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-08-27</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>2021-09-04</td>\n",
" <td>sputnikv</td>\n",
" <td>30000.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-03-03</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>2021-08-14</td>\n",
" <td>sputnikv</td>\n",
" <td>150000.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-02-10</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>18</th>\n",
" <td>2021-08-13</td>\n",
" <td>astrazeneca</td>\n",
" <td>153600.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-02-09</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>19</th>\n",
" <td>2021-08-10</td>\n",
" <td>sputnikv</td>\n",
" <td>125460.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-02-06</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>20</th>\n",
" <td>2021-08-06</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-27</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>21</th>\n",
" <td>2021-08-02</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-23</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>22</th>\n",
" <td>2021-07-30</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-20</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>23</th>\n",
" <td>2021-07-23</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-13</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>24</th>\n",
" <td>2021-07-17</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-07</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25</th>\n",
" <td>2021-07-11</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-01</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>26</th>\n",
" <td>2021-07-11</td>\n",
" <td>janssen</td>\n",
" <td>1008000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-01</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>27</th>\n",
" <td>2021-07-09</td>\n",
" <td>sputnikv</td>\n",
" <td>527000.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-01-05</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>28</th>\n",
" <td>2021-06-24</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-06-14</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>29</th>\n",
" <td>2021-06-22</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-06-12</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>30</th>\n",
" <td>2021-06-13</td>\n",
" <td>astrazeneca</td>\n",
" <td>150000.0</td>\n",
" <td>6.0</td>\n",
" <td>2021-12-10</td>\n",
" <td>True</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" fecha vacuna monto duracion expiracion expirado\n",
"0 2022-02-16 pfizer 1310400.0 6.0 2022-08-15 False\n",
"1 2022-01-01 moderna 1965600.0 7.0 2022-07-30 False\n",
"2 2021-12-26 sinopharm 3000000.0 24.0 2023-12-16 False\n",
"3 2021-12-20 astrazeneca 1000000.0 6.0 2022-06-18 True\n",
"4 2021-12-16 pfizer 999180.0 6.0 2022-06-14 True\n",
"5 2021-12-06 astrazeneca 228000.0 6.0 2022-06-04 True\n",
"6 2021-11-23 pfizer 90090.0 6.0 2022-05-22 True\n",
"7 2021-10-23 pfizer 1000350.0 6.0 2022-04-21 True\n",
"8 2021-10-16 sputnikv 144500.0 6.0 2022-04-14 True\n",
"9 2021-10-13 sputnikv 378000.0 6.0 2022-04-11 True\n",
"10 2021-09-26 pfizer 188370.0 6.0 2022-03-25 True\n",
"11 2021-09-19 astrazeneca 19200.0 6.0 2022-03-18 True\n",
"12 2021-09-19 sputnikv 370000.0 6.0 2022-03-18 True\n",
"13 2021-09-18 astrazeneca 661700.0 6.0 2022-03-17 True\n",
"14 2021-09-12 astrazeneca 150000.0 6.0 2022-03-11 True\n",
"15 2021-09-06 sinopharm 3300000.0 24.0 2023-08-27 False\n",
"16 2021-09-04 sputnikv 30000.0 6.0 2022-03-03 True\n",
"17 2021-08-14 sputnikv 150000.0 6.0 2022-02-10 True\n",
"18 2021-08-13 astrazeneca 153600.0 6.0 2022-02-09 True\n",
"19 2021-08-10 sputnikv 125460.0 6.0 2022-02-06 True\n",
"20 2021-08-06 sinopharm 500000.0 24.0 2023-07-27 False\n",
"21 2021-08-02 sinopharm 500000.0 24.0 2023-07-23 False\n",
"22 2021-07-30 sinopharm 500000.0 24.0 2023-07-20 False\n",
"23 2021-07-23 sinopharm 500000.0 24.0 2023-07-13 False\n",
"24 2021-07-17 sinopharm 500000.0 24.0 2023-07-07 False\n",
"25 2021-07-11 sinopharm 500000.0 24.0 2023-07-01 False\n",
"26 2021-07-11 janssen 1008000.0 24.0 2023-07-01 False\n",
"27 2021-07-09 sputnikv 527000.0 6.0 2022-01-05 True\n",
"28 2021-06-24 sinopharm 500000.0 24.0 2023-06-14 False\n",
"29 2021-06-22 sinopharm 500000.0 24.0 2023-06-12 False\n",
"30 2021-06-13 astrazeneca 150000.0 6.0 2021-12-10 True"
]
},
"execution_count": 55,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"entregas"
]
},
{
"cell_type": "code",
"execution_count": 63,
"id": "05c6fc93-27c6-4676-945a-d9eca8b97b34",
"metadata": {
"execution": {
"iopub.execute_input": "2022-06-24T17:37:11.460567Z",
"iopub.status.busy": "2022-06-24T17:37:11.460169Z",
"iopub.status.idle": "2022-06-24T17:37:11.471677Z",
"shell.execute_reply": "2022-06-24T17:37:11.470401Z",
"shell.execute_reply.started": "2022-06-24T17:37:11.460520Z"
},
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Expiraron 6365450 vacunas o 30.38% del total que llegó desde el 13 de Junio de 2021.\n"
]
}
],
"source": [
"expirado_total = entregas.groupby('expirado').monto.sum().to_dict()[True]\n",
"print('Expiraron {:.0f} vacunas o {:.2%} del total que llegó desde el 13 de Junio de 2021.'.format(expirado_total, expirado_total / entregas.monto.sum()))"
]
},
{
"cell_type": "markdown",
"id": "6aa7be4a-1f5f-4454-a280-99d1fc67ab68",
"metadata": {},
"source": [
"La descomposición de vacunas expiradas por tipo de vacuna:"
]
},
{
"cell_type": "code",
"execution_count": 66,
"id": "ccf50a16-5879-460b-af9a-71303f2ec26f",
"metadata": {
"execution": {
"iopub.execute_input": "2022-06-24T17:38:17.409984Z",
"iopub.status.busy": "2022-06-24T17:38:17.409497Z",
"iopub.status.idle": "2022-06-24T17:38:17.435010Z",
"shell.execute_reply": "2022-06-24T17:38:17.434305Z",
"shell.execute_reply.started": "2022-06-24T17:38:17.409949Z"
},
"tags": []
},
"outputs": [],
"source": [
"ei = entregas.groupby(['vacuna', 'expirado']).monto.sum().reset_index().pivot_table(index='vacuna', columns='expirado', values='monto').fillna(0)\n",
"ei.columns = ['no expirado', 'expirado']\n",
"ei = ei.astype(int)\n",
"ei['porcentaje_expirado'] = (ei.expirado / (ei['no expirado'] + ei.expirado)) * 100"
]
},
{
"cell_type": "code",
"execution_count": 67,
"id": "2028b19f-4760-43ee-b537-8fd75f276080",
"metadata": {
"execution": {
"iopub.execute_input": "2022-06-24T17:38:18.551666Z",
"iopub.status.busy": "2022-06-24T17:38:18.551247Z",
"iopub.status.idle": "2022-06-24T17:38:18.565910Z",
"shell.execute_reply": "2022-06-24T17:38:18.564637Z",
"shell.execute_reply.started": "2022-06-24T17:38:18.551624Z"
},
"tags": []
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>no expirado</th>\n",
" <th>expirado</th>\n",
" <th>porcentaje_expirado</th>\n",
" </tr>\n",
" <tr>\n",
" <th>vacuna</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>astrazeneca</th>\n",
" <td>0</td>\n",
" <td>2362500</td>\n",
" <td>100.00000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>janssen</th>\n",
" <td>1008000</td>\n",
" <td>0</td>\n",
" <td>0.00000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>moderna</th>\n",
" <td>1965600</td>\n",
" <td>0</td>\n",
" <td>0.00000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>pfizer</th>\n",
" <td>1310400</td>\n",
" <td>2277990</td>\n",
" <td>63.48223</td>\n",
" </tr>\n",
" <tr>\n",
" <th>sinopharm</th>\n",
" <td>10300000</td>\n",
" <td>0</td>\n",
" <td>0.00000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>sputnikv</th>\n",
" <td>0</td>\n",
" <td>1724960</td>\n",
" <td>100.00000</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" no expirado expirado porcentaje_expirado\n",
"vacuna \n",
"astrazeneca 0 2362500 100.00000\n",
"janssen 1008000 0 0.00000\n",
"moderna 1965600 0 0.00000\n",
"pfizer 1310400 2277990 63.48223\n",
"sinopharm 10300000 0 0.00000\n",
"sputnikv 0 1724960 100.00000"
]
},
"execution_count": 67,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"ei"
]
},
{
"cell_type": "markdown",
"id": "02aed167-28a9-4268-bfe1-f88f4984cbdf",
"metadata": {},
"source": [
"Finalmente, los lotes que expirarán en los próximos meses ordenados por fecha de expiración de menor a mayor:"
]
},
{
"cell_type": "code",
"execution_count": 71,
"id": "a306c143-952b-4429-bbf3-477855cd1f92",
"metadata": {
"execution": {
"iopub.execute_input": "2022-06-24T17:39:13.300746Z",
"iopub.status.busy": "2022-06-24T17:39:13.300478Z",
"iopub.status.idle": "2022-06-24T17:39:13.327729Z",
"shell.execute_reply": "2022-06-24T17:39:13.327054Z",
"shell.execute_reply.started": "2022-06-24T17:39:13.300712Z"
},
"tags": []
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>fecha</th>\n",
" <th>vacuna</th>\n",
" <th>monto</th>\n",
" <th>duracion</th>\n",
" <th>expiracion</th>\n",
" <th>expirado</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2022-01-01</td>\n",
" <td>moderna</td>\n",
" <td>1965600.0</td>\n",
" <td>7.0</td>\n",
" <td>2022-07-30</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2022-02-16</td>\n",
" <td>pfizer</td>\n",
" <td>1310400.0</td>\n",
" <td>6.0</td>\n",
" <td>2022-08-15</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>29</th>\n",
" <td>2021-06-22</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-06-12</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>28</th>\n",
" <td>2021-06-24</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-06-14</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25</th>\n",
" <td>2021-07-11</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-01</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>26</th>\n",
" <td>2021-07-11</td>\n",
" <td>janssen</td>\n",
" <td>1008000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-01</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>24</th>\n",
" <td>2021-07-17</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-07</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>23</th>\n",
" <td>2021-07-23</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-13</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>22</th>\n",
" <td>2021-07-30</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-20</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>21</th>\n",
" <td>2021-08-02</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-23</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>20</th>\n",
" <td>2021-08-06</td>\n",
" <td>sinopharm</td>\n",
" <td>500000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-07-27</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>2021-09-06</td>\n",
" <td>sinopharm</td>\n",
" <td>3300000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-08-27</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2021-12-26</td>\n",
" <td>sinopharm</td>\n",
" <td>3000000.0</td>\n",
" <td>24.0</td>\n",
" <td>2023-12-16</td>\n",
" <td>False</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" fecha vacuna monto duracion expiracion expirado\n",
"1 2022-01-01 moderna 1965600.0 7.0 2022-07-30 False\n",
"0 2022-02-16 pfizer 1310400.0 6.0 2022-08-15 False\n",
"29 2021-06-22 sinopharm 500000.0 24.0 2023-06-12 False\n",
"28 2021-06-24 sinopharm 500000.0 24.0 2023-06-14 False\n",
"25 2021-07-11 sinopharm 500000.0 24.0 2023-07-01 False\n",
"26 2021-07-11 janssen 1008000.0 24.0 2023-07-01 False\n",
"24 2021-07-17 sinopharm 500000.0 24.0 2023-07-07 False\n",
"23 2021-07-23 sinopharm 500000.0 24.0 2023-07-13 False\n",
"22 2021-07-30 sinopharm 500000.0 24.0 2023-07-20 False\n",
"21 2021-08-02 sinopharm 500000.0 24.0 2023-07-23 False\n",
"20 2021-08-06 sinopharm 500000.0 24.0 2023-07-27 False\n",
"15 2021-09-06 sinopharm 3300000.0 24.0 2023-08-27 False\n",
"2 2021-12-26 sinopharm 3000000.0 24.0 2023-12-16 False"
]
},
"execution_count": 71,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"entregas[~entregas.expirado].sort_values('expiracion')"
]
},
{
"cell_type": "markdown",
"id": "b7401a1a-87d5-467f-85cf-4cf6563167a4",
"metadata": {},
"source": [
"Este análisis asume que las vacunas han sido mantenidas en condiciones ideales y que su fecha de producción es próxima a la fecha de llegada a Bolivia. Además sólo considera lotes desde el 13 de Junio. Para mejorar este análsis se podría terminar de transcribir la serie completa de lotes que llegaron a Bolivia desde enero de 2021 y construir escenarios en base a otros periodos de duración que asuman condiciones distintas y menos ideales de mantenimiento. "
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.9.12"
},
"widgets": {
"application/vnd.jupyter.widget-state+json": {
"state": {},
"version_major": 2,
"version_minor": 0
}
}
},
"nbformat": 4,
"nbformat_minor": 5
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment