Cleaning text data

Cleaning Data in R

Maggie Matsui

Content Developer @ DataCamp

What is text data?

Type of data Example values
Names "Veronica Hopkins", "Josiah", ...
Phone numbers "6171679912", "(868) 949-4489", ...
Emails "[email protected]", "[email protected]", ...
Passwords "JZY46TVG8SM", "iamjosiah21", ...
Comments/Reviews "great service!", "This product broke after 2 days", ...
Cleaning Data in R

Unstructured data problems

  • Formatting inconsistency
    • "6171679912" vs. "(868) 949-4489"
    • "9239 5849 3712 0039" vs. "4490459957881031"
  • Information inconsistency
    • +1 617-167-9912 vs. 617-167-9912
    • "Veronica Hopkins" vs. "Josiah"
  • Invalid data
    • Phone number "0492" is too short
    • Zip code "19888" doesn't exist
Cleaning Data in R

Customer data

customers
# A tibble: 99 x 3
   name            company                     credit_card        
   <chr>           <chr>                       <chr>              
 1 Galena          In Magna Associates         5171 5854 8986 1916
 2 MacKenzie       Iaculis Ltd                 5128-5078-8008-5824
 3 Megan Acosta    Semper LLC                  5502 4529 0732 1744
 4 Phoebe Delacruz Sit Amet Nulla Limited      5419-7308-7424-0944
 5 Jessica         Pellentesque Sed Ltd        5419 2949 5508 9530
# ... with 95 more rows
Cleaning Data in R

Detecting hyphenated credit card numbers

str_detect(customers$credit_card, "-")
FALSE TRUE FALSE TRUE FALSE TRUE TRUE FALSE FALSE TRUE TRUE TRUE FALSE ...
customers %>%
  filter(str_detect(credit_card, "-"))
   name            company                  credit_card        
 1 MacKenzie       Iaculis Ltd              5128-5078-8008-5824
 2 Phoebe Delacruz Sit Amet Nulla Limited   5419-7308-7424-0944
 3 Abel            Lorem PC                 5211-6023-0805-0217 
 ...
Cleaning Data in R

Replacing hyphens

customers %>%

mutate(credit_card_spaces = str_replace_all(credit_card, "-", " "))
   name            company                     credit_card_spaces        
 1 Galena          In Magna Associates         5171 5854 8986 1916
 2 MacKenzie       Iaculis Ltd                 5128 5078 8008 5824
 3 Megan Acosta    Semper LLC                  5502 4529 0732 1744
 4 Phoebe Delacruz Sit Amet Nulla Limited      5419 7308 7424 0944
 5 Jessica         Pellentesque Sed Ltd        5419 2949 5508 9530
 ...
Cleaning Data in R

Removing hyphens and spaces

credit_card_clean <- customers$credit_card %>%

str_remove_all("-") %>% str_remove_all(" ")
customers %>% mutate(credit_card = credit_card_clean)
   name            company                credit_card     
 1 Galena          In Magna Associates    5171585489861916
 2 MacKenzie       Iaculis Ltd            5128507880085824
 3 Megan Acosta    Semper LLC             5502452907321744
 ...
Cleaning Data in R

Finding invalid credit cards

str_length(customers$credit_card)
16 16 16 16 16 16 16 16 16 16 16 16 12 16 16 16 16 16 16 16 16 16 16 16 16 ...
customers %>%
  filter(str_length(credit_card) != 16)
  name            company                   credit_card   
1 Jerry Russell   Sed Eu Company            516294099537
2 Ivor Christian  Ut Tincidunt Incorporated 544571330015
3 Francesca Drake Etiam Consulting          517394144089
Cleaning Data in R

Removing invalid credit cards

customers %>%
  filter(str_length(credit_card) == 16)
   name            company                     credit_card     
 1 Galena          In Magna Associates         5171585489861916
 2 MacKenzie       Iaculis Ltd                 5128507880085824
 3 Megan Acosta    Semper LLC                  5502452907321744
 4 Phoebe Delacruz Sit Amet Nulla Limited      5419730874240944
 5 Jessica         Pellentesque Sed Ltd        5419294955089530
...
Cleaning Data in R

More complex text problems

  • A regular expression is a sequence of characters that allows for robust searching within a string.
  • Certain characters are treated differently in a regular expression:
    • (, ), [, ], $, ., +, *, and others
  • stringr functions use regular expressions
  • Searching for these characters requires using fixed():
    • str_detect(column, fixed("$"))

 

 

Learn more in String Manipulation with stringr in R & Intermediate Regular Expressions in R

Cleaning Data in R

Let's practice!

Cleaning Data in R

Preparing Video For Download...