{ "cells": [ { "cell_type": "markdown", "id": "bbb08f5e-b959-43dd-a413-42df94262198", "metadata": {}, "source": [ "# Producción de Gas y Petróleo en Argentina - Procesamiento de datasets" ] }, { "cell_type": "markdown", "id": "f8069605-a2c1-4c64-ba3c-6b08d15df15f", "metadata": { "tags": [] }, "source": [ "## Librerías y configuraciones" ] }, { "cell_type": "code", "execution_count": 1, "id": "a21349de-04cb-41ea-937f-ae34527b1554", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import requests\n", "import os\n", "\n", "pd.set_option(\"display.max_columns\", None)" ] }, { "cell_type": "markdown", "id": "147af327-3e24-4ce1-b7cf-a0ae22161f44", "metadata": { "tags": [] }, "source": [ "## Descarga y unión de datasets" ] }, { "cell_type": "code", "execution_count": 2, "id": "057a350b-c1aa-4d6a-869c-62ae4231a34a", "metadata": { "tags": [] }, "outputs": [], "source": [ "datasets = [\n", " {\"anio\": 2023, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/231c39b3-e81e-4398-af8d-b115807f2c25/download/produccin-de-pozos-de-gas-y-petrleo-2023.csv\"},\n", " {\"anio\": 2022, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/876b3746-85e2-4039-adeb-b1354436159f/download/produccin-de-pozos-de-gas-y-petrleo-2022.csv\"},\n", " {\"anio\": 2021, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/465be754-a372-4c31-b855-81dc5fe3309f/download/produccin-de-pozos-de-gas-y-petrleo-2021.csv\"},\n", " {\"anio\": 2020, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/c4a4a6a0-e75a-4e12-ae5c-54d53a70348c/download/produccin-de-pozos-de-gas-y-petrleo-2020.csv\"},\n", " {\"anio\": 2019, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/8bc0d61c-0408-43d4-a7bc-7178fcb5d37e/download/produccin-de-pozos-de-gas-y-petrleo-2019.csv\"},\n", " {\"anio\": 2018, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/333fd72a-9b83-4bc1-bc94-0f5940b52331/download/produccin-de-pozos-de-gas-y-petrleo-2018.csv\"},\n", " {\"anio\": 2017, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/df4857e1-7c3f-4980-b5b5-184fe78bfcf0/download/produccin-de-pozos-de-gas-y-petrleo-2017.csv\"},\n", " {\"anio\": 2016, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/d8539ae8-0a71-4339-a16c-139b21bd2cd0/download/produccin-de-pozos-de-gas-y-petrleo-2016.csv\"},\n", " {\"anio\": 2015, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/e375aa35-fd8d-41d6-aa0c-e6879ca567a1/download/produccin-de-pozos-de-gas-y-petrleo-2015.csv\"},\n", " {\"anio\": 2014, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/cd9813a7-1e19-4f60-a02a-7903dd81aff7/download/produccin-de-pozos-de-gas-y-petrleo-2014.csv\"},\n", " {\"anio\": 2013, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/bc7ac8fe-2cec-4dab-acdd-322ea1ccc887/download/produccin-de-pozos-de-gas-y-petrleo-2013.csv\"},\n", " {\"anio\": 2012, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/0dce0e75-1556-47ee-8615-1955fbd54ade/download/produccin-de-pozos-de-gas-y-petrleo-2012.csv\"},\n", " {\"anio\": 2011, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/4817272c-7365-4bdd-b02d-75b118218b10/download/produccin-de-pozos-de-gas-y-petrleo-2011.csv\"},\n", " {\"anio\": 2010, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/364ca28e-d069-4bd6-8771-925f0db152a8/download/produccin-de-pozos-de-gas-y-petrleo-2010.csv\"},\n", " {\"anio\": 2009, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/48585038-055a-4437-bb1d-4fe36073f453/download/produccin-de-pozos-de-gas-y-petrleo-2009.csv\"},\n", " {\"anio\": 2008, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/3430b5a8-a516-42ca-a47d-2e1ce45925fb/download/produccin-de-pozos-de-gas-y-petrleo-2008.csv\"},\n", " {\"anio\": 2007, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/be663a63-f020-4e28-8f31-c5f81d47554d/download/produccin-de-pozos-de-gas-y-petrleo-2007.csv\"},\n", " {\"anio\": 2006, \"link\": \"http://datos.energia.gob.ar/dataset/c846e79c-026c-4040-897f-1ad3543b407c/resource/4e1c55e5-1f1b-4fc8-aa37-2080d9795f29/download/produccin-de-pozos-de-gas-y-petrleo-2006.csv\"}\n", "]" ] }, { "cell_type": "code", "execution_count": 4, "id": "2e22f16a-ef5e-411a-9d35-c324caf81ca6", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "CSV del año 2023 descargado con éxito.\n", "CSV del año 2022 descargado con éxito.\n", "CSV del año 2021 descargado con éxito.\n", "CSV del año 2020 descargado con éxito.\n", "CSV del año 2019 descargado con éxito.\n", "CSV del año 2018 descargado con éxito.\n", "CSV del año 2017 descargado con éxito.\n", "CSV del año 2016 descargado con éxito.\n", "CSV del año 2015 descargado con éxito.\n", "CSV del año 2014 descargado con éxito.\n", "CSV del año 2013 descargado con éxito.\n", "CSV del año 2012 descargado con éxito.\n", "CSV del año 2011 descargado con éxito.\n", "CSV del año 2010 descargado con éxito.\n", "CSV del año 2009 descargado con éxito.\n", "CSV del año 2008 descargado con éxito.\n", "CSV del año 2007 descargado con éxito.\n", "CSV del año 2006 descargado con éxito.\n" ] } ], "source": [ "for i in datasets:\n", " response = requests.get(i[\"link\"])\n", " if response.status_code == 200:\n", " content = response.content\n", " file_path = f\"datasets/{i['anio']}.csv\"\n", " with open(file_path, \"wb\") as file:\n", " file.write(content)\n", " print(f'CSV del año {i[\"anio\"]} descargado con éxito.')\n", " else:\n", " print(f'Fallo al descargar CSV del año {i[\"anio\"]} - Código: {response.status_code}')" ] }, { "cell_type": "markdown", "id": "176acdeb-2735-443e-b891-4464946211df", "metadata": {}, "source": [ "**Elección de columnas relevantes:**" ] }, { "cell_type": "code", "execution_count": 2, "id": "56df2c2f-648e-4279-ad0c-b2ae13de07d4", "metadata": { "tags": [] }, "outputs": [], "source": [ "columnas_a_leer = {\n", " \"prod_pet\": float,\n", " \"prod_gas\": float,\n", " \"prod_agua\": float,\n", " \"tef\": float,\n", " \"tipoextraccion\": str,\n", " \"tipoestado\": str,\n", " \"tipopozo\": str,\n", " \"empresa\": str,\n", " \"sigla\": str,\n", " \"formacion\": str,\n", " \"profundidad\": float,\n", " \"areayacimiento\": str,\n", " \"cuenca\": str,\n", " \"provincia\": str,\n", " \"tipo_de_recurso\": str,\n", " \"clasificacion\": str,\n", " \"fecha_data\": str\n", "}" ] }, { "cell_type": "markdown", "id": "dad3ee28-7bbc-4dae-8345-c0aa7e90858c", "metadata": {}, "source": [ "**Instanciado de dataframes:**" ] }, { "cell_type": "code", "execution_count": 3, "id": "7294e907-e493-4737-a395-ae85a6220618", "metadata": { "tags": [] }, "outputs": [], "source": [ "archivos = os.listdir(\"datasets\")\n", "\n", "df = pd.concat([pd.read_csv(\"datasets/\"+i, header=0, usecols=list(columnas_a_leer), dtype=columnas_a_leer) for i in archivos if i.endswith(\".csv\")])" ] }, { "cell_type": "code", "execution_count": 4, "id": "7ffc8a23-6a17-434a-8d2a-17734db66c48", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Int64Index: 15006994 entries, 0 to 322869\n", "Data columns (total 17 columns):\n", " # Column Dtype \n", "--- ------ ----- \n", " 0 prod_pet float64\n", " 1 prod_gas float64\n", " 2 prod_agua float64\n", " 3 tef float64\n", " 4 tipoextraccion object \n", " 5 tipoestado object \n", " 6 tipopozo object \n", " 7 empresa object \n", " 8 sigla object \n", " 9 profundidad float64\n", " 10 formacion object \n", " 11 areayacimiento object \n", " 12 cuenca object \n", " 13 provincia object \n", " 14 tipo_de_recurso object \n", " 15 clasificacion object \n", " 16 fecha_data object \n", "dtypes: float64(5), object(12)\n", "memory usage: 2.0+ GB\n" ] } ], "source": [ "df.info()" ] }, { "cell_type": "markdown", "id": "d47cffaa-31fe-4433-8d89-04da0453c88f", "metadata": {}, "source": [ "# Procesamiento de dataframe:" ] }, { "cell_type": "markdown", "id": "fd1231b7-6ee6-4ab1-8e11-a6e7c1ec3802", "metadata": {}, "source": [ "Como primera medida, para reducir el espacio del dataframe, crearemos subtablas para unir los datos repetidos en la tabla principal." ] }, { "cell_type": "code", "execution_count": 5, "id": "8c441b5f-d37a-4f26-ab7b-ccf15c9eb6b5", "metadata": { "tags": [] }, "outputs": [], "source": [ "columnas_tablas = [\n", " \"tipoextraccion\",\n", " \"tipoestado\",\n", " \"tipopozo\",\n", " \"empresa\",\n", " \"formacion\",\n", " \"areayacimiento\",\n", " \"cuenca\",\n", " \"provincia\",\n", " \"tipo_de_recurso\",\n", " \"clasificacion\",\n", " \"sigla\",\n", " \"fecha_data\"\n", "]" ] }, { "cell_type": "code", "execution_count": 6, "id": "4f0123d0-2f2c-4be0-bf1c-192d00028e05", "metadata": { "tags": [] }, "outputs": [], "source": [ "tablas = {}\n", "\n", "for i in columnas_tablas:\n", " categorias_unicas = df[i].unique()\n", " tablas.update({i: pd.DataFrame({i: categorias_unicas})})\n", " tablas[i][i+\"_id\"] = range(0, len(categorias_unicas))\n", " df = df.merge(tablas[i], on=i, how=\"left\")\n", " df.drop(i, axis=1, inplace=True)" ] }, { "cell_type": "code", "execution_count": 7, "id": "9b264a6f-8276-478f-ad71-a1f6688d9a8c", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Int64Index: 15006994 entries, 0 to 15006993\n", "Data columns (total 17 columns):\n", " # Column Dtype \n", "--- ------ ----- \n", " 0 prod_pet float64\n", " 1 prod_gas float64\n", " 2 prod_agua float64\n", " 3 tef float64\n", " 4 profundidad float64\n", " 5 tipoextraccion_id int64 \n", " 6 tipoestado_id int64 \n", " 7 tipopozo_id int64 \n", " 8 empresa_id int64 \n", " 9 formacion_id int64 \n", " 10 areayacimiento_id int64 \n", " 11 cuenca_id int64 \n", " 12 provincia_id int64 \n", " 13 tipo_de_recurso_id int64 \n", " 14 clasificacion_id int64 \n", " 15 sigla_id int64 \n", " 16 fecha_data_id int64 \n", "dtypes: float64(5), int64(12)\n", "memory usage: 2.0 GB\n" ] } ], "source": [ "df.info()" ] }, { "cell_type": "markdown", "id": "0cb0fe9b-d3b5-4212-b26c-bb8452738a17", "metadata": {}, "source": [ "Luego convertimos valores flotantes a enteros:" ] }, { "cell_type": "code", "execution_count": 11, "id": "aa837687-4dc5-4991-b9fb-f30faf12d016", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Int64Index: 15006994 entries, 0 to 15006993\n", "Data columns (total 17 columns):\n", " # Column Dtype\n", "--- ------ -----\n", " 0 prod_pet int32\n", " 1 prod_gas int32\n", " 2 prod_agua int32\n", " 3 tef int32\n", " 4 profundidad int32\n", " 5 tipoextraccion_id int64\n", " 6 tipoestado_id int64\n", " 7 tipopozo_id int64\n", " 8 empresa_id int64\n", " 9 formacion_id int64\n", " 10 areayacimiento_id int64\n", " 11 cuenca_id int64\n", " 12 provincia_id int64\n", " 13 tipo_de_recurso_id int64\n", " 14 clasificacion_id int64\n", " 15 sigla_id int64\n", " 16 fecha_data_id int64\n", "dtypes: int32(5), int64(12)\n", "memory usage: 1.7 GB\n" ] } ], "source": [ "columnas_a_redondear = [\n", " \"prod_pet\",\n", " \"prod_gas\",\n", " \"prod_agua\",\n", " \"tef\",\n", " \"profundidad\"\n", "]\n", "\n", "for i in columnas_a_redondear:\n", " df[i] = df[i].round().astype(int)\n", "\n", "df.info()" ] }, { "cell_type": "markdown", "id": "4d40b446-537b-4d80-842d-c8e1aa8787e5", "metadata": {}, "source": [ "Procedemos a guardar todos los dataframes:" ] }, { "cell_type": "code", "execution_count": 8, "id": "bdaa5906-7095-4975-84e4-eecaabcbae88", "metadata": { "tags": [] }, "outputs": [], "source": [ "df.to_csv(\"datasets/produccion.csv\", index=False)\n", "for i in tablas:\n", " tablas[i].to_csv(f\"datasets/{i}.csv\", index=False)" ] }, { "cell_type": "code", "execution_count": 13, "id": "4ae51f59-351a-4526-847d-b1d9880b3955", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
prod_petprod_gasprod_aguatefprofundidadtipoextraccion_idtipoestado_idtipopozo_idempresa_idformacion_idareayacimiento_idcuenca_idprovincia_idtipo_de_recurso_idclasificacion_idsigla_idfecha_data_id
08519808152185000000000000
100002530010001000010
200002058010000000020
300002034121000000030
400001920010000000140
\n", "
" ], "text/plain": [ " prod_pet prod_gas prod_agua tef profundidad tipoextraccion_id \\\n", "0 85 1980 8 15 2185 0 \n", "1 0 0 0 0 2530 0 \n", "2 0 0 0 0 2058 0 \n", "3 0 0 0 0 2034 1 \n", "4 0 0 0 0 1920 0 \n", "\n", " tipoestado_id tipopozo_id empresa_id formacion_id areayacimiento_id \\\n", "0 0 0 0 0 0 \n", "1 1 0 0 0 1 \n", "2 1 0 0 0 0 \n", "3 2 1 0 0 0 \n", "4 1 0 0 0 0 \n", "\n", " cuenca_id provincia_id tipo_de_recurso_id clasificacion_id sigla_id \\\n", "0 0 0 0 0 0 \n", "1 0 0 0 0 1 \n", "2 0 0 0 0 2 \n", "3 0 0 0 0 3 \n", "4 0 0 0 1 4 \n", "\n", " fecha_data_id \n", "0 0 \n", "1 0 \n", "2 0 \n", "3 0 \n", "4 0 " ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.head()" ] }, { "cell_type": "markdown", "id": "2d64ddef-f48e-4df7-a37c-e49742c70f71", "metadata": {}, "source": [ "Chequeo de tamaño de cada columna:" ] }, { "cell_type": "code", "execution_count": 9, "id": "9d783a7d-a4f0-40ef-b432-d85be27ed6cd", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "{'prod_pet': 85.86493682861328,\n", " 'prod_gas': 81.28928089141846,\n", " 'prod_agua': 88.31593704223633,\n", " 'tef': 83.22790718078613,\n", " 'profundidad': 106.83516883850098,\n", " 'tipoextraccion_id': 43.06702518463135,\n", " 'tipoestado_id': 43.656578063964844,\n", " 'tipopozo_id': 42.93544578552246,\n", " 'empresa_id': 50.04568958282471,\n", " 'formacion_id': 49.84333515167236,\n", " 'areayacimiento_id': 65.66456317901611,\n", " 'cuenca_id': 42.93563938140869,\n", " 'provincia_id': 43.344736099243164,\n", " 'tipo_de_recurso_id': 42.935373306274414,\n", " 'clasificacion_id': 42.93537139892578,\n", " 'sigla_id': 97.9739236831665,\n", " 'fecha_data_id': 64.64103317260742}" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "def obtener_tamanio_columnas_csv(ruta_archivo):\n", " # Lee el archivo CSV utilizando pandas\n", " dataframe = pd.read_csv(ruta_archivo)\n", " \n", " # Obtiene el tamaño en disco de cada columna\n", " tamanios = {}\n", " for columna in dataframe.columns:\n", " ruta_temporal = 'temp.csv'\n", " dataframe[columna].to_csv(ruta_temporal, index=False)\n", " tamanio_bytes = os.path.getsize(ruta_temporal)\n", " tamanio_mb = tamanio_bytes / (1024 * 1024) # Convertir a MB\n", " tamanios[columna] = tamanio_mb\n", " \n", " # Elimina el archivo temporal\n", " os.remove(ruta_temporal)\n", " \n", " return tamanios\n", "\n", "display(obtener_tamanio_columnas_csv(\"datasets/produccion.csv\"))" ] } ], "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.11.2" } }, "nbformat": 4, "nbformat_minor": 5 }