Aula_5.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Estrutura de Dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandas import Series, DataFrame\n",
    "import pandas as pd\n",
    "obj = Series([4, 7, -5, 3])\n",
    "obj"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(obj.values)\n",
    "print(obj.index) #obj.index.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "obj2 = Series([4, 7, -5, 3], index=['d', 'b', 'a', 'c'])\n",
    "obj2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "print(obj2[(obj2 > 0) & (obj2 < 5)])\n",
    "print(obj2 * 2)\n",
    "print(np.exp(obj2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sdata = {'Ohio': 35000, 'Texas': 71000, 'Oregon': 16000, 'Utah': 5000}\n",
    "obj3 = Series(sdata)\n",
    "obj3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "states = ['Oregon', 'Texas','California', 'Ohio']\n",
    "obj4 = Series(sdata, index=states)\n",
    "obj4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pd.isnull(obj4)) # == obj4.isnull()\n",
    "print(pd.notnull(obj4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(obj3)\n",
    "print(obj4)\n",
    "obj3 + obj4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "obj4.name = 'population'\n",
    "obj4.index.name = 'state'\n",
    "obj4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "obj.index = ['Bob', 'Steve', 'Jeff', 'Ryan']\n",
    "obj"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {'state': ['Ohio', 'Ohio', 'Ohio', 'Nevada', 'Nevada'],\n",
    "        'year': [2000, 2001, 2002, 2001, 2002],\n",
    "        'pop': [1.5, 1.7, 3.6, 2.4, 2.9]}\n",
    "df = DataFrame(data)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = DataFrame(data, columns=['year', 'state', 'pop', 'debt'],\n",
    "                index=['one', 'two', 'three', 'four', 'five'])\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df['state'])\n",
    "print(df.year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df2.loc['four']) #label\n",
    "print(df.iloc[0]) #int"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['debt'] = np.arange(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "val = Series([-1.2, -1.5, -1.7], index=['two', 'four', 'five'])\n",
    "df2['debt'] = val\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['eastern'] = df2.state == 'Ohio'\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del df2['eastern']\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "obj = Series(range(3), index=['a', 'b', 'c'])\n",
    "obj.index.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('state' in df2.columns)\n",
    "print(0 in df.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aplicação de Função e Mapeamento"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandas import Series, DataFrame\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = DataFrame(np.random.randn(4, 3), columns=list('bde'),\n",
    "                  index=['Utah', 'Ohio', 'Texas', 'Oregon'])\n",
    "np.abs(df) #retorna valor absoluto"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "f = lambda x: x.max() - x.min()\n",
    "print(df.apply(f))\n",
    "print(df.apply(f, axis=1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def f2(x):\n",
    "    return Series([x.min(), x.max()], index=['min', 'max'])\n",
    "\n",
    "df.apply(f2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "format2 = lambda x: '%.2f' % x\n",
    "df.applymap(format2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ordenação e Ranking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "obj = Series(range(4), index=['d', 'a', 'b', 'c'])\n",
    "df2 = DataFrame(np.arange(8).reshape((2, 4)), index=['three', 'one'],\n",
    "                  columns=['d', 'a', 'b', 'c'])\n",
    "print(obj)\n",
    "print(df2)\n",
    "print(obj.sort_index())\n",
    "print(df2.sort_index())\n",
    "print(df2.sort_index(axis=1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "obj = Series([4, 7, -3, 2])\n",
    "obj.sort_values() #igual para pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = DataFrame({'b': [4, 7, -3, 2], 'a': [0, 1, 0, 1]})\n",
    "print(df3)\n",
    "print(df3.sort_index(by='b'))\n",
    "print(df3.sort_index(by=['a','b']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {'name': ['Jason', 'Molly', 'Tina', 'Jake', 'Amy'], \n",
    "        'nota': [8, 7, 7.5, 10, 5]}\n",
    "df4 = DataFrame(data)\n",
    "print(df4)\n",
    "df4['rank'] = df4['nota'].rank(ascending=0)\n",
    "df4.sort_values('rank')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sumarização e Estatística Descritiva"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df5 = DataFrame([[1.4, np.nan], [7.1, -4.5],\n",
    "                [np.nan, np.nan], [0.75, -1.3]],\n",
    "               index=['a', 'b', 'c', 'd'],\n",
    "               columns=['one', 'two'])\n",
    "print(df5)\n",
    "print(df5.sum())\n",
    "print(df5.sum(axis=1))\n",
    "print(df5.count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df5.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "obj = Series(['c', 'a', 'd', 'a', 'a', 'b', 'b', 'c', 'c'])\n",
    "uniques = obj.unique()\n",
    "uniques"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mask = obj.isin(['b', 'c'])\n",
    "mask"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Manipulação de Valores Faltantes "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "string_data = Series(['laranja', 'uva', np.nan, 'abacate'])\n",
    "print(string_data)\n",
    "print(string_data.isnull())\n",
    "string_data[0] = None\n",
    "print(string_data.isnull())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = DataFrame([[1., 6.5, 3.], [1., np.nan, np.nan],\n",
    "                  [np.nan, np.nan, np.nan], [np.nan, 6.5, 3.]])\n",
    "print(data)\n",
    "cleaned = data.dropna()\n",
    "print('\\n',cleaned)\n",
    "data.dropna(how='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(data.fillna(0))\n",
    "print(data.fillna(data.mean()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TODO Section"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manipulação de DataFrame\n",
    "\n",
    "        > Crie, a partir do dicionário abaixo, um DataFrame cujo index seja os valores da variável labels\n",
    "        > encontre a média dos valores da coluna age e preencha os valores faltantes dessa coluna com o valor da média\n",
    "        > crie uma nova coluna chamada 'rank', que mostre os animais que receberam mais visitas\n",
    "        > qual o animal que recebeu a maior quantidade de visitas?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {'animal': ['cat', 'cat', 'snake', 'dog', 'dog', 'cat', 'snake', 'cat', 'dog', 'dog'],\n",
    "        'age': [2.5, 3, 0.5, np.nan, 5, 2, 4.5, np.nan, 7, 3],\n",
    "        'visits': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],\n",
    "        'priority': ['yes', 'yes', 'no', 'yes', 'no', 'no', 'no', 'yes', 'no', 'no']}\n",
    "\n",
    "labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: sua resposta aqui\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Carregamento e Armazenamento de Dados\n",
    "\n",
    "### Arquivo CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "poke = pd.read_csv('bases/Pokemon.csv')\n",
    "poke.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Arquivo JSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "obj = \"\"\"\n",
    "{\"name\": \"Wes\",\n",
    "\"places_lived\": [\"United States\", \"Spain\", \"Germany\"],\n",
    "\"pet\": null,\n",
    "\"siblings\": [{\"name\": \"Scott\", \"age\": 25, \"pet\": \"Zuko\"},\n",
    "{\"name\": \"Katie\", \"age\": 33, \"pet\": \"Cisco\"}]\n",
    "}\n",
    "\"\"\"\n",
    "print(type(obj))\n",
    "print(obj)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "result = json.loads(obj)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "asjson = json.dumps(result)\n",
    "print(type(asjson))\n",
    "asjson"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "siblings = pd.DataFrame(result['siblings'], columns=['name', 'age'])\n",
    "siblings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "query = \"\"\"\n",
    "CREATE TABLE test\n",
    "(a VARCHAR(20), b VARCHAR(20),\n",
    "c REAL, d INTEGER\n",
    ");\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sqlite3.connect(':memory:')\n",
    "con.execute(query)\n",
    "con.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [('Atlanta', 'Georgia', 1.25, 6),\n",
    "('Tallahassee', 'Florida', 2.6, 3),\n",
    "('Sacramento', 'California', 1.7, 5)]\n",
    "stmt = \"INSERT INTO test VALUES(?, ?, ?, ?)\"\n",
    "con.executemany(stmt, data)\n",
    "con.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = con.execute('select * from test')\n",
    "rows = cursor.fetchall()\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(cursor.description)\n",
    "pd.DataFrame(rows, columns=list(zip(*cursor.description))[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas.io.sql as sql\n",
    "sql.read_sql_query('select * from test', con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combinação de Dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],\n",
    "                 'data1': range(7)})\n",
    "\n",
    "df2 = pd.DataFrame({'key': ['a', 'b', 'd','b'],\n",
    "                 'data2': range(4)})\n",
    "\n",
    "pd.merge(df1,df2,on='key') #default inner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = pd.DataFrame({'lkey': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],\n",
    "                 'data1': range(7)})\n",
    "\n",
    "df4 = pd.DataFrame({'rkey': ['a', 'b', 'd'],\n",
    "                 'data2': range(3)})\n",
    "pd.merge(df3, df4, left_on='lkey', right_on='rkey')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df3, df4, how='outer',left_on='lkey',right_on='rkey')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = pd.DataFrame({'key1': ['foo', 'foo', 'bar'],\n",
    "                  'key2': ['one', 'two', 'one'],\n",
    "                  'lval': [1, 2, 3]})\n",
    "right = pd.DataFrame({'key1': ['foo', 'foo', 'bar', 'bar'],\n",
    "                   'key2': ['one', 'one', 'one', 'two'],\n",
    "                   'rval': [4, 5, 6, 7]})\n",
    "pd.merge(left, right, on=['key1', 'key2'], how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "arr = np.arange(12).reshape((3, 4))\n",
    "print(arr)\n",
    "np.concatenate([arr, arr], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s1 = pd.Series([0, 1], index=['a', 'b'])\n",
    "s2 = pd.Series([2, 3, 4], index=['c', 'd', 'e'])\n",
    "s3 = pd.Series([5, 6], index=['f', 'g'])\n",
    "pd.concat([s1, s2, s3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(np.arange(6).reshape(3, 2), \n",
    "                index=['a', 'b', 'c'],\n",
    "                columns=['one', 'two'])\n",
    "df2 = pd.DataFrame(5 + np.arange(4).reshape(2, 2), \n",
    "                index=['a', 'c'],\n",
    "                columns=['three', 'four'])\n",
    "print(df1)\n",
    "print(df2)\n",
    "pd.concat([df1, df2], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TODO Section"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manipulação de Dados usando Pandas\n",
    "\n",
    "Usando o dataset Pokemon.csv, faça:\n",
    "\n",
    "    1) Verifique em qual(is) coluna(s) existem valores faltantes\n",
    "    2) Preencha os valores faltantes da coluna Type 2 com os valores correspondentes da coluna Type 1\n",
    "    3) Crie um DataFrame a partir dos dados originais contendo apenas pokemons lendários. Imprima os 5 primeiros\n",
    "    4) Use apply/applymap para passar todos os valores das colunas Name, Type 1 e Type 2 para minúscula\n",
    "    5) Agrupe os pokemons por Type 1 e retorne uma Série ordenada pela quantidade em ordem decrescente"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Resposta 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Resposta 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Resposta 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Resposta 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Resposta 5"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}