Book Image

Clean Data

By : Megan Squire
Book Image

Clean Data

By: Megan Squire

Overview of this book

<p>Is much of your time spent doing tedious tasks such as cleaning dirty data, accounting for lost data, and preparing data to be used by others? If so, then having the right tools makes a critical difference, and will be a great investment as you grow your data science expertise.</p> <p>The book starts by highlighting the importance of data cleaning in data science, and will show you how to reap rewards from reforming your cleaning process. Next, you will cement your knowledge of the basic concepts that the rest of the book relies on: file formats, data types, and character encodings. You will also learn how to extract and clean data stored in RDBMS, web files, and PDF documents, through practical examples.</p> <p>At the end of the book, you will be given a chance to tackle a couple of real-world projects.</p>

Related Content you might be interested in

Current Title:

Small book image
Clean Data
Megan Squire
May, 2015 | 272 pages
No titles found
Table of Contents (17 chapters)
Clean Data
Clean Data
Credits
Credits
About the Author
About the Author
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Why Do You Need Clean Data?
Why Do You Need Clean Data?
A fresh perspective
The data science process
Communicating about data cleaning
Our data cleaning environment
An introductory example
Summary
2
Fundamentals – Formats, Types, and Encodings
Fundamentals – Formats, Types, and Encodings
File formats
Archiving and compression
Data types, nulls, and encodings
Summary
3
Workhorses of Clean Data – Spreadsheets and Text Editors
Workhorses of Clean Data – Spreadsheets and Text Editors
Spreadsheet data cleaning
Text editor data cleaning
An example project
Summary
4
Speaking the Lingua Franca – Data Conversions
Speaking the Lingua Franca – Data Conversions
Quick tool-based conversions
Converting with PHP
Converting with Python
The example project
Summary
5
Collecting and Cleaning Data from the Web
Collecting and Cleaning Data from the Web
Understanding the HTML page structure
Method one – Python and regular expressions
Method two – Python and BeautifulSoup
Method three – Chrome Scraper
Example project – Extracting data from e-mail and web forums
Summary
6
Cleaning Data in PDF Files
Cleaning Data in PDF Files
Why is cleaning PDF files difficult?
Try simple solutions first – copying
Another technique to try – pdfMiner
Third choice – Tabula
When all else fails – the fourth technique
Summary
7
RDBMS Cleaning Techniques
RDBMS Cleaning Techniques
Getting ready
Step one – download and examine Sentiment140
Step two – clean for database import
Step three – import the data into MySQL in a single table
Step four – clean the & character
Step five – clean other mystery characters
Step seven – separate user mentions, hashtags, and URLs
Step eight – cleaning for lookup tables
Summary
8
Best Practices for Sharing Your Clean Data
Best Practices for Sharing Your Clean Data
Preparing a clean data package
Documenting your data
Setting terms and licenses for your data
Publicizing your data
Summary
9
Stack Overflow Project
Stack Overflow Project
Step one – posing a question about Stack Overflow
Step two – collecting and storing the Stack Overflow data
Step three – cleaning the data
Step four – analyzing the data
Step five – visualizing the data
Step six – problem resolution
Moving from test tables to full tables
Summary
10
Twitter Project
Twitter Project
Step one – posing a question about an archive of tweets
Step two – collecting the data
Step three – data cleaning
Step four – simple data analysis
Step five – visualizing the data
Step six – problem resolution
Moving this process into full (non-test) tables
Summary
Index
Index

Step two – clean for database import

For our purposes—learning how to clean data—it will be sufficient to load the smaller of these files into a single MySQL database table. Everything we need to do to learn, we can accomplish with the smaller file, the one called testdata.manual.2009.06.14.csv.

As we are looking at the data, we may notice a few areas that will trip us up if we try to import this file directly into MySQL. One of the trouble spots is located at line 28 in the file:

"4","46","Thu May 14 02:58:07 UTC 2009","""booz allen""",

Do you see the triple quotation marks """ right before the booz keyword and after the word allen? The same issue comes up later on line 41 with double quotation marks around the song title P.Y.T:

"4","131","Sun May 17 15:05:03 UTC 2009","Danny Gokey","VickyTigger","I'm listening to ""P.Y.T"" by Danny Gokey…"

The problem with these extra quotation marks is that the MySQL import routine will use the quotation marks to delimit the column text. This will produce...

Previous Section
End of Section 4
Next Section