<jupyter_start><jupyter_text>Course: Computational Thinking for Governance Analytics Prof. <NAME>, PhD * Visiting Professor of Computational Policy at Evans School of Public Policy and Governance, and eScience Institute Senior Data Science Fellow, University of Washington.* Professor of Government and Political Methodology, Pontificia Universidad Católica del Perú. _____ Data Preprocessing in Python: Data gathering 0. [The GitHub Repo](part0) 1. [Uploading files](part1a)2. [APIs](part2) 3. [Scraping](part3) 4. [Social media](part4)  One the first steps you should take when preparing an analytics project is to know where to read your files from. One usually gets a file from an email, or finds it in website, and just download it to the local computer. However, different from most software, like SPSS, EXCEL and STATA, **Python** or **R** need to know the **path** to the file precisely. Sometimes you are not aware of the folders' **tree** in your computer. For instance, once I have opened this Jupyter Notebook, I can find its path like this:<jupyter_code>import os
os.getcwd()<jupyter_output><empty_output><jupyter_text>____ 1. Uploading 'proprietary software' files Several times, you may find that you are given a file that was previously prepared with proprietary software. The most common in the policy field are:* SPSS (file extension: **sav**).* STATA (file extension: **dta**).* EXCEL (file extension: **xlsx** or **xls**).Getting these files up and running is the easiest, as they are often well organized and do not bring much pre processing challenges. However, I need to have the file where I am currently writing my code: For sure, you can leave the file anywhere you want in your machine; but you would need to know all the *path* to the file. To avoid that, it is better to store your data in the *cloud*. If you need the file in the same format, GitHub is great choice. For instance, I have a repository (aka *repo*) with several data files [here](https://github.com/EvansDataScience/data). I will upload the file *hsb_ok* into memory in the next examples. The files I will use look like a data table which are known as the **data frame**, like the ones you use in Excel. Python needs the help of external packages to read those data frames. **Pandas** is a great package that will make our life easier, so make sure it is installed:<jupyter_code>!pip show pandas<jupyter_output>Name: pandas
Version: 1.3.5
Summary: Powerful data structures for data analysis, time series, and statistics
Home-page: https://pandas.pydata.org
Author: The Pandas Development Team
Author-email: <EMAIL>
License: BSD-3-Clause
Location: /Users/JoseManuel/anaconda3/envs/Evans_GovAnalytics/lib/python3.7/site-packages
Requires: numpy, python-dateutil, pytz
Required-by: statsmodels, plotnine, mizani<jupyter_text>If you do not have *pandas*, please install it by writing:<jupyter_code>#!pip install pandas   #in<jupyter_output>Requirement already satisfied: pandas in /Users/JoseManuel/anaconda3/envs/Evans_GovAnalytics/lib/python3.7/site-packages (1.2.0)
Collecting pandas
  Downloading pandas-1.3.5-cp37-cp37m-macosx_10_9_x86_64.whl (11.0 MB)
[K     |████████████████████████████████| 11.0 MB 1.0 MB/s eta 0:00:01
[?25hRequirement already satisfied: python-dateutil>=2.7.3 in /Users/JoseManuel/anaconda3/envs/Evans_GovAnalytics/lib/python3.7/site-packages (from pandas) (2.8.1)
Requirement already satisfied: pytz>=2017.3 in /Users/JoseManuel/anaconda3/envs/Evans_GovAnalytics/lib/python3.7/site-packages (from pandas) (2021.1)
Requirement already satisfied: numpy>=1.17.3 in /Users/JoseManuel/anaconda3/envs/Evans_GovAnalytics/lib/python3.7/site-packages (from pandas) (1.20.0)
Requirement already satisfied: six>=1.5 in /Users/JoseManuel/anaconda3/envs/Evans_GovAnalytics/lib/python3.7/site-packages (from python-dateutil>=2.7.3->pandas) (1.15.0)
Installing collected packages: pandas
  Attempting uninstall: pandas
    F[...]<jupyter_text>You can also install it in Anaconda Navigator or via the Terminal. Remember that you are writing an interactive document, so I recommend that you write a **__** before the codes you do not need to execute more than once.  If you have an older version of Pandas than the one I have, you can update yours like this:<jupyter_code>#!pip install pandas -U<jupyter_output><empty_output><jupyter_text>[home](home)____ 1.1 Reading a STATA file:Let me open the **hsb_ok.dta** file:<jupyter_code>import pandas as pd

location='https://github.com/EvansDataScience/data/raw/master/'
fileDTA=location+'hsb_ok.dta'
dataStata=pd.read_stata(fileDTA)<jupyter_output><empty_output><jupyter_text>The object **dataStata** has received a data frame, let's check the contents:<jupyter_code>dataStata<jupyter_output><empty_output><jupyter_text>[home](home)____ 1.2 Reading an SPSS file Pandas [tells](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_spss.html) there is a function **read_spss**, so you may want to try this:<jupyter_code>fileSPSS=location + "hsb_ok.sav"
dataSPSS= pd.read_spss(fileSPSS)
import pyreadstat<jupyter_output><empty_output><jupyter_text>You need here some extra help from the package **pyreadstat**. Please install it:<jupyter_code>#!pip install pyreadstat<jupyter_output><empty_output><jupyter_text>Notice that **pyreadstat** cannot read from an url, so we need some some extra coding:<jupyter_code># to open link:
from urllib.request import urlopen
fileSPSS=location + "hsb_ok.sav"
response = urlopen(fileSPSS) 

# reading contents
content = response.read() 

# opening a file new file
fhandle = open('savingLocally.sav', 'wb') #wb?

# saving contents in that file
fhandle.write(content) 

# closing the file
fhandle.close()<jupyter_output><empty_output><jupyter_text>If you see the folder where you are writing this code, you should find that the file from the url is now in your computer. Now just read it:<jupyter_code>dataSPSS= pd.read_spss("savingLocally.sav")

# here it is
dataSPSS<jupyter_output><empty_output><jupyter_text>[home](home)_____ 1.3 Reading an EXCEL file:The steps to open an Excel file are very simple (you can read from a link):<jupyter_code># we keep using the same location
fileXLSX=location+'hsb_ok.xlsx'
dataExcel=pd.read_excel(fileXLSX) # new function<jupyter_output><empty_output><jupyter_text>If you get an error with a message complaining you do not have the library **xlrd** (xls) or the library **openpyxl** (xlsx), just install both using **!pip install**. If it works well you can see this:<jupyter_code>dataExcel<jupyter_output><empty_output><jupyter_text>Notice that the contents look different; as you do not see the categories but numbers. In general, you should always read the metadata documentation of the files you are downloading. [Here](https://github.com/UWDataScience2020/data/blob/master/hsb_ok_metadata.pdf) is the metadata for the current file.<jupyter_code>dataExcel.columns # just to see the column names<jupyter_output><empty_output><jupyter_text>Remember that an Excel file can have **several sheets**:<jupyter_code>fileXLSXs=location+'WA_COVID19_Cases_Hospitalizations_Deaths.xlsx'
dataExcelCovidCases=pd.read_excel(fileXLSXs,sheet_name='Cases')
dataExcelCovidDeaths=pd.read_excel(fileXLSXs,sheet_name='Deaths') 
dataExcelCovidHospitalizations=pd.read_excel(fileXLSXs,sheet_name='Hospitalizations') 
dataExcelCovidDataDictionary=pd.read_excel(fileXLSXs,sheet_name='Data Dictionary')<jupyter_output><empty_output><jupyter_text>Of course, now you have several data frames:<jupyter_code># basic statistics:
dataExcelCovidCases#.describe()
# checking data frame contents:
dataExcelCovidDeaths.info()
# checking number of rows and columns:
dataExcelCovidHospitalizations.shape
# seeing contents:
dataExcelCovidDataDictionary
# if you need more space:
pd.set_option('max_colwidth', 800)
dataExcelCovidDataDictionary<jupyter_output><empty_output><jupyter_text>[home](home)____ 2. Collecting data from APIsOpen data portals from the government and other organizations have APIs, a service that allows you to collect their data. Let's take a look a Seattle data about [Seattle Real Time Fire 911 Calls](https://dev.socrata.com/foundry/data.seattle.gov/kzjm-xkqj):<jupyter_code>from IPython.display import IFrame  
fromAPI="https://dev.socrata.com/foundry/data.seattle.gov/kzjm-xkqj" 
IFrame(fromAPI, width=900, height=500)<jupyter_output><empty_output><jupyter_text>That page tells you how to get the data into pandas. But first, you need to install **sodapy**. Then you can continue:<jupyter_code>#!pip install sodapy<jupyter_output><empty_output><jupyter_text>Let's follow some steps, according to the API:<jupyter_code>from sodapy import Socrata

# Unauthenticated client (using 'None')

client = Socrata("data.seattle.gov", None)

# If you have credentials:
# client = Socrata(data.seattle.gov,
#                  MyAppToken,
#                  username="<EMAIL>",
#                  password="<PASSWORD>")

# First 500 results, returned as JSON from API / converted to Python list of
# dictionaries by sodapy.
results = client.get("kzjm-xkqj", limit=2000)

# Convert to pandas DataFrame
results_df = pd.DataFrame.from_records(results)<jupyter_output>WARNING:root:Requests made without an app_token will be subject to strict throttling limits.<jupyter_text>You can see the results now:<jupyter_code>results_df<jupyter_output><empty_output><jupyter_text>Data from APIs may need some more pre processing than the previous files. Besides, you should study the API documentation to know how to interact with the portal. Not every open data portal behaves the same. [home](home)____ 3. Scraping Sometimes you are interested in data from the web, which will require further processing. Let me get a table from wikipedia:<jupyter_code>from IPython.display import IFrame  
wikiLink="https://en.wikipedia.org/wiki/Democracy_Index" 
IFrame(wikiLink, width=700, height=300)<jupyter_output><empty_output><jupyter_text>I will use pandas to get the table, but you need to install these first:* html5lib * beautifulsoup* lxml<jupyter_code>dataWIKI=pd.read_html(wikiLink,header=0,flavor='bs4',attrs={'class': 'wikitable'})<jupyter_output><empty_output><jupyter_text>Pandas has this command **read_html** that will save lots of coding, above I just said:* The link to the webpage.* The position of the header.* The external library that will be used to extract the text (_flavor_).* The attributes of the table. dataWIKI is not a data frame:<jupyter_code>type(dataWIKI)<jupyter_output><empty_output><jupyter_text>The command **read_html** returns all the elements from the link with the same attributes. Let's see how many there are:<jupyter_code>len(dataWIKI)<jupyter_output><empty_output><jupyter_text>This means you have six tables. Ours is the first one?<jupyter_code># remember that Python starts counting in ZERO!
dataWIKI[0]
dataWIKI[5]<jupyter_output><empty_output><jupyter_text>[home](home)____ Social media data Social media offer APIs too that allow you to get _some_ data. In general, you need to go register as a developer and create a service, and Twitter, Facebook and others will allow you to get some of their data (the more you pay the more they offer). There is a simple library named **facebook_scraper**, which allows basic interaction facebook. It really gets the messages that are visible and does not use the API so you can not expect much information, but it is worth knowing it:<jupyter_code>!pip install tweepy
# install it
import json
import string
import tweepy

# claves de acceso
consumer_key = 'W450lT5DeXVvt8zbjDdm2Q0wi'
consumer_secret = '<KEY>'
access_token =  '<KEY>'
access_token_secret = '<KEY>'

# preparando:
auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)
api=tweepy.API(auth, wait_on_rate_limit=True,timeout=60,
               parser=tweepy.parsers.JSONParser())
!pip install wordcloud
!pip install stop_words
!pip install unidecode
from wordcloud import WordCloud, STOPWORDS, ImageColorGenerator
from stop_words import get_stop_words
import matplotlib.pyplot as plt
import unidecode

stop_words = get_stop_words('spanish')#('en')

# listanew=["Boca", "Resultado","Urna", "encuesta", "tica", "está", "Boca de Urna"]
# stop_words.extend(listanew)

def intelMinsa(quien='@Minsa_Peru', eliminar=stop_words, ultimos=120):
    tweets = api.user_timeline(screen_name = quien, 
                           count = ultimos, 
                           include_rts = False,
                          tweet_mode="extended")
    # create a dict with the tweets text:
    dataTweets={'eachTweet':[t['full_text'] for t in tweets]}
    text = " ".join(dataTweets['eachTweet'])
    text=text.upper()
    text = unidecode.unidecode(text)
    no_urls_no_tags = " ".join([word for word in text.split()
                                    if 'HTTP' not in word
                                        and not word.startswith('@')
                                        and not word.startswith('#')
                                        and word != 'RT'
                                    ])


    optimizadoTEXT=output = [w.upper() for w in eliminar]
    toPlot=[x  for x in no_urls_no_tags.split() if x not in optimizadoTEXT]
    toPlot=" ".join(toPlot)
    exclude = set(string.punctuation)
    toPlot=''.join(ch for ch in toPlot if ch not in exclude)
   
    
    wc = WordCloud(background_color="white",collocations=False,
                   font_path="/Library/Fonts/Verdana.ttf",
                   stopwords=optimizadoTEXT, width=2800,
                          height=1400)
    wc.generate(toPlot)
    plt.figure(figsize=(20,10))
    plt.imshow(wc, interpolation="bilinear")
    plt.axis("off")
    plt.show()
intelMinsa()
user_name = "@Minsa_Peru"

import pandas as pd
import datetime as dt


tweets = api.user_timeline(screen_name = user_name, 
                           count = 1000, 
                           include_rts = False,
                          tweet_mode="extended")

ids=[t['id'] for t in tweets]
dates=[t['created_at'] for t in tweets]
rts=[t['retweet_count'] for t in tweets]
likes=[t['favorite_count'] for t in tweets]
text=[t['full_text'] for t in tweets]



sms=pd.DataFrame({'ids':ids,'dates':dates,'rts':rts,'likes':likes,'text':text})
sms['dates']=pd.to_datetime(sms['dates'], infer_datetime_format=True).dt.strftime('%Y-%m-%d')
sms['dates']=pd.to_datetime(sms['dates'], infer_datetime_format=True)
sms.sort_values(by='dates',inplace=True)
sms.reset_index(drop=True,inplace=True)
sms.to_csv("victor.csv")

sms<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10007539927342518, 'fraction_numerical': 0.011652614983891973, 'mean_word_length': 4.974610974610974, 'pattern_counts': {'":': 0, '<': 105, '<?xml version=': 0, '>': 111, 'https://': 8, 'lorem ipsum': 0, 'www.': 0, 'xml': 1}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 13, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '25', 'dir': 'jupyter-structured-clean-dedup', 'id': '530492', 'license': 'MIT', 'n_tokens_mistral': 4854, 'n_tokens_neox': 4613, 'n_words': 1444, 'path': '2021/ComputationalThinking/Part1_PythonForPreProcessing/Collecting.ipynb', 'repo_name': 'minsacodes/someEvans', 'size': '14761'}