How to extract Twitter tweets data and followers to Excel

23

In this tutorial,I will show you how to extract or scrape Twitter data such as tweets and followers to excel using ready made Python scripts.I will also show you how to download photos and videos by a #hashtag or search query.I will use both the REST API and the streaming API.Lastly,I will use Quintly to download tweets to Excel (no programming involved).

Below image shows extracted Tweets to Excel
extracted tweets to excel

Below image shows extracted Followers to Excel
extracted twitter followers to excel

The scripts I will share with you are complete working scripts.Even if you are not very familiar with Programming languages or not familiar with Python per se through the simple instructions outlined in this tutorial you can be able to extract data from Twitter.

The tutorial is divided into 4 parts

  • How to extract or scrape Twitter tweets to Excel with columns of tweet’s text,retweet count and favourite count using REST API
  • How to extract or scrape Twitter followers usernames or screennames to Excel using REST API
  • How to download photos and videos by a #hashtag or search query using Streaming API
  • Using Quintly to download Twitter data to Excel without programming

Let’s get started

Requirements

In order to run the scripts in this tutorial, you must install

How to install Python 2.7.x

For Windows and Linux/Ubuntu users follow Step 2 of this tutorial to install Python.

Mac OS X 10.8 comes with Python 2.7 pre-installed by Apple.If not installed you can install it from the command line by writing brew install python but make sure you have brew installed on your MAC.

install python on mac os x

How to install PIP

PIP is a python tool for installing and managing Python packages

For Windows:

  • In order to install pip go to this link and download get-pip.py to your computer by selecting “save link as…” from the pop up window.
    installing pip on windows
  • Then click or open the file to run it
    installing pip on command line
  • Before we can use PIP we need to add the Python scripts folder to the windows path variable.Open the CMD or PowerShell copy and paste below values then close the CMD or Powershell and reopen.
    [Environment]::SetEnvironmentVariable(“Path”,”$env:Path;C:\Python27\Scripts”, “User”)

For MAC OS X:

PIP comes by default with Python for MAC.Once Python is installed we can use PIP right away.

pip installation on mac os

How to install Tweepy

After Python and PIP are installed successfully then we have to install a Python library called Tweepy which enables us to access the Twitter API using Python.

You can install Tweepy by running the following command on the command line.

pip install tweepy

how to install tweepy by pip

Get authorization credentials to access Twitter API

In  order to access and download data from Twitter API, you need to have credentials such as keys and access tokens.You get them by simply creating an APP with Twitter.

Follow these steps:

  • Go to Twitter Application Management and log in,with your Twitter account
  • Click “create New app” button
  • Supply the necessary required fields, read and agree to the Twitter Developer Agreement
  • Submit the form

If everything went fine you will have a window similar to the one below with your keys and access tokens under the keys and access tokens tab.We will use these credentials in our example codes.

twitter api consumer key and consumer secret keytwitter api access token and access token secret

Now that we have all the requirements met let us extract some data to excel.

How to extract or scrape Twitter tweets to Excel using REST API

Download tweets.py script and save it somewhere on your computer.Put the script inside a folder.This is the same folder which the script will save the Excel spreadsheet after scraping tweets.The script will extract a user’s recent tweets up to a maximum of 3200.The script will get the following data:tweet ID, the tweet’s created time,tweet’s text,tweet’s retweet count and tweet’s favourite count.

You can modify the script to return more data.Here is a link which shows the structure of a tweet object together with its attributes.

Now from PowerShell for Windows and Terminal program for Mac OS X/Linux/UNIX navigate and open this folder containing your script.

For example, my script is saved inside “Scraping folder” on my desktop

python script to download a user tweets to excel

Before running the code you need to edit the code to include the credentials provided by the Twitter App Management interface in the previous step.

#Twitter API credentials
consumer_key = "Your consumer key goes here"
consumer_secret = "Your consumer secret goes here"
access_key = "Your access key goes here"
access_secret = "Your access secret goes here"

