Trabalho de Métodos Computacionais

SQLDF

O pacote SQLDF, criado por G. Grothendieck, possui a função sqldf() que cria uma database e importa os data frames para essa base. Além disso, nos permite fazer alterações e consultas em uma base de dados de forma rápida e prática.

Principal argumento da função sqldf(): Na nossa análise, usaremos apenas o argumento x.

No argumento x conseguimos colocar os comandos para fazer a análise.

Alguns comandos chaves:

  • Select - Seleciona as colunas.

  • From - Indica qual base iremos usar.

  • Limit - Indica o número de linhas na pré-visualização.

  • As - Permite mudar o nome das colunas.

  • Count/Distinct - Nos mostra o número de linhas.

  • Where - Acrescenta critérios na hora da pesquisa.

  • Group by - Permite agrupar os resultados.

  • Order by - Ordena a base de dados de acordo com a coluna indicada.

  • Having - Seleciona dados a partir de um critério definido.

  • Case When … Then - Permite criar condições para agrupamentos.

  • Sum/AVG/Max/Min - Realiza operações matemáticas.

help("sqldf")
sqldf(x, stringsAsFactors = FALSE,
   row.names = FALSE, envir = parent.frame(), 
   method = getOption("sqldf.method"),
   file.format = list(), dbname, drv = getOption("sqldf.driver"), 
   user, password = "", host = "localhost", port,
   dll = getOption("sqldf.dll"), connection = getOption("sqldf.connection"),
   verbose = isTRUE(getOption("sqldf.verbose")))

x -> string de caracteres representando a instrução SQL que será executada.

Comandos

Select + From + Limit:

O primeiro passo para começar a análise é selecionar a base de dados. Usamos o comando SELECT para selecionar as colunas interessadas (note que usamos “*”, visto que queremos todas). Usamos também o comando FROM para indicar o nome da nossa base. Estamos usando o comando LIMIT apenas para uma pré-visualização mais organizada.

data("airquality")
library(sqldf)
sqldf('SELECT * FROM airquality LIMIT 10')
   Ozone Solar.R Wind Temp Month Day
1     41     190  7.4   67     5   1
2     36     118  8.0   72     5   2
3     12     149 12.6   74     5   3
4     18     313 11.5   62     5   4
5     NA      NA 14.3   56     5   5
6     28      NA 14.9   66     5   6
7     23     299  8.6   65     5   7
8     19      99 13.8   59     5   8
9      8      19 20.1   61     5   9
10    NA     194  8.6   69     5  10
sqldf('SELECT Ozone FROM airquality LIMIT 10')
   Ozone
1     41
2     36
3     12
4     18
5     NA
6     28
7     23
8     19
9      8
10    NA

As:

Se desajarmos mudar o nome da variável fazemos isso com o comando AS.

sqldf('SELECT Month AS Mês, Day AS Dia FROM airquality LIMIT 10')
   Mês Dia
1    5   1
2    5   2
3    5   3
4    5   4
5    5   5
6    5   6
7    5   7
8    5   8
9    5   9
10   5  10

Where:

Vamos supor que desejamos acrescentar alguns critérios na pesquisa. Podemos fazer isso através do comando WHERE. Dentro desse comando temos diversos métodos de comparação, como And/Or, Between X and Y, In/Not in, que podem ser vistos nos exemplos abaixo:

And/Or:

Adiciona argumentos para análise.

sqldf('SELECT * FROM airquality WHERE Wind > 10 AND MONTH = 6 LIMIT 10')
   Ozone Solar.R Wind Temp Month Day
1     NA     242 16.1   67     6   3
2     NA     264 14.3   79     6   6
3     71     291 13.8   90     6   9
4     39     323 11.5   87     6  10
5     NA     259 10.9   93     6  11
6     NA     332 13.8   80     6  14
7     NA     322 11.5   79     6  15
8     21     191 14.9   77     6  16
9     37     284 20.7   72     6  17
10    12     120 11.5   73     6  19
Between X and Y:

Cria intervalos.

sqldf('SELECT * FROM airquality WHERE Month BETWEEN 5 AND 8 LIMIT 10')
   Ozone Solar.R Wind Temp Month Day
1     41     190  7.4   67     5   1
2     36     118  8.0   72     5   2
3     12     149 12.6   74     5   3
4     18     313 11.5   62     5   4
5     NA      NA 14.3   56     5   5
6     28      NA 14.9   66     5   6
7     23     299  8.6   65     5   7
8     19      99 13.8   59     5   8
9      8      19 20.1   61     5   9
10    NA     194  8.6   69     5  10
In / Not in:

Seleciona grupos de observação de acordo com um critério definido.

sqldf('SELECT * FROM airquality WHERE Month NOT IN (5,6,7,8) LIMIT 10')
   Ozone Solar.R Wind Temp Month Day
