book

Data Wrangling with Python

by Dr. Tirthajyoti Sarkar, Shubhadeep Roychowdhury

February 2019

Beginner to intermediate

452 pages

7h 6m

English

Packt Publishing

Read now

Unlock full access

About the BookAbout the AuthorsLearning ObjectivesApproachAudienceMinimum Hardware RequirementsSoftware RequirementsConventionsInstallation and SetupInstalling the Code BundleAdditional Resources
Introduction to Data Wrangling with PythonIntroductionImportance of Data WranglingPython for Data WranglingLists, Sets, Strings, Tuples, and DictionariesListsExercise 1: Accessing the List MembersExercise 2: Generating a ListExercise 3: Iterating over a List and Checking MembershipExercise 4: Sorting a ListExercise 5: Generating a Random ListActivity 1: Handling ListsSetsIntroduction to SetsUnion and Intersection of SetsCreating Null SetsDictionaryExercise 6: Accessing and Setting Values in a DictionaryExercise 7: Iterating Over a DictionaryExercise 8: Revisiting the Unique Valued List ProblemExercise 9: Deleting Value from DictExercise 10: Dictionary ComprehensionTuplesCreating a Tuple with Different CardinalitiesUnpacking a TupleExercise 11: Handling TuplesStringsExercise 12: Accessing StringsExercise 13: String SlicesString FunctionsExercise 14: Split and JoinActivity 2: Analyze a Multiline String and Generate the Unique Word CountSummary
Advanced Data Structures and File HandlingIntroductionAdvanced Data StructuresIterator Exercise 15: Introduction to the IteratorStacksExercise 16: Implementing a Stack in PythonExercise 17: Implementing a Stack Using User-Defined MethodsExercise 18: Lambda Expression Exercise 19: Lambda Expression for SortingExercise 20: Multi-Element Membership CheckingQueueExercise 21: Implementing a Queue in PythonActivity 3: Permutation, Iterator, Lambda, ListBasic File Operations in PythonExercise 22: File OperationsFile HandlingExercise 23: Opening and Closing a FileThe with StatementOpening a File Using the with StatementExercise 24: Reading a File Line by LineExercise 25: Write to a FileActivity 4: Design Your Own CSV ParserSummary
Introduction to NumPy, Pandas,and MatplotlibIntroductionNumPy ArraysNumPy Array and FeaturesExercise 26: Creating a NumPy Array (from a List)Exercise 27: Adding Two NumPy ArraysExercise 28: Mathematical Operations on NumPy ArraysExercise 29: Advanced Mathematical Operations on NumPy ArraysExercise 30: Generating Arrays Using arange and linspaceExercise 31: Creating Multi-Dimensional ArraysExercise 32: The Dimension, Shape, Size, and Data Type of the Two-dimensional ArrayExercise 33: Zeros, Ones, Random, Identity Matrices, and VectorsExercise 34: Reshaping, Ravel, Min, Max, and SortingExercise 35: Indexing and SlicingConditional SubsettingExercise 36: Array Operations (array-array, array-scalar, and universal functions)Stacking ArraysPandas DataFramesExercise 37: Creating a Pandas SeriesExercise 38: Pandas Series and Data HandlingExercise 39: Creating Pandas DataFramesExercise 40: Viewing a DataFrame PartiallyIndexing and Slicing ColumnsIndexing and Slicing RowsExercise 41: Creating and Deleting a New Column or RowStatistics and Visualization with NumPy and PandasRefresher of Basic Descriptive Statistics (and the Matplotlib Library for Visualization)Exercise 42: Introduction to Matplotlib Through a Scatter PlotDefinition of Statistical Measures – Central Tendency and SpreadRandom Variables and Probability DistributionWhat Is a Probability Distribution?Discrete DistributionsContinuous DistributionsData Wrangling in Statistics and VisualizationUsing NumPy and Pandas to Calculate Basic Descriptive Statistics on the DataFrameRandom Number Generation Using NumPyExercise 43: Generating Random Numbers from a Uniform DistributionExercise 44: Generating Random Numbers from a Binomial Distribution and Bar PlotExercise 45: Generating Random Numbers from Normal Distribution and HistogramsExercise 46: Calculation of Descriptive Statistics from a DataFrameExercise 47: Built-in Plotting UtilitiesActivity 5: Generating Statistics from a CSV FileSummary
A Deep Dive into Data Wrangling with PythonIntroductionSubsetting, Filtering, and GroupingExercise 48: Loading and Examining a Superstore's Sales Data from an Excel FileSubsetting the DataFrameAn Example Use Case: Determining Statistics on Sales and ProfitExercise 49: The unique FunctionConditional Selection and Boolean FilteringExercise 50: Setting and Resetting the IndexExercise 51: The GroupBy MethodDetecting Outliers and Handling Missing ValuesMissing Values in PandasExercise 52: Filling in the Missing Values with fillnaExercise 53: Dropping Missing Values with dropnaOutlier Detection Using a Simple Statistical TestConcatenating, Merging, and JoiningExercise 54: ConcatenationExercise 55: Merging by a Common KeyExercise 56: The join MethodUseful Methods of PandasExercise 57: Randomized SamplingThe value_counts MethodPivot Table FunctionalityExercise 58: Sorting by Column Values – the sort_values MethodExercise 59: Flexibility for User-Defined Functions with the apply MethodActivity 6: Working with the Adult Income Dataset (UCI)Summary
Getting Comfortable with Different Kinds of Data SourcesIntroductionReading Data from Different Text-Based (and Non-Text-Based) SourcesData Files Provided with This ChapterLibraries to Install for This ChapterExercise 60: Reading Data from a CSV File Where Headers Are MissingExercise 61: Reading from a CSV File where Delimiters are not CommasExercise 62: Bypassing the Headers of a CSV FileExercise 63: Skipping Initial Rows and Footers when Reading a CSV FileReading Only the First N Rows (Especially Useful for Large Files)Exercise 64: Combining Skiprows and Nrows to Read Data in Small ChunksSetting the skip_blank_lines OptionRead CSV from a Zip fileReading from an Excel File Using sheet_name and Handling a Distinct sheet_nameExercise 65: Reading a General Delimited Text FileReading HTML Tables Directly from a URLExercise 66: Further Wrangling to Get the Desired DataExercise 67: Reading from a JSON FileReading a Stata FileExercise 68: Reading Tabular Data from a PDF FileIntroduction to Beautiful Soup 4 and Web Page ParsingStructure of HTMLExercise 69: Reading an HTML file and Extracting its Contents Using BeautifulSoupExercise 70: DataFrames and BeautifulSoupExercise 71: Exporting a DataFrame as an Excel FileExercise 72: Stacking URLs from a Document using bs4Activity 7: Reading Tabular Data from a Web Page and Creating DataFramesSummary
Learning the Hidden Secrets of Data WranglingIntroductionAdditional Software Required for This SectionAdvanced List Comprehension and the zip FunctionIntroduction to Generator ExpressionsExercise 73: Generator ExpressionsExercise 74: One-Liner Generator ExpressionExercise 75: Extracting a List with Single WordsExercise 76: The zip FunctionExercise 77: Handling Messy DataData FormattingThe % operatorUsing the format FunctionExercise 78: Data Representation Using {}Identify and Clean OutliersExercise 79: Outliers in Numerical DataZ-scoreExercise 80: The Z-Score Value to Remove OutliersExercise 81: Fuzzy Matching of StringsActivity 8: Handling Outliers and Missing DataSummary
Advanced Web Scraping and Data GatheringIntroductionThe Basics of Web Scraping and the Beautiful Soup LibraryLibraries in PythonExercise 81: Using the Requests Library to Get a Response from the Wikipedia Home PageExercise 82: Checking the Status of the Web RequestChecking the Encoding of the Web PageExercise 83: Creating a Function to Decode the Contents of the Response and Check its LengthExercise 84: Extracting Human-Readable Text From a BeautifulSoup ObjectExtracting Text from a SectionExtracting Important Historical Events that Happened on Today's DateExercise 85: Using Advanced BS4 Techniques to Extract Relevant TextExercise 86: Creating a Compact Function to Extract the "On this Day" Text from the Wikipedia Home PageReading Data from XMLExercise 87: Creating an XML File and Reading XML Element ObjectsExercise 88: Finding Various Elements of Data within a Tree (Element)Reading from a Local XML File into an ElementTree ObjectExercise 89: Traversing the Tree, Finding the Root, and Exploring all Child Nodes and their Tags and AttributesExercise 90: Using the text Method to Extract Meaningful DataExtracting and Printing the GDP/Per Capita Information Using a LoopExercise 91: Finding All the Neighboring Countries for each Country and Printing ThemExercise 92: A Simple Demo of Using XML Data Obtained by Web ScrapingReading Data from an APIDefining the Base URL (or API Endpoint)Exercise 93: Defining and Testing a Function to Pull Country Data from an APIUsing the Built-In JSON Library to Read and Examine DataPrinting All the Data ElementsUsing a Function that Extracts a DataFrame Containing Key InformationExercise 94: Testing the Function by Building a Small Database of Countries' InformationFundamentals of Regular Expressions (RegEx)Regex in the Context of Web ScrapingExercise 95: Using the match Method to Check Whether a Pattern matches a String/SequenceUsing the Compile Method to Create a Regex ProgramExercise 96: Compiling Programs to Match ObjectsExercise 97: Using Additional Parameters in Match to Check for Positional MatchingFinding the Number of Words in a List That End with "ing"Exercise 98: The search Method in RegexExercise 99: Using the span Method of the Match Object to Locate the Position of the Matched PatternExercise 100: Examples of Single Character Pattern Matching with searchExercise 101: Examples of Pattern Matching at the Start or End of a StringExercise 102: Examples of Pattern Matching with Multiple CharactersExercise 103: Greedy versus Non-Greedy MatchingExercise 104: Controlling Repetitions to MatchExercise 105: Sets of Matching CharactersExercise 106: The use of OR in Regex using the OR OperatorThe findall MethodActivity 9: Extracting the Top 100 eBooks from GutenbergActivity 10: Building Your Own Movie Database by Reading an APISummary
RDBMS and SQLIntroductionRefresher of RDBMS and SQLHow is an RDBMS Structured?SQLUsing an RDBMS (MySQL/PostgreSQL/SQLite)Exercise 107: Connecting to Database in SQLiteExercise 108: DDL and DML Commands in SQLiteReading Data from a Database in SQLiteExercise 109: Sorting Values that are Present in the DatabaseExercise 110: Altering the Structure of a Table and Updating the New FieldsExercise 111: Grouping Values in TablesRelation Mapping in DatabasesAdding Rows in the comments TableJoinsRetrieving Specific Columns from a JOIN queryExercise 112: Deleting RowsUpdating Specific Values in a TableExercise 113: RDBMS and DataFramesActivity 11: Retrieving Data Correctly From DatabasesSummary
Application of Data Wrangling in Real LifeIntroductionApplying Your Knowledge to a Real-life Data Wrangling TaskActivity 12: Data Wrangling Task – Fixing UN DataActivity 13: Data Wrangling Task – Cleaning GDP DataActivity 14: Data Wrangling Task – Merging UN Data and GDP DataActivity 15: Data Wrangling Task – Connecting the New Data to the DatabaseAn Extension to Data WranglingAdditional Skills Required to Become a Data ScientistBasic Familiarity with Big Data and Cloud TechnologiesWhat Goes with Data Wrangling?Tips and Tricks for Mastering Machine LearningSummary

