SQL-pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5 entries, 0 to 4\n",
      "Data columns (total 3 columns):\n",
      "id          5 non-null object\n",
      "personal    5 non-null object\n",
      "family      5 non-null object\n",
      "dtypes: object(3)\n",
      "memory usage: 248.0+ bytes\n",
      "         id   personal    family\n",
      "0      dyer    William      Dyer\n",
      "1        pb      Frank   Pabodie\n",
      "2      lake   Anderson      Lake\n",
      "3       roe  Valentina   Roerich\n",
      "4  danforth      Frank  Danforth\n"
     ]
    }
   ],
   "source": [
    "#conexão com banco de dados sqlite3\n",
    "\n",
    "import sqlite3\n",
    "import pandas as pd\n",
    "\n",
    "cnx = sqlite3.connect('/home/silvio/survey.db')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5 entries, 0 to 4\n",
      "Data columns (total 3 columns):\n",
      "id          5 non-null object\n",
      "personal    5 non-null object\n",
      "family      5 non-null object\n",
      "dtypes: object(3)\n",
      "memory usage: 248.0+ bytes\n",
      "         id   personal    family\n",
      "0      dyer    William      Dyer\n",
      "1        pb      Frank   Pabodie\n",
      "2      lake   Anderson      Lake\n",
      "3       roe  Valentina   Roerich\n",
      "4  danforth      Frank  Danforth\n"
     ]
    }
   ],
   "source": [
    "dfPerson = pd.read_sql_query(\"SELECT * FROM person\", cnx)\n",
    "dfPerson.info()\n",
    "dfPerson.describe()\n",
    "print(dfPerson)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             lat        long\n",
      "count   4.000000    4.000000\n",
      "mean  -36.217500  -94.172500\n",
      "std    24.836751   64.150667\n",
      "min   -49.850000 -128.570000\n",
      "25%   -49.115000 -127.182500\n",
      "50%   -48.010000 -125.060000\n",
      "75%   -35.112500  -92.050000\n",
      "max     1.000000    2.000000\n",
      "    name    lat    long\n",
      "0   DR-1 -49.85 -128.57\n",
      "1   DR-3 -47.15 -126.72\n",
      "2  MSK-4 -48.87 -123.40\n",
      "3    DD1   1.00    2.00\n"
     ]
    }
   ],
   "source": [
    "dfsite = pd.read_sql_query(\"SELECT * FROM Site\", cnx)\n",
    "print(dfsite.describe())\n",
    "print(dfsite)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   max(id)\n",
      "0      999\n",
      "999\n"
     ]
    }
   ],
   "source": [
    "dfvisited=pd.read_sql_query(\"SELECT max(id) FROM Visited;\", cnx)\n",
    "print(dfvisited)\n",
    "\n",
    "dfvisited=pd.read_sql_query(\"SELECT * FROM Visited;\", cnx)\n",
    "print(dfvisited['id'].max())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     family   personal\n",
      "0      Dyer    William\n",
      "1   Pabodie      Frank\n",
      "2      Lake   Anderson\n",
      "3   Roerich  Valentina\n",
      "4  Danforth      Frank\n",
      "    taken person quant  reading\n",
      "0     619   dyer   rad     9.82\n",
      "1     619   dyer   sal     0.13\n",
      "2     622   dyer   rad     7.80\n",
      "3     622   dyer   sal     0.09\n",
      "4     734     pb   rad     8.41\n",
      "5     734   lake   sal     0.05\n",
      "6     734     pb  temp   -21.50\n",
      "7     735     pb   rad     7.22\n",
      "8     735   None   sal     0.06\n",
      "9     735   None  temp   -26.00\n",
      "10    751     pb   rad     4.35\n",
      "11    751     pb  temp   -18.50\n",
      "12    751   lake   sal     0.10\n",
      "13    752   lake   rad     2.19\n",
      "14    752   lake   sal     0.09\n",
      "15    752   lake  temp   -16.00\n",
      "16    752    roe   sal    41.60\n",
      "17    837   lake   rad     1.46\n",
      "18    837   lake   sal     0.21\n",
      "19    837    roe   sal    22.50\n",
      "20    844    roe   rad    11.25\n",
      "0        Dyer\n",
      "1     Pabodie\n",
      "2        Lake\n",
      "3     Roerich\n",
      "4    Danforth\n",
      "Name: family, dtype: object\n"
     ]
    }
   ],
   "source": [
    "# Selecionando colunas\n",
    "\n",
    "dfPerson = pd.read_sql_query(\"SELECT family, personal FROM Person;\", cnx)\n",
    "print(dfPerson)\n",
    "dfSurvey = pd.read_sql_query(\"SELECT * FROM Survey;\", cnx)\n",
    "print(dfSurvey)\n",
    "print(dfPerson['family'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   quant\n",
      "0    rad\n",
      "1    sal\n",
      "2    rad\n",
      "3    sal\n",
      "4    rad\n",
      "5    sal\n",
      "6   temp\n",
      "7    rad\n",
      "8    sal\n",
      "9   temp\n",
      "10   rad\n",
      "11  temp\n",
      "12   sal\n",
      "13   rad\n",
      "14   sal\n",
      "15  temp\n",
      "16   sal\n",
      "17   rad\n",
      "18   sal\n",
      "19   sal\n",
      "20   rad\n"
     ]
    }
   ],
   "source": [
    "dfSurvey = pd.read_sql_query(\"SELECT quant FROM Survey\", cnx)\n",
    "print(dfSurvey)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  quant\n",
      "0   rad\n",
      "1   sal\n",
      "2  temp\n",
      "0      rad\n",
      "1      sal\n",
      "2      rad\n",
      "3      sal\n",
      "4      rad\n",
      "5      sal\n",
      "6     temp\n",
      "7      rad\n",
      "8      sal\n",
      "9     temp\n",
      "10     rad\n",
      "11    temp\n",
      "12     sal\n",
      "13     rad\n",
      "14     sal\n",
      "15    temp\n",
      "16     sal\n",
      "17     rad\n",
      "18     sal\n",
      "19     sal\n",
      "20     rad\n",
      "Name: quant, dtype: object\n",
      "['rad' 'sal' 'temp']\n"
     ]
    }
   ],
   "source": [
    "#distinct - opção para remover registros com valores repetidos de uma coluna específica\n",
    "dfSurvey = pd.read_sql_query(\"SELECT DISTINCT quant FROM Survey;\", cnx)\n",
    "print(dfSurvey)\n",
    "\n",
    "dfSurvey = pd.read_sql_query(\"SELECT * FROM Survey;\", cnx)\n",
    "print(dfSurvey['quant'])\n",
    "print(dfSurvey['quant'].unique())\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    taken quant\n",
      "0     619   rad\n",
      "1     619   sal\n",
      "2     622   rad\n",
      "3     622   sal\n",
      "4     734   rad\n",
      "5     734   sal\n",
      "6     734  temp\n",
      "7     735   rad\n",
      "8     735   sal\n",
      "9     735  temp\n",
      "10    751   rad\n",
      "11    751  temp\n",
      "12    751   sal\n",
      "13    752   rad\n",
      "14    752   sal\n",
      "15    752  temp\n",
      "16    837   rad\n",
      "17    837   sal\n",
      "18    844   rad\n"
     ]
    }
   ],
   "source": [
    "dfSurvey = pd.read_sql_query(\"SELECT DISTINCT taken, quant FROM Survey;\", cnx)\n",
    "print(dfSurvey)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         id   personal    family\n",
      "0  danforth      Frank  Danforth\n",
      "1      dyer    William      Dyer\n",
      "2      lake   Anderson      Lake\n",
      "3        pb      Frank   Pabodie\n",
      "4       roe  Valentina   Roerich\n"
     ]
    }
   ],
   "source": [
    "#ordenando\n",
    "dfPerson = pd.read_sql_query(\"SELECT * FROM person ORDER BY id  ASC;\", cnx)\n",
    "print(dfPerson)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         id   personal    family\n",
      "0      dyer    William      Dyer\n",
      "1        pb      Frank   Pabodie\n",
      "2      lake   Anderson      Lake\n",
      "3       roe  Valentina   Roerich\n",
      "4  danforth      Frank  Danforth\n",
      "         id   personal    family\n",
      "4  danforth      Frank  Danforth\n",
      "0      dyer    William      Dyer\n",
      "2      lake   Anderson      Lake\n",
      "1        pb      Frank   Pabodie\n",
      "3       roe  Valentina   Roerich\n"
     ]
    }
   ],
   "source": [
    "#ordenando com Pandas\n",
    "dfPerson = pd.read_sql_query(\"SELECT * FROM person\", cnx)\n",
    "print(dfPerson)\n",
    "print(dfPerson.sort_values(by=['id']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    id  site       dated\n",
      "0  619  DR-1  1927-02-08\n",
      "1  622  DR-1  1927-02-10\n",
      "2  844  DR-1  1932-03-22\n",
      "    id  site       dated\n",
      "0  619  DR-1  1927-02-08\n",
      "1  622  DR-1  1927-02-10\n",
      "   taken person quant  reading\n",
      "0    734   lake   sal     0.05\n",
      "1    751   lake   sal     0.10\n",
      "2    752   lake   rad     2.19\n",
      "3    752   lake   sal     0.09\n",
      "4    752   lake  temp   -16.00\n",
      "5    752    roe   sal    41.60\n",
      "6    837   lake   rad     1.46\n",
      "7    837   lake   sal     0.21\n",
      "8    837    roe   sal    22.50\n",
      "9    844    roe   rad    11.25\n"
     ]
    }
   ],
   "source": [
    "#Filtro por linhas\n",
    "\n",
    "dfSite = pd.read_sql_query(\"SELECT * FROM Visited WHERE site = 'DR-1';\", cnx)\n",
    "print(dfSite)\n",
    "\n",
    "dfSite = pd.read_sql_query(\"SELECT * FROM Visited WHERE site = 'DR-1' AND dated < '1930-01-01';\", cnx)\n",
    "print(dfSite)\n",
    "\n",
    "dfSurvey= pd.read_sql_query(\"SELECT * FROM Survey WHERE person IN ('lake', 'roe');\", cnx)\n",
    "print(dfSurvey)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               id\n",
      "count    4.000000\n",
      "mean   797.250000\n",
      "std    161.303802\n",
      "min    619.000000\n",
      "25%    705.250000\n",
      "50%    785.500000\n",
      "75%    877.500000\n",
      "max    999.000000\n",
      "    id   site       dated\n",
      "0  999   DDF3            \n",
      "1  619   DR-1  1927-02-08\n",
      "2  734   DR-3  1930-01-07\n",
      "3  837  MSK-4  1932-01-14\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               id\n",
      "count    9.000000\n",
      "mean   765.888889\n",
      "std    117.348247\n",
      "min    619.000000\n",
      "25%    734.000000\n",
      "50%    751.000000\n",
      "75%    837.000000\n",
      "max    999.000000\n",
      "    id   site       dated\n",
      "0  619   DR-1  1927-02-08\n",
      "1  622   DR-1  1927-02-10\n",
      "2  734   DR-3  1930-01-07\n",
      "3  735   DR-3  1930-01-12\n",
      "4  751   DR-3  1930-02-26\n",
      "5  752   DR-3        None\n",
      "6  837  MSK-4  1932-01-14\n",
      "7  844   DR-1  1932-03-22\n",
      "8  999   DDF3            \n",
      "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7f7e306b4710>\n",
      "site  DDF3  dated      id  site dated\n",
      "8  999  DDF3      \n",
      "site  DR-1  dated      id  site       dated\n",
      "0  619  DR-1  1927-02-08\n",
      "1  622  DR-1  1927-02-10\n",
      "7  844  DR-1  1932-03-22\n",
      "site  DR-3  dated      id  site       dated\n",
      "2  734  DR-3  1930-01-07\n",
      "3  735  DR-3  1930-01-12\n",
      "4  751  DR-3  1930-02-26\n",
      "5  752  DR-3        None\n",
      "site  MSK-4  dated      id   site       dated\n",
      "6  837  MSK-4  1932-01-14\n",
      "       id  dated\n",
      "site            \n",
      "DDF3    1      1\n",
      "DR-1    3      3\n",
      "DR-3    4      3\n",
      "MSK-4   1      1\n",
      "site  id\n",
      "site  dated\n",
      "            id  site\n",
      "dated               \n",
      "             1     1\n",
      "1927-02-08   1     1\n",
      "1927-02-10   1     1\n",
      "1930-01-07   1     1\n",
      "1930-01-12   1     1\n",
      "1930-02-26   1     1\n",
      "1932-01-14   1     1\n",
      "1932-03-22   1     1\n",
      "dated  id\n",
      "dated  site\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    name    lat    long   id   site       dated\n",
      "0   DR-1 -49.85 -128.57  619   DR-1  1927-02-08\n",
      "1   DR-1 -49.85 -128.57  622   DR-1  1927-02-10\n",
      "2   DR-1 -49.85 -128.57  844   DR-1  1932-03-22\n",
      "3   DR-3 -47.15 -126.72  734   DR-3  1930-01-07\n",
      "4   DR-3 -47.15 -126.72  735   DR-3  1930-01-12\n",
      "5   DR-3 -47.15 -126.72  751   DR-3  1930-02-26\n",
      "6   DR-3 -47.15 -126.72  752   DR-3        None\n",
      "7  MSK-4 -48.87 -123.40  837  MSK-4  1932-01-14\n",
      "    name    lat    long     id   site       dated\n",
      "0   DR-1 -49.85 -128.57  619.0   DR-1  1927-02-08\n",
      "1   DR-1 -49.85 -128.57  622.0   DR-1  1927-02-10\n",
      "2   DR-1 -49.85 -128.57  844.0   DR-1  1932-03-22\n",
      "3   DR-3 -47.15 -126.72  734.0   DR-3  1930-01-07\n",
      "4   DR-3 -47.15 -126.72  735.0   DR-3  1930-01-12\n",
      "5   DR-3 -47.15 -126.72  751.0   DR-3  1930-02-26\n",
      "6   DR-3 -47.15 -126.72  752.0   DR-3        None\n",
      "7  MSK-4 -48.87 -123.40  837.0  MSK-4  1932-01-14\n",
      "8    DD1   1.00    2.00    NaN   None        None\n"
     ]
    }
   ],
   "source": [
    "#Exemplos com merge\n",
    "dfVisitedSiteMerged = pd.read_sql_query(\"SELECT * FROM Site s INNER JOIN Visited v ON s.name = v.site\", cnx)\n",
    "print(dfVisitedSiteMerged)\n",
    "\n",
    "dfVisitedSiteMerged = pd.read_sql_query(\"SELECT * FROM Site s LEFT JOIN Visited v ON s.name = v.site\", cnx)\n",
    "print(dfVisitedSiteMerged)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    name    lat    long   id   site       dated\n",
      "0   DR-1 -49.85 -128.57  619   DR-1  1927-02-08\n",
      "1   DR-1 -49.85 -128.57  622   DR-1  1927-02-10\n",
      "2   DR-1 -49.85 -128.57  844   DR-1  1932-03-22\n",
      "3   DR-3 -47.15 -126.72  734   DR-3  1930-01-07\n",
      "4   DR-3 -47.15 -126.72  735   DR-3  1930-01-12\n",
      "5   DR-3 -47.15 -126.72  751   DR-3  1930-02-26\n",
      "6   DR-3 -47.15 -126.72  752   DR-3        None\n",
      "7  MSK-4 -48.87 -123.40  837  MSK-4  1932-01-14\n",
      "    name    lat    long   id       dated\n",
      "0   DR-1 -49.85 -128.57  619  1927-02-08\n",
      "1   DR-1 -49.85 -128.57  622  1927-02-10\n",
      "2   DR-1 -49.85 -128.57  844  1932-03-22\n",
      "3   DR-3 -47.15 -126.72  734  1930-01-07\n",
      "4   DR-3 -47.15 -126.72  735  1930-01-12\n",
      "5   DR-3 -47.15 -126.72  751  1930-02-26\n",
      "6   DR-3 -47.15 -126.72  752        None\n",
      "7  MSK-4 -48.87 -123.40  837  1932-01-14\n",
      "    name    lat    long   id       dated\n",
      "0   DR-1 -49.85 -128.57  619  1927-02-08\n",
      "1   DR-1 -49.85 -128.57  622  1927-02-10\n",
      "2   DR-1 -49.85 -128.57  844  1932-03-22\n",
      "3   DR-3 -47.15 -126.72  734  1930-01-07\n",
      "4   DR-3 -47.15 -126.72  735  1930-01-12\n",
      "5   DR-3 -47.15 -126.72  751  1930-02-26\n",
      "6   DR-3 -47.15 -126.72  752        None\n",
      "7  MSK-4 -48.87 -123.40  837  1932-01-14\n",
      "    name    lat    long     id       dated\n",
      "0   DR-1 -49.85 -128.57  619.0  1927-02-08\n",
      "1   DR-1 -49.85 -128.57  622.0  1927-02-10\n",
      "2   DR-1 -49.85 -128.57  844.0  1932-03-22\n",
      "3   DR-3 -47.15 -126.72  734.0  1930-01-07\n",
      "4   DR-3 -47.15 -126.72  735.0  1930-01-12\n",
      "5   DR-3 -47.15 -126.72  751.0  1930-02-26\n",
      "6   DR-3 -47.15 -126.72  752.0        None\n",
      "7  MSK-4 -48.87 -123.40  837.0  1932-01-14\n",
      "8    DD1   1.00    2.00    NaN         NaN\n",
      "9    NaN    NaN     NaN  999.0            \n",
      "    name    lat    long     id       dated\n",
      "0   DR-1 -49.85 -128.57  619.0  1927-02-08\n",
      "1   DR-1 -49.85 -128.57  622.0  1927-02-10\n",
      "2   DR-1 -49.85 -128.57  844.0  1932-03-22\n",
      "3   DR-3 -47.15 -126.72  734.0  1930-01-07\n",
      "4   DR-3 -47.15 -126.72  735.0  1930-01-12\n",
      "5   DR-3 -47.15 -126.72  751.0  1930-02-26\n",
      "6   DR-3 -47.15 -126.72  752.0        None\n",
      "7  MSK-4 -48.87 -123.40  837.0  1932-01-14\n",
      "8    DD1   1.00    2.00    NaN         NaN\n",
      "    name    lat    long   id       dated\n",
      "0   DR-1 -49.85 -128.57  619  1927-02-08\n",
      "1   DR-1 -49.85 -128.57  622  1927-02-10\n",
      "2   DR-1 -49.85 -128.57  844  1932-03-22\n",
      "3   DR-3 -47.15 -126.72  734  1930-01-07\n",
      "4   DR-3 -47.15 -126.72  735  1930-01-12\n",
      "5   DR-3 -47.15 -126.72  751  1930-02-26\n",
      "6   DR-3 -47.15 -126.72  752        None\n",
      "7  MSK-4 -48.87 -123.40  837  1932-01-14\n",
      "8    NaN    NaN     NaN  999            \n"
     ]
    }
   ],
   "source": [
    "#dfMerge = pd.merge(dfsite, dfVisited)\n",
    "#print(dfMerge)\n",
    "\n",
    "dfMerge = pd.merge(dfsite, dfVisited, left_on='name',right_on='site')\n",
    "print(dfMerge)\n",
    "\n",
    "dfMerge = pd.merge(dfsite, dfVisited, left_on='name',right_on='site').drop('site', axis=1)\n",
    "print(dfMerge)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    name    lat    long   id       dated\n",
      "0   DR-1 -49.85 -128.57  619  1927-02-08\n",
      "1   DR-1 -49.85 -128.57  622  1927-02-10\n",
      "2   DR-1 -49.85 -128.57  844  1932-03-22\n",
      "3   DR-3 -47.15 -126.72  734  1930-01-07\n",
      "4   DR-3 -47.15 -126.72  735  1930-01-12\n",
      "5   DR-3 -47.15 -126.72  751  1930-02-26\n",
      "6   DR-3 -47.15 -126.72  752        None\n",
      "7  MSK-4 -48.87 -123.40  837  1932-01-14\n",
      "    name    lat    long     id       dated\n",
      "0   DR-1 -49.85 -128.57  619.0  1927-02-08\n",
      "1   DR-1 -49.85 -128.57  622.0  1927-02-10\n",
      "2   DR-1 -49.85 -128.57  844.0  1932-03-22\n",
      "3   DR-3 -47.15 -126.72  734.0  1930-01-07\n",
      "4   DR-3 -47.15 -126.72  735.0  1930-01-12\n",
      "5   DR-3 -47.15 -126.72  751.0  1930-02-26\n",
      "6   DR-3 -47.15 -126.72  752.0        None\n",
      "7  MSK-4 -48.87 -123.40  837.0  1932-01-14\n",
      "8    DD1   1.00    2.00    NaN         NaN\n",
      "9    NaN    NaN     NaN  999.0            \n",
      "    name    lat    long     id       dated\n",
      "0   DR-1 -49.85 -128.57  619.0  1927-02-08\n",
      "1   DR-1 -49.85 -128.57  622.0  1927-02-10\n",
      "2   DR-1 -49.85 -128.57  844.0  1932-03-22\n",
      "3   DR-3 -47.15 -126.72  734.0  1930-01-07\n",
      "4   DR-3 -47.15 -126.72  735.0  1930-01-12\n",
      "5   DR-3 -47.15 -126.72  751.0  1930-02-26\n",
      "6   DR-3 -47.15 -126.72  752.0        None\n",
      "7  MSK-4 -48.87 -123.40  837.0  1932-01-14\n",
      "8    DD1   1.00    2.00    NaN         NaN\n",
      "    name    lat    long   id       dated\n",
      "0   DR-1 -49.85 -128.57  619  1927-02-08\n",
      "1   DR-1 -49.85 -128.57  622  1927-02-10\n",
      "2   DR-1 -49.85 -128.57  844  1932-03-22\n",
      "3   DR-3 -47.15 -126.72  734  1930-01-07\n",
      "4   DR-3 -47.15 -126.72  735  1930-01-12\n",
      "5   DR-3 -47.15 -126.72  751  1930-02-26\n",
      "6   DR-3 -47.15 -126.72  752        None\n",
      "7  MSK-4 -48.87 -123.40  837  1932-01-14\n",
      "8    NaN    NaN     NaN  999            \n"
     ]
    }
   ],
   "source": [
    "dfMerge = pd.merge(dfsite, dfVisited, left_on='name',right_on='site', how='inner').drop('site', axis=1)\n",
    "print(dfMerge)\n",
    "\n",
    "dfMerge = pd.merge(dfsite, dfVisited, left_on='name',right_on='site', how='outer').drop('site', axis=1)\n",
    "print(dfMerge)\n",
    "\n",
    "dfMerge = pd.merge(dfsite, dfVisited, left_on='name',right_on='site', how='left').drop('site', axis=1)\n",
    "print(dfMerge)\n",
    "\n",
    "dfMerge = pd.merge(dfsite, dfVisited, left_on='name',right_on='site', how='right').drop('site', axis=1)\n",
    "print(dfMerge)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  person  count(reading)  round(avg(reading), 2)\n",
      "0   None               2                  -12.97\n",
      "1   dyer               4                    4.46\n",
      "2   lake               7                   -1.70\n",
      "3     pb               5                   -4.00\n",
      "4    roe               3                   25.12\n"
     ]
    }
   ],
   "source": [
    "dfsurvey = pd.read_sql_query(\"SELECT   person, count(reading), round(avg(reading), 2) FROM  Survey GROUP BY person;\",cnx)\n",
    "print(dfsurvey)                            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    taken person quant  reading\n",
      "0     619   dyer   rad     9.82\n",
      "1     619   dyer   sal     0.13\n",
      "2     622   dyer   rad     7.80\n",
      "3     622   dyer   sal     0.09\n",
      "4     734     pb   rad     8.41\n",
      "5     734   lake   sal     0.05\n",
      "6     734     pb  temp   -21.50\n",
      "7     735     pb   rad     7.22\n",
      "8     735   None   sal     0.06\n",
      "9     735   None  temp   -26.00\n",
      "10    751     pb   rad     4.35\n",
      "11    751     pb  temp   -18.50\n",
      "12    751   lake   sal     0.10\n",
      "13    752   lake   rad     2.19\n",
      "14    752   lake   sal     0.09\n",
      "15    752   lake  temp   -16.00\n",
      "16    752    roe   sal    41.60\n",
      "17    837   lake   rad     1.46\n",
      "18    837   lake   sal     0.21\n",
      "19    837    roe   sal    22.50\n",
      "20    844    roe   rad    11.25\n",
      "        taken  quant  reading\n",
      "person                       \n",
      "dyer        4      4        4\n",
      "lake        7      7        7\n",
      "pb          5      5        5\n",
      "roe         3      3        3\n",
      "reading mean  4.46\n",
      "reading mean  -1.6999999999999997\n",
      "reading mean  -4.004\n",
      "reading mean  25.116666666666664\n"
     ]
    }
   ],
   "source": [
    "dfsurvey = pd.read_sql_query(\"SELECT   * FROM  Survey\",cnx)\n",
    "print(dfsurvey)    \n",
    "\n",
    "gby=dfsurvey.groupby('person').count()\n",
    "print(gby)\n",
    "\n",
    "    \n",
    "gby=dfsurvey.groupby('person')['reading'].mean()\n",
    "\n",
    "for (g) in gby:\n",
    "    print(\"reading mean \",g)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               id\n",
      "count    9.000000\n",
      "mean   765.888889\n",
      "std    117.348247\n",
      "min    619.000000\n",
      "25%    734.000000\n",
      "50%    751.000000\n",
      "75%    837.000000\n",
      "max    999.000000\n",
      "    id   site       dated\n",
      "0  619   DR-1  1927-02-08\n",
      "1  622   DR-1  1927-02-10\n",
      "2  734   DR-3  1930-01-07\n",
      "3  735   DR-3  1930-01-12\n",
      "4  751   DR-3  1930-02-26\n",
      "5  752   DR-3        None\n",
      "6  837  MSK-4  1932-01-14\n",
      "7  844   DR-1  1932-03-22\n",
      "8  999   DDF3            \n",
      "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7f7e3043f5c0>\n",
      "site  DDF3  dated      id  site dated\n",
      "8  999  DDF3      \n",
      "site  DR-1  dated      id  site       dated\n",
      "0  619  DR-1  1927-02-08\n",
      "1  622  DR-1  1927-02-10\n",
      "7  844  DR-1  1932-03-22\n",
      "site  DR-3  dated      id  site       dated\n",
      "2  734  DR-3  1930-01-07\n",
      "3  735  DR-3  1930-01-12\n",
      "4  751  DR-3  1930-02-26\n",
      "5  752  DR-3        None\n",
      "site  MSK-4  dated      id   site       dated\n",
      "6  837  MSK-4  1932-01-14\n",
      "       id  dated\n",
      "site            \n",
      "DDF3    1      1\n",
      "DR-1    3      3\n",
      "DR-3    4      3\n",
      "MSK-4   1      1\n",
      "site  id\n",
      "site  dated\n",
      "            id  site\n",
      "dated               \n",
      "             1     1\n",
      "1927-02-08   1     1\n",
      "1927-02-10   1     1\n",
      "1930-01-07   1     1\n",
      "1930-01-12   1     1\n",
      "1930-02-26   1     1\n",
      "1932-01-14   1     1\n",
      "1932-03-22   1     1\n",
      "dated  id\n",
      "dated  site\n"
     ]
    }
   ],
   "source": [
    "#agrupando\n",
    "\n",
    "dfVisited = pd.read_sql_query(\"SELECT * FROM Visited\", cnx)\n",
    "print(dfVisited.describe())\n",
    "print(dfVisited)\n",
    "\n",
    "gby=dfVisited.groupby('site')\n",
    "print(gby)\n",
    "\n",
    "for (site, dated) in gby:\n",
    "    print(\"site \",site,\" dated \",dated)\n",
    "    \n",
    "gby=dfVisited.groupby('site').count()\n",
    "print(gby)\n",
    "\n",
    "for (site) in gby:\n",
    "    print(\"site \",site)\n",
    "    \n",
    "gby=dfVisited.groupby('dated').count()\n",
    "print(gby)\n",
    "\n",
    "for (dated) in gby:\n",
    "    print(\"dated \",dated)"
   ]
  }
 ],
 "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
}