1     96     167  6.9   91     9   1
2     78     197  5.1   92     9   2
3     73     183  2.8   93     9   3
4     91     189  4.6   93     9   4
5     47      95  7.4   87     9   5
6     32      92 15.5   84     9   6
7     20     252 10.9   80     9   7
8     23     220 10.3   78     9   8
9     21     230 10.9   75     9   9
10    24     259  9.7   73     9  10

Order By:

Se quisermos ordenar de acordo com o valor da variável indicada (ASC = Ordem Crescente ; DESC = Ordem Decrescente) usamos o comando ORDER BY.

sqldf('SELECT * FROM airquality ORDER BY Wind ASC LIMIT 10')
   Ozone Solar.R Wind Temp Month Day
1     NA      59  1.7   76     6  22
2    118     225  2.3   94     8  29
3     73     183  2.8   93     9   3
4    168     238  3.4   81     8  25
5    122     255  4.0   89     8   7
6    135     269  4.1   84     7   1
7     NA      91  4.6   76     6  23
8     64     175  4.6   83     7   5
9     66      NA  4.6   87     8   6
10    91     189  4.6   93     9   4

Count + Distinct:

Mostra o número de linhas que não possuem NA. O comando COUNT nos mostra todas as linhas que não possuem valores NA. Entretanto o comando DISTINCT nos mostra o número de linhas que nao se repetem.

sqldf('SELECT COUNT(Ozone) AS Num_Linhas FROM airquality')
  Num_Linhas
1        116
OZONE= sqldf('SELECT DISTINCT(Ozone) FROM airquality')
dim(OZONE)
[1] 68  1
head(OZONE, n = 5)
  Ozone
1    41
2    36
3    12
4    18
5    NA

Group by:

Se quisermos agrupar os resultados usamos o comando GROUP BY.

sqldf('SELECT Month, COUNT(*) FROM airquality GROUP BY Month LIMIT 10')
  Month COUNT(*)
1     5       31
2     6       30
3     7       31
4     8       31
5     9       30

Having:

Se quisermos selecionar os dados a partir de um critério definido, como por exemplo os meses que possuem mais de 30 dias, usamos o comando HAVING.

sqldf('SELECT * FROM airquality GROUP BY Month  HAVING COUNT(Day) > 30 LIMIT 10 ')
  Ozone Solar.R Wind Temp Month Day
1    41     190  7.4   67     5   1
2   135     269  4.1   84     7   1
3    39      83  6.9   81     8   1

Case when… Then:

Para criar condições para agrupamentos podemos usar o comando CASE WHEN…THEN.

summary(airquality$Temp)
   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
  56.00   72.00   79.00   77.88   85.00   97.00 
sqldf('SELECT Temp, Month, Day, 
      CASE WHEN Temp < 72 THEN " Está entre o mínimo e 1 quartil"
      WHEN Temp BETWEEN 72 AND 79 THEN "Está entre o 1 quartil e mediana"
      WHEN Temp  BETWEEN 79 AND 85 THEN "Entre mediana e 3 quartil"
      ELSE "Maior que o 3 quartil" END
      AS Localizacao
      FROM airquality
      LIMIT 10')
   Temp Month Day                      Localizacao
1    67     5   1  Está entre o mínimo e 1 quartil
2    72     5   2 Está entre o 1 quartil e mediana
3    74     5   3 Está entre o 1 quartil e mediana
4    62     5   4  Está entre o mínimo e 1 quartil
5    56     5   5  Está entre o mínimo e 1 quartil
6    66     5   6  Está entre o mínimo e 1 quartil
7    65     5   7  Está entre o mínimo e 1 quartil
8    59     5   8  Está entre o mínimo e 1 quartil
9    61     5   9  Está entre o mínimo e 1 quartil
10   69     5  10  Está entre o mínimo e 1 quartil

Max / Min:

Os comandos MAX/MIN retornam os valores máximos ou mínimos de determinada variável.

sqldf('SELECT MIN(Temp) AS Mínimo,MAX(Temp) AS Máximo FROM airquality')
  Mínimo Máximo
1     56     97

Sum:

O comando SUM determina a soma dos valores de determinada variável.

sqldf('SELECT SUM(Temp) AS Total FROM airquality')
  Total
1 11916

Avg:

O comando AVG determina a média de uma coluna.

sqldf('SELECT AVG(Temp) AS Média FROM airquality')
     Média
1 77.88235

Variance:

O comando VARIANCE determina a variância de uma coluna.

sqldf('SELECT VARIANCE(Temp) AS Var FROM airquality')
       Var
1 89.59133

Stdev:

O comando STDEV determina o desvio padrão de uma coluna.

sqldf('SELECT STDEV(Temp) AS DP FROM airquality')
       DP
1 9.46527

Median:

O comando MEDIAN determina a mediana de uma coluna.

sqldf('SELECT Median(Temp) AS Mediana FROM airquality')
  Mediana
1      79