Folks,

In this blog we will learn how to extract the twitter trends from twitteR Package using R, after that we will learn how to save data into SQL Server using ODBC connection!

R Packages required: 

 

  • install.packages(“twitteR”): It provides an interface to the Twitter web API. For twitteR App & OAuth access setup – Visit this blog.
  • install.packages(“RODBC”) for OBDC connectivity. For ODBC connection setup for SQL Server Database/Oracle – Visit this blog .

Let’s get started!

twitteR package has getTrends function that can be used to extract the twitter trends based on a input parameter (woeid).

A WOEID (Where On Earth IDentifier) is a unique 32-bit reference identifier, originally defined by GeoPlanet and now assigned by Yahoo!, that identifies any feature on Earth.[Source: Wikipedia]

See below the WOEID for INDIA. You can use this link for WOEID look up.

woeid.png

Here is the Script for extracting the twitter trend & saving data into SQL Server for further analysis.

 
#------------------------------------------------------------------------#
# R Script Name: GetTrends.R
# R Script Description: This script collect the twitter trend data 
# from twitter API and dump into database.
#------------------------------------------------------------------------#
#-- Importing Required library
library("twitteR", lib.loc="~/R/win-library/3.3")
library("RODBC", lib.loc="~/R/win-library/3.3")

#-- Provide woeid from internet as per your requirement
#-- Below woeid is for INDIA
woeid <-23424848

#-- Fetching Access Keys for Twitter API, which is present in local file 
OAuth_Location <- "C:\\Users\\lenovo\\Desktop\\OAuth.csv"
twitter_OAuth <- read.csv(file=OAuth_Location, header=TRUE, sep=",",colClasses = "character")

  #-- Calling twitteR OAuth function
  setup_twitter_oauth(twitter_OAuth$Consumer_API_Key, twitter_OAuth$Consumer_API_Secret, 
                      twitter_OAuth$Access_Token, twitter_OAuth$Access_Token_Secret)

  #-- Extracting Trends using getTrends Function
  current_trends  <-  getTrends(woeid) 
  current_trends["trend_date"]  <-  Sys.Date()

  #-- Opening OBBC Connection and Saving Trends in Database
  my_conn  <-  odbcConnect("RSQL", uid="***", pwd="***")
  sqlSave(my_conn, current_trends, tablename = "t_current_trends", append = TRUE)

  #-- Closing/Removing unwanted values and data
  #remove(list=c("current_trends","woeid","OAuth_Location","twitter_OAuth"))
  close(my_conn)

 

Output:- IPL Cricket fever in INDIA, that’s why #KKRvSRH is trending on top today  🙂

trend.png

You can also scheduled this R Script using the windows scheduler, so that you get your trends in scheduled time automatically. For scheduling R script visit this blog.


Thanks!

Happy Learning! Your feedback would be appreciated!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s