Also, input twitter username you want to download tweets from.In this example, we will scrape Donald Trump twitter page.

if __name__ == '__main__':
    #pass in the username of the account you want to download
    get_all_tweets("realDonaldTrump") #Modify this username

Run the code from the command line by typing python tweets.py but make sure while you are on CMD window navigate to the same folder(my script is in scraping folder)  where your script is saved.

downloading tweets to excel

3203 recent tweets of Donald Trump downloaded.

scrape tweets to excel

The Excel spreadsheet will be in the same folder where you saved your script earlier.See how the tweet “Mexico will pay for the wall” has been re-tweeted and Favorited.

extracted tweets to excel

How to extract or scrape Twitter followers usernames to Excel using REST API

Download followers.py script and save it somewhere on your computer.Put the script inside a folder.This is the same folder which the script will save the Excel spreadsheet after scraping tweets.

The script will extract a user’s followers to Excel spreadsheet.This scripts stops after every 300 names for 15 minutes, and then continues. This makes sure that it doesn’t run into problems. This will obviously take ages for large accounts.You basically just have to leave the program running if you want the next set.

Twitter has put limitations on the number of user which can be searched for a certain period of time.Otherwise you can be rate limited.

If you want to terminate the program at any time just press CTRL+C.

Before running the script modify the API credentials and the username whose followers you want to download.

accountvar = "realDonaldTrump" #Modify this username
#todo: upgrade this to read usernames from a file.
print "searching for followers of "+accountvar

consumer_key = "Your consumer key goes here"
consumer_secret = "Your consumer secret goes here"
access_token = "Your access key goes here"
access_token_secret = "Your access secret goes here"

Like previous step you run the script by wrting python followers.py on the command line.

extracting twitter followers to excel

Rate limit reached after extracting 300 followers and then sleeping for 891 seconds(15 minutes)

twitter rate limit reached

Below is the Excel spreadsheet in the same folder where you saved your script earlier with the extracted followers screen names,their followers count,statuses count and their locations.

extracted twitter followers in excel

How to download Tweets,photos and videos by a #hashtag or search query using Streaming API

Streaming API allows us to listen and grab live Twitter data.Using the scripts you can listen and grab any tweet which mentions a certain term or hashtag let say “#Football” in this tutorial and save the tweet to Excel and it’s associated media picture or video to the same folder which contains your script.

Download streaming.py script and save it somewhere on your computer.Put the script inside a folder.This is the same folder which the script will save the Excel spreadsheet after scraping tweets.

Before running the script modify the API credentials and input the search query you want to search.Follow these guidelines on how to build a query for better search results.

# Authentication details. To  obtain these visit dev.twitter.com
consumer_key = "Your consumer key goes here"
consumer_secret = "Your consumer secret goes here"
access_token = "Your access key goes here"
access_token_secret = "Your access secret goes here"

accountvar = "#Football"  #Search query goes here

Like previous step you run the script by wrting python streaming.py on the command line.

extract tweet images and videos from twitter

The script will generate an Excel spreadsheet,images and videos associated with #Football in real time.It will run continuously until you terminate it by pressing CTRL+C.

extract tweets using streaming API

Using Quintly to download Twitter data to Excel without programming

Now I will show you how to extract Twitter data into Excel using Quintly without programming.Quintly is a web-based tool that offers easy,efficient and  automated access to social media profiles.

It provides in-depth analytics, competitive benchmarking, graphs and reports, not only for Twitter but across all Social Media platforms such as Facebook, LinkedIn, Google+, Pinterest and Instagram.

You can use Quintly not only for your own profiles but for any public social media profile including all public Facebook pages and Twitter accounts.You can benchmark up to 100 profiles.
Quintly_banner
With Quintly Twitter analytics you get the following information on your dashboard

  • Follower Analysis– such as the followers count,the followers change rate of your profile or competitor’s profile
  • Detailed Tweet Analysis – such as the number of retweets and favorites of your profile or competitor’s profile
  • Interaction Analytics -retweets, replies and favorites directly on tweets of your profile or competitor’s profile
  • Key Interacting Users -Identify the key interacting users of your profile or competitor’s profile
  • Customer Care Metrics – Sustainably compare the customer care efforts of your profile or competitor’s profile such as response times to user questions and other requests.

