sqldf
Package for dataframes¶# install.packages("babynames", repos = "http://cran.us.r-project.org") # for dataset
# install.packages("sqldf", repos = "http://cran.us.r-project.org") # for package
package 'babynames' successfully unpacked and MD5 sums checked The downloaded binary packages are in C:\Users\User\AppData\Local\Temp\Rtmp2hVGhZ\downloaded_packages
also installing the dependencies 'gsubfn', 'proto', 'chron'
package 'gsubfn' successfully unpacked and MD5 sums checked package 'proto' successfully unpacked and MD5 sums checked package 'chron' successfully unpacked and MD5 sums checked package 'sqldf' successfully unpacked and MD5 sums checked The downloaded binary packages are in C:\Users\User\AppData\Local\Temp\Rtmp2hVGhZ\downloaded_packages
library(sqldf)
library(babynames)
mydata <- babynames
str (mydata)
Classes 'tbl_df', 'tbl' and 'data.frame': 1858689 obs. of 5 variables: $ year: num 1880 1880 1880 1880 1880 1880 1880 1880 1880 1880 ... $ sex : chr "F" "F" "F" "F" ... $ name: chr "Mary" "Anna" "Emma" "Elizabeth" ... $ n : int 7065 2604 2003 1939 1746 1578 1472 1414 1320 1288 ... $ prop: num 0.0724 0.0267 0.0205 0.0199 0.0179 ...
#number of rows in this data.
sqldf("select count(*) from mydata")
count(*) |
---|
1858689 |
# we’ll look at the first 10 rows.
sqldf("select * from mydata limit 10")
# To select some variables instead of all
sqldf("select year, sex, name from mydata limit 10")
# To rename a column in the output table, we can use the AS command
sqldf("select year,sex as 'Gender' from mydata limit 10")
year | sex | name | n | prop |
---|---|---|---|---|
1880 | F | Mary | 7065 | 0.07238433 |
1880 | F | Anna | 2604 | 0.02667923 |
1880 | F | Emma | 2003 | 0.02052170 |
1880 | F | Elizabeth | 1939 | 0.01986599 |
1880 | F | Minnie | 1746 | 0.01788861 |
1880 | F | Margaret | 1578 | 0.01616737 |
1880 | F | Ida | 1472 | 0.01508135 |
1880 | F | Alice | 1414 | 0.01448711 |
1880 | F | Bertha | 1320 | 0.01352404 |
1880 | F | Sarah | 1288 | 0.01319618 |
year | sex | name |
---|---|---|
1880 | F | Mary |
1880 | F | Anna |
1880 | F | Emma |
1880 | F | Elizabeth |
1880 | F | Minnie |
1880 | F | Margaret |
1880 | F | Ida |
1880 | F | Alice |
1880 | F | Bertha |
1880 | F | Sarah |
year | Gender |
---|---|
1880 | F |
1880 | F |
1880 | F |
1880 | F |
1880 | F |
1880 | F |
1880 | F |
1880 | F |
1880 | F |
1880 | F |
#filtering data
sqldf("select year,name, sex as 'Gender' from mydata where sex == 'F' limit 20")
sqldf("select * from mydata where prop > 0.05 limit 20")
sqldf("select * from mydata where sex != 'F' ")
sqldf("select year,name,4*prop as 'final_prop' from mydata where prop <= 0.40 limit 10")
year | name | Gender |
---|---|---|
1880 | Mary | F |
1880 | Anna | F |
1880 | Emma | F |
1880 | Elizabeth | F |
1880 | Minnie | F |
1880 | Margaret | F |
1880 | Ida | F |
1880 | Alice | F |
1880 | Bertha | F |
1880 | Sarah | F |
1880 | Annie | F |
1880 | Clara | F |
1880 | Ella | F |
1880 | Florence | F |
1880 | Cora | F |
1880 | Martha | F |
1880 | Laura | F |
1880 | Nellie | F |
1880 | Grace | F |
1880 | Carrie | F |
year | sex | name | n | prop |
---|---|---|---|---|
1880 | F | Mary | 7065 | 0.07238433 |
1880 | M | John | 9655 | 0.08154630 |
1880 | M | William | 9531 | 0.08049899 |
1880 | M | James | 5927 | 0.05005954 |
1881 | F | Mary | 6919 | 0.06999140 |
1881 | M | John | 8769 | 0.08098299 |
1881 | M | William | 8524 | 0.07872038 |
1881 | M | James | 5441 | 0.05024843 |
1882 | F | Mary | 8148 | 0.07042594 |
1882 | M | John | 9557 | 0.07831617 |
1882 | M | William | 9298 | 0.07619375 |
1883 | F | Mary | 8012 | 0.06673386 |
1883 | M | John | 8894 | 0.07907324 |
1883 | M | William | 8387 | 0.07456569 |
1884 | F | Mary | 9217 | 0.06699083 |
1884 | M | John | 9388 | 0.07648751 |
1884 | M | William | 8897 | 0.07248715 |
1885 | F | Mary | 9128 | 0.06430479 |
1885 | M | John | 8756 | 0.07551791 |
1885 | M | William | 8044 | 0.06937712 |
year | sex | name | n | prop |
---|---|---|---|---|
1880 | M | John | 9655 | 0.081546297 |
1880 | M | William | 9531 | 0.080498991 |
1880 | M | James | 5927 | 0.050059544 |
1880 | M | Charles | 5348 | 0.045169300 |
1880 | M | George | 5126 | 0.043294285 |
1880 | M | Frank | 3242 | 0.027381988 |
1880 | M | Joseph | 2632 | 0.022229917 |
1880 | M | Thomas | 2534 | 0.021402208 |
1880 | M | Henry | 2444 | 0.020642066 |
1880 | M | Robert | 2415 | 0.020397132 |
1880 | M | Edward | 2364 | 0.019966385 |
1880 | M | Harry | 2152 | 0.018175829 |
1880 | M | Walter | 1755 | 0.014822760 |
1880 | M | Arthur | 1599 | 0.013505182 |
1880 | M | Fred | 1569 | 0.013251801 |
1880 | M | Albert | 1493 | 0.012609904 |
1880 | M | Samuel | 1024 | 0.008648722 |
1880 | M | David | 869 | 0.007339589 |
1880 | M | Louis | 828 | 0.006993302 |
1880 | M | Joe | 731 | 0.006174039 |
1880 | M | Charlie | 730 | 0.006165593 |
1880 | M | Clarence | 730 | 0.006165593 |
1880 | M | Richard | 728 | 0.006148701 |
1880 | M | Andrew | 644 | 0.005439235 |
1880 | M | Daniel | 643 | 0.005430789 |
1880 | M | Ernest | 615 | 0.005194301 |
1880 | M | Will | 588 | 0.004966258 |
1880 | M | Jesse | 569 | 0.004805784 |
1880 | M | Oscar | 544 | 0.004594633 |
1880 | M | Lewis | 517 | 0.004366591 |
... | ... | ... | ... | ... |
2015 | M | Zavon | 5 | 2.466855e-06 |
2015 | M | Zay | 5 | 2.466855e-06 |
2015 | M | Zaylor | 5 | 2.466855e-06 |
2015 | M | Zayquan | 5 | 2.466855e-06 |
2015 | M | Zecharia | 5 | 2.466855e-06 |
2015 | M | Zecharya | 5 | 2.466855e-06 |
2015 | M | Zee | 5 | 2.466855e-06 |
2015 | M | Zeev | 5 | 2.466855e-06 |
2015 | M | Zeik | 5 | 2.466855e-06 |
2015 | M | Zekariah | 5 | 2.466855e-06 |
2015 | M | Zepplin | 5 | 2.466855e-06 |
2015 | M | Zerek | 5 | 2.466855e-06 |
2015 | M | Zeyd | 5 | 2.466855e-06 |
2015 | M | Zhi | 5 | 2.466855e-06 |
2015 | M | Ziel | 5 | 2.466855e-06 |
2015 | M | Zihan | 5 | 2.466855e-06 |
2015 | M | Zihao | 5 | 2.466855e-06 |
2015 | M | Ziheng | 5 | 2.466855e-06 |
2015 | M | Zimere | 5 | 2.466855e-06 |
2015 | M | Ziyi | 5 | 2.466855e-06 |
2015 | M | Ziyu | 5 | 2.466855e-06 |
2015 | M | Zoel | 5 | 2.466855e-06 |
2015 | M | Zohar | 5 | 2.466855e-06 |
2015 | M | Zolton | 5 | 2.466855e-06 |
2015 | M | Zyah | 5 | 2.466855e-06 |
2015 | M | Zykell | 5 | 2.466855e-06 |
2015 | M | Zyking | 5 | 2.466855e-06 |
2015 | M | Zykir | 5 | 2.466855e-06 |
2015 | M | Zyrus | 5 | 2.466855e-06 |
2015 | M | Zyus | 5 | 2.466855e-06 |
year | name | final_prop |
---|---|---|
1880 | Mary | 0.28953731 |
1880 | Anna | 0.10671694 |
1880 | Emma | 0.08208680 |
1880 | Elizabeth | 0.07946396 |
1880 | Minnie | 0.07155444 |
1880 | Margaret | 0.06466948 |
1880 | Ida | 0.06032540 |
1880 | Alice | 0.05794844 |
1880 | Bertha | 0.05409614 |
1880 | Sarah | 0.05278472 |
sqldf("select * from mydata order by year desc limit 20") #order by 1 condition
sqldf("select * from mydata order by year desc,n desc limit 20") #order by 2 conditions
sqldf("select * from mydata order by name limit 20") #order alphabetically
year | sex | name | n | prop |
---|---|---|---|---|
2015 | F | Emma | 20355 | 0.010518787 |
2015 | F | Olivia | 19553 | 0.010104340 |
2015 | F | Sophia | 17327 | 0.008954018 |
2015 | F | Ava | 16286 | 0.008416063 |
2015 | F | Isabella | 15504 | 0.008011952 |
2015 | F | Mia | 14820 | 0.007658483 |
2015 | F | Abigail | 12311 | 0.006361916 |
2015 | F | Emily | 11727 | 0.006060124 |
2015 | F | Charlotte | 11332 | 0.005856001 |
2015 | F | Harper | 10241 | 0.005292208 |
2015 | F | Madison | 10038 | 0.005187305 |
2015 | F | Amelia | 9795 | 0.005061730 |
2015 | F | Elizabeth | 9656 | 0.004989900 |
2015 | F | Sofia | 9650 | 0.004986799 |
2015 | F | Evelyn | 9313 | 0.004812649 |
2015 | F | Avery | 9298 | 0.004804897 |
2015 | F | Chloe | 7884 | 0.004074189 |
2015 | F | Ella | 7852 | 0.004057653 |
2015 | F | Grace | 7589 | 0.003921743 |
2015 | F | Victoria | 7575 | 0.003914508 |
year | sex | name | n | prop |
---|---|---|---|---|
2015 | F | Emma | 20355 | 0.010518787 |
2015 | F | Olivia | 19553 | 0.010104340 |
2015 | M | Noah | 19511 | 0.009626163 |
2015 | M | Liam | 18281 | 0.009019316 |
2015 | F | Sophia | 17327 | 0.008954018 |
2015 | M | Mason | 16535 | 0.008157891 |
2015 | F | Ava | 16286 | 0.008416063 |
2015 | M | Jacob | 15816 | 0.007803157 |
2015 | M | William | 15809 | 0.007799703 |
2015 | F | Isabella | 15504 | 0.008011952 |
2015 | M | Ethan | 14991 | 0.007396126 |
2015 | F | Mia | 14820 | 0.007658483 |
2015 | M | James | 14705 | 0.007255022 |
2015 | M | Alexander | 14460 | 0.007134146 |
2015 | M | Michael | 14321 | 0.007065567 |
2015 | M | Benjamin | 13608 | 0.006713793 |
2015 | M | Elijah | 13511 | 0.006665936 |
2015 | M | Daniel | 13408 | 0.006615119 |
2015 | M | Aiden | 13378 | 0.006600318 |
2015 | M | Logan | 12862 | 0.006345739 |
year | sex | name | n | prop |
---|---|---|---|---|
2007 | M | Aaban | 5 | 2.259872e-06 |
2009 | M | Aaban | 6 | 2.833201e-06 |
2010 | M | Aaban | 9 | 4.388578e-06 |
2011 | M | Aaban | 11 | 5.427020e-06 |
2012 | M | Aaban | 11 | 5.435940e-06 |
2013 | M | Aaban | 14 | 6.952863e-06 |
2014 | M | Aaban | 16 | 7.843929e-06 |
2015 | M | Aaban | 15 | 7.400566e-06 |
2011 | F | Aabha | 7 | 3.620673e-06 |
2012 | F | Aabha | 5 | 2.585247e-06 |
2014 | F | Aabha | 9 | 4.621184e-06 |
2015 | F | Aabha | 7 | 3.617367e-06 |
2003 | M | Aabid | 5 | 2.381387e-06 |
2008 | F | Aabriella | 5 | 2.403906e-06 |
2014 | F | Aabriella | 5 | 2.567324e-06 |
2015 | F | Aabriella | 5 | 2.583834e-06 |
2015 | F | Aada | 5 | 2.583834e-06 |
1987 | M | Aadam | 5 | 2.565337e-06 |
1988 | M | Aadam | 5 | 2.498923e-06 |
1993 | M | Aadam | 7 | 3.390149e-06 |
sqldf("select * from mydata where name like 'Ben%' ") #name starts with Ben
sqldf("select * from mydata where name like '%man'limit 30") #name ends with man
sqldf("select * from mydata where name like '%man%' ") #name must contain man
sqldf("select * from mydata where name in ('Coleman','Benjamin','Bennie')") #using IN
sqldf("select * from mydata where year between 2000 and 2014") #using BETWEEN
year | sex | name | n | prop |
---|---|---|---|---|
1880 | F | Bennie | 8 | 8.196385e-05 |
1880 | F | Bena | 6 | 6.147289e-05 |
1880 | M | Benjamin | 490 | 4.138548e-03 |
1880 | M | Ben | 305 | 2.576035e-03 |
1880 | M | Benjiman | 28 | 2.364885e-04 |
1880 | M | Bennie | 26 | 2.195964e-04 |
1880 | M | Bennett | 15 | 1.266903e-04 |
1880 | M | Benjamine | 13 | 1.097982e-04 |
1880 | M | Benjaman | 12 | 1.013522e-04 |
1880 | M | Benton | 10 | 8.446017e-05 |
1880 | M | Benito | 7 | 5.912212e-05 |
1881 | F | Bennie | 9 | 9.104244e-05 |
1881 | M | Benjamin | 481 | 4.442105e-03 |
1881 | M | Ben | 289 | 2.668957e-03 |
1881 | M | Bennie | 32 | 2.955246e-04 |
1881 | M | Benjiman | 21 | 1.939381e-04 |
1881 | M | Benjamine | 17 | 1.569975e-04 |
1881 | M | Bennett | 14 | 1.292920e-04 |
1881 | M | Benton | 11 | 1.015866e-04 |
1881 | M | Benjaman | 10 | 9.235145e-05 |
1882 | F | Bennie | 7 | 6.050339e-05 |
1882 | M | Benjamin | 478 | 3.917037e-03 |
1882 | M | Ben | 334 | 2.737009e-03 |
1882 | M | Benjiman | 35 | 2.868124e-04 |
1882 | M | Bennie | 33 | 2.704231e-04 |
1882 | M | Bennett | 14 | 1.147249e-04 |
1882 | M | Benjaman | 12 | 9.833567e-05 |
1882 | M | Benton | 11 | 9.014103e-05 |
1882 | M | Benjamine | 9 | 7.375175e-05 |
1882 | M | Benedict | 8 | 6.555711e-05 |
... | ... | ... | ... | ... |
2015 | M | Benji | 55 | 2.713541e-05 |
2015 | M | Bennet | 54 | 2.664204e-05 |
2015 | M | Bennie | 52 | 2.565530e-05 |
2015 | M | Benzion | 33 | 1.628125e-05 |
2015 | M | Benyamin | 32 | 1.578787e-05 |
2015 | M | Bensen | 31 | 1.529450e-05 |
2015 | M | Bentzion | 28 | 1.381439e-05 |
2015 | M | Benuel | 27 | 1.332102e-05 |
2015 | M | Benjiman | 22 | 1.085416e-05 |
2015 | M | Beniah | 14 | 6.907195e-06 |
2015 | M | Benjamen | 13 | 6.413824e-06 |
2015 | M | Bensyn | 13 | 6.413824e-06 |
2015 | M | Benz | 13 | 6.413824e-06 |
2015 | M | Benett | 11 | 5.427082e-06 |
2015 | M | Benjamyn | 11 | 5.427082e-06 |
2015 | M | Benno | 11 | 5.427082e-06 |
2015 | M | Beni | 9 | 4.440340e-06 |
2015 | M | Bence | 8 | 3.946969e-06 |
2015 | M | Bentleigh | 8 | 3.946969e-06 |
2015 | M | Bentli | 8 | 3.946969e-06 |
2015 | M | Benedikt | 7 | 3.453597e-06 |
2015 | M | Benjimen | 7 | 3.453597e-06 |
2015 | M | Benoit | 7 | 3.453597e-06 |
2015 | M | Bentlie | 7 | 3.453597e-06 |
2015 | M | Benjamine | 6 | 2.960226e-06 |
2015 | M | Bendrick | 5 | 2.466855e-06 |
2015 | M | Benecio | 5 | 2.466855e-06 |
2015 | M | Benen | 5 | 2.466855e-06 |
2015 | M | Benjerman | 5 | 2.466855e-06 |
2015 | M | Bentlei | 5 | 2.466855e-06 |
year | sex | name | n | prop |
---|---|---|---|---|
1880 | M | Herman | 347 | 2.930768e-03 |
1880 | M | Norman | 102 | 8.614938e-04 |
1880 | M | Sherman | 54 | 4.560849e-04 |
1880 | M | Lyman | 31 | 2.618265e-04 |
1880 | M | Benjiman | 28 | 2.364885e-04 |
1880 | M | Freeman | 26 | 2.195964e-04 |
1880 | M | Benjaman | 12 | 1.013522e-04 |
1880 | M | Coleman | 12 | 1.013522e-04 |
1880 | M | Truman | 12 | 1.013522e-04 |
1880 | M | Tillman | 9 | 7.601416e-05 |
1880 | M | Thurman | 8 | 6.756814e-05 |
1880 | M | Harman | 7 | 5.912212e-05 |
1880 | M | Hyman | 6 | 5.067610e-05 |
1880 | M | Soloman | 6 | 5.067610e-05 |
1880 | M | Firman | 5 | 4.223009e-05 |
1881 | M | Herman | 304 | 2.807484e-03 |
1881 | M | Norman | 95 | 8.773388e-04 |
1881 | M | Sherman | 47 | 4.340518e-04 |
1881 | M | Freeman | 25 | 2.308786e-04 |
1881 | M | Benjiman | 21 | 1.939381e-04 |
1881 | M | Lyman | 21 | 1.939381e-04 |
1881 | M | Truman | 20 | 1.847029e-04 |
1881 | M | Coleman | 16 | 1.477623e-04 |
1881 | M | Benjaman | 10 | 9.235145e-05 |
1881 | M | Soloman | 10 | 9.235145e-05 |
1881 | M | Furman | 7 | 6.464602e-05 |
1881 | M | Tilman | 7 | 6.464602e-05 |
1881 | M | Thurman | 6 | 5.541087e-05 |
1882 | M | Herman | 348 | 2.851734e-03 |
1882 | M | Norman | 107 | 8.768264e-04 |
year | sex | name | n | prop |
---|---|---|---|---|
1880 | F | Amanda | 241 | 2.469161e-03 |
1880 | F | Mandy | 34 | 3.483464e-04 |
1880 | F | Samantha | 21 | 2.151551e-04 |
1880 | F | Manda | 10 | 1.024548e-04 |
1880 | F | Manerva | 10 | 1.024548e-04 |
1880 | F | Manie | 10 | 1.024548e-04 |
1880 | F | Manervia | 5 | 5.122741e-05 |
1880 | F | Manuela | 5 | 5.122741e-05 |
1880 | M | Herman | 347 | 2.930768e-03 |
1880 | M | Norman | 102 | 8.614938e-04 |
1880 | M | Manuel | 75 | 6.334513e-04 |
1880 | M | Emanuel | 57 | 4.814230e-04 |
1880 | M | Sherman | 54 | 4.560849e-04 |
1880 | M | Lyman | 31 | 2.618265e-04 |
1880 | M | Benjiman | 28 | 2.364885e-04 |
1880 | M | Freeman | 26 | 2.195964e-04 |
1880 | M | Benjaman | 12 | 1.013522e-04 |
1880 | M | Coleman | 12 | 1.013522e-04 |
1880 | M | Truman | 12 | 1.013522e-04 |
1880 | M | Hermann | 9 | 7.601416e-05 |
1880 | M | Manford | 9 | 7.601416e-05 |
1880 | M | Tillman | 9 | 7.601416e-05 |
1880 | M | Manley | 8 | 6.756814e-05 |
1880 | M | Thurman | 8 | 6.756814e-05 |
1880 | M | Harman | 7 | 5.912212e-05 |
1880 | M | Armand | 6 | 5.067610e-05 |
1880 | M | Hyman | 6 | 5.067610e-05 |
1880 | M | Soloman | 6 | 5.067610e-05 |
1880 | M | Firman | 5 | 4.223009e-05 |
1880 | M | Manly | 5 | 4.223009e-05 |
... | ... | ... | ... | ... |
2015 | M | Mannie | 6 | 2.960226e-06 |
2015 | M | Mansoor | 6 | 2.960226e-06 |
2015 | M | Mansur | 6 | 2.960226e-06 |
2015 | M | Newman | 6 | 2.960226e-06 |
2015 | M | Noman | 6 | 2.960226e-06 |
2015 | M | Rhoman | 6 | 2.960226e-06 |
2015 | M | Selman | 6 | 2.960226e-06 |
2015 | M | Stedman | 6 | 2.960226e-06 |
2015 | M | Ammanuel | 5 | 2.466855e-06 |
2015 | M | Aryaman | 5 | 2.466855e-06 |
2015 | M | Benjerman | 5 | 2.466855e-06 |
2015 | M | Emmanual | 5 | 2.466855e-06 |
2015 | M | Ferman | 5 | 2.466855e-06 |
2015 | M | Firman | 5 | 2.466855e-06 |
2015 | M | Jackman | 5 | 2.466855e-06 |
2015 | M | Karmani | 5 | 2.466855e-06 |
2015 | M | Kayman | 5 | 2.466855e-06 |
2015 | M | Keimani | 5 | 2.466855e-06 |
2015 | M | Koleman | 5 | 2.466855e-06 |
2015 | M | Manaf | 5 | 2.466855e-06 |
2015 | M | Manhattan | 5 | 2.466855e-06 |
2015 | M | Manit | 5 | 2.466855e-06 |
2015 | M | Mansa | 5 | 2.466855e-06 |
2015 | M | Mantra | 5 | 2.466855e-06 |
2015 | M | Nouman | 5 | 2.466855e-06 |
2015 | M | Olman | 5 | 2.466855e-06 |
2015 | M | Romano | 5 | 2.466855e-06 |
2015 | M | Sukhman | 5 | 2.466855e-06 |
2015 | M | Tasman | 5 | 2.466855e-06 |
2015 | M | Yaman | 5 | 2.466855e-06 |
year | sex | name | n | prop |
---|---|---|---|---|
1880 | F | Bennie | 8 | 8.196385e-05 |
1880 | M | Benjamin | 490 | 4.138548e-03 |
1880 | M | Bennie | 26 | 2.195964e-04 |
1880 | M | Coleman | 12 | 1.013522e-04 |
1881 | F | Bennie | 9 | 9.104244e-05 |
1881 | M | Benjamin | 481 | 4.442105e-03 |
1881 | M | Bennie | 32 | 2.955246e-04 |
1881 | M | Coleman | 16 | 1.477623e-04 |
1882 | F | Bennie | 7 | 6.050339e-05 |
1882 | M | Benjamin | 478 | 3.917037e-03 |
1882 | M | Bennie | 33 | 2.704231e-04 |
1882 | M | Coleman | 18 | 1.475035e-04 |
1883 | F | Bennie | 6 | 4.997543e-05 |
1883 | M | Benjamin | 508 | 4.516439e-03 |
1883 | M | Bennie | 21 | 1.867032e-04 |
1883 | M | Coleman | 12 | 1.066875e-04 |
1884 | F | Bennie | 17 | 1.235591e-04 |
1884 | M | Benjamin | 509 | 4.147011e-03 |
1884 | M | Bennie | 51 | 4.155159e-04 |
1884 | M | Coleman | 14 | 1.140632e-04 |
1885 | F | Bennie | 14 | 9.862697e-05 |
1885 | M | Benjamin | 464 | 4.001863e-03 |
1885 | M | Bennie | 51 | 4.398599e-04 |
1885 | M | Coleman | 17 | 1.466200e-04 |
1886 | F | Bennie | 22 | 1.431025e-04 |
1886 | M | Benjamin | 463 | 3.889384e-03 |
1886 | M | Bennie | 43 | 3.612170e-04 |
1886 | M | Coleman | 11 | 9.240436e-05 |
1887 | F | Bennie | 10 | 6.434096e-05 |
1887 | M | Benjamin | 473 | 4.326945e-03 |
... | ... | ... | ... | ... |
2010 | F | Benjamin | 23 | 1.175357e-05 |
2010 | M | Benjamin | 12421 | 6.056726e-03 |
2010 | M | Coleman | 273 | 1.331202e-04 |
2010 | M | Bennie | 58 | 2.828195e-05 |
2011 | F | Benjamin | 23 | 1.189650e-05 |
2011 | F | Coleman | 7 | 3.620673e-06 |
2011 | M | Benjamin | 13051 | 6.438913e-03 |
2011 | M | Coleman | 221 | 1.090338e-04 |
2011 | M | Bennie | 53 | 2.614837e-05 |
2012 | F | Benjamin | 14 | 7.238692e-06 |
2012 | F | Bennie | 6 | 3.102297e-06 |
2012 | M | Benjamin | 12807 | 6.328917e-03 |
2012 | M | Coleman | 255 | 1.260150e-04 |
2012 | M | Bennie | 42 | 2.075541e-05 |
2013 | F | Benjamin | 19 | 9.890924e-06 |
2013 | F | Bennie | 9 | 4.685174e-06 |
2013 | M | Benjamin | 13460 | 6.684681e-03 |
2013 | M | Coleman | 252 | 1.251515e-04 |
2013 | M | Bennie | 53 | 2.632155e-05 |
2014 | F | Benjamin | 17 | 8.728902e-06 |
2014 | F | Bennie | 7 | 3.594254e-06 |
2014 | F | Coleman | 5 | 2.567324e-06 |
2014 | M | Benjamin | 13761 | 6.746269e-03 |
2014 | M | Coleman | 222 | 1.088345e-04 |
2014 | M | Bennie | 40 | 1.960982e-05 |
2015 | F | Benjamin | 18 | 9.301802e-06 |
2015 | F | Bennie | 6 | 3.100601e-06 |
2015 | M | Benjamin | 13608 | 6.713793e-03 |
2015 | M | Coleman | 248 | 1.223560e-04 |
2015 | M | Bennie | 52 | 2.565530e-05 |
year | sex | name | n | prop |
---|---|---|---|---|
2000 | F | Emily | 25953 | 0.013012538 |
2000 | F | Hannah | 23075 | 0.011569542 |
2000 | F | Madison | 19967 | 0.010011226 |
2000 | F | Ashley | 17997 | 0.009023491 |
2000 | F | Sarah | 17689 | 0.008869063 |
2000 | F | Alexis | 17627 | 0.008837977 |
2000 | F | Samantha | 17264 | 0.008655973 |
2000 | F | Jessica | 15705 | 0.007874308 |
2000 | F | Elizabeth | 15089 | 0.007565453 |
2000 | F | Taylor | 15077 | 0.007559436 |
2000 | F | Lauren | 14173 | 0.007106181 |
2000 | F | Alyssa | 13553 | 0.006795320 |
2000 | F | Kayla | 13311 | 0.006673984 |
2000 | F | Abigail | 13087 | 0.006561673 |
2000 | F | Brianna | 12874 | 0.006454877 |
2000 | F | Olivia | 12852 | 0.006443846 |
2000 | F | Emma | 12542 | 0.006288416 |
2000 | F | Megan | 11433 | 0.005732376 |
2000 | F | Grace | 11283 | 0.005657168 |
2000 | F | Victoria | 10922 | 0.005476166 |
2000 | F | Rachel | 10667 | 0.005348312 |
2000 | F | Anna | 10581 | 0.005305193 |
2000 | F | Sydney | 10242 | 0.005135222 |
2000 | F | Destiny | 9845 | 0.004936171 |
2000 | F | Morgan | 9502 | 0.004764194 |
2000 | F | Jennifer | 9384 | 0.004705031 |
2000 | F | Jasmine | 9092 | 0.004558625 |
2000 | F | Haley | 9068 | 0.004546592 |
2000 | F | Julia | 8764 | 0.004394170 |
2000 | F | Kaitlyn | 8759 | 0.004391663 |
... | ... | ... | ... | ... |
2014 | M | Zeid | 5 | 2.451228e-06 |
2014 | M | Zeidan | 5 | 2.451228e-06 |
2014 | M | Zekeriah | 5 | 2.451228e-06 |
2014 | M | Zenas | 5 | 2.451228e-06 |
2014 | M | Zephen | 5 | 2.451228e-06 |
2014 | M | Zerick | 5 | 2.451228e-06 |
2014 | M | Zhaiden | 5 | 2.451228e-06 |
2014 | M | Zhalen | 5 | 2.451228e-06 |
2014 | M | Zhayden | 5 | 2.451228e-06 |
2014 | M | Ziar | 5 | 2.451228e-06 |
2014 | M | Zichen | 5 | 2.451228e-06 |
2014 | M | Ziden | 5 | 2.451228e-06 |
2014 | M | Zierre | 5 | 2.451228e-06 |
2014 | M | Ziion | 5 | 2.451228e-06 |
2014 | M | Zijun | 5 | 2.451228e-06 |
2014 | M | Zilas | 5 | 2.451228e-06 |
2014 | M | Zirui | 5 | 2.451228e-06 |
2014 | M | Zivon | 5 | 2.451228e-06 |
2014 | M | Ziyah | 5 | 2.451228e-06 |
2014 | M | Ziyang | 5 | 2.451228e-06 |
2014 | M | Zmarion | 5 | 2.451228e-06 |
2014 | M | Zo | 5 | 2.451228e-06 |
2014 | M | Zoraiz | 5 | 2.451228e-06 |
2014 | M | Zyeir | 5 | 2.451228e-06 |
2014 | M | Zyel | 5 | 2.451228e-06 |
2014 | M | Zykeem | 5 | 2.451228e-06 |
2014 | M | Zymeer | 5 | 2.451228e-06 |
2014 | M | Zymiere | 5 | 2.451228e-06 |
2014 | M | Zyran | 5 | 2.451228e-06 |
2014 | M | Zyrin | 5 | 2.451228e-06 |
#multiple filters - and,or,not
sqldf("select * from mydata where year >= 1980 and prop <0.5")
sqldf("select * from mydata where year >= 1980 and prop <0.5 order by prop desc")
sqldf("select * from mydata where name != '%man%' or year > 2000") #no man in name
sqldf("select * from mydata where prop > 0.07 and year not between 2000 and 2014")
sqldf("select * from mydata where n > 10000 order by name desc ")
year | sex | name | n | prop |
---|---|---|---|---|
1980 | F | Jennifer | 58381 | 0.032794114 |
1980 | F | Amanda | 35819 | 0.020120456 |
1980 | F | Jessica | 33921 | 0.019054301 |
1980 | F | Melissa | 31632 | 0.017768511 |
1980 | F | Sarah | 25747 | 0.014462754 |
1980 | F | Heather | 19972 | 0.011218788 |
1980 | F | Nicole | 19918 | 0.011188455 |
1980 | F | Amy | 19832 | 0.011140146 |
1980 | F | Elizabeth | 19530 | 0.010970505 |
1980 | F | Michelle | 19121 | 0.010740759 |
1980 | F | Kimberly | 18498 | 0.010390804 |
1980 | F | Angela | 17968 | 0.010093089 |
1980 | F | Stephanie | 17841 | 0.010021750 |
1980 | F | Tiffany | 16227 | 0.009115125 |
1980 | F | Christina | 16139 | 0.009065693 |
1980 | F | Lisa | 15661 | 0.008797188 |
1980 | F | Rebecca | 15283 | 0.008584855 |
1980 | F | Crystal | 14257 | 0.008008525 |
1980 | F | Kelly | 13715 | 0.007704069 |
1980 | F | Erin | 13434 | 0.007546224 |
1980 | F | Laura | 12918 | 0.007256374 |
1980 | F | Amber | 12555 | 0.007052467 |
1980 | F | Rachel | 11623 | 0.006528939 |
1980 | F | Jamie | 11523 | 0.006472766 |
1980 | F | April | 11475 | 0.006445804 |
1980 | F | Mary | 11475 | 0.006445804 |
1980 | F | Sara | 11148 | 0.006262119 |
1980 | F | Andrea | 10452 | 0.005871158 |
1980 | F | Shannon | 9668 | 0.005430765 |
1980 | F | Megan | 9139 | 0.005133612 |
... | ... | ... | ... | ... |
2015 | M | Zavon | 5 | 2.466855e-06 |
2015 | M | Zay | 5 | 2.466855e-06 |
2015 | M | Zaylor | 5 | 2.466855e-06 |
2015 | M | Zayquan | 5 | 2.466855e-06 |
2015 | M | Zecharia | 5 | 2.466855e-06 |
2015 | M | Zecharya | 5 | 2.466855e-06 |
2015 | M | Zee | 5 | 2.466855e-06 |
2015 | M | Zeev | 5 | 2.466855e-06 |
2015 | M | Zeik | 5 | 2.466855e-06 |
2015 | M | Zekariah | 5 | 2.466855e-06 |
2015 | M | Zepplin | 5 | 2.466855e-06 |
2015 | M | Zerek | 5 | 2.466855e-06 |
2015 | M | Zeyd | 5 | 2.466855e-06 |
2015 | M | Zhi | 5 | 2.466855e-06 |
2015 | M | Ziel | 5 | 2.466855e-06 |
2015 | M | Zihan | 5 | 2.466855e-06 |
2015 | M | Zihao | 5 | 2.466855e-06 |
2015 | M | Ziheng | 5 | 2.466855e-06 |
2015 | M | Zimere | 5 | 2.466855e-06 |
2015 | M | Ziyi | 5 | 2.466855e-06 |
2015 | M | Ziyu | 5 | 2.466855e-06 |
2015 | M | Zoel | 5 | 2.466855e-06 |
2015 | M | Zohar | 5 | 2.466855e-06 |
2015 | M | Zolton | 5 | 2.466855e-06 |
2015 | M | Zyah | 5 | 2.466855e-06 |
2015 | M | Zykell | 5 | 2.466855e-06 |
2015 | M | Zyking | 5 | 2.466855e-06 |
2015 | M | Zykir | 5 | 2.466855e-06 |
2015 | M | Zyrus | 5 | 2.466855e-06 |
2015 | M | Zyus | 5 | 2.466855e-06 |
year | sex | name | n | prop |
---|---|---|---|---|
1980 | M | Michael | 68673 | 0.03702663 |
1981 | M | Michael | 68765 | 0.03692913 |
1983 | M | Michael | 67991 | 0.03649914 |
1982 | M | Michael | 68216 | 0.03615618 |
1984 | M | Michael | 67731 | 0.03610903 |
1985 | M | Michael | 64895 | 0.03374050 |
1986 | M | Michael | 64188 | 0.03342284 |
1980 | F | Jennifer | 58381 | 0.03279411 |
1987 | M | Michael | 63638 | 0.03265058 |
1988 | M | Michael | 64117 | 0.03204469 |
1984 | M | Christopher | 60017 | 0.03199651 |
1981 | F | Jennifer | 57043 | 0.03190267 |
1983 | M | Christopher | 59336 | 0.03185294 |
1982 | F | Jennifer | 57109 | 0.03148729 |
1982 | M | Christopher | 59222 | 0.03138913 |
1989 | M | Michael | 65378 | 0.03120357 |
1985 | M | Christopher | 59593 | 0.03098386 |
1983 | F | Jennifer | 54336 | 0.03037049 |
1990 | M | Michael | 65275 | 0.03034828 |
1987 | F | Jessica | 55990 | 0.02988406 |
1986 | M | Christopher | 56677 | 0.02951185 |
1987 | F | Ashley | 54845 | 0.02927293 |
1991 | M | Michael | 60783 | 0.02868492 |
1986 | F | Jessica | 52667 | 0.02854992 |
1984 | F | Jennifer | 50562 | 0.02804978 |
1987 | M | Christopher | 54476 | 0.02794985 |
1981 | M | Christopher | 50224 | 0.02697199 |
1983 | M | Matthew | 50204 | 0.02695067 |
1986 | F | Ashley | 49674 | 0.02692746 |
1988 | F | Jessica | 51532 | 0.02680799 |
... | ... | ... | ... | ... |
2007 | M | Zamarius | 5 | 2.259872e-06 |
2007 | M | Zamauri | 5 | 2.259872e-06 |
2007 | M | Zandre | 5 | 2.259872e-06 |
2007 | M | Zareb | 5 | 2.259872e-06 |
2007 | M | Zaveion | 5 | 2.259872e-06 |
2007 | M | Zavery | 5 | 2.259872e-06 |
2007 | M | Zavyer | 5 | 2.259872e-06 |
2007 | M | Zayde | 5 | 2.259872e-06 |
2007 | M | Zaydn | 5 | 2.259872e-06 |
2007 | M | Zaydrian | 5 | 2.259872e-06 |
2007 | M | Zayion | 5 | 2.259872e-06 |
2007 | M | Zed | 5 | 2.259872e-06 |
2007 | M | Zell | 5 | 2.259872e-06 |
2007 | M | Zeno | 5 | 2.259872e-06 |
2007 | M | Zentavious | 5 | 2.259872e-06 |
2007 | M | Zerik | 5 | 2.259872e-06 |
2007 | M | Zerion | 5 | 2.259872e-06 |
2007 | M | Zhayne | 5 | 2.259872e-06 |
2007 | M | Zione | 5 | 2.259872e-06 |
2007 | M | Zolan | 5 | 2.259872e-06 |
2007 | M | Zy | 5 | 2.259872e-06 |
2007 | M | Zyaun | 5 | 2.259872e-06 |
2007 | M | Zyel | 5 | 2.259872e-06 |
2007 | M | Zyheem | 5 | 2.259872e-06 |
2007 | M | Zyian | 5 | 2.259872e-06 |
2007 | M | Zylin | 5 | 2.259872e-06 |
2007 | M | Zyonn | 5 | 2.259872e-06 |
2007 | M | Zyquarius | 5 | 2.259872e-06 |
2007 | M | Zyquavious | 5 | 2.259872e-06 |
2007 | M | Zyrese | 5 | 2.259872e-06 |
year | sex | name | n | prop |
---|---|---|---|---|
1880 | F | Mary | 7065 | 0.072384329 |
1880 | F | Anna | 2604 | 0.026679234 |
1880 | F | Emma | 2003 | 0.020521700 |
1880 | F | Elizabeth | 1939 | 0.019865989 |
1880 | F | Minnie | 1746 | 0.017888611 |
1880 | F | Margaret | 1578 | 0.016167370 |
1880 | F | Ida | 1472 | 0.015081349 |
1880 | F | Alice | 1414 | 0.014487111 |
1880 | F | Bertha | 1320 | 0.013524036 |
1880 | F | Sarah | 1288 | 0.013196180 |
1880 | F | Annie | 1258 | 0.012888816 |
1880 | F | Clara | 1226 | 0.012560961 |
1880 | F | Ella | 1156 | 0.011843777 |
1880 | F | Florence | 1063 | 0.010890947 |
1880 | F | Cora | 1045 | 0.010706528 |
1880 | F | Martha | 1040 | 0.010655301 |
1880 | F | Laura | 1012 | 0.010368428 |
1880 | F | Nellie | 995 | 0.010194254 |
1880 | F | Grace | 982 | 0.010061063 |
1880 | F | Carrie | 949 | 0.009722962 |
1880 | F | Maude | 858 | 0.008790623 |
1880 | F | Mabel | 808 | 0.008278349 |
1880 | F | Bessie | 796 | 0.008155403 |
1880 | F | Jennie | 793 | 0.008124667 |
1880 | F | Gertrude | 787 | 0.008063194 |
1880 | F | Julia | 783 | 0.008022212 |
1880 | F | Hattie | 769 | 0.007878775 |
1880 | F | Edith | 768 | 0.007868530 |
1880 | F | Mattie | 704 | 0.007212819 |
1880 | F | Rose | 700 | 0.007171837 |
... | ... | ... | ... | ... |
2015 | M | Zavon | 5 | 2.466855e-06 |
2015 | M | Zay | 5 | 2.466855e-06 |
2015 | M | Zaylor | 5 | 2.466855e-06 |
2015 | M | Zayquan | 5 | 2.466855e-06 |
2015 | M | Zecharia | 5 | 2.466855e-06 |
2015 | M | Zecharya | 5 | 2.466855e-06 |
2015 | M | Zee | 5 | 2.466855e-06 |
2015 | M | Zeev | 5 | 2.466855e-06 |
2015 | M | Zeik | 5 | 2.466855e-06 |
2015 | M | Zekariah | 5 | 2.466855e-06 |
2015 | M | Zepplin | 5 | 2.466855e-06 |
2015 | M | Zerek | 5 | 2.466855e-06 |
2015 | M | Zeyd | 5 | 2.466855e-06 |
2015 | M | Zhi | 5 | 2.466855e-06 |
2015 | M | Ziel | 5 | 2.466855e-06 |
2015 | M | Zihan | 5 | 2.466855e-06 |
2015 | M | Zihao | 5 | 2.466855e-06 |
2015 | M | Ziheng | 5 | 2.466855e-06 |
2015 | M | Zimere | 5 | 2.466855e-06 |
2015 | M | Ziyi | 5 | 2.466855e-06 |
2015 | M | Ziyu | 5 | 2.466855e-06 |
2015 | M | Zoel | 5 | 2.466855e-06 |
2015 | M | Zohar | 5 | 2.466855e-06 |
2015 | M | Zolton | 5 | 2.466855e-06 |
2015 | M | Zyah | 5 | 2.466855e-06 |
2015 | M | Zykell | 5 | 2.466855e-06 |
2015 | M | Zyking | 5 | 2.466855e-06 |
2015 | M | Zykir | 5 | 2.466855e-06 |
2015 | M | Zyrus | 5 | 2.466855e-06 |
2015 | M | Zyus | 5 | 2.466855e-06 |
year | sex | name | n | prop |
---|---|---|---|---|
1880 | F | Mary | 7065 | 0.07238433 |
1880 | M | John | 9655 | 0.08154630 |
1880 | M | William | 9531 | 0.08049899 |
1881 | M | John | 8769 | 0.08098299 |
1881 | M | William | 8524 | 0.07872038 |
1882 | F | Mary | 8148 | 0.07042594 |
1882 | M | John | 9557 | 0.07831617 |
1882 | M | William | 9298 | 0.07619375 |
1883 | M | John | 8894 | 0.07907324 |
1883 | M | William | 8387 | 0.07456569 |
1884 | M | John | 9388 | 0.07648751 |
1884 | M | William | 8897 | 0.07248715 |
1885 | M | John | 8756 | 0.07551791 |
1886 | M | John | 9026 | 0.07582198 |
1887 | M | John | 8110 | 0.07418927 |
1888 | M | John | 9247 | 0.07118279 |
1889 | M | John | 8548 | 0.07181142 |
1890 | M | John | 8502 | 0.07102698 |
1891 | M | John | 7680 | 0.07028655 |
year | sex | name | n | prop |
---|---|---|---|---|
1985 | M | Zachary | 11342 | 0.005896984 |
1986 | M | Zachary | 11718 | 0.006101590 |
1987 | M | Zachary | 13200 | 0.006772489 |
1988 | M | Zachary | 15864 | 0.007928583 |
1989 | M | Zachary | 18074 | 0.008626347 |
1990 | M | Zachary | 20424 | 0.009495723 |
1991 | M | Zachary | 21382 | 0.010090666 |
1992 | M | Zachary | 24797 | 0.011816969 |
1993 | M | Zachary | 25533 | 0.012365811 |
1994 | M | Zachary | 25132 | 0.012333047 |
1995 | M | Zachary | 23128 | 0.011502044 |
1996 | M | Zachary | 22366 | 0.011165643 |
1997 | M | Zachary | 21927 | 0.010979624 |
1998 | M | Zachary | 20579 | 0.010153414 |
1999 | M | Zachary | 19981 | 0.009804605 |
2000 | M | Zachary | 19848 | 0.009510411 |
2001 | M | Zachary | 18185 | 0.008797813 |
2002 | M | Zachary | 16622 | 0.008048713 |
2003 | M | Zachary | 15539 | 0.007400874 |
2004 | M | Zachary | 13707 | 0.006491013 |
2005 | M | Zachary | 12280 | 0.005777790 |
2006 | M | Zachary | 11001 | 0.005023136 |
2007 | M | Zachary | 10210 | 0.004614658 |
1911 | M | William | 10593 | 0.043882979 |
1912 | M | William | 19626 | 0.043472675 |
1913 | M | William | 23537 | 0.043892414 |
1914 | M | William | 29755 | 0.043544688 |
1915 | M | William | 38564 | 0.043775569 |
1916 | M | William | 40421 | 0.043780828 |
1917 | M | William | 42421 | 0.044219357 |
... | ... | ... | ... | ... |
2011 | F | Abigail | 13237 | 0.006846693 |
2012 | F | Abigail | 12679 | 0.006555670 |
2013 | F | Abigail | 12387 | 0.006448362 |
2014 | F | Abigail | 12049 | 0.006186738 |
2015 | F | Abigail | 12311 | 0.006361916 |
1974 | M | Aaron | 10514 | 0.006446609 |
1975 | M | Aaron | 10548 | 0.006498331 |
1976 | M | Aaron | 11350 | 0.006948990 |
1977 | M | Aaron | 11634 | 0.006803966 |
1978 | M | Aaron | 12316 | 0.007206600 |
1979 | M | Aaron | 13075 | 0.007297551 |
1980 | M | Aaron | 13172 | 0.007101988 |
1981 | M | Aaron | 14689 | 0.007888490 |
1982 | M | Aaron | 14411 | 0.007638188 |
1983 | M | Aaron | 14457 | 0.007760852 |
1984 | M | Aaron | 13401 | 0.007144396 |
1985 | M | Aaron | 13134 | 0.006828689 |
1986 | M | Aaron | 12690 | 0.006607713 |
1987 | M | Aaron | 12678 | 0.006504667 |
1988 | M | Aaron | 14397 | 0.007195399 |
1989 | M | Aaron | 15314 | 0.007309056 |
1990 | M | Aaron | 14550 | 0.006764727 |
1991 | M | Aaron | 14240 | 0.006720189 |
1992 | M | Aaron | 14506 | 0.006912810 |
1993 | M | Aaron | 13825 | 0.006695544 |
1994 | M | Aaron | 14380 | 0.007056709 |
1995 | M | Aaron | 13286 | 0.006607409 |
1996 | M | Aaron | 11969 | 0.005975212 |
1997 | M | Aaron | 11166 | 0.005591211 |
1998 | M | Aaron | 10544 | 0.005202274 |
#basic aggregations
sqldf("select sum(n) as 'Total_Count' from mydata")
sqldf("select min(n), max(n) from mydata")
sqldf("select year,avg(n) as 'Average' from mydata group by year order by Average desc") #average by year
sqldf("select year,count(*) as count from mydata group by year limit 100") #count by year
sqldf("select year,n,count(*) as 'my_count' from mydata where n > 10000 group by year order by my_count desc limit 100") #multiple filters
Total_Count |
---|
340851912 |
min(n) | max(n) |
---|---|
5 | 99680 |
year | Average |
---|---|
1956 | 363.4601 |
1957 | 363.2088 |
1954 | 362.9615 |
1955 | 361.0543 |
1958 | 358.6307 |
1952 | 356.5054 |
1953 | 355.6499 |
1959 | 353.1235 |
1951 | 352.0081 |
1960 | 348.4466 |
1947 | 347.3426 |
1950 | 339.9868 |
1961 | 339.9606 |
1949 | 339.4959 |
1948 | 337.2323 |
1962 | 330.6214 |
1946 | 329.3071 |
1963 | 322.4454 |
1964 | 313.7025 |
1965 | 303.3916 |
1943 | 300.0556 |
1945 | 293.8928 |
1944 | 293.8638 |
1942 | 289.8327 |
1966 | 286.1308 |
1967 | 273.8182 |
1941 | 268.0701 |
1968 | 261.3431 |
1940 | 256.9531 |
1969 | 252.9251 |
... | ... |
1895 | 115.1276 |
1902 | 115.0309 |
1893 | 114.8788 |
1897 | 114.5832 |
1892 | 114.4735 |
2007 | 114.2577 |
1904 | 113.3097 |
1888 | 112.9664 |
1903 | 112.4830 |
2008 | 111.9629 |
1890 | 111.8371 |
1889 | 111.5629 |
1899 | 111.5171 |
2015 | 111.3190 |
2014 | 111.1683 |
2009 | 109.9298 |
1901 | 109.6784 |
2013 | 109.2867 |
2010 | 108.3133 |
2012 | 108.2038 |
1891 | 107.7726 |
2011 | 107.6944 |
1886 | 106.7387 |
1884 | 105.9917 |
1885 | 104.9930 |
1887 | 104.2545 |
1882 | 104.1533 |
1883 | 104.1003 |
1880 | 100.7410 |
1881 | 99.5845 |
year | count |
---|---|
1880 | 2000 |
1881 | 1935 |
1882 | 2127 |
1883 | 2084 |
1884 | 2297 |
1885 | 2294 |
1886 | 2392 |
1887 | 2373 |
1888 | 2651 |
1889 | 2590 |
1890 | 2695 |
1891 | 2660 |
1892 | 2921 |
1893 | 2831 |
1894 | 2941 |
1895 | 3049 |
1896 | 3091 |
1897 | 3028 |
1898 | 3264 |
1899 | 3042 |
1900 | 3731 |
1901 | 3153 |
1902 | 3362 |
1903 | 3389 |
1904 | 3561 |
1905 | 3655 |
1906 | 3633 |
1907 | 3948 |
1908 | 4018 |
1909 | 4227 |
... | ... |
1950 | 10309 |
1951 | 10460 |
1952 | 10654 |
1953 | 10831 |
1954 | 10963 |
1955 | 11114 |
1956 | 11339 |
1957 | 11564 |
1958 | 11521 |
1959 | 11771 |
1960 | 11924 |
1961 | 12178 |
1962 | 12206 |
1963 | 12278 |
1964 | 12394 |
1965 | 11953 |
1966 | 12148 |
1967 | 12400 |
1968 | 12930 |
1969 | 13746 |
1970 | 14782 |
1971 | 15291 |
1972 | 15414 |
1973 | 15676 |
1974 | 16243 |
1975 | 16934 |
1976 | 17395 |
1977 | 18171 |
1978 | 18224 |
1979 | 19032 |
year | n | my_count |
---|---|---|
1961 | 10019 | 88 |
1958 | 10083 | 87 |
1962 | 10043 | 84 |
1959 | 10551 | 83 |
1960 | 10371 | 82 |
1957 | 10341 | 81 |
1963 | 10195 | 80 |
1964 | 10296 | 79 |
1965 | 10681 | 78 |
1990 | 10680 | 78 |
1956 | 10146 | 77 |
1985 | 10140 | 77 |
1991 | 10133 | 77 |
1989 | 10101 | 75 |
1966 | 10241 | 74 |
1988 | 10147 | 74 |
1992 | 10146 | 74 |
1954 | 10073 | 73 |
1955 | 10109 | 73 |
1982 | 10054 | 73 |
1984 | 10016 | 73 |
1987 | 10101 | 73 |
1993 | 10176 | 73 |
1952 | 10299 | 72 |
1953 | 10010 | 72 |
1967 | 11007 | 72 |
1983 | 10478 | 72 |
1986 | 10316 | 72 |
1981 | 10109 | 70 |
1969 | 11154 | 69 |
... | ... | ... |
2011 | 10016 | 43 |
2015 | 10027 | 41 |
1940 | 10240 | 40 |
2014 | 10322 | 40 |
2012 | 10032 | 39 |
1921 | 10157 | 38 |
1927 | 10850 | 38 |
2013 | 10090 | 38 |
1922 | 10120 | 37 |
1924 | 10172 | 37 |
1928 | 10648 | 37 |
1920 | 10239 | 36 |
1925 | 10149 | 36 |
1926 | 10338 | 36 |
1929 | 10837 | 36 |
1939 | 10050 | 36 |
1923 | 11070 | 35 |
1930 | 11653 | 35 |
1931 | 10691 | 35 |
1932 | 10345 | 35 |
1938 | 10256 | 33 |
1918 | 10104 | 31 |
1919 | 10615 | 31 |
1934 | 10635 | 31 |
1935 | 10416 | 30 |
1937 | 10124 | 30 |
1916 | 10134 | 29 |
1917 | 10696 | 29 |
1933 | 10960 | 29 |
1936 | 10191 | 29 |
read.csv.sql
and working with joining the tables¶crash <- read.csv.sql("data/crashes.csv",sql = "select * from file")
roads <- read.csv.sql("data/roads.csv",sql = "select * from file")
head(crash)
head(roads)
Year | Road | N_Crashes | Volume |
---|---|---|---|
1991 | Interstate 65 | 25 | 40000 |
1992 | Interstate 65 | 37 | 41000 |
1993 | Interstate 65 | 45 | 45000 |
1994 | Interstate 65 | 46 | 45600 |
1995 | Interstate 65 | 46 | 49000 |
1996 | Interstate 65 | 59 | 51000 |
Road | District | Length |
---|---|---|
Interstate 65 | Greenfield | 262 |
Interstate 70 | Vincennes | 156 |
US-36 | Crawfordsville | 139 |
US-40 | Greenfield | 150 |
US-52 | Crawfordsville | 172 |
#join the data sets
sqldf("select * from crash join roads on crash.Road = roads.Road ")#inner join
sqldf("select crash.Year, crash.Volume, roads.* from crash left join roads on crash.Road = roads.Road") #left join
Year | Road | N_Crashes | Volume | Road..5 | District | Length |
---|---|---|---|---|---|---|
1991 | Interstate 65 | 25 | 40000 | Interstate 65 | Greenfield | 262 |
1992 | Interstate 65 | 37 | 41000 | Interstate 65 | Greenfield | 262 |
1993 | Interstate 65 | 45 | 45000 | Interstate 65 | Greenfield | 262 |
1994 | Interstate 65 | 46 | 45600 | Interstate 65 | Greenfield | 262 |
1995 | Interstate 65 | 46 | 49000 | Interstate 65 | Greenfield | 262 |
1996 | Interstate 65 | 59 | 51000 | Interstate 65 | Greenfield | 262 |
1997 | Interstate 65 | 76 | 52000 | Interstate 65 | Greenfield | 262 |
1998 | Interstate 65 | 90 | 58000 | Interstate 65 | Greenfield | 262 |
1999 | Interstate 65 | 95 | 65000 | Interstate 65 | Greenfield | 262 |
2000 | Interstate 65 | 95 | 74000 | Interstate 65 | Greenfield | 262 |
2001 | Interstate 65 | 95 | 80000 | Interstate 65 | Greenfield | 262 |
2002 | Interstate 65 | 122 | 85000 | Interstate 65 | Greenfield | 262 |
2003 | Interstate 65 | 125 | 86000 | Interstate 65 | Greenfield | 262 |
2004 | Interstate 65 | 125 | 87500 | Interstate 65 | Greenfield | 262 |
2005 | Interstate 65 | 135 | 87000 | Interstate 65 | Greenfield | 262 |
2006 | Interstate 65 | 138 | 95000 | Interstate 65 | Greenfield | 262 |
2007 | Interstate 65 | 141 | 110000 | Interstate 65 | Greenfield | 262 |
2008 | Interstate 65 | 157 | 115000 | Interstate 65 | Greenfield | 262 |
2009 | Interstate 65 | 165 | 121000 | Interstate 65 | Greenfield | 262 |
2010 | Interstate 65 | 182 | 122000 | Interstate 65 | Greenfield | 262 |
2011 | Interstate 65 | 183 | 125000 | Interstate 65 | Greenfield | 262 |
2012 | Interstate 65 | 190 | 127000 | Interstate 65 | Greenfield | 262 |
1991 | Interstate 70 | 84 | 76000 | Interstate 70 | Vincennes | 156 |
1992 | Interstate 70 | 82 | 79000 | Interstate 70 | Vincennes | 156 |
1993 | Interstate 70 | 84 | 82000 | Interstate 70 | Vincennes | 156 |
1994 | Interstate 70 | 84 | 91000 | Interstate 70 | Vincennes | 156 |
1995 | Interstate 70 | 81 | 93000 | Interstate 70 | Vincennes | 156 |
1996 | Interstate 70 | 76 | 93200 | Interstate 70 | Vincennes | 156 |
1997 | Interstate 70 | 74 | 93500 | Interstate 70 | Vincennes | 156 |
1998 | Interstate 70 | 72 | 93800 | Interstate 70 | Vincennes | 156 |
... | ... | ... | ... | ... | ... | ... |
2005 | US-40 | 105 | 56750 | US-40 | Greenfield | 150 |
2006 | US-40 | 52 | 58000 | US-40 | Greenfield | 150 |
2007 | US-40 | 45 | 59500 | US-40 | Greenfield | 150 |
2008 | US-40 | 23 | 61000 | US-40 | Greenfield | 150 |
2009 | US-40 | 67 | 65000 | US-40 | Greenfield | 150 |
2010 | US-40 | 102 | 67000 | US-40 | Greenfield | 150 |
2011 | US-40 | 87 | 67500 | US-40 | Greenfield | 150 |
2012 | US-40 | 32 | 67500 | US-40 | Greenfield | 150 |
1991 | US-36 | 27 | 35200 | US-36 | Crawfordsville | 139 |
1992 | US-36 | 45 | 45000 | US-36 | Crawfordsville | 139 |
1993 | US-36 | 64 | 50000 | US-36 | Crawfordsville | 139 |
1994 | US-36 | 67 | 48000 | US-36 | Crawfordsville | 139 |
1995 | US-36 | 67 | 49500 | US-36 | Crawfordsville | 139 |
1996 | US-36 | 51 | 47000 | US-36 | Crawfordsville | 139 |
1997 | US-36 | 51 | 44000 | US-36 | Crawfordsville | 139 |
1998 | US-36 | 43 | 42000 | US-36 | Crawfordsville | 139 |
1999 | US-36 | 44 | 36000 | US-36 | Crawfordsville | 139 |
2000 | US-36 | 58 | 35400 | US-36 | Crawfordsville | 139 |
2001 | US-36 | 61 | 39500 | US-36 | Crawfordsville | 139 |
2002 | US-36 | 61 | 32000 | US-36 | Crawfordsville | 139 |
2003 | US-36 | 43 | 28000 | US-36 | Crawfordsville | 139 |
2004 | US-36 | 42 | 29500 | US-36 | Crawfordsville | 139 |
2005 | US-36 | 42 | 26000 | US-36 | Crawfordsville | 139 |
2006 | US-36 | 35 | 26000 | US-36 | Crawfordsville | 139 |
2007 | US-36 | 49 | 24000 | US-36 | Crawfordsville | 139 |
2008 | US-36 | 52 | 24500 | US-36 | Crawfordsville | 139 |
2009 | US-36 | 55 | 24700 | US-36 | Crawfordsville | 139 |
2010 | US-36 | 35 | 23000 | US-36 | Crawfordsville | 139 |
2011 | US-36 | 33 | 21000 | US-36 | Crawfordsville | 139 |
2012 | US-36 | 31 | 20500 | US-36 | Crawfordsville | 139 |
Year | Volume | Road | District | Length |
---|---|---|---|---|
1991 | 40000 | Interstate 65 | Greenfield | 262 |
1992 | 41000 | Interstate 65 | Greenfield | 262 |
1993 | 45000 | Interstate 65 | Greenfield | 262 |
1994 | 45600 | Interstate 65 | Greenfield | 262 |
1995 | 49000 | Interstate 65 | Greenfield | 262 |
1996 | 51000 | Interstate 65 | Greenfield | 262 |
1997 | 52000 | Interstate 65 | Greenfield | 262 |
1998 | 58000 | Interstate 65 | Greenfield | 262 |
1999 | 65000 | Interstate 65 | Greenfield | 262 |
2000 | 74000 | Interstate 65 | Greenfield | 262 |
2001 | 80000 | Interstate 65 | Greenfield | 262 |
2002 | 85000 | Interstate 65 | Greenfield | 262 |
2003 | 86000 | Interstate 65 | Greenfield | 262 |
2004 | 87500 | Interstate 65 | Greenfield | 262 |
2005 | 87000 | Interstate 65 | Greenfield | 262 |
2006 | 95000 | Interstate 65 | Greenfield | 262 |
2007 | 110000 | Interstate 65 | Greenfield | 262 |
2008 | 115000 | Interstate 65 | Greenfield | 262 |
2009 | 121000 | Interstate 65 | Greenfield | 262 |
2010 | 122000 | Interstate 65 | Greenfield | 262 |
2011 | 125000 | Interstate 65 | Greenfield | 262 |
2012 | 127000 | Interstate 65 | Greenfield | 262 |
1991 | 76000 | Interstate 70 | Vincennes | 156 |
1992 | 79000 | Interstate 70 | Vincennes | 156 |
1993 | 82000 | Interstate 70 | Vincennes | 156 |
1994 | 91000 | Interstate 70 | Vincennes | 156 |
1995 | 93000 | Interstate 70 | Vincennes | 156 |
1996 | 93200 | Interstate 70 | Vincennes | 156 |
1997 | 93500 | Interstate 70 | Vincennes | 156 |
1998 | 93800 | Interstate 70 | Vincennes | 156 |
... | ... | ... | ... | ... |
2005 | 26000 | US-36 | Crawfordsville | 139 |
2006 | 26000 | US-36 | Crawfordsville | 139 |
2007 | 24000 | US-36 | Crawfordsville | 139 |
2008 | 24500 | US-36 | Crawfordsville | 139 |
2009 | 24700 | US-36 | Crawfordsville | 139 |
2010 | 23000 | US-36 | Crawfordsville | 139 |
2011 | 21000 | US-36 | Crawfordsville | 139 |
2012 | 20500 | US-36 | Crawfordsville | 139 |
1991 | 20350 | NA | NA | NA |
1992 | 21200 | NA | NA | NA |
1993 | 23200 | NA | NA | NA |
1994 | 21200 | NA | NA | NA |
1995 | 23200 | NA | NA | NA |
1996 | 20000 | NA | NA | NA |
1997 | 18000 | NA | NA | NA |
1998 | 19500 | NA | NA | NA |
1999 | 21000 | NA | NA | NA |
2000 | 20700 | NA | NA | NA |
2001 | 21200 | NA | NA | NA |
2002 | 21600 | NA | NA | NA |
2003 | 21700 | NA | NA | NA |
2004 | 22100 | NA | NA | NA |
2005 | 22300 | NA | NA | NA |
2006 | 22700 | NA | NA | NA |
2007 | 21900 | NA | NA | NA |
2008 | 21850 | NA | NA | NA |
2009 | 22100 | NA | NA | NA |
2010 | 21500 | NA | NA | NA |
2011 | 20300 | NA | NA | NA |
2012 | 21200 | NA | NA | NA |
#joining while aggregation
sqldf("select crash.Year, crash.Volume, roads.* from crash left join roads on crash.Road = roads.Road order by 1")
sqldf("select crash.Year, crash.Volume, roads.* from crash left join roads on crash.Road = roads.Road where roads.Road != 'US-36' order by 1")
sqldf("select Road, avg(roads.Length) as 'Avg_Length', avg(N_Crashes) as 'Avg_Crash' from roads join crash using (Road) group by Road")
Year | Volume | Road | District | Length |
---|---|---|---|---|
1991 | 40000 | Interstate 65 | Greenfield | 262 |
1991 | 76000 | Interstate 70 | Vincennes | 156 |
1991 | 21000 | US-40 | Greenfield | 150 |
1991 | 35200 | US-36 | Crawfordsville | 139 |
1991 | 20350 | NA | NA | NA |
1992 | 41000 | Interstate 65 | Greenfield | 262 |
1992 | 79000 | Interstate 70 | Vincennes | 156 |
1992 | 21500 | US-40 | Greenfield | 150 |
1992 | 45000 | US-36 | Crawfordsville | 139 |
1992 | 21200 | NA | NA | NA |
1993 | 45000 | Interstate 65 | Greenfield | 262 |
1993 | 82000 | Interstate 70 | Vincennes | 156 |
1993 | 23000 | US-40 | Greenfield | 150 |
1993 | 50000 | US-36 | Crawfordsville | 139 |
1993 | 23200 | NA | NA | NA |
1994 | 45600 | Interstate 65 | Greenfield | 262 |
1994 | 91000 | Interstate 70 | Vincennes | 156 |
1994 | 21000 | US-40 | Greenfield | 150 |
1994 | 48000 | US-36 | Crawfordsville | 139 |
1994 | 21200 | NA | NA | NA |
1995 | 49000 | Interstate 65 | Greenfield | 262 |
1995 | 93000 | Interstate 70 | Vincennes | 156 |
1995 | 24000 | US-40 | Greenfield | 150 |
1995 | 49500 | US-36 | Crawfordsville | 139 |
1995 | 23200 | NA | NA | NA |
1996 | 51000 | Interstate 65 | Greenfield | 262 |
1996 | 93200 | Interstate 70 | Vincennes | 156 |
1996 | 23500 | US-40 | Greenfield | 150 |
1996 | 47000 | US-36 | Crawfordsville | 139 |
1996 | 20000 | NA | NA | NA |
... | ... | ... | ... | ... |
2007 | 110000 | Interstate 65 | Greenfield | 262 |
2007 | 106350 | Interstate 70 | Vincennes | 156 |
2007 | 59500 | US-40 | Greenfield | 150 |
2007 | 24000 | US-36 | Crawfordsville | 139 |
2007 | 21900 | NA | NA | NA |
2008 | 115000 | Interstate 65 | Greenfield | 262 |
2008 | 108000 | Interstate 70 | Vincennes | 156 |
2008 | 61000 | US-40 | Greenfield | 150 |
2008 | 24500 | US-36 | Crawfordsville | 139 |
2008 | 21850 | NA | NA | NA |
2009 | 121000 | Interstate 65 | Greenfield | 262 |
2009 | 110000 | Interstate 70 | Vincennes | 156 |
2009 | 65000 | US-40 | Greenfield | 150 |
2009 | 24700 | US-36 | Crawfordsville | 139 |
2009 | 22100 | NA | NA | NA |
2010 | 122000 | Interstate 65 | Greenfield | 262 |
2010 | 110500 | Interstate 70 | Vincennes | 156 |
2010 | 67000 | US-40 | Greenfield | 150 |
2010 | 23000 | US-36 | Crawfordsville | 139 |
2010 | 21500 | NA | NA | NA |
2011 | 125000 | Interstate 65 | Greenfield | 262 |
2011 | 111000 | Interstate 70 | Vincennes | 156 |
2011 | 67500 | US-40 | Greenfield | 150 |
2011 | 21000 | US-36 | Crawfordsville | 139 |
2011 | 20300 | NA | NA | NA |
2012 | 127000 | Interstate 65 | Greenfield | 262 |
2012 | 113000 | Interstate 70 | Vincennes | 156 |
2012 | 67500 | US-40 | Greenfield | 150 |
2012 | 20500 | US-36 | Crawfordsville | 139 |
2012 | 21200 | NA | NA | NA |
Year | Volume | Road | District | Length |
---|---|---|---|---|
1991 | 40000 | Interstate 65 | Greenfield | 262 |
1991 | 76000 | Interstate 70 | Vincennes | 156 |
1991 | 21000 | US-40 | Greenfield | 150 |
1992 | 41000 | Interstate 65 | Greenfield | 262 |
1992 | 79000 | Interstate 70 | Vincennes | 156 |
1992 | 21500 | US-40 | Greenfield | 150 |
1993 | 45000 | Interstate 65 | Greenfield | 262 |
1993 | 82000 | Interstate 70 | Vincennes | 156 |
1993 | 23000 | US-40 | Greenfield | 150 |
1994 | 45600 | Interstate 65 | Greenfield | 262 |
1994 | 91000 | Interstate 70 | Vincennes | 156 |
1994 | 21000 | US-40 | Greenfield | 150 |
1995 | 49000 | Interstate 65 | Greenfield | 262 |
1995 | 93000 | Interstate 70 | Vincennes | 156 |
1995 | 24000 | US-40 | Greenfield | 150 |
1996 | 51000 | Interstate 65 | Greenfield | 262 |
1996 | 93200 | Interstate 70 | Vincennes | 156 |
1996 | 23500 | US-40 | Greenfield | 150 |
1997 | 52000 | Interstate 65 | Greenfield | 262 |
1997 | 93500 | Interstate 70 | Vincennes | 156 |
1997 | 23400 | US-40 | Greenfield | 150 |
1998 | 58000 | Interstate 65 | Greenfield | 262 |
1998 | 93800 | Interstate 70 | Vincennes | 156 |
1998 | 40000 | US-40 | Greenfield | 150 |
1999 | 65000 | Interstate 65 | Greenfield | 262 |
1999 | 95100 | Interstate 70 | Vincennes | 156 |
1999 | 42000 | US-40 | Greenfield | 150 |
2000 | 74000 | Interstate 65 | Greenfield | 262 |
2000 | 97000 | Interstate 70 | Vincennes | 156 |
2000 | 45000 | US-40 | Greenfield | 150 |
... | ... | ... | ... | ... |
2003 | 86000 | Interstate 65 | Greenfield | 262 |
2003 | 101000 | Interstate 70 | Vincennes | 156 |
2003 | 55200 | US-40 | Greenfield | 150 |
2004 | 87500 | Interstate 65 | Greenfield | 262 |
2004 | 102000 | Interstate 70 | Vincennes | 156 |
2004 | 55300 | US-40 | Greenfield | 150 |
2005 | 87000 | Interstate 65 | Greenfield | 262 |
2005 | 106000 | Interstate 70 | Vincennes | 156 |
2005 | 56750 | US-40 | Greenfield | 150 |
2006 | 95000 | Interstate 65 | Greenfield | 262 |
2006 | 106200 | Interstate 70 | Vincennes | 156 |
2006 | 58000 | US-40 | Greenfield | 150 |
2007 | 110000 | Interstate 65 | Greenfield | 262 |
2007 | 106350 | Interstate 70 | Vincennes | 156 |
2007 | 59500 | US-40 | Greenfield | 150 |
2008 | 115000 | Interstate 65 | Greenfield | 262 |
2008 | 108000 | Interstate 70 | Vincennes | 156 |
2008 | 61000 | US-40 | Greenfield | 150 |
2009 | 121000 | Interstate 65 | Greenfield | 262 |
2009 | 110000 | Interstate 70 | Vincennes | 156 |
2009 | 65000 | US-40 | Greenfield | 150 |
2010 | 122000 | Interstate 65 | Greenfield | 262 |
2010 | 110500 | Interstate 70 | Vincennes | 156 |
2010 | 67000 | US-40 | Greenfield | 150 |
2011 | 125000 | Interstate 65 | Greenfield | 262 |
2011 | 111000 | Interstate 70 | Vincennes | 156 |
2011 | 67500 | US-40 | Greenfield | 150 |
2012 | 127000 | Interstate 65 | Greenfield | 262 |
2012 | 113000 | Interstate 70 | Vincennes | 156 |
2012 | 67500 | US-40 | Greenfield | 150 |
Road | Avg_Length | Avg_Crash |
---|---|---|
Interstate 65 | 262 | 107.81818 |
Interstate 70 | 156 | 65.18182 |
US-36 | 139 | 48.00000 |
US-40 | 150 | 68.68182 |
#besides Road, adding Year variable as a key
roads$Year <- crash$Year[1:5]
sqldf("select crash.Year, crash.Volume, roads.* from crash left join roads on crash.Road = roads.Road and crash.Year = roads.Year order by 1") #multiple keys
Year | Volume | Road | District | Length | Year..6 |
---|---|---|---|---|---|
1991 | 40000 | Interstate 65 | Greenfield | 262 | 1991 |
1991 | 76000 | NA | NA | NA | NA |
1991 | 21000 | NA | NA | NA | NA |
1991 | 35200 | NA | NA | NA | NA |
1991 | 20350 | NA | NA | NA | NA |
1992 | 41000 | NA | NA | NA | NA |
1992 | 79000 | Interstate 70 | Vincennes | 156 | 1992 |
1992 | 21500 | NA | NA | NA | NA |
1992 | 45000 | NA | NA | NA | NA |
1992 | 21200 | NA | NA | NA | NA |
1993 | 45000 | NA | NA | NA | NA |
1993 | 82000 | NA | NA | NA | NA |
1993 | 23000 | NA | NA | NA | NA |
1993 | 50000 | US-36 | Crawfordsville | 139 | 1993 |
1993 | 23200 | NA | NA | NA | NA |
1994 | 45600 | NA | NA | NA | NA |
1994 | 91000 | NA | NA | NA | NA |
1994 | 21000 | US-40 | Greenfield | 150 | 1994 |
1994 | 48000 | NA | NA | NA | NA |
1994 | 21200 | NA | NA | NA | NA |
1995 | 49000 | NA | NA | NA | NA |
1995 | 93000 | NA | NA | NA | NA |
1995 | 24000 | NA | NA | NA | NA |
1995 | 49500 | NA | NA | NA | NA |
1995 | 23200 | NA | NA | NA | NA |
1996 | 51000 | NA | NA | NA | NA |
1996 | 93200 | NA | NA | NA | NA |
1996 | 23500 | NA | NA | NA | NA |
1996 | 47000 | NA | NA | NA | NA |
1996 | 20000 | NA | NA | NA | NA |
... | ... | ... | ... | ... | ... |
2007 | 110000 | NA | NA | NA | NA |
2007 | 106350 | NA | NA | NA | NA |
2007 | 59500 | NA | NA | NA | NA |
2007 | 24000 | NA | NA | NA | NA |
2007 | 21900 | NA | NA | NA | NA |
2008 | 115000 | NA | NA | NA | NA |
2008 | 108000 | NA | NA | NA | NA |
2008 | 61000 | NA | NA | NA | NA |
2008 | 24500 | NA | NA | NA | NA |
2008 | 21850 | NA | NA | NA | NA |
2009 | 121000 | NA | NA | NA | NA |
2009 | 110000 | NA | NA | NA | NA |
2009 | 65000 | NA | NA | NA | NA |
2009 | 24700 | NA | NA | NA | NA |
2009 | 22100 | NA | NA | NA | NA |
2010 | 122000 | NA | NA | NA | NA |
2010 | 110500 | NA | NA | NA | NA |
2010 | 67000 | NA | NA | NA | NA |
2010 | 23000 | NA | NA | NA | NA |
2010 | 21500 | NA | NA | NA | NA |
2011 | 125000 | NA | NA | NA | NA |
2011 | 111000 | NA | NA | NA | NA |
2011 | 67500 | NA | NA | NA | NA |
2011 | 21000 | NA | NA | NA | NA |
2011 | 20300 | NA | NA | NA | NA |
2012 | 127000 | NA | NA | NA | NA |
2012 | 113000 | NA | NA | NA | NA |
2012 | 67500 | NA | NA | NA | NA |
2012 | 20500 | NA | NA | NA | NA |
2012 | 21200 | NA | NA | NA | NA |
SQLite
package for sqlite database¶Download SQLite from here: https://www.sqlite.org/download.html
How to download & Install SQLite: http://www.sqlitetutorial.net/download-install-sqlite/
SQLite tutorials:
https://www.guru99.com/sqlite-tutorial.html
https://www.sqlite.org/cli.html
SQLite DB Browser(visual & open source tool): http://sqlitebrowser.org/
# install required packages
# install.packages("RSQLite", repos = "http://cran.us.r-project.org")
# install.packages("DBI", repos = "http://cran.us.r-project.org")
also installing the dependencies 'bit', 'prettyunits', 'bit64', 'blob', 'DBI', 'memoise', 'plogr'
package 'bit' successfully unpacked and MD5 sums checked package 'prettyunits' successfully unpacked and MD5 sums checked package 'bit64' successfully unpacked and MD5 sums checked package 'blob' successfully unpacked and MD5 sums checked package 'DBI' successfully unpacked and MD5 sums checked package 'memoise' successfully unpacked and MD5 sums checked package 'plogr' successfully unpacked and MD5 sums checked package 'RSQLite' successfully unpacked and MD5 sums checked The downloaded binary packages are in C:\Users\User\AppData\Local\Temp\RtmpSAhvkS\downloaded_packages package 'DBI' successfully unpacked and MD5 sums checked The downloaded binary packages are in C:\Users\User\AppData\Local\Temp\RtmpSAhvkS\downloaded_packages
library(RSQLite)
library(DBI) # database interface (DBI) connectivity between DBMS(database management systems)
# and R
# To create a new SQLite database, you simply supply the filename to dbConnect():
# mydb <- dbConnect(RSQLite::SQLite(), "my-db.sqlite")
# dbDisconnect(mydb)
# unlink("my-db.sqlite")
mydb <- dbConnect(SQLite(), dbname = "data/ex1.db")
#mydb <- dbConnect(SQLite(), dbname = "c:/sqlite/ex1.db") # if its a local system
# list out tables from database
dbListTables(mydb)
# sending query and selecting the table
dbGetQuery(mydb, 'SELECT * FROM tbl1')
one | two |
---|---|
hello! | 10 |
goodbye | 20 |
lucid! | 40 |
solutions! | 90 |
data | 4 |
science | 54 |
dbListFields(mydb, 'tbl1')
# to disconnect the database
# dbDisconnect(mydb)
mydb <- dbConnect(RSQLite::SQLite(), "") # If you just need a temporary database, use ""
dbWriteTable(mydb, "mtcars", mtcars) # You can easily copy an R data frame into a SQLite database with dbWriteTable()
dbWriteTable(mydb, "iris", iris) # to write the dataframe to sqlite database
dbListTables(mydb) # to list out the tables
# query with dbGetQuery():
dbGetQuery(mydb, 'SELECT * FROM mtcars LIMIT 5')
mpg | cyl | disp | hp | drat | wt | qsec | vs | am | gear | carb |
---|---|---|---|---|---|---|---|---|---|---|
21.0 | 6 | 160 | 110 | 3.90 | 2.620 | 16.46 | 0 | 1 | 4 | 4 |
21.0 | 6 | 160 | 110 | 3.90 | 2.875 | 17.02 | 0 | 1 | 4 | 4 |
22.8 | 4 | 108 | 93 | 3.85 | 2.320 | 18.61 | 1 | 1 | 4 | 1 |
21.4 | 6 | 258 | 110 | 3.08 | 3.215 | 19.44 | 1 | 0 | 3 | 1 |
18.7 | 8 | 360 | 175 | 3.15 | 3.440 | 17.02 | 0 | 0 | 3 | 2 |
dbGetQuery(mydb, 'SELECT * FROM iris WHERE "Sepal.Length" < 4.6')
Error in result_create(conn@ptr, statement): no such table: iris Traceback: 1. dbGetQuery(mydb, "SELECT * FROM iris WHERE \"Sepal.Length\" < 4.6") 2. dbGetQuery(mydb, "SELECT * FROM iris WHERE \"Sepal.Length\" < 4.6") 3. .local(conn, statement, ...) 4. dbSendQuery(conn, statement, ...) 5. dbSendQuery(conn, statement, ...) 6. .local(conn, statement, ...) 7. new("SQLiteResult", sql = statement, ptr = result_create(conn@ptr, . statement), conn = conn, bigint = conn@bigint) 8. initialize(value, ...) 9. initialize(value, ...) 10. result_create(conn@ptr, statement)
dbDisconnect(mydb)
Warning message in connection_release(conn@ptr): "Already disconnected"
mydb <- dbConnect(SQLite(), dbname = "data/ex1.db")
dbWriteTable (conn = mydb, name = "tutorial_us_housing_units", value = "data/tutorial_us_housing_units.csv",
row.names = FALSE, header = TRUE, overwrite =TRUE)
dbListTables(mydb) # The tables in the database
dbListFields(mydb, "tutorial_us_housing_units") # The columns in a table
SELECT
, WHERE
, LIMIT
¶head(dbGetQuery (mydb, 'SELECT * FROM tutorial_us_housing_units'))
year | month | month_name | south | west | midwest | northeast |
---|---|---|---|---|---|---|
1968 | 1 | January | 35.6 | 17.0 | 22.6 | 12.9 |
1968 | 2 | February | 31.5 | 18.6 | 23.3 | 9.7 |
1968 | 3 | March | 42.5 | 17.4 | 24.4 | 10.7 |
1968 | 4 | April | 42.9 | 20.6 | 27.0 | 12.0 |
1968 | 5 | May | 46.2 | 20.0 | 25.1 | 20.0 |
1968 | 6 | June | 37.7 | 23.2 | 26.9 | 16.8 |
head(dbGetQuery (mydb, 'SELECT year,
month,
west
FROM tutorial_us_housing_units'))
head(dbGetQuery (mydb, 'SELECT west AS "West Region"
FROM tutorial_us_housing_units'))
head(dbGetQuery (mydb, 'SELECT west AS West_Region,
south AS South_Region
FROM tutorial_us_housing_units'))
head(dbGetQuery (mydb, 'SELECT *
FROM tutorial_us_housing_units
LIMIT 100'))
head(dbGetQuery (mydb, 'SELECT *
FROM tutorial_us_housing_units
WHERE month = 1'))
year | month | west |
---|---|---|
1968 | 1 | 17.0 |
1968 | 2 | 18.6 |
1968 | 3 | 17.4 |
1968 | 4 | 20.6 |
1968 | 5 | 20.0 |
1968 | 6 | 23.2 |
Meaning | Operator |
---|---|
Equal to | = |
Not equal to | <> or != |
Greater than | > |
Less than | < |
Greater than or equal to | >= |
Less than or equal to | <= |
head(dbGetQuery (mydb, 'SELECT *
FROM tutorial_us_housing_units
WHERE west > 30'))
# head(dbGetQuery (mydb, 'SELECT *
# FROM tutorial_us_housing_units
# WHERE month_name != 'January''))
# head(dbGetQuery (mydb, 'SELECT *
# FROM tutorial_us_housing_units
# WHERE month_name > 'J''))
head(dbGetQuery (mydb, 'SELECT year,
month,
west,
south,
west + south AS south_plus_west
FROM tutorial_us_housing_units'))
head(dbGetQuery (mydb, 'SELECT year,
month,
west,
south,
west + south - 4 * year AS nonsense_column
FROM tutorial_us_housing_units'))
head(dbGetQuery (mydb, 'SELECT year,
month,
west,
south,
(west + south)/2 AS south_west_avg
FROM tutorial_us_housing_units'))
year | month | month_name | south | west | midwest | northeast |
---|---|---|---|---|---|---|
1968 | 1 | January | 35.6 | 17.0 | 22.6 | 12.9 |
1968 | 2 | February | 31.5 | 18.6 | 23.3 | 9.7 |
1968 | 3 | March | 42.5 | 17.4 | 24.4 | 10.7 |
1968 | 4 | April | 42.9 | 20.6 | 27.0 | 12.0 |
1968 | 5 | May | 46.2 | 20.0 | 25.1 | 20.0 |
1968 | 6 | June | 37.7 | 23.2 | 26.9 | 16.8 |
year | month | month_name | south | west | midwest | northeast |
---|---|---|---|---|---|---|
1968 | 1 | January | 35.6 | 17.0 | 22.6 | 12.9 |
1968 | 2 | February | 31.5 | 18.6 | 23.3 | 9.7 |
1968 | 3 | March | 42.5 | 17.4 | 24.4 | 10.7 |
1968 | 4 | April | 42.9 | 20.6 | 27.0 | 12.0 |
1968 | 5 | May | 46.2 | 20.0 | 25.1 | 20.0 |
1968 | 6 | June | 37.7 | 23.2 | 26.9 | 16.8 |
year | month | month_name | south | west | midwest | northeast |
---|---|---|---|---|---|---|
1968 | 1 | January | 35.6 | 17.0 | 22.6 | 12.9 |
1969 | 1 | January | 40.1 | 19.7 | 18.0 | 11.0 |
1970 | 1 | January | 43.7 | 25.2 | 23.1 | 13.2 |
1971 | 1 | January | 46.8 | 22.3 | 24.0 | 14.2 |
1972 | 1 | January | 67.6 | 32.8 | 26.6 | 18.2 |
1973 | 1 | January | 72.2 | 33.7 | 32.4 | 23.2 |
year | month | month_name | south | west | midwest | northeast |
---|---|---|---|---|---|---|
1970 | 9 | September | 49.7 | 31.2 | 29.7 | 18.3 |
1971 | 4 | April | 60.6 | 32.3 | 25.1 | 15.2 |
1971 | 5 | May | 60.4 | 30.3 | 27.1 | 15.9 |
1971 | 6 | June | 63.4 | 30.8 | 28.7 | 21.4 |
1971 | 7 | July | 62.3 | 36.7 | 28.5 | 18.0 |
1971 | 8 | August | 69.3 | 43.1 | 37.9 | 21.2 |
year | month | west | south | south_plus_west |
---|---|---|---|---|
1968 | 1 | 17.0 | 35.6 | 52.6 |
1968 | 2 | 18.6 | 31.5 | 50.1 |
1968 | 3 | 17.4 | 42.5 | 59.9 |
1968 | 4 | 20.6 | 42.9 | 63.5 |
1968 | 5 | 20.0 | 46.2 | 66.2 |
1968 | 6 | 23.2 | 37.7 | 60.9 |
year | month | west | south | nonsense_column |
---|---|---|---|---|
1968 | 1 | 17.0 | 35.6 | -7819.4 |
1968 | 2 | 18.6 | 31.5 | -7821.9 |
1968 | 3 | 17.4 | 42.5 | -7812.1 |
1968 | 4 | 20.6 | 42.9 | -7808.5 |
1968 | 5 | 20.0 | 46.2 | -7805.8 |
1968 | 6 | 23.2 | 37.7 | -7811.1 |
year | month | west | south | south_west_avg |
---|---|---|---|---|
1968 | 1 | 17.0 | 35.6 | 26.30 |
1968 | 2 | 18.6 | 31.5 | 25.05 |
1968 | 3 | 17.4 | 42.5 | 29.95 |
1968 | 4 | 20.6 | 42.9 | 31.75 |
1968 | 5 | 20.0 | 46.2 | 33.10 |
1968 | 6 | 23.2 | 37.7 | 30.45 |
Operator | Meaning |
---|---|
LIKE | allows you to match similar values, instead of exact values |
IN | allows you to specify a list of values you’d like to include |
BETWEEN | allows you to select only rows within a certain range. |
IS NULL | allows you to select rows that contain no data in a given column |
AND | allows you to select only rows that satisfy two conditions. |
OR | allows you to select rows that satisfy either of two conditions |
NOT | allows you to select rows that do not match a certain condition |
dbWriteTable(conn = mydb, name = "tutorial_billboard_top_hundred_year_end", value = "data/tutorial_billboard_top_hundred_year_end.csv",
row.names = FALSE, header = TRUE, overwrite =TRUE)
Error in connection_import_file(conn@ptr, name, value, sep, eol, skip): RS_sqlite_import: data/tutorial_billboard_top_100_year_end.csv line 15 expected 6 columns of data but found 8 Traceback: 1. dbWriteTable(conn = mydb, name = "tutorial_billboard_top_100_year_end", . value = "data/tutorial_billboard_top_100_year_end.csv", row.names = FALSE, . header = TRUE, overwrite = TRUE) 2. dbWriteTable(conn = mydb, name = "tutorial_billboard_top_100_year_end", . value = "data/tutorial_billboard_top_100_year_end.csv", row.names = FALSE, . header = TRUE, overwrite = TRUE) 3. .local(conn, name, value, ...) 4. connection_import_file(conn@ptr, name, value, sep, eol, skip)
RMySQL
package for mySQL database¶# install.packages("RMySQL", repos = "http://cran.us.r-project.org")
library(RMySQL)
library(DBI)
Warning message: "package 'RMySQL' was built under R version 3.4.3" Attaching package: 'RMySQL' The following object is masked from 'package:RSQLite': isIdCurrent
mydb = dbConnect(MySQL(), user='root', password='m*********3', dbname='contacts', host='localhost')
dbListTables(mydb)
dbListFields(mydb, 'person')
rs = dbSendQuery(mydb, "select * from person")
# the results of this query remain on the MySQL server
# to access the results in R we need to use the fetch function.
data = fetch(rs, n = -1)
data
# This saves the results of the query as a data frame object. The n in the
# function specifies the number of records to retrieve, using n=-1 retrieves all
# pending records
person_id | person_first_name | person_last_name | person_contacted_number | person_date_last_contacted | person_date_added |
---|---|---|---|---|---|
1 | Jon | Flanders | 5 | 2013-09-14 11:43:31 | 2013-01-14 11:43:31 |
2 | Shannon | Ahern | 0 | 2013-08-14 11:43:31 | 2013-02-14 11:43:31 |
3 | Fritz | Onion | 1 | 2013-07-14 11:43:31 | 2013-03-14 11:43:31 |
Resourses for practice SQL for data analysis using R
https://www.tutorialgateway.org/mysql-tutorial/
www.sqlfiddle.com/
https://www.commonlounge.com/discussion/20f16f60d5604d5ba18fdbb9aec0560b
https://community.modeanalytics.com/sql/
https://www.red-gate.com/simple-talk/dotnet/software-tools/sql-and-r/
http://dept.stat.lsa.umich.edu/~jerrick/courses/stat701/notes/sql.html