Handout_Last

Good afternoon all! First let us find out what our working directory is.

getwd()

[1] "C:/Users/antho/OneDrive/Documents/School/4.DataSecurity&Governance"

Today we are going to query the table quiz2 by using Transact-SQL inside the R chunks. First, we are going to need to install the package sqldf.

#install.packages("sqldf")

library(sqldf)

Above we install and load the sqldf package.

Then we upload our dataset quiz 2 to the data1 dataframe

data1<-read.csv("quiz2.csv",header = TRUE,sep = ",")

Then we do some eda with a statistical summary

summary(data1)

  subregion            region               pop              income          ipaddr           ufo2010      
 Length:29          Length:29          Min.   :   2311   Min.   :26784   Min.   :    637   Min.   :  0.00  
 Class :character   Class :character   1st Qu.:  19005   1st Qu.:37970   1st Qu.:  12294   1st Qu.:  0.00  
 Mode  :character   Mode  :character   Median :  32122   Median :41595   Median :  30418   Median :  2.00  
                                       Mean   : 135940   Mean   :43858   Mean   : 440130   Mean   : 16.66  
                                       3rd Qu.: 101482   3rd Qu.:47469   3rd Qu.: 102104   3rd Qu.:  9.00  
                                       Max.   :1554720   Max.   :70821   Max.   :5394949   Max.   :169.00  
   infections  
 Min.   :  39  
 1st Qu.: 123  
 Median : 245  
 Mean   :1117  
 3rd Qu.: 672  
 Max.   :6781  

Let us run a query that returns every single record from the table data1. We use the * in the sql script to signify all columns and the FROM argument to pull it designate the data1 data frame as the source.

sqldf("SELECT * FROM data1")

Now I would like you to create a query that returns every single record (row) for the fields region and population.

Let us now create a query that returns the first 10 records of the data1 table.

sqldf('SELECT * FROM data1 LIMIT 10')

sqldf("SELECT * FROM data1 ORDER BY infections DESC  LIMIT 5")

Interpret the output of the query ran above

It shows everything from the top 5 records who have the highest infections values, organized by the most to least infections.

run a similar query (limit 7) including the attributes ipadddr , ufo2010 ,and infections.

sqldf("SELECT region, infections, ufo2010 FROM data1 ORDER BY infections DESC  LIMIT 7")

sqldf('SELECT region,infections FROM data1 WHERE infections > 1000 order by 2 desc')

Above Shows the region and infections where the infections are greater than 1,000 and then organize those records by infections from greatest to least.

#Now I would like you to inspect (create a query) income,region and population when the condition is income>=50,000. Make comments.

we can see that our querys condidtions requires the records that have income higher or equal to 50,000 and also for infections to be greater than 2000 if not than the record must have population greater than 100,000. We can see that this only produces 9 records. Alameda, California with the highest population, income, infection, and ufo sighting.

sqldf('SELECT * FROM data1 WHERE (income > 50000 AND infections > 2000) OR pop > 100000')

Interpret the query ran above and create a query that recreates a similar case scenario (one that is critical for the success of this mini-project).

sqldf('SELECT * FROM data1 WHERE (income > 45000 AND infections > 800) OR pop > 80000')

Let us run a query that returns every region and population with a number of infections between 1000 and 7000.

sqldf('SELECT region,pop FROM data1 WHERE infections between  1000 and 7000')

Interpret the query below and list a hypothetical case scenario in which this query would be useful.

This shows us the regions and population where infections are above 1000 none of our infections values passes 7000 so we can use that as our limit

sqldf('SELECT * FROM data1 WHERE subregion LIKE "A%"')

Last,let us find the average number of infections in our table.

sqldf("SELECT AVG(infections) FROM data1")

We see that the average infections is close to the threshold we used in the 1000-7000 infections table we did above. Showing that that table displays the population of regions with above average infections.

Let us inspect the new table defined below data2.

data2<-read.csv("finalexam.csv",header = TRUE,sep = ",")

Above we import the final exam csv with column headers Below we display the top six records

head(data2)

Notice it is similar to our previous dataset except it doesnt have subregion and it is region field has been changed to a different classification of three classes East, West, and Central.

