String Manipulation
Contents
13.2. String Manipulation¶
There are a few basic string manipulation tools that we use all the time when working with text:
Transform upper case characters to lower case (or vice versa).
Replace a substring with another or delete a substring.
Split a string into pieces at a particular character.
Slice a string at specified locations.
We’ll show how we can combine these tools to clean up the county names data.
Remember that we have two tables that we want to join, but the county names are
spelled inconsistently. Below, we’ve displayed the election
and census
dataframes.
County | State | Voted | |
---|---|---|---|
0 | De Witt County | IL | 97.8 |
1 | Lac qui Parle County | MN | 98.8 |
2 | Lewis and Clark County | MT | 95.2 |
3 | St John the Baptist Parish | LA | 52.6 |
County | State | Population | |
---|---|---|---|
0 | DeWitt | IL | 16,798 |
1 | Lac Qui Parle | MN | 8,067 |
2 | Lewis & Clark | MT | 55,716 |
3 | St. John the Baptist | LA | 43,044 |
13.2.1. Converting Text to a Standard Format with Python String Methods¶
We need to address the following inconsistencies between the county names in the two tables.
Capitalization:
qui
vsQui
Omission of words:
County
andParish
are absent from thecensus
tableDifferent abbreviation conventions:
&
vsand
Different punctuation conventions:
St.
vsSt
Use of whitespace:
DeWitt
vsDe Witt
When we clean text, we usually start by converting all of the characters to
lowercase—it’s easier to work with all lowercase characters than to try to
track combinations of uppercase and lowercase. Next, we want to fix
inconsistent words by replacing &
with and
and removing County
and
Parish
. Finally, we’ll fix up the punctuation and whitespace inconsistencies.
Following these steps, we create a method called clean_county
that cleans
an input county name using two of Python’s string methods.
def clean_county(county):
return (county
.lower()
.replace('county', '')
.replace('parish', '')
.replace('&', 'and')
.replace('.', '')
.replace(' ', ''))
Python provides a variety of methods for basic string manipulation. Although simple, these methods are the primitives that piece together to form more complex string operations. These methods are conveniently defined on all Python strings and do not require importing other modules. Although it is worth familiarizing yourself with the complete list of string methods, we describe a few of the most commonly used methods in the table below.
Method |
Description |
---|---|
|
Returns a copy of a string with all letters converted to lowercase |
|
Replaces all instances of the substring |
|
Removes leading and trailing whitespace from |
|
Returns substrings of |
|
Slices |
We next verify that the clean_county
method produces matching counties for all the counties in both tables:
([clean_county(county) for county in election['County']],
[clean_county(county) for county in census['County']])
(['dewitt', 'lacquiparle', 'lewisandclark', 'stjohnthebaptist'],
['dewitt', 'lacquiparle', 'lewisandclark', 'stjohnthebaptist'])
Since each county name in both tables now has the same transformed representation, we can successfully join the two tables.
13.2.2. String Methods in pandas¶
In the code above we used a loop to transform each county name. pandas
Series
objects provide a convenient way to apply string methods to each item in the
series.
The .str
property on pandas
Series exposes the same string methods as
Python does. Calling a method on the .str
property calls the method on each
item in the series. This allows us to transform each string in the series
without using a loop. We save the transformed counties back into their
originating tables:
election['County'] = (election['County']
.str.lower()
.str.replace('parish', '')
.str.replace('county', '')
.str.replace('&', 'and')
.str.replace('.', '', regex=False)
.str.replace(' ', ''))
census['County'] = (census['County']
.str.lower()
.str.replace('parish', '')
.str.replace('county', '')
.str.replace('&', 'and')
.str.replace('.', '', regex=False)
.str.replace(' ', ''))
Now, the two tables should contain the same string representation of the county names and we can join these tables.
election.merge(census, on=['County','State'])
County | State | Voted | Population | |
---|---|---|---|---|
0 | dewitt | IL | 97.8 | 16,798 |
1 | lacquiparle | MN | 98.8 | 8,067 |
2 | lewisandclark | MT | 95.2 | 55,716 |
3 | stjohnthebaptist | LA | 52.6 | 43,044 |
Note
Note that we merged on two columns: the county name and the state. We did this because some states have counties with the same name. For example, California and New York both have a county called King.
Python’s string methods form a set of simple and useful operations for string
manipulation. pandas
Series implement the same methods that apply the
underlying Python method to each string in the series. To see the complete list
of methods, we recommend looking at the Python documentation on str
methods and the Pandas documentation for the .str
accessor. We did the canonicalization task above using only
str.lower()
and multiple calls to str.replace()
. Next, we’ll extract
text with another string method, str.split()
.
13.2.3. Splitting Strings to Extract Pieces of Text¶
Let’s say we want to extract the date from a web server’s log entry shown below.
log_entry
169.237.46.168 - - [26/Jan/2004:10:47:58 -0800]"GET /stat141/Winter04 HTTP/1.1"
301 328 "http://anson.ucdavis.edu/courses""Mozilla/4.0 (compatible; MSIE 6.0;
Windows NT 5.0; .NET CLR 1.1.4322)"
String splitting can help us narrow in on the pieces of information that form the date. For example, when we split the string on the left bracket, we get two strings:
log_entry.split('[')
['169.237.46.168 - - ',
'26/Jan/2004:10:47:58 -0800]"GET /stat141/Winter04 HTTP/1.1" 301 328 "http://anson.ucdavis.edu/courses""Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322)"']
The second string has the date information, and if we want the day, month, and year, we can split that string on a colon.
log_entry.split('[')[1].split(':')[0]
'26/Jan/2004'
To separate out these three parts of the date, we can split on the forward slash. All together we split the original string three times, each time keeping only the pieces we are interested in.
(log_entry.split('[')[1]
.split(':')[0]
.split('/'))
['26', 'Jan', '2004']
By repeatedly using split()
, we can extract out all the parts of the log
entry. But this approach is complicated—if we wanted to
also get the hour, minute, second, and time zone of the activity,
we would need to use split()
six times in total.
There’s a simpler way to extract out the parts:
import re
pattern = r'[ \[/:\]]'
re.split(pattern, log_entry)[4:11]
['26', 'Jan', '2004', '10', '47', '58', '-0800']
This alternative approach uses a powerful tool called a regular expression, which we’ll cover in the next section.