Step 1:Sign up for a free account with Quintly

A free trial account will enable you to try all of the packages for 14 days without putting in a credit card. You will be able to export data into Excel or Powerpoint.

quintly twitter analytics price

Step 2:Add a profile

After creating an account with Quintly, go to “Manage Profiles” and then click “Add Profile”.

quintly manage profiles

On the pop up window search for the Twitter account (Example:Donald Trump) that you want to analyse and then click on “Add Selected Profiles” as shown below.

quintly add twitter profile

Step 3:Analysing Twitter data and exporting to Excel

Click  “Twitter” on the dashboard menu, and then choose any metric you want to analyse on the left side table such as profile Tweets and Followers. You can also specify the date range (don’t forget that you are limited to within 30 days for a free account).

Below I selected “Own and Retweeted Tweets table” so that I can replicate what we did in the previous steps without programming.Remember the tweet “Mexico will pay for the wall!”

Quintly twitter analytics dashboard

Once you have done this, export the results into either Excel, CSV, JPG, PNG, PDF or PPTX for further analysis.

extract twitter data to excel with quintly without programming

Conclusion

The tutorial just covered few attributes we can extract from a tweet object.You can modify the codes and extract more tweet object attributes.Here you can find a full list of a tweet object attributes in JSON format returned by Twitter API.

If you have any question or comment please let me know below.

Click Here to Leave a Comment Below 23 comments
Michal - September 6, 2016

Hi Paulo,

first of all thank you a lot for your tutorials, I’m learning a lot with them.

I have an issue when trying to running the;”python tweet.py”

when I run the command in powershell it gives me an error at line 4 of the module:

“ImportError: No module named tweepy”

I didn’t get how to install the tweepy Python library, can you please help

btw I’m using a Windows Pc

thank a lot

M

Reply
    Paulo - September 6, 2016

    To install Tweepy first make sure you have pip installed.
    You can verify if pip is installed by typing pip on the command line and you should get something similar to the below image.
    verifying pip installation
    After successfully installation of pip,you can type the following command to install Tweepy.

    pip install tweepy

    You should get something similar to the below image when Tweepy starts the installation process.
    tweepy installation

    Hopefully, it helps.

    Reply
    Good job - April 18, 2017

    Good post, keep

    Reply
daniel - September 15, 2016

These are really great scripts. Probably the best I have come across since I have delved into mapping Twitter data. Im primarily concerned with geolocation and the streaming api. Do you have a suggestion on how/where I can add lines to the script to include x/y in the exported csv when they are supplied by the user? Specifically on the streaming version. Im doing my best to add it in there but haven’t been successful. Many thanks for sharing your work! THANKS

Reply
    Paulo - September 16, 2016

    Hi Daniel,

    Please refer to this tutorial where I extract data from Twitter API in raw JSON then you can modify my scripts to write the “geo” attribute to CSV.

    Reply
daniel - September 17, 2016

Thank you so much! I’ll let you know how it goes! Im very raw in Python and really appreciate the spirit of the open source. I hope you know how much it helps us out.

Reply
James - December 19, 2016

Hi Paulo.

Firstly, I just wanted to say thank you, your twitter scraping script was actually the very thing I was looking to teach myself, yet did not know how to put into words!

I came across your site a few months ago, and have finally been able to put some of your script to work – If you don’t mind, I would like to ask 2 follow up questions?

1 – is it possible to create a “loop” of sorts to go through a list of names in an excel sheet and have the program search each of them within the same script.

2 and if yes, could i modify the script to dump all data to a single csv instead of one for each screen_name?

Again, thank you for your great advice!, It’s actually motivated me to try and learn coding! Im starting on Python 2.7 with code acadamy & automate the easy stuff

Thanks again!