Solution of Activity 1: Handling ListsSolution of Activity 2: Analyze a Multiline String and Generate the Unique Word CountSolution of Activity 3: Permutation, Iterator, Lambda, ListSolution of Activity 4: Design Your Own CSV ParserSolution of Activity 5: Generating Statistics from a CSV FileSolution of Activity 6: Working with the Adult Income Dataset (UCI)Solution of Activity 7: Reading Tabular Data from a Web Page and Creating DataFramesSolution of Activity 8: Handling Outliers and Missing Data Solution of Activity 9: Extracting the Top 100 eBooks from GutenbergSolution of Activity 10: Extracting the top 100 eBooks from Gutenberg.orgSolution of Activity 11: Retrieving Data Correctly from DatabasesSolution of Activity 12: Data Wrangling Task – Fixing UN DataActivity 13: Data Wrangling Task – Cleaning GDP DataSolution of Activity 14: Data Wrangling Task – Merging UN Data and GDP DataActivity 15: Data Wrangling Task – Connecting the New Data to a Database

Content preview from Data Wrangling with Python

Chapter 8 RDBMS and SQL

Learning Objectives

By the end of this chapter, you will be able to:

Apply the basics of RDBMS to query databases using Python
Convert data from SQL into a pandas DataFrame

This chapter explains the concepts of databases, including their creation, manipulation and control, and transforming tables into pandas DataFrames.

Introduction

This chapter of our data journey is focused on RDBMS (Relational Database Management Systems) and SQL (Structured Query Language). In the previous chapter, we stored and read data from a file. In this chapter, we will read structured data, design access to the data, and create query interfaces for databases.

Data has been stored in RDBMS format for years. The reasons behind it are ...