Let us apply summary to data2 and examine the output.

summary(data2)

    region               pop              income          ipaddr           ufo2010         infections  
 Length:29          Min.   :   2311   Min.   :26784   Min.   :    637   Min.   :  0.00   Min.   :  39  
 Class :character   1st Qu.:  19005   1st Qu.:37970   1st Qu.:  12294   1st Qu.:  0.00   1st Qu.: 123  
 Mode  :character   Median :  32122   Median :41595   Median :  30418   Median :  2.00   Median : 245  
                    Mean   : 135940   Mean   :43858   Mean   : 440130   Mean   : 16.66   Mean   :1117  
                    3rd Qu.: 101482   3rd Qu.:47469   3rd Qu.: 102104   3rd Qu.:  9.00   3rd Qu.: 672  
                    Max.   :1554720   Max.   :70821   Max.   :5394949   Max.   :169.00   Max.   :6781  

summary(data1)

  subregion            region               pop              income          ipaddr           ufo2010      
 Length:29          Length:29          Min.   :   2311   Min.   :26784   Min.   :    637   Min.   :  0.00  
 Class :character   Class :character   1st Qu.:  19005   1st Qu.:37970   1st Qu.:  12294   1st Qu.:  0.00  
 Mode  :character   Mode  :character   Median :  32122   Median :41595   Median :  30418   Median :  2.00  
                                       Mean   : 135940   Mean   :43858   Mean   : 440130   Mean   : 16.66  
                                       3rd Qu.: 101482   3rd Qu.:47469   3rd Qu.: 102104   3rd Qu.:  9.00  
                                       Max.   :1554720   Max.   :70821   Max.   :5394949   Max.   :169.00  
   infections  
 Min.   :  39  
 1st Qu.: 123  
 Median : 245  
 Mean   :1117  
 3rd Qu.: 672  
 Max.   :6781  

After comparing the summary of data2 to data1 We can see that the values havent changed from the previous data set, except for what we know of the region column.

Next, I would like you to complete the following steps:

1-Run a query that returns full details for every record from the west region.

sqldf('SELECT * FROM data2 WHERE region="West"')

NA

Here we are showing only records in the west region with all of their attributes.We can see the top value (6781) which we know as california, amongst those 7 records

2-Run a query that returns full details for every record from the east region where the number of infections is greater than 1500.

sqldf('SELECT * FROM data2 WHERE region="East" and infections >1500')

NA

we can see that there are only 3 records with infections over 1,500 on the east coast, all their attributes are display with them.

3-Run a few queries that can potentially help us to make the point that:

a.Region affects the number of infections

sqldf('SELECT region, infections FROM data2 group by region')

Above we focus on each group of regions and infections. West has the most infections followed by east. We could have orderd by infections descending.

b.Population affects the number of infections

sqldf('SELECT pop, infections FROM data2 order by pop desc')

Here we focus on the top populations to the least populations along side their infection count

c.Income affects the number of infections

sqldf('SELECT income, infections FROM data2 order by income desc')

Here we focus on the top income to the least income along side their infection count

d.ufo2010 affects the number of infections

sqldf('SELECT ufo2010, infections FROM data2 order by ufo2010 desc')

Here we focus on the top ufo sighting to the least ufo sighting along side their infection count

e. The different interactions of region and income, and income and population affects the number of infections.

sqldf('SELECT region, income FROM data2 group by region order by income desc')

Now we see how the different regions group in income from highest icome to lowest income. we can see west leads the regions in this as well.

sqldf('SELECT pop, income FROM data2 order by income desc')

Finally we see the populations with the most income all the way to the least.

Explain every single query you include in this part of the handout.

Lastly

Lets re-run all of these but order the records by the infections column in descending order so we can see them consistent as the other values change around it.

sqldf('SELECT region, infections FROM data2 group by region order by infections desc')

sqldf('SELECT pop, infections FROM data2 order by infections desc')

sqldf('SELECT income, infections FROM data2 order by infections desc')

sqldf('SELECT ufo2010, infections FROM data2 order by infections desc')

We can see that re-runing it this way better informs us on the top infections and least infections. Giving us a better sense of the distribution of other fields against the infections feature.