Reply
    Paulo - December 19, 2016

    Hi James,
    It is possible to load the screen names from excel sheet and dump all the data to a single CSV. All the skills to achieve it can be learned from “Automate the boring stuff with Python book”. Stick with it and write some codes.

    All the bests in learning Python. I self-taught myself Python and I’m in love with it. I’m no expert but I can read, edit and reuse other people codes.

    Do check out GitHub for some cool open source Python projects/codes for inspiration and getting your creative juice flowing.

    If your code throws an error, Google it and Just be persistent in learning that is the only way of learning to code.Be persistent.

    Reply
    qp - January 20, 2017

    Hey James!
    I was wondering if you figured it out? I am trying to do the same…
    I just started with Python for this (slightly over ambitious) project, so this is beyond my skills to customise and was hoping for advice/feedback…

    Reply
Jean - January 30, 2017

Hi Paulo,

I would like to take the above scripts and add in a few of the other fields such as followers_count.

Could you explain how I would do this?

Thanks.

Reply
    Paulo - February 1, 2017

    Hi Jean,

    My scripts are already outputting followers_count.

    Reply
Lisa - March 14, 2017

Hi Paulo,

I tried to run the streaming.py as described, but I kept getting a csv file with zero bytes,
and the output in Jupyter notebook print “Showing all new tweets for #Football”, and not data return,
any idea?

Thanks a lot!

Lisa

Reply
    Paulo - March 21, 2017

    Hi Lisa,
    I tested the script. It is still working fine.
    Get in touch through the contact form and I will have a look on your issue.

    Reply
Jonathan - March 19, 2017

Good Day Paulo,

I would just like to thank you for the above tutorial, it has proven to be very useful. I am very new to python and after running the above code to save all the tweets by Donald Trump to an excel file, all the data is saved to one column instead of separate columns. Any help in solving this issue would be greatly appreciated.

Reply
    Paulo - March 21, 2017

    Hi Jonathan,

    Which program are you using to open the CSV? I had someone facing the same issue using MS Excel 2016.

    To resolve the issue she imported CSV file in Excel with Text import wizard in Excel. Give it a try and let me know if it works.

    Import Text in Excel

    Reply
      Jonathan - March 23, 2017

      Good Day Paulo,

      Yes, I was using MS Excel 2016 as well. Importing the data as suggested as fixed the issue. Thank you for your response and the great tutorial

      Reply
Bjørn Erik - March 21, 2017

Hi! I ran your script and it worked. Love it!
However i have one big issue. The user im scraping is using some norwegian characters, æ ø å namely. These show up as some weird symbols in my excel-file, which im guessing is a result of utf-8? Id like to preserve the norwegian characters in the database. How do i work around that?

Thanks.

Reply
    Paulo - March 21, 2017

    Hi Bjorn,

    If you are using Windows, you can open the CSV with Notepad, “Save as” new file name then set the encoding to UTF-8.

    Encoding UTF-8

    Reply
Jason - April 20, 2017

Hey Paulo
Still using your script months later – it’s great! Thank you
Quick question – these scraped Twitter IDs can be used for creating custom audiences for twitter campaigns (obviously!). But I recently noticed that when you run a campaign within twitter – they allow you to choose these audiences without having to add the IDs separately.
So, for example, if you want to target a particular audience (lets say ‘@happy_guy’ with 224k followers) – there’s no real need to download all his followers – as twitter allows you to select his name when you create your target – and it does it all automatically.
Or am I missing something obvious (ie a benefit that having the individual IDs gives you that you don’t get when you use the twitter account within their campaign tools)?
Thanks
Jason
PS I do realise that with the individual IDs – you can strip out and filter by geography and by number of tweets/size of followers. But is there any other obvious advantage to downloading all these twitter IDs? Thanks xx

Reply
    Paulo - April 24, 2017

    Hi Jason,
    I’m not into Twitter ads, I created the tutorial out of curiosity playing with Python, and Twitter API.

    Reply

Leave a Reply: