How to work with Google n-gram data sets in R using MySQL

April 12, 2012

N-gram lead. By Kristoffer Magnusson

Introduction

Google Ngram is a corpus of n-grams compiled from data from Google Books. Here I’m going to show how to analyze individual word counts from Google 1-grams in R using MySQL. I’ve also written an R script to automatically extract and plot multiple word counts. To read more about the datasets go to: http://books.google.com/ngrams/datasets. Of course, one could just use Google Ngram Viewer but what’s the fun in that? And it won’t really give the output that I’m looking for. Since it’s case sensitive queries like “psychotherapy” and “Psychotherapy” will give different results. Using R one can combine match counts regardless of case lettering and display the results in a more intuitive way using ggplot2. If you’re not interested in the technical aspects of this post, you could just jump to the end of it to view an example of different applications of the n-gram database.

Setting up MySQL

Get MySQL

First you need to install and setup MySQL on your system. I’m on Mac OS and it was really straightforward to get MySQL up and running. Here’s the documentation on how to do it on Mac OS.

Download the raw data

Go to http://books.google.com/ngrams/datasets and get the data files for Google 1-gram [highlight]files 0-9[/highlight]. After you’ve downloaded the files unzip them.

Import Google 1-gram into a MySQL database

Since I figured it would take a couple of hours to build the database I first combined all 10 files into one csv-file using cat in Terminal:

cat *.csv > outputfile

Since I’m not really well versed in working with MySQL I used a free GUI (Sequel Pro) to create and import the data. I setup my DB like this:

