Pandas Text Data Processing
| |
I. The str Object
1. Design Intent of the str Object
The str object is an attribute defined on Index or Series, dedicated to processing the text content of each element. It has a large number of methods defined internally, so to process a series as text you first need to obtain its str object. The Python standard library also has a str module; for the sake of convenience, pandas copies many of its designs — converting letters to uppercase, for instance:
| |
'ABCD'
| |
<pandas.core.strings.accessor.StringMethods at 0x1488ea6db08>
| |
0 ABCD
1 EFG
2 HI
dtype: object
According to the API documentation, of the 50 str object methods in pandas, 31 share both the names and the functionality of methods in the standard library’s str module, which provides a powerful tool for batch processing of series.
2. The [] Indexer
With a str object, you can think of strings as being serialized: in an ordinary string, [] retrieves the element at a given position:
| |
'a'
Slicing likewise yields a substring:
| |
'db'
Using the [] indexer on a str object accomplishes exactly the same thing, and returns a missing value when the position is out of range:
| |
0 a
1 e
2 h
dtype: object
| |
0 db
1 g
2 i
dtype: object
| |
0 c
1 g
2 NaN
dtype: object
3. The string Dtype
As mentioned in the previous chapter, pandas introduced the string type in version 1.0.0. The motivation was that string data used to be stored exclusively in Series of object dtype, whereas object should really hold mixed types — floats, strings, dicts, lists, and custom types kept side by side. Strings therefore deserved a storage type of their own, just as numeric data and category data do, and so string was introduced.
Broadly speaking, most str methods produce the same results on object and string series, but there are substantial differences in the two cases described below.
First, you should use the str attribute only when every value in a series is a string, although this is not strictly required: the necessary condition is that the series contain at least one Iterable object, including but not limited to strings, dicts, and lists. For an iterable object, the str accessor of a string series and that of an object series may return different results.
| |
0 temp_1
1 b
2 NaN
3 y
dtype: object
| |
0 1
1 '
2 .
3 y
dtype: string
Except for the last string element, the first three return different values. With dtype object, [] indexing is applied to each element individually: the dict yields the string temp_1, the list yields its second value, the third element is not iterable and hence produces a missing value, and the fourth is indexed as a string. The str accessor of the string dtype, by contrast, first turns each whole element into its literal string representation — for a list, for example, the first character is “{” — whereas for the final string element the representation happens to be unchanged, so the result agrees with that of object.
Apart from the different ways of serializing certain objects, another difference between the two is that string is a Nullable type whereas object is not. This means that for a string series, when a str method returns an integer Series or a boolean Series, the corresponding dtype is the Nullable type Int or boolean; with object, you get int/float and bool/object instead, depending on whether missing values are present. String comparisons behave in much the same way: string returns a Nullable type, while object does not.
| |
0 1
dtype: int64
| |
0 1
dtype: Int64
| |
0 True
dtype: bool
| |
0 True
dtype: boolean
| |
| |
0 1.0
1 NaN
dtype: float64
| |
0 1
1 <NA>
dtype: Int64
| |
0 True
1 False
dtype: bool
| |
0 True
1 <NA>
dtype: boolean
One last point deserves attention: for a series whose elements are all numbers, the str attribute cannot be used directly even when its dtype is object or category. To treat the numbers as string, force a conversion to a string Series with astype:
| |
0 2
1 4
2 7
dtype: string
II. Regular Expression Basics
The two tables in this section come from the learn-regex-zh project on regular expressions, released under the MIT license. Only the fundamentals are covered here; readers who want a systematic treatment can consult Regular Expressions You Should Know.
1. Matching Ordinary Characters
A regular expression is a tool that matches content within a string from left to right according to a certain pattern. For ordinary characters it can locate where they occur. For illustration, we use the findall function of Python’s re module to match every non-overlapping occurrence of a pattern; the first argument is the regular expression, and the second is the string to be matched. For example, find apple in the following string:
| |
['Apple', 'Apple']
2. Metacharacter Basics
| Metacharacter | Description |
|---|---|
| . | Matches any character except a newline |
| \[ \] | A character class, matching any character contained in the brackets |
| \[^ \] | A negated character class, matching any character not contained in the brackets |
| * | Matches the preceding subexpression zero or more times |
| + | Matches the preceding subexpression one or more times |
| ? | Matches the preceding subexpression zero or one time |
| {n,m} | Curly braces, matching the preceding character at least n times but no more than m times |
| (xyz) | A group, matching the characters xyz in exactly that order |
| | | Alternation, matching the characters before or after the symbol |
| \ | The escape character, which restores a metacharacter to its original meaning |
| ^ | Matches the start of a line |
$ | Matches the end of a line |
| |
['a', 'b', 'c']
| |
['a', 'c']
| |
['b']
| |
['aa', 'aa', 'bb', 'bb']
| |
['aaa', 'bbb']
| |
['a', 'a', 'a', 'a']
| |
['ab', 'aa', 'c', 'ad', 'aa', 'e']
3. Shorthand Character Sets
In addition, regular expressions provide a class of shorthand character sets, each equivalent to a set of characters:
| Abbreviation | Description |
|---|---|
| \w | Matches all letters, digits, and underscores: \[a-zA-Z0-9\_\] |
| \W | Matches characters that are not letters or digits: \[^\\w\] |
| \d | Matches digits: \[0-9\] |
| \D | Matches non-digits: \[^\\d\] |
| \s | Matches whitespace: \[\\t\\n\\f\\r\\p{Z}\] |
| \S | Matches non-whitespace: \[^\\s\] |
| \B | Matches a position at the beginning or end of a group of non-empty characters, rather than a specific character |
| |
['is', 'Is']
| |
['09', '7w', 'c_', '9q']
| |
['8?', 'p@']
| |
['t d', 'g w', 's t', 'e s']
| |
[('黄浦区', '方浜中路', '249号'), ('宝山区', '密山路', '5号')]
III. Five Kinds of Text Processing Operations
1. Splitting
str.split splits a column of strings. Its first argument is a regular expression, while the optional arguments include n, the maximum number of splits from left to right, and expand, whether to spread the result into several columns:
| |
0 [上海, 黄浦, 方浜中, 249号]
1 [上海, 宝山, 密山, 5号]
dtype: object
| |
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | 上海 | 黄浦 | 方浜中路249号 |
| 1 | 上海 | 宝山 | 密山路5号 |
A similar function is str.rsplit, whose difference is that with the n parameter it limits the maximum number of splits from right to left. In the current version, however, a bug prevents rsplit from splitting on regular expressions:
| |
| 0 | |
|---|---|
| 0 | 上海市黄浦区方浜中路249号 |
| 1 | 上海市宝山区密山路5号 |
2. Concatenation
There are two functions for concatenation: str.join and str.cat. str.join joins the string lists in a Series with a separator, returning a missing value if a list contains a non-string element:
| |
0 a-b
1 NaN
2 NaN
dtype: object
str.cat merges two series. Its main parameters are the separator sep, the join style join, and the placeholder for missing values na_rep; the join style defaults to a left join keyed on the index.
| |
0 a-cat
1 b-dog
dtype: object
| |
0 a-?
1 b-cat
2 ?-dog
dtype: object
3. Matching
str.contains returns a boolean series telling whether each string contains the regular pattern:
| |
0 True
1 True
2 False
dtype: bool
str.startswith and str.endswith return boolean series indicating whether each string begins or ends with the given pattern; neither supports regular expressions:
| |
0 True
1 False
2 False
dtype: bool
| |
0 True
1 True
2 False
dtype: bool
To check a start- or end-of-string pattern with a regular expression, turn to str.match, which returns a boolean series indicating whether the beginning of each string matches the given regular pattern:
| |
0 True
1 True
2 False
dtype: bool
| |
0 False
1 True
2 True
dtype: bool
Of course, the same effect can be achieved by using ^ and $ inside the regular expression passed to str.contains:
| |
0 True
1 True
2 False
dtype: bool
| |
0 False
1 True
2 True
dtype: bool
Besides the boolean-returning matches described above, there are matching functions that return positions: str.find and str.rfind give the index of the first match from left to right and from right to left respectively, or -1 if nothing is found. Note that these two functions do not support regular expressions and work only with literal substrings:
| |
0 11
dtype: int64
| |
0 33
dtype: int64
4. Replacement
str.replace and replace are not the same function; for string replacement you should use the former.
| |
0 a_new_b
1 c_new
dtype: object
When different parts need different replacements, you can use subgroups and pass a custom replacement function to handle them separately. Here group(k) denotes the k-th matched subgroup, that is, the content between a pair of parentheses:
| |
0 Shanghai HP District Mid Fangbin Road No. 249
1 Shanghai BS District Mishan Road No. 5
2 Beijing CP District Beinong Road No. 2
dtype: object
The numeric labels here are not very intuitive; named subgroups let you state directly what each subgroup stands for:
| |
0 Shanghai HP District Mid Fangbin Road No. 249
1 Shanghai BS District Mishan Road No. 5
2 Beijing CP District Beinong Road No. 2
dtype: object
Although this looks a bit cumbersome, real data processing usually builds the mapping dictionary from the data in code, so the actual implementation is much more concise.
5. Extraction
Extraction can be regarded either as a matching operation that returns concrete elements rather than booleans or positions, or as a special kind of splitting. In the str.split example above the separators were discarded, which is not what the user wanted; that is when str.extract comes in handy:
| |
| 0 | 1 | 2 | 3 | |
|---|---|---|---|---|
| 0 | 上海市 | 黄浦区 | 方浜中路 | 249号 |
| 1 | 上海市 | 宝山区 | 密山路 | 5号 |
| 2 | 北京市 | 昌平区 | 北农路 | 2号 |
Naming the subgroups also lets you name the columns of the resulting DataFrame directly:
| |
| 市名 | 区名 | 路名 | 编号 | |
|---|---|---|---|---|
| 0 | 上海市 | 黄浦区 | 方浜中路 | 249号 |
| 1 | 上海市 | 宝山区 | 密山路 | 5号 |
| 2 | 北京市 | 昌平区 | 北农路 | 2号 |
Unlike str.extract, which matches only once, str.extractall finds every pattern that qualifies; when there are several matches, they are stored with a multi-level index:
| |
| 0 | 1 | ||
|---|---|---|---|
| match | |||
| my_A | 0 | 135 | 15 |
| 1 | 26 | 5 | |
| my_B | 0 | 674 | 2 |
| 1 | 25 | 6 |
| |
| name1 | name2 | ||
|---|---|---|---|
| match | |||
| my_A | 0 | 135 | 15 |
| 1 | 26 | 5 | |
| my_B | 0 | 674 | 2 |
| 1 | 25 | 6 |
str.findall is similar to str.extractall, but the former stores the results in a list whereas the latter uses a multi-level index, with one row per group of matches rather than combining all matches into a list.
| |
my_A [(135, 15), (26, 5)]
my_B [(674, 2), (25, 6)]
dtype: object
IV. Common String Functions
Besides the five kinds of string operations introduced above, the str object defines a number of other useful methods, which are presented here:
1. Letter-Case Functions
upper, lower, title, capitalize, swapcase — these five functions mainly convert letter case, and their behavior is easy to grasp from the examples below:
| |
0 LOWER
1 CAPITALS
2 THIS IS A SENTENCE
3 SWAPCASE
dtype: object
| |
0 lower
1 capitals
2 this is a sentence
3 swapcase
dtype: object
| |
0 Lower
1 Capitals
2 This Is A Sentence
3 Swapcase
dtype: object
| |
0 Lower
1 Capitals
2 This is a sentence
3 Swapcase
dtype: object
| |
0 LOWER
1 capitals
2 THIS IS A SENTENCE
3 sWaPcAsE
dtype: object
2. Numeric Functions
The method worth highlighting here is pd.to_numeric. Although it is not a method on the str object, it converts and filters numeric values stored as characters with great speed. Its main parameters are errors and downcast, controlling how non-numeric values are handled and the target type. For values that cannot be converted to numbers there are three errors options: raise, coerce, ignore mean, respectively, raise an error, set them to missing, and keep the original strings.
| |
0 1
1 2.2
2 2e
3 ??
4 -2.1
5 0
dtype: object
| |
0 1.0
1 2.2
2 NaN
3 NaN
4 -2.1
5 0.0
dtype: float64
When cleaning data, the coerce setting lets you quickly inspect the non-numeric rows:
| |
2 2e
3 ??
dtype: object
3. Statistical Functions
count and len return, respectively, the number of occurrences of a regular pattern and the length of a string:
| |
0 2
1 2
dtype: int64
| |
0 14
1 19
dtype: int64
4. Formatting Functions
Formatting functions fall into two groups: the trimming type and the padding type. The first group has three members — strip, rstrip, lstrip — which remove whitespace on both sides, on the right, and on the left respectively. These functions are useful in data cleaning, especially when column names contain stray spaces.
| |
Int64Index([4, 4, 4], dtype='int64')
| |
Int64Index([5, 4, 5], dtype='int64')
| |
Int64Index([4, 5, 5], dtype='int64')
Among padding functions, pad is the most flexible: you can choose the target string length, the direction of padding, and the padding character:
| |
0 ****a
1 ****b
2 ****c
dtype: object
| |
0 a****
1 b****
2 c****
dtype: object
| |
0 **a**
1 **b**
2 **c**
dtype: object
The three cases above can also be handled equivalently with rjust, ljust, center; note that ljust pads on the right, not on the left:
| |
0 ****a
1 ****b
2 ****c
dtype: object
| |
0 a****
1 b****
2 c****
dtype: object
| |
0 **a**
1 **b**
2 **c**
dtype: object
When reading excel files you often need to prepend zeros to numbers: security codes, for instance, are read as the number 7 instead of “000007”. Besides the left-padding functions above, pandas also offers zfill for this purpose.
| |
0 000007
1 000155
2 303000
dtype: string
| |
0 000007
1 000155
2 303000
dtype: string
| |
0 000007
1 000155
2 303000
dtype: string
V. Exercises
Ex1: The House Information Dataset
Here is a dataset of house information:
| |
| floor | year | area | price | |
|---|---|---|---|---|
| 0 | 高层(共6层) | 1986年建 | 58.23㎡ | 155万 |
| 1 | 中层(共20层) | 2020年建 | 88㎡ | 155万 |
| 2 | 低层(共28层) | 2010年建 | 89.33㎡ | 365万 |
- Store the
yearcolumn as integer years. - Replace the
floorcolumn with two columns,Level, Highest, whose elements are thestringfloor category (high-rise, mid-rise, low-rise) and the integer highest floor number, respectively. - Compute the average price per square meter,
avg_price, and store it in the table using the format*** yuan per square meter, where***is an integer.
Ex2: The Game of Thrones Script Dataset
Here is a dataset of Game of Thrones scripts:
| |
| Release Date | Season | Episode | Episode Title | Name | Sentence | |
|---|---|---|---|---|---|---|
| 0 | 2011-04-17 | Season 1 | Episode 1 | Winter is Coming | waymar royce | What do you expect? They're savages. One lot s... |
| 1 | 2011-04-17 | Season 1 | Episode 1 | Winter is Coming | will | I've never seen wildlings do a thing like this... |
| 2 | 2011-04-17 | Season 1 | Episode 1 | Winter is Coming | waymar royce | How close did you get? |
- Count the number of lines of dialogue in each
Episode. - Using spaces as word separators, find the top five people with the highest average number of words per line of dialogue.
- If a person’s line contains a question mark, the next speaker is the respondent. If the previous person’s line contains $n$ question marks, the respondent is considered to have answered $n$ questions. Find the top five people who answered the most questions.
