<jupyter_start><jupyter_text>Cleaning the locationsAfter we have tagged all tweets with a sentiment score, we want to map their locations over the Netherlands. Before we can do this, we need to clean the user locations to locations that can be picked up by the geocoder of OSM: [Nominatim](https://nominatim.openstreetmap.org/ui/search.html). In this notebook, we will showcase how we cleaned our tweets before geolocating. For this, we mainly made use of the regular expression, cbsodata and pandas modules.<jupyter_code># Importing modules
import pandas as pd
import numpy as np
import re

# Load in data from previous notebook
df = pd.read_csv('final_sentiment_tweets.csv')
df.shape<jupyter_output><empty_output><jupyter_text>Cleaning for geocodingThe cleaning for geocoding consists of dropping unnecessary columns, and rows with invalid locations that the user inputted as their user location. Since we use user profile locations and not geotagged tweets, there is a lot of non-sensical locations that users used as input (e.g. 'The moon', 'Everywhere') or locations that are not useful for our project (e.g. 'North-Holland', 'The Netherlands'). Therefore, we have to sift through the locations, so that we end up with a clean list of tweets with residency places.<jupyter_code># Inspect the data
df.shape

# Select the column 'location' and delete the rows with empty cells in this location column
df['location'].replace('', np.nan, inplace=True)

# delete the NaN cells in location
df.dropna(subset=['location'], inplace=True)

# Inspect the data 
df.shape<jupyter_output><empty_output><jupyter_text>Filtering the invalid locationsHere the invalid locations are filtered, such as provinces or regions. Some unique cases (e.g. 's-Gravenhage, Jordaan) had enough cases to make changing by hand worthwile.<jupyter_code># Get rid of invalid words and locations, replace with empty string or correct version
df['location'] = (df['location']
                  .str.replace("Netherlands", '', case=False)
                  .str.replace("The Netherlands", '', case=False)
                  .str.replace("Nederland", '', case=False)
                  .str.replace("NL", '', case=False)
                  .str.replace("the", '', case=False)
                  .str.replace("[0-9]*", '', case = False)
                  .str.replace("Drenthe", '', case = False)
                  .str.replace("Flevoland", '', case = False)
                  .str.replace("Fryslân", '', case = False)
                  .str.replace("Friesland", '', case = False)
                  .str.replace("Gelderland", '', case = False)
                  .str.replace("Groningen", '', case = False)
                  .str.replace("Limburg", '', case = False)
                  .str.replace("<NAME>", '', case = False)
                  .str.replace("Noord-Brabant", '', case = False)
                  .str.replace("<NAME>", '', case = False)
                  .str.replace("Noord-Holland", '', case = False)
                  .str.replace("Overijssel", '', case = False)
                  .str.replace("<NAME>", '', case = False)
                  .str.replace("Zuid-Holland", '', case = False)
                  .str.replace("Zeeland", '', case = False)
                  .str.replace("Achterhoek", '', case = False)
                  .str.lower()
                  .str.replace('([^0-9a-z-\' \t])',' ')
                  .str.replace(' +',' ')
                  .str.replace(' hague', 'den haag')
                  .str.replace(' north-holland','')
                  .str.replace(' north holland','')
                  .str.replace('den haag','\'s-gravenhage')
                  .str.replace(' nootdorp','')
                  .str.replace('jordaan amsterdam','amsterdam')
                  .str.replace('amsterdam area','amsterdam')
                  .str.replace('\'s heerenberg','\'s-heerenberg')
                  .str.replace('\'s-heerenberg montferland','\'s-heerenberg')
                  .str.replace('den bosch', '\'s-hertogenbosch'))

# Drop again the cells that are left empty 
df['location'].replace('', np.nan, inplace=True)
df.dropna(subset=['location'], inplace=True)<jupyter_output><ipython-input-14-a32023a26c18>:2: FutureWarning: The default value of regex will change from True to False in a future version.
  df['location'] = (df['location']<jupyter_text>Checking results and exporting<jupyter_code># Strip whitespaces
df['location'] = df['location'].str.strip()

# Check the locations (locs should be cities/villages etc)
locs = df['location'].value_counts()
locs

# Save location for next script
df.to_csv('cleaned_geo_tweets.csv', header=True, index=False)

#Check data structure
df.head(5)<jupyter_output><empty_output><jupyter_text>As can be seen above, the locations have been somewhat filtered. However, there are still a lot of invalid locations in there. Therefore some more thorough cleaning is needed in the next notebook.<jupyter_code># Count of locations
locs = df['location'].value_counts()
locs<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1288782816229117, 'fraction_numerical': 0.003381066030230708, 'mean_word_length': 2.9166666666666665, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 24, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '361187', 'license': 'MIT', 'n_tokens_mistral': 1468, 'n_tokens_neox': 1398, 'n_words': 445, 'path': '4_Clean_Tweets_Geocoding.ipynb', 'repo_name': 'Rienje/Twitter_analysis_Dutch_elections_2021', 'size': '5048'}