CREATE TABLE `1_grams` (
`n_gram` text,
`year` int(11) DEFAULT NULL,
`match_count` int(11) DEFAULT NULL,
`page_count` int(11) DEFAULT NULL,
`volume_count` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And imported newly created CSV-file into this structure. I figured it took about 8 hours to build it on my 2,4 GHz Core 2 Duo iMac from 2009, but I didn’t time it. The resulting database contained 470 million rows and landed at 24 GB using InnoDB indexing.

Querying MySQL from R

I’m using the RMySQL-package to get data from MySQL into R. I wrote a function that accepts search terms and fetches the matching results from my Google 1-gram database. I’ve masked my user and password, so you’ve got to change ‘user=”*”, password=”*”` to your own user name and password.

Optimization

MySQL is well optimized to handle OR statements, and it’s a lot faster to send all terms in the same query then to send new queries for each term. Consequently I needed a function that would write out my MySQL query combining the different search terms used. Like this:

createQuery <- function(query) {
 # Function used later to create 'OR' segments in query
  createOR <- function(index) {
    x <- paste("OR n_gram = '", query[index] ,"'", sep = "")
    return(x)
  }
  # Create MySQL query depending on number of items in query. 
  if (length(query) == 1) {
    result <- paste("SELECT n_gram, year, match_count FROM 1_grams WHERE n_gram  = '", query, "'", sep = "")
  } else
  {
    index <- 2:length(query)
    x <- laply(index, createOR)
    result <- paste("SELECT n_gram, year, match_count FROM 1_grams WHERE n_gram  = '", 
                    query[1],"' ", paste(x, collapse = " "), sep="")
  }
  return(result)
}

Which I then put into the function that connects to MySQL. Using system.time() [highlight]I clocked the run time to about 15 minutes[/highlight] independent of how many search terms I used. I would say that’s pretty decent considering it’s ~470 million rows of data, that I’m hosting on an external FireWire 800 drive.

getMySQL <- function (query) {
    connection <- dbConnect(MySQL(), user="*****", password="*****", dbname="n_grams", host="localhost")
    results <- dbSendQuery(connection, paste(createQuery(query)))
    data <- fetch(results, n=-1)
    dbDisconnect(connection)
    return(data)
  }
  data <- getMySQL(query)

Cleaning up the data

The MySQL query created a data frame with “n_gram”, “year” and “match_count”. However, since the raw data is case sensitive there are a lot of duplicates with just different lower- and uppercase configurations. Therefore I created a function to combine all 1-grams regardless of letter casing. Google’s n-gram database is not perfect, so sometimes you fetch OCR-errors with your query. I had to add some code to get rid of those erroneous words otherwise tolower() would return an error and the script would stop.

# Remove junk words due to OCR-error
  data$n_gram <- iconv(data$n_gram, "UTF-8", 'ASCII')
  # conditional statement to not do anything if there are no missing values.
  if (length(which(is.na(data))) > 0) {
    cat("Removed", length(which(is.na(data))), "values of missing data (probably OCR-error)\n")
    data <- data[-which(is.na(data)),]
  } else cat("Found no missing data\n")
   
  # Convert all n-grams to lowercase
  data$n_gram <- tolower(data$n_gram)
# Outer function to combine ngrams
CreateDf <- function (query) {
  # Create a year.index with duplicates removed. 
  year.index <- data.frame("year" = sort(unique(data$year[data$n_gram == query])))
   
  # Function to sum 'match_count' from the same years
  MeltYear <- function(query, year) {
    # sum rows from the same year and put into a data.frame
    df2 <- data.frame("n_gram" = query, "year" = year, 
                      "match_count" = sum(data$match_count[data$n_gram == query & data$year == year]))
    return(df2)
  }
  # I only want to pass one argument with ldply(), so I create a wrapper for MeltYear()
  ApplyIndex <- function (year) {  
    x <- MeltYear(query, year)
    return(x)
  }
  # Apply function ApplyIndex() for each element in year.index$year and
  # combine results into a data.frame.    
  df <- ldply(year.index$year, ApplyIndex)
  return(df)
}

Creating the final data frame

To create the final data frame I run CreateDf() for each query term and combine them into one data frame with ldply(). Lastly I import data containing total counts for each year, which allows me to calculate relative values for each n-gram.

df <- ldply(query, CreateDf)
 # Add data about relative proportions
  load(file="google_n-gram_total_table")
  match <- match(df$year, total$year)
  df$total_count <- total$total_count[match]
  df$relative <- df$match_count / df$total_count
  return(df)

Results

Without smoothing

This is what the raw data look like.
Ggplot2 of "women" and "men" from google 1-gram data sets. By Kristoffer Magnusson

With smoothing

Here I added a smoothing function and ran some more queries.

Women vs Men

Google N-gram "Women" & "Men" with a smoother. By Kristoffer Magnusson

Sweden vs Norway vs Denmark vs Finland

Google N-gram of "Sweden", "Norway", "Denmark" and "Finland". By Kristoffer Magnusson

Psychodynamic vs Psychoanalysis vs Psychoanalytic vs Psychotherapy

Google N-gram of "Psychodynamic", "Psychoanalysis", "Psychoanalytic" and "Psychotherapy". By Kristoffer Magnusson

Jesus vs Christ

Ggplot2 of "Jesus", "Christ" from google 1-gram data sets with a smoother. By Kristoffer Magnusson

Gay vs Lesbian vs Homosexual vs Heterosexual

Ggplot2 of "Gay", "Lesbian","Homosexual" and "Heterosexual" from google 1-gram data sets with a smoother. By Kristoffer Magnusson

Socialism vs Capitalism

Ggplot2 of "Socialism" and "Capitalism" from google 1-gram data sets with a smoother. By Kristoffer Magnusson

But isn’t this exactly like using Google Ngram Viewer except a lot sexier?

Well, yes, this is exactly like using Google Ngram Viewer except with sexier graphics. However, you could do much more with this data than with Google Ngram Viewer. One could, for instance, aggregate the data with another data set. For example I could combine “socialism” and “capitalism” with data about which US political party were in power at that time. If you have more computer power than I do you could work with 2-9-grams and generate much cooler data.

Ggplot2 of "Socialism" and "Capitalism" from google 1-gram data sets aggregated with data about US political parties. By Kristoffer Magnusson

Ggplot2 R code used here

Smoothed plots

I actually had to write a function to get direct.labels() to display annotations after the smoothed curve instead of after the line of the raw data.

# Set year range
yrMin <- 1900
yrMax <- 2000
 
# Use loess() to get x-values for annotation
funcDlMove <- function (n_gram) {
  model <- loess(relative ~ year, df[df$n_gram==n_gram & df$year >= yrMin & df$year <= yrMax,], span=0.3)
  Y <- model$fitted[length(model$fitted)]
  Y <- dl.move(n_gram, y=Y,x=yrMax, hjust=-0.05)
  return(Y)
}
# Index of n-words for lapply()
index <- unique(df$n_gram)
# Custom 'method' for direct.labels()
mymethod <- c(
  "last.bumpup", 
  lapply(index, funcDlMove)
  )
 
# Plot
PLOT <- ggplot(df[df$year >= yrMin & df$year <= yrMax,], aes(year, relative, group=n_gram, fill=n_gram, color=n_gram)) +
    xlim(c(yrMin ,yrMax + 20)) + 
    geom_line(alpha = I(7/10), color="grey", show_guide=F) +
    stat_smooth(size=2, span=0.3, se=F, show_guide=F) +
    ylab("Match Count (% of yearly total)") +
    xlab("Year") +
    opts(title = "Google 1-gram match count") 
direct.label(PLOT, mymethod)

Aggregated plot

I added party data using an example in the book ggplot2: elegant graphics for data analysis. Which I just added to the previous syntax already saved in PLOT

data(presidential)
presidential$start <- as.integer(format(presidential$start, "%Y"))
presidential$end <- as.integer(format(presidential$end, "%Y"))
 
yrng <- range(df$relative)
xrng <- c(yrMin,yrMax)
 
P <- PLOT + geom_rect(aes(NULL, NULL, xmin = start, xmax = end, 
                     fill = party, group=1, color= NULL), ymin = yrng[1], ymax = yrng[2], 
                 data = presidential, alpha = I(2/10), show_guide=T) + scale_fill_manual("Party", values = 
                   c("Democratic" = "blue", "Republican" = "red"))
direct.label(P, mymethod)

Written by Kristoffer Magnusson, a researcher in clinical psychology. You should follow him on Bluesky or on Twitter.

Big Data ggplot2 MySQL R Tutorial Google n-gram

Published April 12, 2012 (View on GitHub)

Buy Me A Coffee

Buy Me a Coffee ☕

(or use PayPal)

A huge thanks to the 175 supporters who've bought me a 422 coffees!

Steffen bought ☕☕☕☕☕☕☕☕☕☕☕☕ (12) coffees

I love your visualizations. Some of the best out there!!!

Jason Rinaldo bought ☕☕☕☕☕☕☕☕☕☕ (10) coffees

I've been looking for applets that show this for YEARS, for demonstrations for classes. Thank you so much! Students do not need to tolarate my whiteboard scrawl now. I'm sure they'd appreciate you, too.l

Shawn Bergman bought ☕☕☕☕☕ (5) coffees

Thank you for putting this together! I am using these visuals and this information to teach my Advanced Quant class.

anthonystevendick@gmail.com bought ☕☕☕☕☕ (5) coffees

I've been using a lot of your ideas in a paper I'm writing and even borrowed some of your code (cited of course). But this site has been so helpful I think, in addition, I owe you a few coffees!

Chip Reichardt bought ☕☕☕☕☕ (5) coffees

Hi Krisoffer, these are great applets and I've examined many. I'm writing a chapter for the second edition of "Teaching statistics and quantitative methods in the 21st century" by Joe Rodgers (Routledge). My chapter is on the use of applets in teaching statistics. I could well be describing 5 of yours. Would you permit me to publish one or more screen shots of the output from one or more of your applets. I promise I will be saying very positive things about your applets. If you are inclined to respond, my email address if Chip.Reichardt@du.edu.

Someone bought ☕☕☕☕☕ (5) coffees

Nice work! Saw some of your other publications and they are also really intriguing. Thanks so much!

JDMM bought ☕☕☕☕☕ (5) coffees

You finally helped me understand correlation! Many, many thanks... 😄

@VicCazares bought ☕☕☕☕☕ (5) coffees

Good stuff! It's been so helpful for teaching a Psych Stats class. Cheers!

Dustin M. Burt bought ☕☕☕☕☕ (5) coffees

Excellent and informative visualizations!

Someone bought ☕☕☕☕☕ (5) coffees

@metzpsych bought ☕☕☕☕☕ (5) coffees

Always the clearest, loveliest simulations for complex concepts. Amazing resource for teaching intro stats!

Ryo bought ☕☕☕☕☕ (5) coffees

For a couple years now I've been wanting to create visualizations like these as a way to commit these foundational concepts to memory. But after finding your website I'm both relieved that I don't have to do that now and pissed off that I couldn't create anything half as beautiful and informative as you have done here. Wonderful job.

Diarmuid Harvey bought ☕☕☕☕☕ (5) coffees

You have an extremely useful site with very accessible content that I have been using to introduce colleagues and students to some of the core concepts of statistics. Keep up the good work, and thanks!

Michael Hansen bought ☕☕☕☕☕ (5) coffees

Keep up the good work!

Michael Villanueva bought ☕☕☕☕☕ (5) coffees

I wish I could learn more from you about stats and math -- you use language in places that I do not understand. Cohen's D visualizations opened my understanding. Thank you

Someone bought ☕☕☕☕☕ (5) coffees

Thank you, Kristoffer

Pål from Norway bought ☕☕☕☕☕ (5) coffees

Great webpage, I use it to illustrate several issues when I have a lecture in research methods. Thanks, it is really helpful for the students:)

@MAgrochao bought ☕☕☕☕☕ (5) coffees

Joseph Bulbulia bought ☕☕☕☕☕ (5) coffees

Hard to overstate the importance of this work Kristoffer. Grateful for all you are doing.

@TDmyersMT bought ☕☕☕☕☕ (5) coffees

Some really useful simulations, great teaching resources.

@lakens bought ☕☕☕☕☕ (5) coffees

Thanks for fixing the bug yesterday!

@LinneaGandhi bought ☕☕☕☕☕ (5) coffees

This is awesome! Thank you for creating these. Definitely using for my students, and me! :-)

@ICH8412 bought ☕☕☕☕☕ (5) coffees

very useful for my students I guess

@KelvinEJones bought ☕☕☕☕☕ (5) coffees

Preparing my Master's student for final oral exam and stumbled on your site. We are discussing in lab meeting today. Coffee for everyone.

Someone bought ☕☕☕☕☕ (5) coffees

What a great site

@Daniel_Brad4d bought ☕☕☕☕☕ (5) coffees

Wonderful work!

David Loschelder bought ☕☕☕☕☕ (5) coffees

Terrific work. So very helpful. Thank you very much.

@neilmeigh bought ☕☕☕☕☕ (5) coffees

I am so grateful for your page and can't thank you enough!

@giladfeldman bought ☕☕☕☕☕ (5) coffees

Wonderful work, I use it every semester and it really helps the students (and me) understand things better. Keep going strong.

Dean Norris bought ☕☕☕☕☕ (5) coffees

Sal bought ☕☕☕☕☕ (5) coffees

Really super useful, especially for teaching. Thanks for this!

dde@paxis.org bought ☕☕☕☕☕ (5) coffees

Very helpful to helping teach teachers about the effects of the Good Behavior Game

@akreutzer82 bought ☕☕☕☕☕ (5) coffees

Amazing visualizations! Thank you!

@rdh_CLE bought ☕☕☕☕☕ (5) coffees

So good!

tchipman1@gsu.edu bought ☕☕☕ (3) coffees

Hey, your stuff is cool - thanks for the visual

Hugo Quené bought ☕☕☕ (3) coffees

Hi Kristoffer, Some time ago I've come up with a similar illustration about CIs as you have produced, and I'm now also referring to your work:<br>https://hugoquene.github.io/QMS-EN/ch-testing.html#sec:t-confidenceinterval-mean<br>With kind regards, Hugo Quené<br>(Utrecht University, Netherlands)

Tor bought ☕☕☕ (3) coffees

Thanks so much for helping me understand these methods!

Amanda Sharples bought ☕☕☕ (3) coffees

Soyol bought ☕☕☕ (3) coffees

Someone bought ☕☕☕ (3) coffees

Kenneth Nilsson bought ☕☕☕ (3) coffees

Keep up the splendid work!

@jeremywilmer bought ☕☕☕ (3) coffees

Love this website; use it all the time in my teaching and research.

Someone bought ☕☕☕ (3) coffees

Powerlmm was really helpful, and I appreciate your time in putting such an amazing resource together!

DR AMANDA C DE C WILLIAMS bought ☕☕☕ (3) coffees

This is very helpful, for my work and for teaching and supervising

Georgios Halkias bought ☕☕☕ (3) coffees

Regina bought ☕☕☕ (3) coffees

Love your visualizations!

Susan Evans bought ☕☕☕ (3) coffees

Thanks. I really love the simplicity of your sliders. Thanks!!

@MichaMarie8 bought ☕☕☕ (3) coffees

Thanks for making this Interpreting Correlations: Interactive Visualizations site - it's definitely a great help for this psych student! 😃

Zakaria Giunashvili, from Georgia bought ☕☕☕ (3) coffees

brilliant simulations that can be effectively used in training

Someone bought ☕☕☕ (3) coffees

@PhysioSven bought ☕☕☕ (3) coffees

Amazing illustrations, there is not enough coffee in the world for enthusiasts like you! Thanks!

Cheryl@CurtinUniAus bought ☕☕☕ (3) coffees

🌟What a great contribution - thanks Kristoffer!

vanessa moran bought ☕☕☕ (3) coffees

Wow - your website is fantastic, thank you for making it.

Someone bought ☕☕☕ (3) coffees

mikhail.saltychev@gmail.com bought ☕☕☕ (3) coffees

Thank you Kristoffer This is a nice site, which I have been used for a while. Best Prof. Mikhail Saltychev (Turku University, Finland)

Someone bought ☕☕☕ (3) coffees

Ruslan Klymentiev bought ☕☕☕ (3) coffees

@lkizbok bought ☕☕☕ (3) coffees

Keep up the nice work, thank you!

@TELLlab bought ☕☕☕ (3) coffees

Thanks - this will help me to teach tomorrow!

SCCT/Psychology bought ☕☕☕ (3) coffees

Keep the visualizations coming!

@elena_bolt bought ☕☕☕ (3) coffees

Thank you so much for your work, Kristoffer. I use your visualizations to explain concepts to my tutoring students and they are a huge help.

A random user bought ☕☕☕ (3) coffees

Thank you for making such useful and pretty tools. It not only helped me understand more about power, effect size, etc, but also made my quanti-method class more engaging and interesting. Thank you and wish you a great 2021!

@hertzpodcast bought ☕☕☕ (3) coffees

We've mentioned your work a few times on our podcast and we recently sent a poster to a listener as prize so we wanted to buy you a few coffees. Thanks for the great work that you do!Dan Quintana and James Heathers - Co-hosts of Everything Hertz

Cameron Proctor bought ☕☕☕ (3) coffees

Used your vizualization in class today. Thanks!

eshulman@brocku.ca bought ☕☕☕ (3) coffees

My students love these visualizations and so do I! Thanks for helping me make stats more intuitive.

Someone bought ☕☕☕ (3) coffees

Adrian Helgå Vestøl bought ☕☕☕ (3) coffees

@misteryosupjoo bought ☕☕☕ (3) coffees

For a high school teacher of psychology, I would be lost without your visualizations. The ability to interact and manipulate allows students to get it in a very sticky manner. Thank you!!!

Chi bought ☕☕☕ (3) coffees

You Cohen's d post really helped me explaining the interpretation to people who don't know stats! Thank you!

Someone bought ☕☕☕ (3) coffees

You doing useful work !! thanks !!

@ArtisanalANN bought ☕☕☕ (3) coffees

Enjoy.

@jsholtes bought ☕☕☕ (3) coffees

Teaching stats to civil engineer undergrads (first time teaching for me, first time for most of them too) and grasping for some good explanations of hypothesis testing, power, and CI's. Love these interactive graphics!

@notawful bought ☕☕☕ (3) coffees

Thank you for using your stats and programming gifts in such a useful, generous manner. -Jess

Mateu Servera bought ☕☕☕ (3) coffees

A job that must have cost far more coffees than we can afford you ;-). Thank you.

@cdrawn bought ☕☕☕ (3) coffees

Thank you! Such a great resource for teaching these concepts, especially CI, Power, correlation.

Julia bought ☕☕☕ (3) coffees

Fantastic work with the visualizations!

@felixthoemmes bought ☕☕☕ (3) coffees

@dalejbarr bought ☕☕☕ (3) coffees

Your work is amazing! I use your visualizations often in my teaching. Thank you.

@PsychoMouse bought ☕☕☕ (3) coffees

Excellent! Well done! SOOOO Useful!😊 🐭

Someone bought ☕☕ (2) coffees

Thanks, your work is great!!

Dan Sanes bought ☕☕ (2) coffees

this is a superb, intuitive teaching tool!

@whlevine bought ☕☕ (2) coffees

Thank you so much for these amazing visualizations. They're a great teaching tool and the allow me to show students things that it would take me weeks or months to program myself.

Someone bought ☕☕ (2) coffees

@notawful bought ☕☕ (2) coffees

Thank you for sharing your visualization skills with the rest of us! I use them frequently when teaching intro stats.

Someone bought ☕ (1) coffee

You are awesome

Thom Marchbank bought ☕ (1) coffee

Your visualisations are so useful! Thank you so much for your work.

georgina g. bought ☕ (1) coffee

thanks for helping me in my psych degree!

Someone bought ☕ (1) coffee

Thank You for this work.

Kosaku Noba bought ☕ (1) coffee

Nice visualization, I bought a cup of coffee.

Someone bought ☕ (1) coffee

Thomas bought ☕ (1) coffee

Great. Use it for teaching in psychology.

Someone bought ☕ (1) coffee

It is the best statistics visualization so far!

Ergun Pascu bought ☕ (1) coffee

AMAZING Tool!!! Thank You!

Ann Calhoun-Sauls bought ☕ (1) coffee

This has been a wonderful resource for my statistics and research methods classes. I also occassionally use it for other courses such as Theories of Personality and Social Psychology

David Britt bought ☕ (1) coffee

nicely reasoned

Mike bought ☕ (1) coffee

I appreciate your making this site available. Statistics are not in my wheelhouse, but the ability to display my data more meaningfully in my statistics class is both educational and visually appealing. Thank you!

Jayne T Jacobs bought ☕ (1) coffee

Andrew J O'Neill bought ☕ (1) coffee

Thanks for helping understand stuff!

Someone bought ☕ (1) coffee

Shawn Hemelstrand bought ☕ (1) coffee

Thank you for this great visual. I use it all the time to demonstrate Cohen's d and why mean differences affect it's approximation.

Adele Fowler-Davis bought ☕ (1) coffee

Thank you so much for your excellent post on longitudinal models. Keep up the good work!

Stewart bought ☕ (1) coffee

This tool is awesome!

Someone bought ☕ (1) coffee

Aidan Nelson bought ☕ (1) coffee

Such an awesome page, Thank you

Someone bought ☕ (1) coffee

Ellen Kearns bought ☕ (1) coffee

Dr Nazam Hussain bought ☕ (1) coffee

Someone bought ☕ (1) coffee

Eva bought ☕ (1) coffee

I've been learning about power analysis and effect sizes (trying to decide on effect sizes for my planned study to calculate sample size) and your Cohen's d interactive tool is incredibly useful for understanding the implications of different effect sizes!

Someone bought ☕ (1) coffee

Thanks a lot!

Someone bought ☕ (1) coffee

Reena Murmu Nielsen bought ☕ (1) coffee

Tony Andrea bought ☕ (1) coffee

Thanks mate

Tzao bought ☕ (1) coffee

Thank you, this really helps as I am a stats idiot :)

Melanie Pflaum bought ☕ (1) coffee

Sacha Elms bought ☕ (1) coffee

Yihan Xu bought ☕ (1) coffee

Really appreciate your good work!

@stevenleung bought ☕ (1) coffee

Your visualizations really help me understand the math.

Junhan Chen bought ☕ (1) coffee

Someone bought ☕ (1) coffee

Michael Hansen bought ☕ (1) coffee

ALEXANDER VIETHEER bought ☕ (1) coffee

mather bought ☕ (1) coffee

Someone bought ☕ (1) coffee

Bastian Jaeger bought ☕ (1) coffee

Thanks for making the poster designs OA, I just hung two in my office and they look great!

@ValerioVillani bought ☕ (1) coffee

Thanks for your work.

Someone bought ☕ (1) coffee

Great work!

@YashvinSeetahul bought ☕ (1) coffee

Someone bought ☕ (1) coffee

Angela bought ☕ (1) coffee

Thank you for building such excellent ways to convey difficult topics to students!

@inthelabagain bought ☕ (1) coffee

Really wonderful visuals, and such a fantastic and effective teaching tool. So many thanks!

Someone bought ☕ (1) coffee

Yashashree Panda bought ☕ (1) coffee

I really like your work.

Ben bought ☕ (1) coffee

You're awesome. I have students in my intro stats class say, "I get it now," after using your tool. Thanks for making my job easier.

Gabriel Recchia bought ☕ (1) coffee

Incredibly useful tool!

Shiseida Sade Kelly Aponte bought ☕ (1) coffee

Thanks for the assistance for RSCH 8210.

@Benedikt_Hell bought ☕ (1) coffee

Great tools! Thank you very much!

Amalia Alvarez bought ☕ (1) coffee

@noelnguyen16 bought ☕ (1) coffee

Hi Kristoffer, many thanks for making all this great stuff available to the community!

Eran Barzilai bought ☕ (1) coffee

These visualizations are awesome! thank you for creating it

Someone bought ☕ (1) coffee

Chris SG bought ☕ (1) coffee

Very nice.

Gray Church bought ☕ (1) coffee

Thank you for the visualizations. They are fun and informative.

Qamar bought ☕ (1) coffee

Tanya McGhee bought ☕ (1) coffee

@schultemi bought ☕ (1) coffee

Neilo bought ☕ (1) coffee

Really helpful visualisations, thanks!

Someone bought ☕ (1) coffee

This is amazing stuff. Very slick.

Someone bought ☕ (1) coffee

Sarko bought ☕ (1) coffee

Thanks so much for creating this! Really helpful for being able to explain effect size to a clinician I'm doing an analysis for.

@DominikaSlus bought ☕ (1) coffee

Thank you! This page is super useful. I'll spread the word.

Someone bought ☕ (1) coffee

Melinda Rice bought ☕ (1) coffee

Thank you so much for creating these tools! As we face the challenge of teaching statistical concepts online, this is an invaluable resource.

@tmoldwin bought ☕ (1) coffee

Fantastic resource. I think you would be well served to have one page indexing all your visualizations, that would make it more accessible for sharing as a common resource.

Someone bought ☕ (1) coffee

Fantastic Visualizations! Amazing way to to demonstrate how n/power/beta/alpha/effect size are all interrelated - especially for visual learners! Thank you for creating this?

@jackferd bought ☕ (1) coffee

Incredible visualizations and the best power analysis software on R.

Cameron Proctor bought ☕ (1) coffee

Great website!

Someone bought ☕ (1) coffee

Hanah Chapman bought ☕ (1) coffee

Thank you for this work!!

Someone bought ☕ (1) coffee

Jayme bought ☕ (1) coffee

Nice explanation and visual guide of Cohen's d

Bart Comly Boyce bought ☕ (1) coffee

thank you

Dr. Mitchell Earleywine bought ☕ (1) coffee

This site is superb!

Florent bought ☕ (1) coffee

Zampeta bought ☕ (1) coffee

thank you for sharing your work.

Mila bought ☕ (1) coffee

Thank you for the website, made me smile AND smarter :O enjoy your coffee! :)

Deb bought ☕ (1) coffee

Struggling with statistics and your interactive diagram made me smile to see that someone cares enough about us strugglers to make a visual to help us out!😍

Someone bought ☕ (1) coffee

@exerpsysing bought ☕ (1) coffee

Much thanks! Visualizations are key to my learning style!

Someone bought ☕ (1) coffee

Archived Comments (10)

Jake 2019-07-12

I have been trying to get your code to run but am wondering if there is something I am doing wrong. It appears that I'm having a problem loading the google_n-gram_total_table.txt file. I get an error that the function is deprecated and I have not been able to figure out an alternative to load the database and use it. Any thoughts or ways the code can be modified to work again? Thanks!

Nothing good, just me trying 2019-03-19

Amazing!

Hassan Monfared 2014-11-30

Grate work

Ron Wates 2013-07-12

Hi Kris,

Thanks for your R/MySQL advice. It answered a question that I'd been struggling with for some time.

Thanks,

Ron Wates.

Andrew Breidenbach 2012-10-12

Hi Kris,

I am trying to recreate this experiment (as part of my thesis) on my 2.6 GHz Thinkpad which runs Windows. I've pretty much followed your instructions so far, albeit I've used the command line to set up the DB and the table instead of a GUI. I'm trying to load the giant .csv file directly through the command line and I'm unsure if it's working or not. I actually did try to use a GUI at one point, HeidiSQL, to import the .csv but it froze/stopped responding once it realized how large the file was. What was this process like for you? Am I not being patient enough? Also, do you have any tips on how to get RMySQL to work for Windows? This data is so darn cool but the size of the files makes working with them unwieldy!

Kristoffer Magnusson 2012-10-12

Hi Andrew! If I remember correctly it took about 8 hours to import to 1-gram csv-file into MySQL (I'm guessing that's dependent on what indexing that's being used). After I finished this article I began working with the 2-gram data files, but my computer crashed after the import had been loading for 8 days, so I gave it up. So some patience is needed :). Unfortunately, I've never worked with RMySQL on Windows so I can't really help you there.

Hope this helps!

Tobias 2012-06-08

Thanks for the tutorial, but maybe you could set the length of the Year, Match_count, Page_count, Volume_Count to a shorter value than 11, that needs less storage especially with a big table.

Neil Murphy 2012-05-09

Excellent article, it inspires me to use some of your ideas especially as I am just starting tio research natural language propcessing, information retrieval etc.

best wishes and thanks

Tal Galili 2012-04-14

Fantastic post Kris!

Kristoffer Magnusson 2012-04-14

Thank you!

How to work with Google n-gram data sets in R using MySQL

Introduction

Setting up MySQL

Get MySQL

Download the raw data

Import Google 1-gram into a MySQL database

Querying MySQL from R

Optimization

Cleaning up the data

Creating the final data frame

Results

Without smoothing

With smoothing

Women vs Men

Sweden vs Norway vs Denmark vs Finland

Psychodynamic vs Psychoanalysis vs Psychoanalytic vs Psychotherapy

Jesus vs Christ

Gay vs Lesbian vs Homosexual vs Heterosexual

Socialism vs Capitalism

But isn’t this exactly like using Google Ngram Viewer except a lot sexier?

Ggplot2 R code used here

Smoothed plots

Aggregated plot

Buy Me A Coffee

Sponsors

Questions & Comments

Archived Comments (10)