DataFramesAndBigMac.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Leemos CSV, infiriendo el schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = sqlContext.read.csv('../../data/big-mac-source-data.csv', header=True, inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "StructType(List(StructField(name,StringType,true),StructField(iso_a3,StringType,true),StructField(currency_code,StringType,true),StructField(local_price,DoubleType,true),StructField(dollar_ex,DoubleType,true),StructField(GDP_dollar,DoubleType,true),StructField(date,TimestampType,true)))"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- name: string (nullable = true)\n",
      " |-- iso_a3: string (nullable = true)\n",
      " |-- currency_code: string (nullable = true)\n",
      " |-- local_price: double (nullable = true)\n",
      " |-- dollar_ex: double (nullable = true)\n",
      " |-- GDP_dollar: double (nullable = true)\n",
      " |-- date: timestamp (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Definimos el schema a mano"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.types import StructType, StructField, StringType, DoubleType, TimestampType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "Schema = StructType([ StructField(\"name\", StringType(), True),\n",
    "                      StructField(\"iso_a3\", StringType(), True),\n",
    "                      StructField(\"currency_code\", StringType(), True),\n",
    "                      StructField(\"local_price\", DoubleType(), True),\n",
    "                      StructField(\"dollar_ex\", DoubleType(), True),\n",
    "                      StructField(\"GDP_dollar\", DoubleType(), True),\n",
    "                     StructField(\"date\", TimestampType(), True)\n",
    "                    ])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = sqlContext.read.csv('../../data/big-mac-source-data.csv', header=True, schema=Schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- name: string (nullable = true)\n",
      " |-- iso_a3: string (nullable = true)\n",
      " |-- currency_code: string (nullable = true)\n",
      " |-- local_price: double (nullable = true)\n",
      " |-- dollar_ex: double (nullable = true)\n",
      " |-- GDP_dollar: double (nullable = true)\n",
      " |-- date: timestamp (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Información general sobre los datos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1514"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(name='Argentina', iso_a3='ARG', currency_code='ARS', local_price=2.5, dollar_ex=1.0, GDP_dollar=None, date=datetime.datetime(2000, 4, 1, 0, 0)),\n",
       " Row(name='Australia', iso_a3='AUS', currency_code='AUD', local_price=2.59, dollar_ex=1.68, GDP_dollar=None, date=datetime.datetime(2000, 4, 1, 0, 0)),\n",
       " Row(name='Brazil', iso_a3='BRA', currency_code='BRL', local_price=2.95, dollar_ex=1.79, GDP_dollar=None, date=datetime.datetime(2000, 4, 1, 0, 0)),\n",
       " Row(name='Britain', iso_a3='GBR', currency_code='GBP', local_price=1.9, dollar_ex=0.632911392, GDP_dollar=None, date=datetime.datetime(2000, 4, 1, 0, 0)),\n",
       " Row(name='Canada', iso_a3='CAN', currency_code='CAD', local_price=2.85, dollar_ex=1.47, GDP_dollar=None, date=datetime.datetime(2000, 4, 1, 0, 0))]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+\n",
      "|          name|iso_a3|currency_code|local_price|  dollar_ex|GDP_dollar|               date|\n",
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+\n",
      "|     Argentina|   ARG|          ARS|        2.5|        1.0|      null|2000-04-01 00:00:00|\n",
      "|     Australia|   AUS|          AUD|       2.59|       1.68|      null|2000-04-01 00:00:00|\n",
      "|        Brazil|   BRA|          BRL|       2.95|       1.79|      null|2000-04-01 00:00:00|\n",
      "|       Britain|   GBR|          GBP|        1.9|0.632911392|      null|2000-04-01 00:00:00|\n",
      "|        Canada|   CAN|          CAD|       2.85|       1.47|      null|2000-04-01 00:00:00|\n",
      "|         Chile|   CHL|          CLP|     1260.0|      514.0|      null|2000-04-01 00:00:00|\n",
      "|         China|   CHN|          CNY|        9.9|       8.28|      null|2000-04-01 00:00:00|\n",
      "|Czech Republic|   CZE|          CZK|      54.37|       39.1|      null|2000-04-01 00:00:00|\n",
      "|       Denmark|   DNK|          DKK|      24.75|       8.04|      null|2000-04-01 00:00:00|\n",
      "|     Euro area|   EUZ|          EUR|       2.56|1.075268817|      null|2000-04-01 00:00:00|\n",
      "|     Hong Kong|   HKG|          HKD|       10.2|       7.79|      null|2000-04-01 00:00:00|\n",
      "|       Hungary|   HUN|          HUF|      339.0|      279.0|      null|2000-04-01 00:00:00|\n",
      "|     Indonesia|   IDN|          IDR|    14500.0|     7945.0|      null|2000-04-01 00:00:00|\n",
      "|        Israel|   ISR|          ILS|       14.5|       4.05|      null|2000-04-01 00:00:00|\n",
      "|         Japan|   JPN|          JPY|      294.0|      106.0|      null|2000-04-01 00:00:00|\n",
      "|      Malaysia|   MYS|          MYR|       4.52|        3.8|      null|2000-04-01 00:00:00|\n",
      "|        Mexico|   MEX|          MXN|       20.9|       9.41|      null|2000-04-01 00:00:00|\n",
      "|   New Zealand|   NZL|          NZD|        3.4|       2.01|      null|2000-04-01 00:00:00|\n",
      "|        Poland|   POL|          PLN|        5.5|        4.3|      null|2000-04-01 00:00:00|\n",
      "|        Russia|   RUS|          RUB|       39.5|       28.5|      null|2000-04-01 00:00:00|\n",
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+---------+------+-------------+------------------+-----------------+------------------+\n",
      "|summary|     name|iso_a3|currency_code|       local_price|        dollar_ex|        GDP_dollar|\n",
      "+-------+---------+------+-------------+------------------+-----------------+------------------+\n",
      "|  count|     1514|  1514|         1514|              1514|             1514|               901|\n",
      "|   mean|     null|  null|         null| 9058.013232345338| 3450.15151106356|27514.171355266484|\n",
      "| stddev|     null|  null|         null|173614.45356185062|66303.78425411126|21072.286557425992|\n",
      "|    min|Argentina|   ARE|          AED|               0.0|              0.0|          1049.749|\n",
      "|    max|  Vietnam|   ZAF|          ZAR|         4000000.0|        1600500.0|        100578.968|\n",
      "+-------+---------+------+-------------+------------------+-----------------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.describe().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+---------+------+-------------+------------------+-----------------+------------------+\n",
      "|summary|     name|iso_a3|currency_code|       local_price|        dollar_ex|        GDP_dollar|\n",
      "+-------+---------+------+-------------+------------------+-----------------+------------------+\n",
      "|  count|     1514|  1514|         1514|              1514|             1514|               901|\n",
      "|   mean|     null|  null|         null| 9058.013232345338| 3450.15151106356|27514.171355266484|\n",
      "| stddev|     null|  null|         null|173614.45356185062|66303.78425411126|21072.286557425992|\n",
      "|    min|Argentina|   ARE|          AED|               0.0|              0.0|          1049.749|\n",
      "|    25%|     null|  null|         null|              4.56|           1.2112|          9565.962|\n",
      "|    50%|     null|  null|         null|              14.9|          5.75305|          21558.78|\n",
      "|    75%|     null|  null|         null|              85.0|           33.135|         43724.031|\n",
      "|    max|  Vietnam|   ZAF|          ZAR|         4000000.0|        1600500.0|        100578.968|\n",
      "+-------+---------+------+-------------+------------------+-----------------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.summary().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['name',\n",
       " 'iso_a3',\n",
       " 'currency_code',\n",
       " 'local_price',\n",
       " 'dollar_ex',\n",
       " 'GDP_dollar',\n",
       " 'date']"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column<b'local_price'>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.local_price"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Agrupando y agregando"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Usando agg y un dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-----------------+----------------+\n",
      "|       name|   max(dollar_ex)|max(local_price)|\n",
      "+-----------+-----------------+----------------+\n",
      "|     Russia|           74.655|           137.0|\n",
      "|     Sweden|             10.4|            52.0|\n",
      "|Philippines|             55.9|           163.0|\n",
      "|   Malaysia|           4.4715|            9.05|\n",
      "|  Singapore|             1.82|             5.8|\n",
      "|    Britain|       0.82877507|            3.29|\n",
      "|     Turkey|        1600500.0|       4000000.0|\n",
      "|    Germany|      0.955246692|            4.14|\n",
      "|     Jordan|          0.71005|             2.3|\n",
      "|     France|      0.955246692|             4.2|\n",
      "|     Greece|      0.955246692|            3.35|\n",
      "|  Sri Lanka|          182.245|           640.0|\n",
      "|     Taiwan|             34.8|            79.0|\n",
      "|   Slovakia|0.892339267389462|             3.2|\n",
      "|  Argentina|          41.8045|           120.0|\n",
      "|    Belgium|      0.955246692|             4.2|\n",
      "|      Qatar|          3.64175|            13.0|\n",
      "|    Finland|      0.955246692|            4.75|\n",
      "|  Nicaragua|          33.1621|           110.0|\n",
      "|       Peru|             3.49|            11.0|\n",
      "+-----------+-----------------+----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby('name').agg({'local_price': 'max', 'dollar_ex': 'max'}).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Usando las funciones para cada operación"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+------------------+\n",
      "|       name|  avg(local_price)|\n",
      "+-----------+------------------+\n",
      "|     Russia| 79.88870967741936|\n",
      "|     Sweden| 39.60491358870968|\n",
      "|Philippines|112.00399999999999|\n",
      "|   Malaysia| 6.878709677419354|\n",
      "|  Singapore| 4.442258064516129|\n",
      "|    Britain|  2.51516129032258|\n",
      "|     Turkey|403455.45827586204|\n",
      "|    Germany|3.7529411764705882|\n",
      "|     Jordan|2.0666666666666664|\n",
      "|     France|3.9470588235294124|\n",
      "|     Greece|3.1819411764705885|\n",
      "|  Sri Lanka| 350.5769230769231|\n",
      "|     Taiwan| 73.54838709677419|\n",
      "|   Slovakia|3.1333333333333333|\n",
      "|  Argentina|27.982580645161292|\n",
      "|    Belgium| 3.900000000000001|\n",
      "|      Qatar|12.333333333333334|\n",
      "|    Finland| 4.227058823529412|\n",
      "|  Nicaragua|105.33333333333333|\n",
      "|       Peru| 9.696142857142856|\n",
      "+-----------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby('name').mean('local_price').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+----------------+\n",
      "|       name|max(local_price)|\n",
      "+-----------+----------------+\n",
      "|     Russia|           137.0|\n",
      "|     Sweden|            52.0|\n",
      "|Philippines|           163.0|\n",
      "|   Malaysia|            9.05|\n",
      "|  Singapore|             5.8|\n",
      "|    Britain|            3.29|\n",
      "|     Turkey|       4000000.0|\n",
      "|    Germany|            4.14|\n",
      "|     Jordan|             2.3|\n",
      "|     France|             4.2|\n",
      "|     Greece|            3.35|\n",
      "|  Sri Lanka|           640.0|\n",
      "|     Taiwan|            79.0|\n",
      "|   Slovakia|             3.2|\n",
      "|  Argentina|           120.0|\n",
      "|    Belgium|             4.2|\n",
      "|      Qatar|            13.0|\n",
      "|    Finland|            4.75|\n",
      "|  Nicaragua|           110.0|\n",
      "|       Peru|            11.0|\n",
      "+-----------+----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby('name').max('local_price').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Con agg y usando las funciones de spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import count, avg, min, max, round"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+------------------+----------------+----------------+\n",
      "|       name|  avg(local_price)|min(local_price)|max(local_price)|\n",
      "+-----------+------------------+----------------+----------------+\n",
      "|     Russia| 79.88870967741936|            35.0|           137.0|\n",
      "|     Sweden| 39.60491358870968|            24.0|            52.0|\n",
      "|Philippines|112.00399999999999|            59.0|           163.0|\n",
      "|   Malaysia| 6.878709677419354|            4.52|            9.05|\n",
      "|  Singapore| 4.442258064516129|             3.2|             5.8|\n",
      "|    Britain|  2.51516129032258|            1.88|            3.29|\n",
      "|     Turkey|403455.45827586204|             4.0|       4000000.0|\n",
      "|    Germany|3.7529411764705882|             3.4|            4.14|\n",
      "|     Jordan|2.0666666666666664|            1.95|             2.3|\n",
      "|     France|3.9470588235294124|             3.5|             4.2|\n",
      "|     Greece|3.1819411764705885|             2.6|            3.35|\n",
      "|  Sri Lanka| 350.5769230769231|           140.0|           640.0|\n",
      "|     Taiwan| 73.54838709677419|            69.0|            79.0|\n",
      "|   Slovakia|3.1333333333333333|             3.0|             3.2|\n",
      "|  Argentina|27.982580645161292|             2.5|           120.0|\n",
      "|    Belgium| 3.900000000000001|             3.7|             4.2|\n",
      "|      Qatar|12.333333333333334|            12.0|            13.0|\n",
      "|    Finland| 4.227058823529412|            3.75|            4.75|\n",
      "|  Nicaragua|105.33333333333333|           103.0|           110.0|\n",
      "|       Peru| 9.696142857142856|             7.9|            11.0|\n",
      "+-----------+------------------+----------------+----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby('name').agg(avg('local_price'), min('local_price'), max('local_price')).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Con agg y usando UDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import pandas_udf, PandasUDFType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "@pandas_udf('double', PandasUDFType.GROUPED_AGG)\n",
    "def max_udf(v):\n",
    "    return v.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+--------------------+\n",
      "|       name|max_udf(local_price)|\n",
      "+-----------+--------------------+\n",
      "|     Russia|               137.0|\n",
      "|     Sweden|                52.0|\n",
      "|Philippines|               163.0|\n",
      "|   Malaysia|                9.05|\n",
      "|  Singapore|                 5.8|\n",
      "|    Britain|                3.29|\n",
      "|     Turkey|           4000000.0|\n",
      "|    Germany|                4.14|\n",
      "|     Jordan|                 2.3|\n",
      "|     France|                 4.2|\n",
      "|     Greece|                3.35|\n",
      "|  Sri Lanka|               640.0|\n",
      "|     Taiwan|                79.0|\n",
      "|   Slovakia|                 3.2|\n",
      "|  Argentina|               120.0|\n",
      "|    Belgium|                 4.2|\n",
      "|      Qatar|                13.0|\n",
      "|    Finland|                4.75|\n",
      "|  Nicaragua|               110.0|\n",
      "|       Peru|                11.0|\n",
      "+-----------+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby('name').agg(max_udf(df.local_price)).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Funciones de agregación sin grupos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+\n",
      "| avg(local_price)|\n",
      "+-----------------+\n",
      "|9058.013232345338|\n",
      "+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupBy().agg({ 'local_price': 'mean' }).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+\n",
      "| avg(local_price)|\n",
      "+-----------------+\n",
      "|9058.013232345338|\n",
      "+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.agg({ 'local_price': 'mean' }).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filtrando filas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.filter('name = \"Argentina\"').count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+------+-------------+-----------+---------+----------+-------------------+\n",
      "|     name|iso_a3|currency_code|local_price|dollar_ex|GDP_dollar|               date|\n",
      "+---------+------+-------------+-----------+---------+----------+-------------------+\n",
      "|Argentina|   ARG|          ARS|        2.5|      1.0|      null|2000-04-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        2.5|      1.0|      null|2001-04-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        2.5|     3.13|      null|2002-04-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        4.1|     2.88|      null|2003-04-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       4.36|     2.95|      null|2004-05-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       4.75|    2.897|      null|2005-06-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       4.75|   3.0638|      null|2006-01-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        7.0|   3.0565|      null|2006-05-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       8.25|  3.08875|      null|2007-01-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       8.25|   3.0915|      null|2007-06-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       11.0|   3.0195|      null|2008-06-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       11.5|  3.81125|      null|2009-07-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        7.0|   3.7985|      null|2010-01-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       14.0|  3.93375|      null|2010-07-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       20.0|   4.1325|  9138.177|2011-07-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       20.0|   4.3135|  9138.177|2012-01-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       19.0|  4.56625|  9138.177|2012-07-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       19.0|   4.9765| 10959.082|2013-01-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       21.0|   5.4125| 10959.082|2013-07-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       21.0|  6.92125| 10959.082|2014-01-01 00:00:00|\n",
      "+---------+------+-------------+-----------+---------+----------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.filter('name = \"Argentina\"').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.filter(df.name == 'Argentina').count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+------+-------------+-----------+---------+----------+-------------------+\n",
      "|       name|iso_a3|currency_code|local_price|dollar_ex|GDP_dollar|               date|\n",
      "+-----------+------+-------------+-----------+---------+----------+-------------------+\n",
      "|      Chile|   CHL|          CLP|     1260.0|    514.0|      null|2000-04-01 00:00:00|\n",
      "|    Hungary|   HUN|          HUF|      339.0|    279.0|      null|2000-04-01 00:00:00|\n",
      "|  Indonesia|   IDN|          IDR|    14500.0|   7945.0|      null|2000-04-01 00:00:00|\n",
      "|      Japan|   JPN|          JPY|      294.0|    106.0|      null|2000-04-01 00:00:00|\n",
      "|South Korea|   KOR|          KRW|     3000.0|   1108.0|      null|2000-04-01 00:00:00|\n",
      "|      Chile|   CHL|          CLP|     1260.0|    601.0|      null|2001-04-01 00:00:00|\n",
      "|    Hungary|   HUN|          HUF|      399.0|    303.0|      null|2001-04-01 00:00:00|\n",
      "|  Indonesia|   IDN|          IDR|    14700.0|  10855.0|      null|2001-04-01 00:00:00|\n",
      "|      Japan|   JPN|          JPY|      294.0|    124.0|      null|2001-04-01 00:00:00|\n",
      "|South Korea|   KOR|          KRW|     3000.0|   1325.0|      null|2001-04-01 00:00:00|\n",
      "|      Chile|   CHL|          CLP|     1400.0|    655.0|      null|2002-04-01 00:00:00|\n",
      "|    Hungary|   HUN|          HUF|      459.0|    272.0|      null|2002-04-01 00:00:00|\n",
      "|  Indonesia|   IDN|          IDR|    16000.0|   9430.0|      null|2002-04-01 00:00:00|\n",
      "|      Japan|   JPN|          JPY|      262.0|    130.0|      null|2002-04-01 00:00:00|\n",
      "|South Korea|   KOR|          KRW|     3100.0|   1304.0|      null|2002-04-01 00:00:00|\n",
      "|     Turkey|   TUR|          TRY|  4000000.0|1324500.0|      null|2002-04-01 00:00:00|\n",
      "|  Venezuela|   VEN|          VEF|     2500.0|    857.0|      null|2002-04-01 00:00:00|\n",
      "|      Chile|   CHL|          CLP|     1400.0|    716.0|      null|2003-04-01 00:00:00|\n",
      "|    Hungary|   HUN|          HUF|      490.0|    224.0|      null|2003-04-01 00:00:00|\n",
      "|  Indonesia|   IDN|          IDR|    16100.0|   8740.0|      null|2003-04-01 00:00:00|\n",
      "+-----------+------+-------------+-----------+---------+----------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.filter(df.dollar_ex > 100).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+------+-------------+-----------+---------+----------+-------------------+\n",
      "|     name|iso_a3|currency_code|local_price|dollar_ex|GDP_dollar|               date|\n",
      "+---------+------+-------------+-----------+---------+----------+-------------------+\n",
      "|Argentina|   ARG|          ARS|        2.5|      1.0|      null|2000-04-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        2.5|      1.0|      null|2001-04-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        2.5|     3.13|      null|2002-04-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        4.1|     2.88|      null|2003-04-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       4.36|     2.95|      null|2004-05-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|       29.8|   29.735|      null|2004-05-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       4.75|    2.897|      null|2005-06-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|       44.0|   24.125|      null|2005-06-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       4.75|   3.0638|      null|2006-01-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        7.0|   3.0565|      null|2006-05-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|      42.28|   23.925|      null|2006-05-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       8.25|  3.08875|      null|2007-01-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|       55.0|   23.925|      null|2007-01-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       8.25|   3.0915|      null|2007-06-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|       62.0|   23.945|      null|2007-06-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       11.0|   3.0195|      null|2008-06-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|       61.0|    19.15|      null|2008-06-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       11.5|  3.81125|      null|2009-07-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|       61.0|    23.17|      null|2009-07-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        7.0|   3.7985|      null|2010-01-01 00:00:00|\n",
      "+---------+------+-------------+-----------+---------+----------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.filter(df.name.isin(\"Argentina\", \"Uruguay\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+------+-------------+-----------+---------+----------+-------------------+\n",
      "|     name|iso_a3|currency_code|local_price|dollar_ex|GDP_dollar|               date|\n",
      "+---------+------+-------------+-----------+---------+----------+-------------------+\n",
      "|Argentina|   ARG|          ARS|        2.5|      1.0|      null|2000-04-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        2.5|      1.0|      null|2001-04-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        2.5|     3.13|      null|2002-04-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        4.1|     2.88|      null|2003-04-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       4.36|     2.95|      null|2004-05-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|       29.8|   29.735|      null|2004-05-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       4.75|    2.897|      null|2005-06-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|       44.0|   24.125|      null|2005-06-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       4.75|   3.0638|      null|2006-01-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        7.0|   3.0565|      null|2006-05-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|      42.28|   23.925|      null|2006-05-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       8.25|  3.08875|      null|2007-01-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|       55.0|   23.925|      null|2007-01-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       8.25|   3.0915|      null|2007-06-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|       62.0|   23.945|      null|2007-06-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       11.0|   3.0195|      null|2008-06-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|       61.0|    19.15|      null|2008-06-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|       11.5|  3.81125|      null|2009-07-01 00:00:00|\n",
      "|  Uruguay|   URY|          UYU|       61.0|    23.17|      null|2009-07-01 00:00:00|\n",
      "|Argentina|   ARG|          ARS|        7.0|   3.7985|      null|2010-01-01 00:00:00|\n",
      "+---------+------+-------------+-----------+---------+----------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df[df.name.isin(\"Argentina\", \"Uruguay\")].show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Descartando columnas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-----------+-------------------+\n",
      "|local_price|  dollar_ex|               date|\n",
      "+-----------+-----------+-------------------+\n",
      "|        2.5|        1.0|2000-04-01 00:00:00|\n",
      "|       2.59|       1.68|2000-04-01 00:00:00|\n",
      "|       2.95|       1.79|2000-04-01 00:00:00|\n",
      "|        1.9|0.632911392|2000-04-01 00:00:00|\n",
      "|       2.85|       1.47|2000-04-01 00:00:00|\n",
      "|     1260.0|      514.0|2000-04-01 00:00:00|\n",
      "|        9.9|       8.28|2000-04-01 00:00:00|\n",
      "|      54.37|       39.1|2000-04-01 00:00:00|\n",
      "|      24.75|       8.04|2000-04-01 00:00:00|\n",
      "|       2.56|1.075268817|2000-04-01 00:00:00|\n",
      "|       10.2|       7.79|2000-04-01 00:00:00|\n",
      "|      339.0|      279.0|2000-04-01 00:00:00|\n",
      "|    14500.0|     7945.0|2000-04-01 00:00:00|\n",
      "|       14.5|       4.05|2000-04-01 00:00:00|\n",
      "|      294.0|      106.0|2000-04-01 00:00:00|\n",
      "|       4.52|        3.8|2000-04-01 00:00:00|\n",
      "|       20.9|       9.41|2000-04-01 00:00:00|\n",
      "|        3.4|       2.01|2000-04-01 00:00:00|\n",
      "|        5.5|        4.3|2000-04-01 00:00:00|\n",
      "|       39.5|       28.5|2000-04-01 00:00:00|\n",
      "+-----------+-----------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.drop('name', 'iso_a3', 'currency_code', 'GDP_dollar').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Evolución del precio del BigMac en Argentina"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### En pesos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+---------+-------------------+\n",
      "|local_price|dollar_ex|               date|\n",
      "+-----------+---------+-------------------+\n",
      "|        2.5|      1.0|2000-04-01 00:00:00|\n",
      "|        2.5|      1.0|2001-04-01 00:00:00|\n",
      "|        2.5|     3.13|2002-04-01 00:00:00|\n",
      "|        4.1|     2.88|2003-04-01 00:00:00|\n",
      "|       4.36|     2.95|2004-05-01 00:00:00|\n",
      "|       4.75|    2.897|2005-06-01 00:00:00|\n",
      "|       4.75|   3.0638|2006-01-01 00:00:00|\n",
      "|        7.0|   3.0565|2006-05-01 00:00:00|\n",
      "|       8.25|  3.08875|2007-01-01 00:00:00|\n",
      "|       8.25|   3.0915|2007-06-01 00:00:00|\n",
      "|       11.0|   3.0195|2008-06-01 00:00:00|\n",
      "|       11.5|  3.81125|2009-07-01 00:00:00|\n",
      "|        7.0|   3.7985|2010-01-01 00:00:00|\n",
      "|       14.0|  3.93375|2010-07-01 00:00:00|\n",
      "|       20.0|   4.1325|2011-07-01 00:00:00|\n",
      "|       20.0|   4.3135|2012-01-01 00:00:00|\n",
      "|       19.0|  4.56625|2012-07-01 00:00:00|\n",
      "|       19.0|   4.9765|2013-01-01 00:00:00|\n",
      "|       21.0|   5.4125|2013-07-01 00:00:00|\n",
      "|       21.0|  6.92125|2014-01-01 00:00:00|\n",
      "+-----------+---------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "info_ar = df.filter('name = \"Argentina\"').drop('name', 'iso_a3', 'currency_code', 'GDP_dollar')\n",
    "info_ar.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_ar = info_ar.rdd\\\n",
    "                 .map(lambda x: (time.mktime(x.date.timetuple()), x.local_price))\\\n",
    "                 .collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_ar_list = list()\n",
    "for data in data_ar:\n",
    "    data_list = list(data)\n",
    "    item = list()\n",
    "    item.append(data_list[0]*1000)\n",
    "    item.append(float(data_list[1]))\n",
    "    data_ar_list.append(item)\n",
    "#data_ar_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "from highcharts import Highchart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe style=\"border:0;outline:none;overflow:hidden\" srcdoc=\"&lt;!DOCTYPE html&gt; &lt;html lang=&quot;en&quot;&gt; &lt;head&gt; &lt;meta charset=&quot;utf-8&quot; /&gt; &lt;link href=&quot;https://www.highcharts.com/highslide/highslide.css&quot; rel=&quot;stylesheet&quot; /&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/highcharts.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/highcharts-more.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/modules/heatmap.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/modules/exporting.js&quot;&gt;&lt;/script&gt; &lt;/head&gt; &lt;body style=&quot;margin:0;padding:0&quot;&gt; &lt;div id=&quot;container&quot; style=&quot;&quot;&gt;Loading....&lt;/div&gt; &lt;script&gt; $(function(){ Highcharts.setOptions({&quot;global&quot;: {}, &quot;lang&quot;: {}}); var option = {&quot;chart&quot;: {&quot;renderTo&quot;: &quot;container&quot;}, &quot;colors&quot;: {}, &quot;credits&quot;: {&quot;enabled&quot;: false}, &quot;drilldown&quot;: {}, &quot;exporting&quot;: {}, &quot;labels&quot;: {}, &quot;legend&quot;: {&quot;enabled&quot;: false}, &quot;loading&quot;: {}, &quot;navigation&quot;: {}, &quot;pane&quot;: {}, &quot;plotOptions&quot;: {}, &quot;series&quot;: {}, &quot;subtitle&quot;: {}, &quot;title&quot;: {&quot;text&quot;: &quot;Precio BigMac en Pesos en Argentina&quot;}, &quot;tooltip&quot;: {&quot;xDateFormat&quot;: &quot;%m/%Y&quot;}, &quot;xAxis&quot;: {&quot;type&quot;: &quot;datetime&quot;}, &quot;yAxis&quot;: {&quot;title&quot;: {&quot;text&quot;: &quot;Precio&quot;}}}; var chart = new Highcharts.Chart(option); var data = [{&quot;data&quot;: [[954558000000.0, 2.5], [986094000000.0, 2.5], [1017630000000.0, 2.5], [1049166000000.0, 4.1], [1083380400000.0, 4.36], [1117594800000.0, 4.75], [1136084400000.0, 4.75], [1146452400000.0, 7.0], [1167620400000.0, 8.25], [1180666800000.0, 8.25], [1212289200000.0, 11.0], [1246417200000.0, 11.5], [1262314800000.0, 7.0], [1277953200000.0, 14.0], [1309489200000.0, 20.0], [1325386800000.0, 20.0], [1341111600000.0, 19.0], [1357009200000.0, 19.0], [1372647600000.0, 21.0], [1388545200000.0, 21.0], [1404183600000.0, 21.0], [1420081200000.0, 28.0], [1435719600000.0, 28.0], [1451617200000.0, 33.0], [1467342000000.0, 50.0], [1483239600000.0, 55.0], [1498878000000.0, 70.0], [1514775600000.0, 75.0], [1530414000000.0, 75.0], [1546311600000.0, 100.0], [1562641200000.0, 120.0]], &quot;type&quot;: &quot;area&quot;, &quot;marker&quot;: {&quot;enabled&quot;: false}, &quot;name&quot;: &quot;Precio ($)&quot;}]; var dataLen = data.length; for (var ix = 0; ix &lt; dataLen; ix++) { chart.addSeries(data[ix]); } }); &lt;/script&gt; &lt;/body&gt; &lt;/html&gt;\" height=520 width=820></iframe>"
      ],
      "text/plain": [
       "<highcharts.highcharts.highcharts.Highchart at 0x7fc64ac31240>"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chart = Highchart()\n",
    "\n",
    "chart.set_options('chart', {})\n",
    "\n",
    "options = {\n",
    "    'title': {\n",
    "        'text': 'Precio BigMac en Pesos en Argentina'\n",
    "    },\n",
    "    'xAxis': {\n",
    "        'type': 'datetime'\n",
    "    },\n",
    "    'yAxis': {\n",
    "        'title': {\n",
    "            'text': 'Precio'\n",
    "        }\n",
    "    },\n",
    "    'legend': {\n",
    "        'enabled': False\n",
    "    },\n",
    "    'tooltip': {\n",
    "        'xDateFormat': '%m/%Y'\n",
    "    }\n",
    "}\n",
    "\n",
    "chart.set_dict_options(options)\n",
    "\n",
    "chart.add_data_set(data_ar_list, 'area', 'Precio ($)', marker={'enabled': False}) \n",
    "\n",
    "chart"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### En dólares"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_ar_usd = df.filter('name = \"Argentina\"')\\\n",
    "                .drop('name', 'iso_a3', 'currency_code', 'GDP_dollar')\\\n",
    "                .rdd\\\n",
    "                .map(lambda x: (int(time.mktime(x.date.timetuple())), float(x.local_price)/float(x.dollar_ex)))\\\n",
    "                .collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(954558000, 2.5),\n",
       " (986094000, 2.5),\n",
       " (1017630000, 0.7987220447284346),\n",
       " (1049166000, 1.423611111111111),\n",
       " (1083380400, 1.4779661016949153),\n",
       " (1117594800, 1.6396272005522956),\n",
       " (1136084400, 1.550362295188981),\n",
       " (1146452400, 2.290201210534925),\n",
       " (1167620400, 2.670983407527317),\n",
       " (1180666800, 2.668607472100922),\n",
       " (1212289200, 3.642987249544627),\n",
       " (1246417200, 3.0173827484421123),\n",
       " (1262314800, 1.842832697117283),\n",
       " (1277953200, 3.558945027009851),\n",
       " (1309489200, 4.839685420447671),\n",
       " (1325386800, 4.636606004404776),\n",
       " (1341111600, 4.160963591568573),\n",
       " (1357009200, 3.8179443383904355),\n",
       " (1372647600, 3.8799076212471135),\n",
       " (1388545200, 3.0341340075853354),\n",
       " (1404183600, 2.5707727620504977),\n",
       " (1420081200, 3.2520325203252036),\n",
       " (1435719600, 3.0651340996168583),\n",
       " (1451617200, 2.3897025544472004),\n",
       " (1467342000, 3.3478406427854033),\n",
       " (1483239600, 3.4683903515686585),\n",
       " (1498878000, 4.1255341093266535),\n",
       " (1514775600, 3.9603960396039604),\n",
       " (1530414000, 2.7051397655545535),\n",
       " (1546311600, 2.669870511280203),\n",
       " (1562641200, 2.8705043715389493)]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_ar_usd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_ar_usd_list = list()\n",
    "for data in data_ar_usd:\n",
    "    data_list = list(data)\n",
    "    item = list()\n",
    "    item.append(data_list[0]*1000)\n",
    "    item.append(data_list[1])\n",
    "    data_ar_usd_list.append(item)\n",
    "#data_ar_usd_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe style=\"border:0;outline:none;overflow:hidden\" srcdoc=\"&lt;!DOCTYPE html&gt; &lt;html lang=&quot;en&quot;&gt; &lt;head&gt; &lt;meta charset=&quot;utf-8&quot; /&gt; &lt;link href=&quot;https://www.highcharts.com/highslide/highslide.css&quot; rel=&quot;stylesheet&quot; /&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/highcharts.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/highcharts-more.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/modules/heatmap.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/modules/exporting.js&quot;&gt;&lt;/script&gt; &lt;/head&gt; &lt;body style=&quot;margin:0;padding:0&quot;&gt; &lt;div id=&quot;container&quot; style=&quot;&quot;&gt;Loading....&lt;/div&gt; &lt;script&gt; $(function(){ Highcharts.setOptions({&quot;global&quot;: {}, &quot;lang&quot;: {}}); var option = {&quot;chart&quot;: {&quot;renderTo&quot;: &quot;container&quot;}, &quot;colors&quot;: {}, &quot;credits&quot;: {&quot;enabled&quot;: false}, &quot;drilldown&quot;: {}, &quot;exporting&quot;: {}, &quot;labels&quot;: {}, &quot;legend&quot;: {&quot;enabled&quot;: false}, &quot;loading&quot;: {}, &quot;navigation&quot;: {}, &quot;pane&quot;: {}, &quot;plotOptions&quot;: {}, &quot;series&quot;: {}, &quot;subtitle&quot;: {}, &quot;title&quot;: {&quot;text&quot;: &quot;Precio BigMac en USD en Argentina&quot;}, &quot;tooltip&quot;: {&quot;xDateFormat&quot;: &quot;%m/%Y&quot;}, &quot;xAxis&quot;: {&quot;type&quot;: &quot;datetime&quot;}, &quot;yAxis&quot;: {&quot;title&quot;: {&quot;text&quot;: &quot;Precio (USD)&quot;}}}; var chart = new Highcharts.Chart(option); var data = [{&quot;data&quot;: [[954558000000, 2.5], [986094000000, 2.5], [1017630000000, 0.7987220447284346], [1049166000000, 1.423611111111111], [1083380400000, 1.4779661016949153], [1117594800000, 1.6396272005522956], [1136084400000, 1.550362295188981], [1146452400000, 2.290201210534925], [1167620400000, 2.670983407527317], [1180666800000, 2.668607472100922], [1212289200000, 3.642987249544627], [1246417200000, 3.0173827484421123], [1262314800000, 1.842832697117283], [1277953200000, 3.558945027009851], [1309489200000, 4.839685420447671], [1325386800000, 4.636606004404776], [1341111600000, 4.160963591568573], [1357009200000, 3.8179443383904355], [1372647600000, 3.8799076212471135], [1388545200000, 3.0341340075853354], [1404183600000, 2.5707727620504977], [1420081200000, 3.2520325203252036], [1435719600000, 3.0651340996168583], [1451617200000, 2.3897025544472004], [1467342000000, 3.3478406427854033], [1483239600000, 3.4683903515686585], [1498878000000, 4.1255341093266535], [1514775600000, 3.9603960396039604], [1530414000000, 2.7051397655545535], [1546311600000, 2.669870511280203], [1562641200000, 2.8705043715389493]], &quot;type&quot;: &quot;area&quot;, &quot;marker&quot;: {&quot;enabled&quot;: false}, &quot;name&quot;: &quot;Precio (USD)&quot;}]; var dataLen = data.length; for (var ix = 0; ix &lt; dataLen; ix++) { chart.addSeries(data[ix]); } }); &lt;/script&gt; &lt;/body&gt; &lt;/html&gt;\" height=520 width=820></iframe>"
      ],
      "text/plain": [
       "<highcharts.highcharts.highcharts.Highchart at 0x7fc64abb5940>"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chart = Highchart()\n",
    "\n",
    "chart.set_options('chart', {})\n",
    "\n",
    "options = {\n",
    "    'title': {\n",
    "        'text': 'Precio BigMac en USD en Argentina'\n",
    "    },\n",
    "    'xAxis': {\n",
    "        'type': 'datetime'\n",
    "    },\n",
    "    'yAxis': {\n",
    "        'title': {\n",
    "            'text': 'Precio (USD)'\n",
    "        }\n",
    "    },\n",
    "    'legend': {\n",
    "        'enabled': False\n",
    "    },\n",
    "    'tooltip': {\n",
    "        'xDateFormat': '%m/%Y'\n",
    "    }\n",
    "}\n",
    "\n",
    "chart.set_dict_options(options)\n",
    "\n",
    "chart.add_data_set(data_ar_usd_list, 'area', 'Precio (USD)', marker={'enabled': False}) \n",
    "\n",
    "chart"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Agregar una columna"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_usd = df.withColumn('usd_price', round((df.local_price / df.dollar_ex),2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+---------+\n",
      "|          name|iso_a3|currency_code|local_price|  dollar_ex|GDP_dollar|               date|usd_price|\n",
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+---------+\n",
      "|     Argentina|   ARG|          ARS|        2.5|        1.0|      null|2000-04-01 00:00:00|      2.5|\n",
      "|     Australia|   AUS|          AUD|       2.59|       1.68|      null|2000-04-01 00:00:00|     1.54|\n",
      "|        Brazil|   BRA|          BRL|       2.95|       1.79|      null|2000-04-01 00:00:00|     1.65|\n",
      "|       Britain|   GBR|          GBP|        1.9|0.632911392|      null|2000-04-01 00:00:00|      3.0|\n",
      "|        Canada|   CAN|          CAD|       2.85|       1.47|      null|2000-04-01 00:00:00|     1.94|\n",
      "|         Chile|   CHL|          CLP|     1260.0|      514.0|      null|2000-04-01 00:00:00|     2.45|\n",
      "|         China|   CHN|          CNY|        9.9|       8.28|      null|2000-04-01 00:00:00|      1.2|\n",
      "|Czech Republic|   CZE|          CZK|      54.37|       39.1|      null|2000-04-01 00:00:00|     1.39|\n",
      "|       Denmark|   DNK|          DKK|      24.75|       8.04|      null|2000-04-01 00:00:00|     3.08|\n",
      "|     Euro area|   EUZ|          EUR|       2.56|1.075268817|      null|2000-04-01 00:00:00|     2.38|\n",
      "|     Hong Kong|   HKG|          HKD|       10.2|       7.79|      null|2000-04-01 00:00:00|     1.31|\n",
      "|       Hungary|   HUN|          HUF|      339.0|      279.0|      null|2000-04-01 00:00:00|     1.22|\n",
      "|     Indonesia|   IDN|          IDR|    14500.0|     7945.0|      null|2000-04-01 00:00:00|     1.83|\n",
      "|        Israel|   ISR|          ILS|       14.5|       4.05|      null|2000-04-01 00:00:00|     3.58|\n",
      "|         Japan|   JPN|          JPY|      294.0|      106.0|      null|2000-04-01 00:00:00|     2.77|\n",
      "|      Malaysia|   MYS|          MYR|       4.52|        3.8|      null|2000-04-01 00:00:00|     1.19|\n",
      "|        Mexico|   MEX|          MXN|       20.9|       9.41|      null|2000-04-01 00:00:00|     2.22|\n",
      "|   New Zealand|   NZL|          NZD|        3.4|       2.01|      null|2000-04-01 00:00:00|     1.69|\n",
      "|        Poland|   POL|          PLN|        5.5|        4.3|      null|2000-04-01 00:00:00|     1.28|\n",
      "|        Russia|   RUS|          RUB|       39.5|       28.5|      null|2000-04-01 00:00:00|     1.39|\n",
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+---------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_usd.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|    avg(usd_price)|\n",
      "+------------------+\n",
      "|3.3926437541308734|\n",
      "+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_usd.groupBy().avg('usd_price').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Info de todos los paises para la última medición"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_df = df_usd.filter('date = \"2019-07-09\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+------+-------------+-----------+-----------------+----------+-------------------+---------+\n",
      "|                name|iso_a3|currency_code|local_price|        dollar_ex|GDP_dollar|               date|usd_price|\n",
      "+--------------------+------+-------------+-----------+-----------------+----------+-------------------+---------+\n",
      "|United Arab Emirates|   ARE|          AED|      14.75|          3.67315|  37732.66|2019-07-09 00:00:00|     4.02|\n",
      "|           Argentina|   ARG|          ARS|      120.0|          41.8045|  14588.01|2019-07-09 00:00:00|     2.87|\n",
      "|           Australia|   AUS|          AUD|       6.15|  1.4436263894904|  55957.72|2019-07-09 00:00:00|     4.26|\n",
      "|             Austria|   AUT|          EUR|       3.81|0.892339267389462|  47383.87|2019-07-09 00:00:00|     4.27|\n",
      "|          Azerbaijan|   AZE|          AZN|       3.95|           1.6965|   4200.33|2019-07-09 00:00:00|     2.33|\n",
      "|             Belgium|   BEL|          EUR|        4.2|0.892339267389462|  43672.41|2019-07-09 00:00:00|     4.71|\n",
      "|             Bahrain|   BHR|          BHD|        1.4|            0.377|  24399.25|2019-07-09 00:00:00|     3.71|\n",
      "|              Brazil|   BRA|          BRL|       17.5|           3.8073|   9928.23|2019-07-09 00:00:00|      4.6|\n",
      "|              Canada|   CAN|          CAD|       6.77|          1.31305|  45223.97|2019-07-09 00:00:00|     5.16|\n",
      "|         Switzerland|   CHE|          CHF|        6.5|          0.99355|  80642.58|2019-07-09 00:00:00|     6.54|\n",
      "|               Chile|   CHL|          CLP|     2640.0|           689.75|  15127.76|2019-07-09 00:00:00|     3.83|\n",
      "|               China|   CHN|          CNY|       21.0|           6.8846|    8677.4|2019-07-09 00:00:00|     3.05|\n",
      "|            Colombia|   COL|          COP|    11900.0|           3227.0|   6325.49|2019-07-09 00:00:00|     3.69|\n",
      "|          Costa Rica|   CRI|          CRC|     2290.0|          581.935|  11810.18|2019-07-09 00:00:00|     3.94|\n",
      "|      Czech Republic|   CZE|          CZK|       85.0|          22.7993|  20409.98|2019-07-09 00:00:00|     3.73|\n",
      "|             Germany|   DEU|          EUR|       4.14|0.892339267389462|  44770.58|2019-07-09 00:00:00|     4.64|\n",
      "|             Denmark|   DNK|          DKK|       30.0|           6.6593|   57380.2|2019-07-09 00:00:00|      4.5|\n",
      "|               Egypt|   EGY|          EGP|       42.0|           16.615|   2495.02|2019-07-09 00:00:00|     2.53|\n",
      "|               Spain|   ESP|          EUR|       3.95|0.892339267389462|  28377.76|2019-07-09 00:00:00|     4.43|\n",
      "|             Estonia|   EST|          EUR|        3.2|0.892339267389462|  20240.91|2019-07-09 00:00:00|     3.59|\n",
      "+--------------------+------+-------------+-----------+-----------------+----------+-------------------+---------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "countries_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ordenando"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_sorted_df = countries_df.orderBy(countries_df.usd_price.asc())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "== Parsed Logical Plan ==\n",
      "Sort [usd_price#1306 ASC NULLS FIRST], true\n",
      "+- Filter (date#30 = cast(2019-07-09 as timestamp))\n",
      "   +- Project [name#24, iso_a3#25, currency_code#26, local_price#27, dollar_ex#28, GDP_dollar#29, date#30, round((local_price#27 / dollar_ex#28), 2) AS usd_price#1306]\n",
      "      +- Relation[name#24,iso_a3#25,currency_code#26,local_price#27,dollar_ex#28,GDP_dollar#29,date#30] csv\n",
      "\n",
      "== Analyzed Logical Plan ==\n",
      "name: string, iso_a3: string, currency_code: string, local_price: double, dollar_ex: double, GDP_dollar: double, date: timestamp, usd_price: double\n",
      "Sort [usd_price#1306 ASC NULLS FIRST], true\n",
      "+- Filter (date#30 = cast(2019-07-09 as timestamp))\n",
      "   +- Project [name#24, iso_a3#25, currency_code#26, local_price#27, dollar_ex#28, GDP_dollar#29, date#30, round((local_price#27 / dollar_ex#28), 2) AS usd_price#1306]\n",
      "      +- Relation[name#24,iso_a3#25,currency_code#26,local_price#27,dollar_ex#28,GDP_dollar#29,date#30] csv\n",
      "\n",
      "== Optimized Logical Plan ==\n",
      "Sort [usd_price#1306 ASC NULLS FIRST], true\n",
      "+- Project [name#24, iso_a3#25, currency_code#26, local_price#27, dollar_ex#28, GDP_dollar#29, date#30, round((local_price#27 / dollar_ex#28), 2) AS usd_price#1306]\n",
      "   +- Filter (isnotnull(date#30) && (date#30 = 1562641200000000))\n",
      "      +- Relation[name#24,iso_a3#25,currency_code#26,local_price#27,dollar_ex#28,GDP_dollar#29,date#30] csv\n",
      "\n",
      "== Physical Plan ==\n",
      "*(2) Sort [usd_price#1306 ASC NULLS FIRST], true, 0\n",
      "+- Exchange rangepartitioning(usd_price#1306 ASC NULLS FIRST, 200)\n",
      "   +- *(1) Project [name#24, iso_a3#25, currency_code#26, local_price#27, dollar_ex#28, GDP_dollar#29, date#30, round((local_price#27 / dollar_ex#28), 2) AS usd_price#1306]\n",
      "      +- *(1) Filter (isnotnull(date#30) && (date#30 = 1562641200000000))\n",
      "         +- *(1) FileScan csv [name#24,iso_a3#25,currency_code#26,local_price#27,dollar_ex#28,GDP_dollar#29,date#30] Batched: false, Format: CSV, Location: InMemoryFileIndex[file:/home/damu/Downloads/spark-2.4.0-bin-hadoop2.7/data/big-mac-source-data.csv], PartitionFilters: [], PushedFilters: [IsNotNull(date), EqualTo(date,2019-07-09 00:00:00.0)], ReadSchema: struct<name:string,iso_a3:string,currency_code:string,local_price:double,dollar_ex:double,GDP_dol...\n"
     ]
    }
   ],
   "source": [
    "countries_sorted_df.explain(True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+------+-------------+-----------+-----------------+----------+-------------------+---------+\n",
      "|        name|iso_a3|currency_code|local_price|        dollar_ex|GDP_dollar|               date|usd_price|\n",
      "+------------+------+-------------+-----------+-----------------+----------+-------------------+---------+\n",
      "|      Russia|   RUS|          RUB|      130.0|         63.83875|  10961.99|2019-07-09 00:00:00|     2.04|\n",
      "|    Malaysia|   MYS|          MYR|       8.85|           4.1425|   9827.67|2019-07-09 00:00:00|     2.14|\n",
      "|South Africa|   ZAF|          ZAR|       31.0|           14.175|   6182.25|2019-07-09 00:00:00|     2.19|\n",
      "|     Romania|   ROU|          RON|        9.3|           4.2192|  10761.71|2019-07-09 00:00:00|      2.2|\n",
      "|     Ukraine|   UKR|          UAH|       57.0|          25.6475|   2655.94|2019-07-09 00:00:00|     2.22|\n",
      "|   Indonesia|   IDN|          IDR|    32000.0|          14130.0|   3884.72|2019-07-09 00:00:00|     2.26|\n",
      "|      Taiwan|   TWN|          TWD|       72.0|           31.172|  24389.68|2019-07-09 00:00:00|     2.31|\n",
      "|  Azerbaijan|   AZE|          AZN|       3.95|           1.6965|   4200.33|2019-07-09 00:00:00|     2.33|\n",
      "|     Moldova|   MDA|          MDL|       43.0|          17.8142|   2726.54|2019-07-09 00:00:00|     2.41|\n",
      "|      Turkey|   TUR|          TRY|      13.99|            5.722|  10537.19|2019-07-09 00:00:00|     2.44|\n",
      "|       Egypt|   EGY|          EGP|       42.0|           16.615|   2495.02|2019-07-09 00:00:00|     2.53|\n",
      "|   Hong Kong|   HKG|          HKD|       20.5|           7.8105|  46091.22|2019-07-09 00:00:00|     2.62|\n",
      "|      Mexico|   MEX|          MXN|       50.0|         18.89525|   9376.99|2019-07-09 00:00:00|     2.65|\n",
      "|       India|   IND|          INR|      183.0|          68.5544|   2014.01|2019-07-09 00:00:00|     2.67|\n",
      "|    Slovenia|   SVN|          EUR|        2.4|0.892339267389462|  23502.25|2019-07-09 00:00:00|     2.69|\n",
      "| Philippines|   PHL|          PHP|      142.0|          51.3175|    2988.9|2019-07-09 00:00:00|     2.77|\n",
      "|     Vietnam|   VNM|          VND|    65000.0|          23231.0|   2353.36|2019-07-09 00:00:00|      2.8|\n",
      "|      Poland|   POL|          PLN|       10.8|          3.80775|  13867.61|2019-07-09 00:00:00|     2.84|\n",
      "|   Argentina|   ARG|          ARS|      120.0|          41.8045|  14588.01|2019-07-09 00:00:00|     2.87|\n",
      "|    Pakistan|   PAK|          PKR|      480.0|           157.45|   1545.94|2019-07-09 00:00:00|     3.05|\n",
      "+------------+------+-------------+-----------+-----------------+----------+-------------------+---------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "countries_sorted_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_info = countries_sorted_df.drop('iso_a3', 'currency_code', 'local_price', 'dollar_ex', 'GDP_dollar', 'date')\\\n",
    "                                    .collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(name='Russia', usd_price=2.04),\n",
       " Row(name='Malaysia', usd_price=2.14),\n",
       " Row(name='South Africa', usd_price=2.19),\n",
       " Row(name='Romania', usd_price=2.2),\n",
       " Row(name='Ukraine', usd_price=2.22),\n",
       " Row(name='Indonesia', usd_price=2.26),\n",
       " Row(name='Taiwan', usd_price=2.31),\n",
       " Row(name='Azerbaijan', usd_price=2.33),\n",
       " Row(name='Moldova', usd_price=2.41),\n",
       " Row(name='Turkey', usd_price=2.44),\n",
       " Row(name='Egypt', usd_price=2.53),\n",
       " Row(name='Hong Kong', usd_price=2.62),\n",
       " Row(name='Mexico', usd_price=2.65),\n",
       " Row(name='India', usd_price=2.67),\n",
       " Row(name='Slovenia', usd_price=2.69),\n",
       " Row(name='Philippines', usd_price=2.77),\n",
       " Row(name='Vietnam', usd_price=2.8),\n",
       " Row(name='Poland', usd_price=2.84),\n",
       " Row(name='Argentina', usd_price=2.87),\n",
       " Row(name='China', usd_price=3.05),\n",
       " Row(name='Pakistan', usd_price=3.05),\n",
       " Row(name='Hungary', usd_price=3.1),\n",
       " Row(name='Oman', usd_price=3.14),\n",
       " Row(name='Lithuania', usd_price=3.19),\n",
       " Row(name='Latvia', usd_price=3.19),\n",
       " Row(name='Peru', usd_price=3.19),\n",
       " Row(name='Jordan', usd_price=3.24),\n",
       " Row(name='Guatemala', usd_price=3.26),\n",
       " Row(name='Nicaragua', usd_price=3.32),\n",
       " Row(name='Croatia', usd_price=3.33),\n",
       " Row(name='Saudi Arabia', usd_price=3.47),\n",
       " Row(name='Honduras', usd_price=3.51),\n",
       " Row(name='Qatar', usd_price=3.57),\n",
       " Row(name='Estonia', usd_price=3.59),\n",
       " Row(name='Japan', usd_price=3.59),\n",
       " Row(name='Slovakia', usd_price=3.59),\n",
       " Row(name='Kuwait', usd_price=3.61),\n",
       " Row(name='Sri Lanka', usd_price=3.64),\n",
       " Row(name='Portugal', usd_price=3.64),\n",
       " Row(name='Colombia', usd_price=3.69),\n",
       " Row(name='Greece', usd_price=3.7),\n",
       " Row(name='Bahrain', usd_price=3.71),\n",
       " Row(name='Czech Republic', usd_price=3.73),\n",
       " Row(name='South Korea', usd_price=3.81),\n",
       " Row(name='Chile', usd_price=3.83),\n",
       " Row(name='Thailand', usd_price=3.86),\n",
       " Row(name='Costa Rica', usd_price=3.94),\n",
       " Row(name='United Arab Emirates', usd_price=4.02),\n",
       " Row(name='Britain', usd_price=4.1),\n",
       " Row(name='New Zealand', usd_price=4.23),\n",
       " Row(name='Australia', usd_price=4.26),\n",
       " Row(name='Singapore', usd_price=4.26),\n",
       " Row(name='Austria', usd_price=4.27),\n",
       " Row(name='Lebanon', usd_price=4.31),\n",
       " Row(name='Spain', usd_price=4.43),\n",
       " Row(name='Denmark', usd_price=4.5),\n",
       " Row(name='Netherlands', usd_price=4.55),\n",
       " Row(name='Euro area', usd_price=4.57),\n",
       " Row(name='Brazil', usd_price=4.6),\n",
       " Row(name='Germany', usd_price=4.64),\n",
       " Row(name='Uruguay', usd_price=4.66),\n",
       " Row(name='Belgium', usd_price=4.71),\n",
       " Row(name='France', usd_price=4.71),\n",
       " Row(name='Israel', usd_price=4.77),\n",
       " Row(name='Ireland', usd_price=4.82),\n",
       " Row(name='Italy', usd_price=4.82),\n",
       " Row(name='Norway', usd_price=4.85),\n",
       " Row(name='Canada', usd_price=5.16),\n",
       " Row(name='Finland', usd_price=5.32),\n",
       " Row(name='Sweden', usd_price=5.38),\n",
       " Row(name='United States', usd_price=5.74),\n",
       " Row(name='Switzerland', usd_price=6.54)]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries = list()\n",
    "for info in countries_info:\n",
    "    countries.append(info.name)\n",
    "#countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_price = list()\n",
    "for info in countries_info:\n",
    "    countries_price.append(info.usd_price)\n",
    "#countries_price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe style=\"border:0;outline:none;overflow:hidden\" srcdoc=\"&lt;!DOCTYPE html&gt; &lt;html lang=&quot;en&quot;&gt; &lt;head&gt; &lt;meta charset=&quot;utf-8&quot; /&gt; &lt;link href=&quot;https://www.highcharts.com/highslide/highslide.css&quot; rel=&quot;stylesheet&quot; /&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/highcharts.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/highcharts-more.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/modules/heatmap.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/modules/exporting.js&quot;&gt;&lt;/script&gt; &lt;/head&gt; &lt;body style=&quot;margin:0;padding:0&quot;&gt; &lt;div id=&quot;container&quot; style=&quot;height:1500px;&quot;&gt;Loading....&lt;/div&gt; &lt;script&gt; $(function(){ Highcharts.setOptions({&quot;global&quot;: {}, &quot;lang&quot;: {}}); var option = {&quot;chart&quot;: {&quot;renderTo&quot;: &quot;container&quot;, &quot;inverted&quot;: true, &quot;height&quot;: 1500}, &quot;colors&quot;: {}, &quot;credits&quot;: {&quot;enabled&quot;: false}, &quot;drilldown&quot;: {}, &quot;exporting&quot;: {}, &quot;labels&quot;: {}, &quot;legend&quot;: {&quot;enabled&quot;: false}, &quot;loading&quot;: {}, &quot;navigation&quot;: {}, &quot;pane&quot;: {}, &quot;plotOptions&quot;: {&quot;series&quot;: {}}, &quot;series&quot;: {}, &quot;subtitle&quot;: {}, &quot;title&quot;: {&quot;text&quot;: &quot;Precio BigMac en USD Enero 2019&quot;}, &quot;tooltip&quot;: {&quot;xDateFormat&quot;: &quot;%m/%Y&quot;}, &quot;xAxis&quot;: {&quot;categories&quot;: [&quot;Russia&quot;, &quot;Malaysia&quot;, &quot;South Africa&quot;, &quot;Romania&quot;, &quot;Ukraine&quot;, &quot;Indonesia&quot;, &quot;Taiwan&quot;, &quot;Azerbaijan&quot;, &quot;Moldova&quot;, &quot;Turkey&quot;, &quot;Egypt&quot;, &quot;Hong Kong&quot;, &quot;Mexico&quot;, &quot;India&quot;, &quot;Slovenia&quot;, &quot;Philippines&quot;, &quot;Vietnam&quot;, &quot;Poland&quot;, &quot;Argentina&quot;, &quot;China&quot;, &quot;Pakistan&quot;, &quot;Hungary&quot;, &quot;Oman&quot;, &quot;Lithuania&quot;, &quot;Latvia&quot;, &quot;Peru&quot;, &quot;Jordan&quot;, &quot;Guatemala&quot;, &quot;Nicaragua&quot;, &quot;Croatia&quot;, &quot;Saudi Arabia&quot;, &quot;Honduras&quot;, &quot;Qatar&quot;, &quot;Estonia&quot;, &quot;Japan&quot;, &quot;Slovakia&quot;, &quot;Kuwait&quot;, &quot;Sri Lanka&quot;, &quot;Portugal&quot;, &quot;Colombia&quot;, &quot;Greece&quot;, &quot;Bahrain&quot;, &quot;Czech Republic&quot;, &quot;South Korea&quot;, &quot;Chile&quot;, &quot;Thailand&quot;, &quot;Costa Rica&quot;, &quot;United Arab Emirates&quot;, &quot;Britain&quot;, &quot;New Zealand&quot;, &quot;Australia&quot;, &quot;Singapore&quot;, &quot;Austria&quot;, &quot;Lebanon&quot;, &quot;Spain&quot;, &quot;Denmark&quot;, &quot;Netherlands&quot;, &quot;Euro area&quot;, &quot;Brazil&quot;, &quot;Germany&quot;, &quot;Uruguay&quot;, &quot;Belgium&quot;, &quot;France&quot;, &quot;Israel&quot;, &quot;Ireland&quot;, &quot;Italy&quot;, &quot;Norway&quot;, &quot;Canada&quot;, &quot;Finland&quot;, &quot;Sweden&quot;, &quot;United States&quot;, &quot;Switzerland&quot;]}, &quot;yAxis&quot;: {&quot;min&quot;: 0, &quot;title&quot;: {&quot;text&quot;: &quot;Precio (USD)&quot;}}}; var chart = new Highcharts.Chart(option); var data = [{&quot;data&quot;: [2.04, 2.14, 2.19, 2.2, 2.22, 2.26, 2.31, 2.33, 2.41, 2.44, 2.53, 2.62, 2.65, 2.67, 2.69, 2.77, 2.8, 2.84, 2.87, 3.05, 3.05, 3.1, 3.14, 3.19, 3.19, 3.19, 3.24, 3.26, 3.32, 3.33, 3.47, 3.51, 3.57, 3.59, 3.59, 3.59, 3.61, 3.64, 3.64, 3.69, 3.7, 3.71, 3.73, 3.81, 3.83, 3.86, 3.94, 4.02, 4.1, 4.23, 4.26, 4.26, 4.27, 4.31, 4.43, 4.5, 4.55, 4.57, 4.6, 4.64, 4.66, 4.71, 4.71, 4.77, 4.82, 4.82, 4.85, 5.16, 5.32, 5.38, 5.74, 6.54], &quot;type&quot;: &quot;bar&quot;, &quot;marker&quot;: {&quot;enabled&quot;: false}, &quot;name&quot;: &quot;Precio (USD)&quot;}]; var dataLen = data.length; for (var ix = 0; ix &lt; dataLen; ix++) { chart.addSeries(data[ix]); } }); &lt;/script&gt; &lt;/body&gt; &lt;/html&gt;\" height=1500 width=820></iframe>"
      ],
      "text/plain": [
       "<highcharts.highcharts.highcharts.Highchart at 0x7fc64abc9550>"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chart = Highchart()\n",
    "\n",
    "chart.set_options('chart', {'inverted': True, 'height': 1500})\n",
    "\n",
    "options = {\n",
    "    'title': {\n",
    "        'text': 'Precio BigMac en USD Enero 2019'\n",
    "    },\n",
    "    'xAxis': {\n",
    "        'categories': countries\n",
    "    },\n",
    "    'plotOptions': {\n",
    "        'series': {\n",
    "            'pointWidth': 5\n",
    "        } \n",
    "    },\n",
    "    'yAxis': {\n",
    "        'min': 0,\n",
    "        'title': {\n",
    "            'text': 'Precio (USD)'\n",
    "        }\n",
    "    },\n",
    "    'legend': {\n",
    "        'enabled': False\n",
    "    },\n",
    "    'tooltip': {\n",
    "        'xDateFormat': '%m/%Y'\n",
    "    }\n",
    "}\n",
    "\n",
    "chart.set_dict_options(options)\n",
    "\n",
    "chart.add_data_set(countries_price, 'bar', 'Precio (USD)', marker={'enabled': False}) \n",
    "\n",
    "chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_price_usa = list()\n",
    "for info in countries_info:\n",
    "    countries_price_usa.append(info.usd_price-5.74)\n",
    "#countries_price_usa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe style=\"border:0;outline:none;overflow:hidden\" srcdoc=\"&lt;!DOCTYPE html&gt; &lt;html lang=&quot;en&quot;&gt; &lt;head&gt; &lt;meta charset=&quot;utf-8&quot; /&gt; &lt;link href=&quot;https://www.highcharts.com/highslide/highslide.css&quot; rel=&quot;stylesheet&quot; /&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/highcharts.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/highcharts-more.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/modules/heatmap.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/modules/exporting.js&quot;&gt;&lt;/script&gt; &lt;/head&gt; &lt;body style=&quot;margin:0;padding:0&quot;&gt; &lt;div id=&quot;container&quot; style=&quot;height:1500px;&quot;&gt;Loading....&lt;/div&gt; &lt;script&gt; $(function(){ Highcharts.setOptions({&quot;global&quot;: {}, &quot;lang&quot;: {}}); var option = {&quot;chart&quot;: {&quot;renderTo&quot;: &quot;container&quot;, &quot;inverted&quot;: true, &quot;height&quot;: 1500}, &quot;colors&quot;: {}, &quot;credits&quot;: {&quot;enabled&quot;: false}, &quot;drilldown&quot;: {}, &quot;exporting&quot;: {}, &quot;labels&quot;: {}, &quot;legend&quot;: {&quot;enabled&quot;: false}, &quot;loading&quot;: {}, &quot;navigation&quot;: {}, &quot;pane&quot;: {}, &quot;plotOptions&quot;: {&quot;series&quot;: {}}, &quot;series&quot;: {}, &quot;subtitle&quot;: {}, &quot;title&quot;: {&quot;text&quot;: &quot;Precio BigMac en USD con USA como referencia (Enero 2019)&quot;}, &quot;tooltip&quot;: {&quot;xDateFormat&quot;: &quot;%m/%Y&quot;}, &quot;xAxis&quot;: {&quot;categories&quot;: [&quot;Russia&quot;, &quot;Malaysia&quot;, &quot;South Africa&quot;, &quot;Romania&quot;, &quot;Ukraine&quot;, &quot;Indonesia&quot;, &quot;Taiwan&quot;, &quot;Azerbaijan&quot;, &quot;Moldova&quot;, &quot;Turkey&quot;, &quot;Egypt&quot;, &quot;Hong Kong&quot;, &quot;Mexico&quot;, &quot;India&quot;, &quot;Slovenia&quot;, &quot;Philippines&quot;, &quot;Vietnam&quot;, &quot;Poland&quot;, &quot;Argentina&quot;, &quot;China&quot;, &quot;Pakistan&quot;, &quot;Hungary&quot;, &quot;Oman&quot;, &quot;Lithuania&quot;, &quot;Latvia&quot;, &quot;Peru&quot;, &quot;Jordan&quot;, &quot;Guatemala&quot;, &quot;Nicaragua&quot;, &quot;Croatia&quot;, &quot;Saudi Arabia&quot;, &quot;Honduras&quot;, &quot;Qatar&quot;, &quot;Estonia&quot;, &quot;Japan&quot;, &quot;Slovakia&quot;, &quot;Kuwait&quot;, &quot;Sri Lanka&quot;, &quot;Portugal&quot;, &quot;Colombia&quot;, &quot;Greece&quot;, &quot;Bahrain&quot;, &quot;Czech Republic&quot;, &quot;South Korea&quot;, &quot;Chile&quot;, &quot;Thailand&quot;, &quot;Costa Rica&quot;, &quot;United Arab Emirates&quot;, &quot;Britain&quot;, &quot;New Zealand&quot;, &quot;Australia&quot;, &quot;Singapore&quot;, &quot;Austria&quot;, &quot;Lebanon&quot;, &quot;Spain&quot;, &quot;Denmark&quot;, &quot;Netherlands&quot;, &quot;Euro area&quot;, &quot;Brazil&quot;, &quot;Germany&quot;, &quot;Uruguay&quot;, &quot;Belgium&quot;, &quot;France&quot;, &quot;Israel&quot;, &quot;Ireland&quot;, &quot;Italy&quot;, &quot;Norway&quot;, &quot;Canada&quot;, &quot;Finland&quot;, &quot;Sweden&quot;, &quot;United States&quot;, &quot;Switzerland&quot;]}, &quot;yAxis&quot;: {&quot;title&quot;: {&quot;text&quot;: &quot;Precio (USD)&quot;}}}; var chart = new Highcharts.Chart(option); var data = [{&quot;data&quot;: [-3.7, -3.6, -3.5500000000000003, -3.54, -3.52, -3.4800000000000004, -3.43, -3.41, -3.33, -3.3000000000000003, -3.2100000000000004, -3.12, -3.0900000000000003, -3.0700000000000003, -3.0500000000000003, -2.97, -2.9400000000000004, -2.9000000000000004, -2.87, -2.6900000000000004, -2.6900000000000004, -2.64, -2.6, -2.5500000000000003, -2.5500000000000003, -2.5500000000000003, -2.5, -2.4800000000000004, -2.4200000000000004, -2.41, -2.27, -2.2300000000000004, -2.1700000000000004, -2.1500000000000004, -2.1500000000000004, -2.1500000000000004, -2.1300000000000003, -2.1, -2.1, -2.0500000000000003, -2.04, -2.0300000000000002, -2.0100000000000002, -1.9300000000000002, -1.9100000000000001, -1.8800000000000003, -1.8000000000000003, -1.7200000000000006, -1.6400000000000006, -1.5099999999999998, -1.4800000000000004, -1.4800000000000004, -1.4700000000000006, -1.4300000000000006, -1.3100000000000005, -1.2400000000000002, -1.1900000000000004, -1.17, -1.1400000000000006, -1.1000000000000005, -1.08, -1.0300000000000002, -1.0300000000000002, -0.9700000000000006, -0.9199999999999999, -0.9199999999999999, -0.8900000000000006, -0.5800000000000001, -0.41999999999999993, -0.3600000000000003, 0.0, 0.7999999999999998], &quot;type&quot;: &quot;bar&quot;, &quot;marker&quot;: {&quot;enabled&quot;: false}, &quot;name&quot;: &quot;Precio (USD)&quot;}]; var dataLen = data.length; for (var ix = 0; ix &lt; dataLen; ix++) { chart.addSeries(data[ix]); } }); &lt;/script&gt; &lt;/body&gt; &lt;/html&gt;\" height=1500 width=820></iframe>"
      ],
      "text/plain": [
       "<highcharts.highcharts.highcharts.Highchart at 0x7fc664156cf8>"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chart = Highchart()\n",
    "\n",
    "chart.set_options('chart', {'inverted': True, 'height': 1500})\n",
    "\n",
    "options = {\n",
    "    'title': {\n",
    "        'text': 'Precio BigMac en USD con USA como referencia (Enero 2019)'\n",
    "    },\n",
    "    'xAxis': {\n",
    "        'categories': countries\n",
    "    },\n",
    "    'plotOptions': {\n",
    "        'series': {\n",
    "            'pointWidth': 5\n",
    "        } \n",
    "    },\n",
    "    'yAxis': {\n",
    "        'title': {\n",
    "            'text': 'Precio (USD)'\n",
    "        }\n",
    "    },\n",
    "    'legend': {\n",
    "        'enabled': False\n",
    "    },\n",
    "    'tooltip': {\n",
    "        'xDateFormat': '%m/%Y'\n",
    "    }\n",
    "}\n",
    "\n",
    "chart.set_dict_options(options)\n",
    "\n",
    "chart.add_data_set(countries_price_usa, 'bar', 'Precio (USD)', marker={'enabled': False}) \n",
    "\n",
    "chart"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Evolución del precio del BigMac en USA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_us = df.filter('name = \"United States\"')\\\n",
    "            .drop('name', 'iso_a3', 'currency_code', 'GDP_dollar')\\\n",
    "            .rdd\\\n",
    "            .map(lambda x: (time.mktime(x.date.timetuple()), x.local_price))\\\n",
    "            .collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_us_list = list()\n",
    "for data in data_us:\n",
    "    data_list = list(data)\n",
    "    item = list()\n",
    "    item.append(data_list[0]*1000)\n",
    "    item.append(float(data_list[1]))\n",
    "    data_us_list.append(item)\n",
    "#data_us_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe style=\"border:0;outline:none;overflow:hidden\" srcdoc=\"&lt;!DOCTYPE html&gt; &lt;html lang=&quot;en&quot;&gt; &lt;head&gt; &lt;meta charset=&quot;utf-8&quot; /&gt; &lt;link href=&quot;https://www.highcharts.com/highslide/highslide.css&quot; rel=&quot;stylesheet&quot; /&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/highcharts.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/highcharts-more.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/modules/heatmap.js&quot;&gt;&lt;/script&gt; &lt;script type=&quot;text/javascript&quot; src=&quot;https://code.highcharts.com/6/modules/exporting.js&quot;&gt;&lt;/script&gt; &lt;/head&gt; &lt;body style=&quot;margin:0;padding:0&quot;&gt; &lt;div id=&quot;container&quot; style=&quot;&quot;&gt;Loading....&lt;/div&gt; &lt;script&gt; $(function(){ Highcharts.setOptions({&quot;global&quot;: {}, &quot;lang&quot;: {}}); var option = {&quot;chart&quot;: {&quot;renderTo&quot;: &quot;container&quot;}, &quot;colors&quot;: {}, &quot;credits&quot;: {&quot;enabled&quot;: false}, &quot;drilldown&quot;: {}, &quot;exporting&quot;: {}, &quot;labels&quot;: {}, &quot;legend&quot;: {&quot;enabled&quot;: false}, &quot;loading&quot;: {}, &quot;navigation&quot;: {}, &quot;pane&quot;: {}, &quot;plotOptions&quot;: {}, &quot;series&quot;: {}, &quot;subtitle&quot;: {}, &quot;title&quot;: {&quot;text&quot;: &quot;Precio BigMac USA&quot;}, &quot;tooltip&quot;: {&quot;xDateFormat&quot;: &quot;%m/%Y&quot;}, &quot;xAxis&quot;: {&quot;type&quot;: &quot;datetime&quot;}, &quot;yAxis&quot;: {&quot;title&quot;: {&quot;text&quot;: &quot;Precio (USD)&quot;}}}; var chart = new Highcharts.Chart(option); var data = [{&quot;data&quot;: [[954558000000.0, 2.51], [986094000000.0, 2.54], [1017630000000.0, 2.49], [1049166000000.0, 2.71], [1083380400000.0, 2.9], [1117594800000.0, 3.06], [1136084400000.0, 3.15], [1146452400000.0, 3.1], [1167620400000.0, 3.22], [1180666800000.0, 3.41], [1212289200000.0, 3.57], [1246417200000.0, 3.57], [1262314800000.0, 3.58], [1277953200000.0, 3.733333333], [1309489200000.0, 4.065], [1325386800000.0, 4.19722], [1341111600000.0, 4.3275], [1357009200000.0, 4.367395833], [1372647600000.0, 4.556666667], [1388545200000.0, 4.624166667], [1404183600000.0, 4.795], [1420081200000.0, 4.79], [1435719600000.0, 4.79], [1451617200000.0, 4.93], [1467342000000.0, 5.04], [1483239600000.0, 5.06], [1498878000000.0, 5.3], [1514775600000.0, 5.28], [1530414000000.0, 5.51], [1546311600000.0, 5.58], [1562641200000.0, 5.74]], &quot;type&quot;: &quot;area&quot;, &quot;marker&quot;: {&quot;enabled&quot;: false}, &quot;name&quot;: &quot;Precio (USD)&quot;}]; var dataLen = data.length; for (var ix = 0; ix &lt; dataLen; ix++) { chart.addSeries(data[ix]); } }); &lt;/script&gt; &lt;/body&gt; &lt;/html&gt;\" height=520 width=820></iframe>"
      ],
      "text/plain": [
       "<highcharts.highcharts.highcharts.Highchart at 0x7fc64abc8208>"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chart = Highchart()\n",
    "\n",
    "chart.set_options('chart', {})\n",
    "\n",
    "options = {\n",
    "    'title': {\n",
    "        'text': 'Precio BigMac USA'\n",
    "    },\n",
    "    'xAxis': {\n",
    "        'type': 'datetime'\n",
    "    },\n",
    "    'yAxis': {\n",
    "        'title': {\n",
    "            'text': 'Precio (USD)'\n",
    "        }\n",
    "    },\n",
    "    'legend': {\n",
    "        'enabled': False\n",
    "    },\n",
    "    'tooltip': {\n",
    "        'xDateFormat': '%m/%Y'\n",
    "    }\n",
    "}\n",
    "\n",
    "chart.set_dict_options(options)\n",
    "\n",
    "chart.add_data_set(data_us_list, 'area', 'Precio (USD)', marker={'enabled': False}) \n",
    "\n",
    "chart"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Eliminar duplicados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "74"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropDuplicates(['name']).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Eliminar nulos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1514"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "901"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropna().count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Parameters\n",
    "how – ‘any’ or ‘all’. If ‘any’, drop a row if it contains any nulls. If ‘all’, drop a row only if all its values are null.\n",
    "\n",
    "thresh – int, default None If specified, drop rows that have less than thresh non-null values. This overwrites the how parameter.\n",
    "\n",
    "subset – optional list of column names to consider."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "901"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropna(thresh=7).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1514"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropna(subset=['name']).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Completar nulos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+\n",
      "|          name|iso_a3|currency_code|local_price|  dollar_ex|GDP_dollar|               date|\n",
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+\n",
      "|     Argentina|   ARG|          ARS|        2.5|        1.0|      null|2000-04-01 00:00:00|\n",
      "|     Australia|   AUS|          AUD|       2.59|       1.68|      null|2000-04-01 00:00:00|\n",
      "|        Brazil|   BRA|          BRL|       2.95|       1.79|      null|2000-04-01 00:00:00|\n",
      "|       Britain|   GBR|          GBP|        1.9|0.632911392|      null|2000-04-01 00:00:00|\n",
      "|        Canada|   CAN|          CAD|       2.85|       1.47|      null|2000-04-01 00:00:00|\n",
      "|         Chile|   CHL|          CLP|     1260.0|      514.0|      null|2000-04-01 00:00:00|\n",
      "|         China|   CHN|          CNY|        9.9|       8.28|      null|2000-04-01 00:00:00|\n",
      "|Czech Republic|   CZE|          CZK|      54.37|       39.1|      null|2000-04-01 00:00:00|\n",
      "|       Denmark|   DNK|          DKK|      24.75|       8.04|      null|2000-04-01 00:00:00|\n",
      "|     Euro area|   EUZ|          EUR|       2.56|1.075268817|      null|2000-04-01 00:00:00|\n",
      "|     Hong Kong|   HKG|          HKD|       10.2|       7.79|      null|2000-04-01 00:00:00|\n",
      "|       Hungary|   HUN|          HUF|      339.0|      279.0|      null|2000-04-01 00:00:00|\n",
      "|     Indonesia|   IDN|          IDR|    14500.0|     7945.0|      null|2000-04-01 00:00:00|\n",
      "|        Israel|   ISR|          ILS|       14.5|       4.05|      null|2000-04-01 00:00:00|\n",
      "|         Japan|   JPN|          JPY|      294.0|      106.0|      null|2000-04-01 00:00:00|\n",
      "|      Malaysia|   MYS|          MYR|       4.52|        3.8|      null|2000-04-01 00:00:00|\n",
      "|        Mexico|   MEX|          MXN|       20.9|       9.41|      null|2000-04-01 00:00:00|\n",
      "|   New Zealand|   NZL|          NZD|        3.4|       2.01|      null|2000-04-01 00:00:00|\n",
      "|        Poland|   POL|          PLN|        5.5|        4.3|      null|2000-04-01 00:00:00|\n",
      "|        Russia|   RUS|          RUB|       39.5|       28.5|      null|2000-04-01 00:00:00|\n",
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+\n",
      "|          name|iso_a3|currency_code|local_price|  dollar_ex|GDP_dollar|               date|\n",
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+\n",
      "|     Argentina|   ARG|          ARS|        2.5|        1.0|       0.0|2000-04-01 00:00:00|\n",
      "|     Australia|   AUS|          AUD|       2.59|       1.68|       0.0|2000-04-01 00:00:00|\n",
      "|        Brazil|   BRA|          BRL|       2.95|       1.79|       0.0|2000-04-01 00:00:00|\n",
      "|       Britain|   GBR|          GBP|        1.9|0.632911392|       0.0|2000-04-01 00:00:00|\n",
      "|        Canada|   CAN|          CAD|       2.85|       1.47|       0.0|2000-04-01 00:00:00|\n",
      "|         Chile|   CHL|          CLP|     1260.0|      514.0|       0.0|2000-04-01 00:00:00|\n",
      "|         China|   CHN|          CNY|        9.9|       8.28|       0.0|2000-04-01 00:00:00|\n",
      "|Czech Republic|   CZE|          CZK|      54.37|       39.1|       0.0|2000-04-01 00:00:00|\n",
      "|       Denmark|   DNK|          DKK|      24.75|       8.04|       0.0|2000-04-01 00:00:00|\n",
      "|     Euro area|   EUZ|          EUR|       2.56|1.075268817|       0.0|2000-04-01 00:00:00|\n",
      "|     Hong Kong|   HKG|          HKD|       10.2|       7.79|       0.0|2000-04-01 00:00:00|\n",
      "|       Hungary|   HUN|          HUF|      339.0|      279.0|       0.0|2000-04-01 00:00:00|\n",
      "|     Indonesia|   IDN|          IDR|    14500.0|     7945.0|       0.0|2000-04-01 00:00:00|\n",
      "|        Israel|   ISR|          ILS|       14.5|       4.05|       0.0|2000-04-01 00:00:00|\n",
      "|         Japan|   JPN|          JPY|      294.0|      106.0|       0.0|2000-04-01 00:00:00|\n",
      "|      Malaysia|   MYS|          MYR|       4.52|        3.8|       0.0|2000-04-01 00:00:00|\n",
      "|        Mexico|   MEX|          MXN|       20.9|       9.41|       0.0|2000-04-01 00:00:00|\n",
      "|   New Zealand|   NZL|          NZD|        3.4|       2.01|       0.0|2000-04-01 00:00:00|\n",
      "|        Poland|   POL|          PLN|        5.5|        4.3|       0.0|2000-04-01 00:00:00|\n",
      "|        Russia|   RUS|          RUB|       39.5|       28.5|       0.0|2000-04-01 00:00:00|\n",
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.fillna(0).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "value:\n",
    "\n",
    "* It will take a dictionary to specify which column will replace with which value.\n",
    "* A value (int , float, string) for all columns.\n",
    " \n",
    "subset: Specify some selected columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reemplazar valores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+\n",
      "|          name|iso_a3|currency_code|local_price|  dollar_ex|GDP_dollar|               date|\n",
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+\n",
      "|            AR|   ARG|          ARS|        2.5|        1.0|      null|2000-04-01 00:00:00|\n",
      "|     Australia|   AUS|          AUD|       2.59|       1.68|      null|2000-04-01 00:00:00|\n",
      "|        Brazil|   BRA|          BRL|       2.95|       1.79|      null|2000-04-01 00:00:00|\n",
      "|       Britain|   GBR|          GBP|        1.9|0.632911392|      null|2000-04-01 00:00:00|\n",
      "|        Canada|   CAN|          CAD|       2.85|       1.47|      null|2000-04-01 00:00:00|\n",
      "|         Chile|   CHL|          CLP|     1260.0|      514.0|      null|2000-04-01 00:00:00|\n",
      "|         China|   CHN|          CNY|        9.9|       8.28|      null|2000-04-01 00:00:00|\n",
      "|Czech Republic|   CZE|          CZK|      54.37|       39.1|      null|2000-04-01 00:00:00|\n",
      "|       Denmark|   DNK|          DKK|      24.75|       8.04|      null|2000-04-01 00:00:00|\n",
      "|     Euro area|   EUZ|          EUR|       2.56|1.075268817|      null|2000-04-01 00:00:00|\n",
      "|     Hong Kong|   HKG|          HKD|       10.2|       7.79|      null|2000-04-01 00:00:00|\n",
      "|       Hungary|   HUN|          HUF|      339.0|      279.0|      null|2000-04-01 00:00:00|\n",
      "|     Indonesia|   IDN|          IDR|    14500.0|     7945.0|      null|2000-04-01 00:00:00|\n",
      "|        Israel|   ISR|          ILS|       14.5|       4.05|      null|2000-04-01 00:00:00|\n",
      "|         Japan|   JPN|          JPY|      294.0|      106.0|      null|2000-04-01 00:00:00|\n",
      "|      Malaysia|   MYS|          MYR|       4.52|        3.8|      null|2000-04-01 00:00:00|\n",
      "|        Mexico|   MEX|          MXN|       20.9|       9.41|      null|2000-04-01 00:00:00|\n",
      "|   New Zealand|   NZL|          NZD|        3.4|       2.01|      null|2000-04-01 00:00:00|\n",
      "|        Poland|   POL|          PLN|        5.5|        4.3|      null|2000-04-01 00:00:00|\n",
      "|        Russia|   RUS|          RUB|       39.5|       28.5|      null|2000-04-01 00:00:00|\n",
      "+--------------+------+-------------+-----------+-----------+----------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.replace({'Argentina': 'AR'}, subset=['name']).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Parameters\n",
    "to_replace – bool, int, long, float, string, list or dict. Value to be replaced. If the value is a dict, then value is ignored or can be omitted, and to_replace must be a mapping between a value and a replacement.\n",
    "\n",
    "value – bool, int, long, float, string, list or None. The replacement value must be a bool, int, long, float, string or None. If value is a list, value should be of the same length and type as to_replace. If value is a scalar and to_replace is a sequence, then value is used as a replacement for each item in to_replace.\n",
    "\n",
    "subset – optional list of column names to consider. Columns specified in subset that do not have matching data type are ignored. For example, if value is a string, and subset contains a non-string column, then the non-string column is simply ignored."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Columna a partir de expresión SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|         usd_price|\n",
      "+------------------+\n",
      "|               2.5|\n",
      "|4.3511999999999995|\n",
      "| 5.280500000000001|\n",
      "|      1.2025316448|\n",
      "|            4.1895|\n",
      "|          647640.0|\n",
      "|            81.972|\n",
      "|          2125.867|\n",
      "|198.98999999999998|\n",
      "|     2.75268817152|\n",
      "|            79.458|\n",
      "|           94581.0|\n",
      "|        1.152025E8|\n",
      "|58.724999999999994|\n",
      "|           31164.0|\n",
      "|            17.176|\n",
      "|196.66899999999998|\n",
      "| 6.833999999999999|\n",
      "|             23.65|\n",
      "|           1125.75|\n",
      "+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.selectExpr(\"local_price * dollar_ex as usd_price\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Renombrar columna"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+------+-------------+-------+-----------+----------+-------------------+\n",
      "|          name|iso_a3|currency_code|  price|  dollar_ex|GDP_dollar|               date|\n",
      "+--------------+------+-------------+-------+-----------+----------+-------------------+\n",
      "|     Argentina|   ARG|          ARS|    2.5|        1.0|      null|2000-04-01 00:00:00|\n",
      "|     Australia|   AUS|          AUD|   2.59|       1.68|      null|2000-04-01 00:00:00|\n",
      "|        Brazil|   BRA|          BRL|   2.95|       1.79|      null|2000-04-01 00:00:00|\n",
      "|       Britain|   GBR|          GBP|    1.9|0.632911392|      null|2000-04-01 00:00:00|\n",
      "|        Canada|   CAN|          CAD|   2.85|       1.47|      null|2000-04-01 00:00:00|\n",
      "|         Chile|   CHL|          CLP| 1260.0|      514.0|      null|2000-04-01 00:00:00|\n",
      "|         China|   CHN|          CNY|    9.9|       8.28|      null|2000-04-01 00:00:00|\n",
      "|Czech Republic|   CZE|          CZK|  54.37|       39.1|      null|2000-04-01 00:00:00|\n",
      "|       Denmark|   DNK|          DKK|  24.75|       8.04|      null|2000-04-01 00:00:00|\n",
      "|     Euro area|   EUZ|          EUR|   2.56|1.075268817|      null|2000-04-01 00:00:00|\n",
      "|     Hong Kong|   HKG|          HKD|   10.2|       7.79|      null|2000-04-01 00:00:00|\n",
      "|       Hungary|   HUN|          HUF|  339.0|      279.0|      null|2000-04-01 00:00:00|\n",
      "|     Indonesia|   IDN|          IDR|14500.0|     7945.0|      null|2000-04-01 00:00:00|\n",
      "|        Israel|   ISR|          ILS|   14.5|       4.05|      null|2000-04-01 00:00:00|\n",
      "|         Japan|   JPN|          JPY|  294.0|      106.0|      null|2000-04-01 00:00:00|\n",
      "|      Malaysia|   MYS|          MYR|   4.52|        3.8|      null|2000-04-01 00:00:00|\n",
      "|        Mexico|   MEX|          MXN|   20.9|       9.41|      null|2000-04-01 00:00:00|\n",
      "|   New Zealand|   NZL|          NZD|    3.4|       2.01|      null|2000-04-01 00:00:00|\n",
      "|        Poland|   POL|          PLN|    5.5|        4.3|      null|2000-04-01 00:00:00|\n",
      "|        Russia|   RUS|          RUB|   39.5|       28.5|      null|2000-04-01 00:00:00|\n",
      "+--------------+------+-------------+-------+-----------+----------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.withColumnRenamed('local_price','price').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivoteando"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+\n",
      "|       name|2000-04-01 00:00:00|2001-04-01 00:00:00|2002-04-01 00:00:00|2003-04-01 00:00:00|2004-05-01 00:00:00|2005-06-01 00:00:00|2006-01-01 00:00:00|2006-05-01 00:00:00|2007-01-01 00:00:00|2007-06-01 00:00:00|2008-06-01 00:00:00|2009-07-01 00:00:00|2010-01-01 00:00:00|2010-07-01 00:00:00|2011-07-01 00:00:00|2012-01-01 00:00:00|2012-07-01 00:00:00|2013-01-01 00:00:00|2013-07-01 00:00:00|2014-01-01 00:00:00|2014-07-01 00:00:00|2015-01-01 00:00:00|2015-07-01 00:00:00|2016-01-01 00:00:00|2016-07-01 00:00:00|2017-01-01 00:00:00|2017-07-01 00:00:00|2018-01-01 00:00:00|2018-07-01 00:00:00|2019-01-01 00:00:00|2019-07-09 00:00:00|\n",
      "+-----------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+\n",
      "|     Russia|               39.5|               35.0|               39.0|               41.0|               42.0|               42.0|               46.0|               48.0|               49.0|               52.0|               59.0|               67.0|               70.0|               71.0|               75.0|               81.0|               75.0|              72.88|               87.0|               89.0|               89.0|               89.0|              107.0|              114.0|              130.0|              130.0|              137.0|              130.0|              130.0|             110.17|              130.0|\n",
      "|     Sweden|               24.0|               24.0|               26.0|               30.0|               30.0|               31.0|               33.0|               33.0|               32.0|               33.0|               38.0|               39.0|               39.0|               48.4|               48.4|               41.0|        39.97301987|        40.55930138|              41.61|               40.7|               40.7|               40.7|               43.7|               45.0|              44.94|               48.0|              48.97|               49.1|               51.0|               52.0|               51.0|\n",
      "|Philippines|               null|               59.0|               65.0|               65.0|               69.0|               80.0|               82.0|               85.0|               85.0|               85.0|               87.0|              99.39|             101.52|              102.0|              118.0|              118.0|              118.0|              118.0|             115.21|              135.0|              160.0|              163.0|              163.0|              131.0|              133.0|              133.0|              134.0|              134.0|              140.0|              140.0|              142.0|\n",
      "|   Malaysia|               4.52|               4.52|               5.04|               5.04|               5.04|               5.25|                5.5|                5.5|                5.5|                5.5|                5.5|               6.77|               7.05|               7.05|                7.2|               7.35|                7.4|               7.95|                7.3|                7.4|               7.63|               7.63|               7.65|                8.0|                8.0|                8.0|                8.6|                9.0|               8.45|               9.05|               8.85|\n",
      "|  Singapore|                3.2|                3.3|                3.3|                3.3|                3.3|                3.6|                3.6|                3.6|                3.6|               3.95|               3.95|               4.22|               4.45|               4.23|               4.41|               4.85|                4.4|                4.5|                4.7|                4.6|                4.7|                4.7|                4.7|                4.7|               5.45|                5.6|                5.6|                5.8|                5.8|                5.8|                5.8|\n",
      "|    Britain|                1.9|               1.99|               1.99|               1.99|               1.88|               1.88|               1.88|               1.94|               1.94|               1.99|               2.29|               2.29|               2.29|               2.29|               2.39|               2.49|               2.69|               2.69|               2.69|               2.79|               2.89|               2.89|               2.89|               2.89|               2.99|               3.09|               3.19|               3.19|               3.19|               3.19|               3.29|\n",
      "|     Turkey|               null|               null|          4000000.0|          3750000.0|          3950000.0|                4.0|                4.1|                4.2|               4.55|               4.75|               5.15|               5.65|               5.65|               5.95|                6.5|                6.6|               8.25|               8.45|                8.5|                8.5|               9.25|               9.25|              10.25|              10.25|              10.75|              10.75|              10.75|              10.75|              10.75|              10.75|              13.99|\n",
      "|    Germany|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|                3.4|               3.53|               3.64|               3.64|               3.64|               3.67|               3.67|               3.67|               3.59|               3.59|               3.79|               3.79|                3.9|                3.9|               4.12|               4.12|               4.14|\n",
      "|     Jordan|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               1.95|               1.95|                2.3|\n",
      "|     France|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|                3.5|                3.6|                3.6|                3.6|                3.9|                3.8|                3.9|                3.9|                4.1|                4.1|                4.1|                4.1|                4.1|                4.2|                4.2|                4.2|                4.2|\n",
      "|     Greece|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               3.26|                3.3|              2.683|                3.3|                2.6|               3.05|               3.05|               3.05|               3.05|               3.35|               3.35|               3.35|               3.35|               3.35|               3.35|               3.35|                3.3|\n",
      "|  Sri Lanka|               null|               null|               null|               null|              140.0|              175.0|               null|              190.0|              190.0|              210.0|              210.0|              210.0|              210.0|              210.0|              280.0|              290.0|              290.0|              350.0|              370.0|              350.0|              350.0|              350.0|              350.0|              350.0|              530.0|              550.0|              580.0|              580.0|              580.0|              580.0|              640.0|\n",
      "|     Taiwan|               70.0|               70.0|               70.0|               70.0|               75.0|               75.0|               75.0|               75.0|               75.0|               75.0|               75.0|               75.0|               75.0|               75.0|               75.0|               75.0|               75.0|               75.0|               79.0|               79.0|               79.0|               79.0|               79.0|               69.0|               69.0|               69.0|               69.0|               69.0|               69.0|               69.0|               72.0|\n",
      "|   Slovakia|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|                3.0|                3.2|                3.2|\n",
      "|  Argentina|                2.5|                2.5|                2.5|                4.1|               4.36|               4.75|               4.75|                7.0|               8.25|               8.25|               11.0|               11.5|                7.0|               14.0|               20.0|               20.0|               19.0|               19.0|               21.0|               21.0|               21.0|               28.0|               28.0|               33.0|               50.0|               55.0|               70.0|               75.0|               75.0|              100.0|              120.0|\n",
      "|    Belgium|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               3.75|                3.7|                3.8|                3.8|                3.7|               3.95|                3.7|                3.7|                3.7|               3.95|               3.95|               3.95|               4.05|                4.1|                4.1|                4.2|                4.2|\n",
      "|      Qatar|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               12.0|               12.0|               13.0|\n",
      "|    Finland|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               3.75|               3.75|               3.75|               3.75|                4.1|                4.1|                4.1|                4.1|                4.1|                4.1|                4.6|               4.55|               4.56|               4.56|               4.62|               4.62|               4.75|\n",
      "|  Nicaragua|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|               null|              103.0|              103.0|              110.0|\n",
      "|       Peru|               null|               null|                8.5|                7.9|               8.99|               8.99|                9.5|                9.5|                9.5|                9.5|                9.5|              8.056|              8.056|               10.0|               10.0|               10.0|               null|               10.0|               10.0|               10.0|               10.0|               10.0|               10.0|               10.0|               10.0|               11.0|               10.5|               10.5|               10.5|               10.5|               10.5|\n",
      "+-----------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupBy(\"name\").pivot(\"date\").min('local_price').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Obtener Panda DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "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>name</th>\n",
       "      <th>iso_a3</th>\n",
       "      <th>currency_code</th>\n",
       "      <th>local_price</th>\n",
       "      <th>dollar_ex</th>\n",
       "      <th>GDP_dollar</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>ARG</td>\n",
       "      <td>ARS</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Australia</td>\n",
       "      <td>AUS</td>\n",
       "      <td>AUD</td>\n",
       "      <td>2.59</td>\n",
       "      <td>1.680000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>BRA</td>\n",
       "      <td>BRL</td>\n",
       "      <td>2.95</td>\n",
       "      <td>1.790000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Britain</td>\n",
       "      <td>GBR</td>\n",
       "      <td>GBP</td>\n",
       "      <td>1.90</td>\n",
       "      <td>0.632911</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Canada</td>\n",
       "      <td>CAN</td>\n",
       "      <td>CAD</td>\n",
       "      <td>2.85</td>\n",
       "      <td>1.470000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Chile</td>\n",
       "      <td>CHL</td>\n",
       "      <td>CLP</td>\n",
       "      <td>1260.00</td>\n",
       "      <td>514.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>China</td>\n",
       "      <td>CHN</td>\n",
       "      <td>CNY</td>\n",
       "      <td>9.90</td>\n",
       "      <td>8.280000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Czech Republic</td>\n",
       "      <td>CZE</td>\n",
       "      <td>CZK</td>\n",
       "      <td>54.37</td>\n",
       "      <td>39.100000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Denmark</td>\n",
       "      <td>DNK</td>\n",
       "      <td>DKK</td>\n",
       "      <td>24.75</td>\n",
       "      <td>8.040000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Euro area</td>\n",
       "      <td>EUZ</td>\n",
       "      <td>EUR</td>\n",
       "      <td>2.56</td>\n",
       "      <td>1.075269</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>HKG</td>\n",
       "      <td>HKD</td>\n",
       "      <td>10.20</td>\n",
       "      <td>7.790000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Hungary</td>\n",
       "      <td>HUN</td>\n",
       "      <td>HUF</td>\n",
       "      <td>339.00</td>\n",
       "      <td>279.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Indonesia</td>\n",
       "      <td>IDN</td>\n",
       "      <td>IDR</td>\n",
       "      <td>14500.00</td>\n",
       "      <td>7945.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Israel</td>\n",
       "      <td>ISR</td>\n",
       "      <td>ILS</td>\n",
       "      <td>14.50</td>\n",
       "      <td>4.050000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Japan</td>\n",
       "      <td>JPN</td>\n",
       "      <td>JPY</td>\n",
       "      <td>294.00</td>\n",
       "      <td>106.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Malaysia</td>\n",
       "      <td>MYS</td>\n",
       "      <td>MYR</td>\n",
       "      <td>4.52</td>\n",
       "      <td>3.800000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Mexico</td>\n",
       "      <td>MEX</td>\n",
       "      <td>MXN</td>\n",
       "      <td>20.90</td>\n",
       "      <td>9.410000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>New Zealand</td>\n",
       "      <td>NZL</td>\n",
       "      <td>NZD</td>\n",
       "      <td>3.40</td>\n",
       "      <td>2.010000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Poland</td>\n",
       "      <td>POL</td>\n",
       "      <td>PLN</td>\n",
       "      <td>5.50</td>\n",
       "      <td>4.300000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Russia</td>\n",
       "      <td>RUS</td>\n",
       "      <td>RUB</td>\n",
       "      <td>39.50</td>\n",
       "      <td>28.500000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Singapore</td>\n",
       "      <td>SGP</td>\n",
       "      <td>SGD</td>\n",
       "      <td>3.20</td>\n",
       "      <td>1.700000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>South Africa</td>\n",
       "      <td>ZAF</td>\n",
       "      <td>ZAR</td>\n",
       "      <td>9.00</td>\n",
       "      <td>6.720000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>South Korea</td>\n",
       "      <td>KOR</td>\n",
       "      <td>KRW</td>\n",
       "      <td>3000.00</td>\n",
       "      <td>1108.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Sweden</td>\n",
       "      <td>SWE</td>\n",
       "      <td>SEK</td>\n",
       "      <td>24.00</td>\n",
       "      <td>8.840000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>CHE</td>\n",
       "      <td>CHF</td>\n",
       "      <td>5.90</td>\n",
       "      <td>1.700000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Taiwan</td>\n",
       "      <td>TWN</td>\n",
       "      <td>TWD</td>\n",
       "      <td>70.00</td>\n",
       "      <td>30.600000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Thailand</td>\n",
       "      <td>THA</td>\n",
       "      <td>THB</td>\n",
       "      <td>55.00</td>\n",
       "      <td>38.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>USD</td>\n",
       "      <td>2.51</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>ARG</td>\n",
       "      <td>ARS</td>\n",
       "      <td>2.50</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2001-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Australia</td>\n",
       "      <td>AUS</td>\n",
       "      <td>AUD</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1.980000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2001-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1484</th>\n",
       "      <td>Latvia</td>\n",
       "      <td>LVA</td>\n",
       "      <td>EUR</td>\n",
       "      <td>2.85</td>\n",
       "      <td>0.892339</td>\n",
       "      <td>15652.61</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1485</th>\n",
       "      <td>Moldova</td>\n",
       "      <td>MDA</td>\n",
       "      <td>MDL</td>\n",
       "      <td>43.00</td>\n",
       "      <td>17.814200</td>\n",
       "      <td>2726.54</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1486</th>\n",
       "      <td>Mexico</td>\n",
       "      <td>MEX</td>\n",
       "      <td>MXN</td>\n",
       "      <td>50.00</td>\n",
       "      <td>18.895250</td>\n",
       "      <td>9376.99</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1487</th>\n",
       "      <td>Malaysia</td>\n",
       "      <td>MYS</td>\n",
       "      <td>MYR</td>\n",
       "      <td>8.85</td>\n",
       "      <td>4.142500</td>\n",
       "      <td>9827.67</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1488</th>\n",
       "      <td>Nicaragua</td>\n",
       "      <td>NIC</td>\n",
       "      <td>NIO</td>\n",
       "      <td>110.00</td>\n",
       "      <td>33.162100</td>\n",
       "      <td>2221.80</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1489</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>NLD</td>\n",
       "      <td>EUR</td>\n",
       "      <td>4.06</td>\n",
       "      <td>0.892339</td>\n",
       "      <td>48555.35</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1490</th>\n",
       "      <td>Norway</td>\n",
       "      <td>NOR</td>\n",
       "      <td>NOK</td>\n",
       "      <td>42.00</td>\n",
       "      <td>8.652750</td>\n",
       "      <td>75513.64</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491</th>\n",
       "      <td>New Zealand</td>\n",
       "      <td>NZL</td>\n",
       "      <td>NZD</td>\n",
       "      <td>6.40</td>\n",
       "      <td>1.513432</td>\n",
       "      <td>41350.26</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1492</th>\n",
       "      <td>Oman</td>\n",
       "      <td>OMN</td>\n",
       "      <td>OMR</td>\n",
       "      <td>1.21</td>\n",
       "      <td>0.385000</td>\n",
       "      <td>17128.37</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1493</th>\n",
       "      <td>Pakistan</td>\n",
       "      <td>PAK</td>\n",
       "      <td>PKR</td>\n",
       "      <td>480.00</td>\n",
       "      <td>157.450000</td>\n",
       "      <td>1545.94</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1494</th>\n",
       "      <td>Peru</td>\n",
       "      <td>PER</td>\n",
       "      <td>PEN</td>\n",
       "      <td>10.50</td>\n",
       "      <td>3.286800</td>\n",
       "      <td>6728.09</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1495</th>\n",
       "      <td>Philippines</td>\n",
       "      <td>PHL</td>\n",
       "      <td>PHP</td>\n",
       "      <td>142.00</td>\n",
       "      <td>51.317500</td>\n",
       "      <td>2988.90</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1496</th>\n",
       "      <td>Poland</td>\n",
       "      <td>POL</td>\n",
       "      <td>PLN</td>\n",
       "      <td>10.80</td>\n",
       "      <td>3.807750</td>\n",
       "      <td>13867.61</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1497</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>PRT</td>\n",
       "      <td>EUR</td>\n",
       "      <td>3.25</td>\n",
       "      <td>0.892339</td>\n",
       "      <td>21334.13</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1498</th>\n",
       "      <td>Qatar</td>\n",
       "      <td>QAT</td>\n",
       "      <td>QAR</td>\n",
       "      <td>13.00</td>\n",
       "      <td>3.641750</td>\n",
       "      <td>62826.06</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1499</th>\n",
       "      <td>Romania</td>\n",
       "      <td>ROU</td>\n",
       "      <td>RON</td>\n",
       "      <td>9.30</td>\n",
       "      <td>4.219200</td>\n",
       "      <td>10761.71</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1500</th>\n",
       "      <td>Russia</td>\n",
       "      <td>RUS</td>\n",
       "      <td>RUB</td>\n",
       "      <td>130.00</td>\n",
       "      <td>63.838750</td>\n",
       "      <td>10961.99</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1501</th>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>SAU</td>\n",
       "      <td>SAR</td>\n",
       "      <td>13.00</td>\n",
       "      <td>3.750350</td>\n",
       "      <td>21153.21</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1502</th>\n",
       "      <td>Singapore</td>\n",
       "      <td>SGP</td>\n",
       "      <td>SGD</td>\n",
       "      <td>5.80</td>\n",
       "      <td>1.361300</td>\n",
       "      <td>59990.06</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1503</th>\n",
       "      <td>Slovakia</td>\n",
       "      <td>SVK</td>\n",
       "      <td>EUR</td>\n",
       "      <td>3.20</td>\n",
       "      <td>0.892339</td>\n",
       "      <td>17627.11</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1504</th>\n",
       "      <td>Slovenia</td>\n",
       "      <td>SVN</td>\n",
       "      <td>EUR</td>\n",
       "      <td>2.40</td>\n",
       "      <td>0.892339</td>\n",
       "      <td>23502.25</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1505</th>\n",
       "      <td>Sweden</td>\n",
       "      <td>SWE</td>\n",
       "      <td>SEK</td>\n",
       "      <td>51.00</td>\n",
       "      <td>9.475050</td>\n",
       "      <td>52924.37</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1506</th>\n",
       "      <td>Thailand</td>\n",
       "      <td>THA</td>\n",
       "      <td>THB</td>\n",
       "      <td>119.00</td>\n",
       "      <td>30.827500</td>\n",
       "      <td>6730.56</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1507</th>\n",
       "      <td>Turkey</td>\n",
       "      <td>TUR</td>\n",
       "      <td>TRY</td>\n",
       "      <td>13.99</td>\n",
       "      <td>5.722000</td>\n",
       "      <td>10537.19</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1508</th>\n",
       "      <td>Taiwan</td>\n",
       "      <td>TWN</td>\n",
       "      <td>TWD</td>\n",
       "      <td>72.00</td>\n",
       "      <td>31.172000</td>\n",
       "      <td>24389.68</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1509</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>UKR</td>\n",
       "      <td>UAH</td>\n",
       "      <td>57.00</td>\n",
       "      <td>25.647500</td>\n",
       "      <td>2655.94</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1510</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>URY</td>\n",
       "      <td>UYU</td>\n",
       "      <td>164.00</td>\n",
       "      <td>35.169500</td>\n",
       "      <td>16941.56</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1511</th>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>USD</td>\n",
       "      <td>5.74</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>59895.00</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1512</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>VNM</td>\n",
       "      <td>VND</td>\n",
       "      <td>65000.00</td>\n",
       "      <td>23231.000000</td>\n",
       "      <td>2353.36</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1513</th>\n",
       "      <td>South Africa</td>\n",
       "      <td>ZAF</td>\n",
       "      <td>ZAR</td>\n",
       "      <td>31.00</td>\n",
       "      <td>14.175000</td>\n",
       "      <td>6182.25</td>\n",
       "      <td>2019-07-09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1514 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                name iso_a3 currency_code  local_price     dollar_ex  \\\n",
       "0          Argentina    ARG           ARS         2.50      1.000000   \n",
       "1          Australia    AUS           AUD         2.59      1.680000   \n",
       "2             Brazil    BRA           BRL         2.95      1.790000   \n",
       "3            Britain    GBR           GBP         1.90      0.632911   \n",
       "4             Canada    CAN           CAD         2.85      1.470000   \n",
       "5              Chile    CHL           CLP      1260.00    514.000000   \n",
       "6              China    CHN           CNY         9.90      8.280000   \n",
       "7     Czech Republic    CZE           CZK        54.37     39.100000   \n",
       "8            Denmark    DNK           DKK        24.75      8.040000   \n",
       "9          Euro area    EUZ           EUR         2.56      1.075269   \n",
       "10         Hong Kong    HKG           HKD        10.20      7.790000   \n",
       "11           Hungary    HUN           HUF       339.00    279.000000   \n",
       "12         Indonesia    IDN           IDR     14500.00   7945.000000   \n",
       "13            Israel    ISR           ILS        14.50      4.050000   \n",
       "14             Japan    JPN           JPY       294.00    106.000000   \n",
       "15          Malaysia    MYS           MYR         4.52      3.800000   \n",
       "16            Mexico    MEX           MXN        20.90      9.410000   \n",
       "17       New Zealand    NZL           NZD         3.40      2.010000   \n",
       "18            Poland    POL           PLN         5.50      4.300000   \n",
       "19            Russia    RUS           RUB        39.50     28.500000   \n",
       "20         Singapore    SGP           SGD         3.20      1.700000   \n",
       "21      South Africa    ZAF           ZAR         9.00      6.720000   \n",
       "22       South Korea    KOR           KRW      3000.00   1108.000000   \n",
       "23            Sweden    SWE           SEK        24.00      8.840000   \n",
       "24       Switzerland    CHE           CHF         5.90      1.700000   \n",
       "25            Taiwan    TWN           TWD        70.00     30.600000   \n",
       "26          Thailand    THA           THB        55.00     38.000000   \n",
       "27     United States    USA           USD         2.51      1.000000   \n",
       "28         Argentina    ARG           ARS         2.50      1.000000   \n",
       "29         Australia    AUS           AUD         3.00      1.980000   \n",
       "...              ...    ...           ...          ...           ...   \n",
       "1484          Latvia    LVA           EUR         2.85      0.892339   \n",
       "1485         Moldova    MDA           MDL        43.00     17.814200   \n",
       "1486          Mexico    MEX           MXN        50.00     18.895250   \n",
       "1487        Malaysia    MYS           MYR         8.85      4.142500   \n",
       "1488       Nicaragua    NIC           NIO       110.00     33.162100   \n",
       "1489     Netherlands    NLD           EUR         4.06      0.892339   \n",
       "1490          Norway    NOR           NOK        42.00      8.652750   \n",
       "1491     New Zealand    NZL           NZD         6.40      1.513432   \n",
       "1492            Oman    OMN           OMR         1.21      0.385000   \n",
       "1493        Pakistan    PAK           PKR       480.00    157.450000   \n",
       "1494            Peru    PER           PEN        10.50      3.286800   \n",
       "1495     Philippines    PHL           PHP       142.00     51.317500   \n",
       "1496          Poland    POL           PLN        10.80      3.807750   \n",
       "1497        Portugal    PRT           EUR         3.25      0.892339   \n",
       "1498           Qatar    QAT           QAR        13.00      3.641750   \n",
       "1499         Romania    ROU           RON         9.30      4.219200   \n",
       "1500          Russia    RUS           RUB       130.00     63.838750   \n",
       "1501    Saudi Arabia    SAU           SAR        13.00      3.750350   \n",
       "1502       Singapore    SGP           SGD         5.80      1.361300   \n",
       "1503        Slovakia    SVK           EUR         3.20      0.892339   \n",
       "1504        Slovenia    SVN           EUR         2.40      0.892339   \n",
       "1505          Sweden    SWE           SEK        51.00      9.475050   \n",
       "1506        Thailand    THA           THB       119.00     30.827500   \n",
       "1507          Turkey    TUR           TRY        13.99      5.722000   \n",
       "1508          Taiwan    TWN           TWD        72.00     31.172000   \n",
       "1509         Ukraine    UKR           UAH        57.00     25.647500   \n",
       "1510         Uruguay    URY           UYU       164.00     35.169500   \n",
       "1511   United States    USA           USD         5.74      1.000000   \n",
       "1512         Vietnam    VNM           VND     65000.00  23231.000000   \n",
       "1513    South Africa    ZAF           ZAR        31.00     14.175000   \n",
       "\n",
       "      GDP_dollar       date  \n",
       "0            NaN 2000-04-01  \n",
       "1            NaN 2000-04-01  \n",
       "2            NaN 2000-04-01  \n",
       "3            NaN 2000-04-01  \n",
       "4            NaN 2000-04-01  \n",
       "5            NaN 2000-04-01  \n",
       "6            NaN 2000-04-01  \n",
       "7            NaN 2000-04-01  \n",
       "8            NaN 2000-04-01  \n",
       "9            NaN 2000-04-01  \n",
       "10           NaN 2000-04-01  \n",
       "11           NaN 2000-04-01  \n",
       "12           NaN 2000-04-01  \n",
       "13           NaN 2000-04-01  \n",
       "14           NaN 2000-04-01  \n",
       "15           NaN 2000-04-01  \n",
       "16           NaN 2000-04-01  \n",
       "17           NaN 2000-04-01  \n",
       "18           NaN 2000-04-01  \n",
       "19           NaN 2000-04-01  \n",
       "20           NaN 2000-04-01  \n",
       "21           NaN 2000-04-01  \n",
       "22           NaN 2000-04-01  \n",
       "23           NaN 2000-04-01  \n",
       "24           NaN 2000-04-01  \n",
       "25           NaN 2000-04-01  \n",
       "26           NaN 2000-04-01  \n",
       "27           NaN 2000-04-01  \n",
       "28           NaN 2001-04-01  \n",
       "29           NaN 2001-04-01  \n",
       "...          ...        ...  \n",
       "1484    15652.61 2019-07-09  \n",
       "1485     2726.54 2019-07-09  \n",
       "1486     9376.99 2019-07-09  \n",
       "1487     9827.67 2019-07-09  \n",
       "1488     2221.80 2019-07-09  \n",
       "1489    48555.35 2019-07-09  \n",
       "1490    75513.64 2019-07-09  \n",
       "1491    41350.26 2019-07-09  \n",
       "1492    17128.37 2019-07-09  \n",
       "1493     1545.94 2019-07-09  \n",
       "1494     6728.09 2019-07-09  \n",
       "1495     2988.90 2019-07-09  \n",
       "1496    13867.61 2019-07-09  \n",
       "1497    21334.13 2019-07-09  \n",
       "1498    62826.06 2019-07-09  \n",
       "1499    10761.71 2019-07-09  \n",
       "1500    10961.99 2019-07-09  \n",
       "1501    21153.21 2019-07-09  \n",
       "1502    59990.06 2019-07-09  \n",
       "1503    17627.11 2019-07-09  \n",
       "1504    23502.25 2019-07-09  \n",
       "1505    52924.37 2019-07-09  \n",
       "1506     6730.56 2019-07-09  \n",
       "1507    10537.19 2019-07-09  \n",
       "1508    24389.68 2019-07-09  \n",
       "1509     2655.94 2019-07-09  \n",
       "1510    16941.56 2019-07-09  \n",
       "1511    59895.00 2019-07-09  \n",
       "1512     2353.36 2019-07-09  \n",
       "1513     6182.25 2019-07-09  \n",
       "\n",
       "[1514 rows x 7 columns]"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.join(other, on=None, how=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.registerTempTable('bigmac')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+----------------+\n",
      "|       name|max(local_price)|\n",
      "+-----------+----------------+\n",
      "|     Turkey|       4000000.0|\n",
      "|    Vietnam|         65000.0|\n",
      "|  Indonesia|         35750.0|\n",
      "|   Colombia|         11900.0|\n",
      "|  Venezuela|         10950.0|\n",
      "|    Lebanon|          6500.0|\n",
      "|South Korea|          4500.0|\n",
      "|      Chile|          2640.0|\n",
      "| Costa Rica|          2290.0|\n",
      "|    Hungary|           900.0|\n",
      "+-----------+----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sqlCtx.sql('select name, max(local_price) from bigmac group by name order by max(local_price) desc limit 10 ').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PySpark DataFrame Vs Pandas DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Operation on Pyspark DataFrame run parallel on different nodes in cluster but, in case of pandas it is not possible.\n",
    "* Operations in PySpark DataFrame are lazy in nature but, in case of pandas we get the result as soon as we apply any operation.\n",
    "* In PySpark DataFrame, we can’t change the DataFrame due to it’s immutable property, we need to transform it. But in pandas it is not the case.\n",
    "* Pandas API support more operations than PySpark DataFrame. Still pandas API is more powerful than Spark.\n",
    "* Complex operations in pandas are easier to perform than Pyspark DataFrame"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}