- Home
- Uncategorized
- Microsoft SQL Server
- Automation
- Tips and Tricks
- Oracle
- MySQL & MariaDB
- NoSQL Databases
- DB2
- Cloud Offerings
- Tools
- Business Intelligence
- Data Science
- Azure
- AWS
- Google Cloud
- Big Data
- Hadoop
- Hardware
- Microsoft
- Vendors
- Relational Databases
- Oracle Database
- Machine Learning
- Featured
- Big Data Applications
- IBM
- Internet of Things
- Data Vizualisation
- Data Science Techniques
- Data Science Use Cases
- DynamoDB
- MongoDB
- Cassandra
- PostgreSQL
- Migration to Cloud
- Events
- Books
- Online Training
- Training & Certifications
- Education
- SAP
- Tableau
- Microsoft Power BI
- Neo4j
- Python
- R
- Tutorials
- Datawarehousing
- Polybase
- Teradata
- Amazon Redshift
- MapR
- Cloudera
- Hortonworks
- IBM BigInsights
- SAS
- CRM
- NuoDB
- OrientDB
- Actian
- Graph Databases
- Document Stores
- Key/Value Stores
- ArangoDB
- Column Stores
- HBASE
- Multi-Model Databases
- In-Memory
- Redis
- MemSQL
- Industry Trends
- Distributed
- Hazelcast
- MapD
- AllegroGraph
- erwin
- Players
- CLUSTRIX
- Integrators
- IDERA
- Workato
- Veriflow
- Arisant
- Liquid Web
- Artificial Intelligence
- puppet
- chef
- ansible
- cronJ
- Sketch – Data Trivia
Sketch – Data Trivia
Feed: R-bloggers.
Author: Tony Hirst.
A bit more tinkering with F1 data from the ergast db, this time trying to generating trivia / facts around races.
The facts are identified using SQL queries:
#starts for a team q=paste0('SELECT d.code, COUNT(code) AS startsforteam, c.name AS name FROM drivers d JOIN races r JOIN results rs JOIN constructors c WHERE c.constructorId=rs.constructorId AND d.driverId=rs.driverId AND r.raceId=rs.raceId AND d.code IN (',driversThisYear_str,') ',upto,' GROUP BY d.code, c.name HAVING (startsforteam+1) % 50 = 0') startsTeammod50=dbGetQuery(ergastdb, q) #looking for poles to date modulo 5 q=paste0('SELECT d.code, COUNT(code) AS poles FROM drivers d JOIN qualifying q JOIN races r WHERE r.raceId=q.raceId AND d.code IN (',driversThisYear_str,') AND d.driverId=q.driverId AND q.position=1',upto,' GROUP BY code HAVING poles>1 AND (poles+1) % 5 = 0') lookingpolesmod5=dbGetQuery(ergastdb, q)
Some of the queries also embed query fragments, which I intend to develop further…
upto=paste0(' AND (yearI'm using knitr to generate Github flavoured markdown (gfm) from my Rmd docs – here’s part of the header:
--- output: md_document: variant: gfm ---
The following recipe then takes results from the trivia queries and spiels the output:
if (nrow(startsTeammod50)>0) { for (row in 1:nrow(startsTeammod50)) { text = '- `r startsTeammod50[row, "code"]` is looking for their `r toOrdinal(startsTeammod50[row, "startsforteam"]+1)` start for `r startsTeammod50[row, "name"]`' cat(paste0(knit_child(text=text,quiet=TRUE),'n')) } } if (nrow(lookingpolesmod5)>0) { for (row in 1:nrow(lookingpolesmod5)) { text = '- `r lookingpolesmod5[row, "code"]` is looking for their `r toOrdinal(lookingpolesmod5[row, "poles"]+1)` ever pole position' cat(paste0(knit_child(text=text,quiet=TRUE),'n')) } }
We then get outputs of the form:
- BOT is looking for their 100th race start
- HAM is looking for their 100th start for Mercedes
See more example outputs here: Bahrain F1 2018 – Race Trivia.
This is another recipe I need to work up a little further and add to Wrangling F1 Data With R.
R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more…
Leave a Reply
You must be logged in to post a comment.