Data Analysis with Python and PySpark (Final Release) (Jonathan Rioux) (z-library.sk, 1lib.sk, z-lib.sk)

M A N N I N G Jonathan Rioux

An RDD versus a data frame. In the RDD, we think of each record as an independent entity. With the data frame, we mostly interact with columns, performing functions on them. We still can access the rows of a data frame, via RDD, if necessary. Resilient distributed data set (RDD) Record/Object 1 Record/Object 2 Record/Object 3 Record/Object 4 Record/Object 5 Record/Object 6 … Record/Object N Data frame (DF) Col 1 Col 2 Col N… (1, 1) (2, 1) (3, 1) (4, 1) (5, 1) (6, 1) … (N, )1 (1, )2 (2, )2 (3, )2 (4, )2 (5, )2 (6, )2 … (N, )2 (1, )N (2, )N (3, )N (4, )N (5, )N (6, )N … (N, )N In an RDD, we think of each record as being an independent object on which we perform functions to transform them. Think “collection,” not “structure.” A data frame organizes the records in columns. We perform transformations either directly on those columns or on the data frame as a whole; we typically don’t access records horizontally (record by record) as we do with the RDD.

Data Analysis with Python and PySpark JONATHAN RIOUX MANN I NG SHELTER ISLAND

For online information and ordering of this and other Manning books, please visit www.manning.com. The publisher offers discounts on this book when ordered in quantity. For more information, please contact Special Sales Department Manning Publications Co. 20 Baldwin Road PO Box 761 Shelter Island, NY 11964 Email: orders@manning.com ©2022 by Manning Publications Co. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by means electronic, mechanical, photocopying, or otherwise, without prior written permission of the publisher. Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in the book, and Manning Publications was aware of a trademark claim, the designations have been printed in initial caps or all caps. Recognizing the importance of preserving what has been written, it is Manning’s policy to have the books we publish printed on acid-free paper, and we exert our best efforts to that end. Recognizing also our responsibility to conserve the resources of our planet, Manning books are printed on paper that is at least 15 percent recycled and processed without the use of elemental chlorine. The author and publisher have made every effort to ensure that the information in this book was correct at press time. The author and publisher do not assume and hereby disclaim any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from negligence, accident, or any other cause, or from any usage of the information herein. Manning Publications Co. Development editor: Marina Michaels 20 Baldwin Road Technical development editor: Arthur Zubarev PO Box 761 Review editor: Aleksander Dragosavljević Shelter Island, NY 11964 Production editor: Keri Hales Copy editor: Michele Mitchell Proofreader: Melody Dolab Technical proofreader: Alex Ott Typesetter: Dennis Dalinnik Cover designer: Marija Tudor ISBN: 9781617297205 Printed in the United States of America

contents preface xi acknowledgments xiii about this book xv about the author xviii about the cover illustration xix 1 Introduction 1 1.1 What is PySpark? 2 Taking it from the start: What is Spark? 2 ■ PySpark = Spark + Python 3 ■ Why PySpark? 4 1.2 Your very own factory: How PySpark works 6 Some physical planning with the cluster manager 7 ■ A factory made efficient through a lazy leader 10 1.3 What will you learn in this book? 13 1.4 What do I need to get started? 14 PART 1 GET ACQUAINTED: FIRST STEPS IN PYSPARK .......15 2 Your first data program in PySpark 17 2.1 Setting up the PySpark shell 18 The SparkSession entry point 20 ■ Configuring how chatty spark is: The log level 22iii

CONTENTSiv2.2 Mapping our program 23 2.3 Ingest and explore: Setting the stage for data transformation 24 Reading data into a data frame with spark.read 25 ■ From structure to content: Exploring our data frame with show() 28 2.4 Simple column transformations: Moving from a sentence to a list of words 31 Selecting specific columns using select() 32 ■ Transforming columns: Splitting a string into a list of words 33 ■ Renaming columns: alias and withColumnRenamed 35 ■ Reshaping your data: Exploding a list into rows 36 ■ Working with words: Changing case and removing punctuation 37 2.5 Filtering rows 40 3 Submitting and scaling your first PySpark program 45 3.1 Grouping records: Counting word frequencies 46 3.2 Ordering the results on the screen using orderBy 48 3.3 Writing data from a data frame 50 3.4 Putting it all together: Counting 52 Simplifying your dependencies with PySpark’s import conventions 53 Simplifying our program via method chaining 54 3.5 Using spark-submit to launch your program in batch mode 56 3.6 What didn’t happen in this chapter 58 3.7 Scaling up our word frequency program 58 4 Analyzing tabular data with pyspark.sql 62 4.1 What is tabular data? 63 How does PySpark represent tabular data? 64 4.2 PySpark for analyzing and processing tabular data 65 4.3 Reading and assessing delimited data in PySpark 67 A first pass at the SparkReader specialized for CSV files 67 Customizing the SparkReader object to read CSV data files 69 Exploring the shape of our data universe 72 4.4 The basics of data manipulation: Selecting, dropping, renaming, ordering, diagnosing 73 Knowing what we want: Selecting columns 73 ■ Keeping what we need: Deleting columns 76 ■ Creating what’s not there: New

CONTENTS vcolumns with withColumn() 78 ■ Tidying our data frame: Renaming and reordering columns 81 ■ Diagnosing a data frame with describe() and summary() 83 5 Data frame gymnastics: Joining and grouping 87 5.1 From many to one: Joining data 88 What’s what in the world of joins 88 ■ Knowing our left from our right 89 ■ The rules to a successful join: The predicates 90 How do you do it: The join method 92 ■ Naming conventions in the joining world 96 5.2 Summarizing the data via groupby and GroupedData 100 A simple groupby blueprint 101 ■ A column is a column: Using agg() with custom column definitions 105 5.3 Taking care of null values: Drop and fill 106 Dropping it like it’s hot: Using dropna() to remove records with null values 107 ■ Filling values to our heart’s content using fillna() 108 5.4 What was our question again? Our end-to-end program 109 PART 2 GET PROFICIENT: TRANSLATE YOUR IDEAS INTO CODE .....................................................115 6 Multidimensional data frames: Using PySpark with JSON data 117 6.1 Reading JSON data: Getting ready for the schemapocalypse 118 Starting small: JSON data as a limited Python dictionary 119 Going bigger: Reading JSON data in PySpark 121 6.2 Breaking the second dimension with complex data types 123 When you have more than one value: The array 125 ■ The map type: Keys and values within a column 129 6.3 The struct: Nesting columns within columns 131 Navigating structs as if they were nested columns 132 6.4 Building and using the data frame schema 135 Using Spark types as the base blocks of a schema 135 ■ Reading a JSON document with a strict schema in place 138 ■ Going full circle: Specifying your schemas in JSON 141

CONTENTSvi6.5 Putting it all together: Reducing duplicate data with complex data types 144 Getting to the “just right” data frame: Explode and collect 146 Building your own hierarchies: Struct as a function 148 7 Bilingual PySpark: Blending Python and SQL code 151 7.1 Banking on what we know: pyspark.sql vs. plain SQL 152 7.2 Preparing a data frame for SQL 154 Promoting a data frame to a Spark table 154 ■ Using the Spark catalog 156 7.3 SQL and PySpark 157 7.4 Using SQL-like syntax within data frame methods 159 Get the rows and columns you want: select and where 159 Grouping similar records together: group by and order by 160 Filtering after grouping using having 161 ■ Creating new tables/ views using the CREATE keyword 163 ■ Adding data to our table using UNION and JOIN 164 ■ Organizing your SQL code better through subqueries and common table expressions 166 ■ A quick summary of PySpark vs. SQL syntax 168 7.5 Simplifying our code: Blending SQL and Python 169 Using Python to increase the resiliency and simplifying the data reading stage 169 ■ Using SQL-style expressions in PySpark 170 7.6 Conclusion 172 8 Extending PySpark with Python: RDD and UDFs 175 8.1 PySpark, freestyle: The RDD 176 Manipulating data the RDD way: map(), filter(), and reduce() 177 8.2 Using Python to extend PySpark via UDFs 185 It all starts with plain Python: Using typed Python functions 186 From Python function to UDFs using udf() 188 9 Big data is just a lot of small data: Using pandas UDFs 192 9.1 Column transformations with pandas: Using Series UDF 194 Connecting Spark to Google’s BigQuery 194 ■ Series to Series UDF: Column functions, but with pandas 199 ■ Scalar UDF + cold start = Iterator of Series UDF 202

CONTENTS vii9.2 UDFs on grouped data: Aggregate and apply 205 Group aggregate UDFs 207 ■ Group map UDF 208 9.3 What to use, when 210 10 Your data under a different lens: Window functions 215 10.1 Growing and using a simple window function 216 Identifying the coldest day of each year, the long way 217 Creating and using a simple window function to get the coldest days 219 ■ Comparing both approaches 223 10.2 Beyond summarizing: Using ranking and analytical functions 224 Ranking functions: Quick, who’s first? 225 ■ Analytic functions: Looking back and ahead 230 10.3 Flex those windows! Using row and range boundaries 232 Counting, window style: Static, growing, unbounded 233 What you are vs. where you are: Range vs. rows 235 10.4 Going full circle: Using UDFs within windows 239 10.5 Look in the window: The main steps to a successful window function 240 11 Faster PySpark: Understanding Spark’s query planning 244 11.1 Open sesame: Navigating the Spark UI to understand the environment 245 Reviewing the configuration: The environment tab 247 Greater than the sum of its parts: The Executors tab and resource management 249 ■ Look at what you’ve done: Diagnosing a completed job via the Spark UI 254 ■ Mapping the operations via Spark query plans: The SQL tab 257 The core of Spark: The parsed, analyzed, optimized, and physical plans 260 11.2 Thinking about performance: Operations and memory 263 Narrow vs. wide operations 264 ■ Caching a data frame: Powerful, but often deadly (for perf) 269

CONTENTSviiiPART 3 GET CONFIDENT: USING MACHINE LEARNING WITH PYSPARK................................................275 12 Setting the stage: Preparing features for machine learning 277 12.1 Reading, exploring, and preparing our machine learning data set 278 Standardizing column names using toDF() 279 ■ Exploring our data and getting our first feature columns 281 ■ Addressing data mishaps and building our first feature set 283 ■ Weeding out useless records and imputing binary features 286 ■ Taking care of extreme values: Cleaning continuous columns 287 ■ Weeding out the rare binary occurrence columns 290 12.2 Feature creation and refinement 291 Creating custom features 292 ■ Removing highly correlated features 293 12.3 Feature preparation with transformers and estimators 296 Imputing continuous features using the Imputer estimator 298 Scaling our features using the MinMaxScaler estimator 300 13 Robust machine learning with ML Pipelines 303 13.1 Transformers and estimators: The building blocks of ML in Spark 304 Data comes in, data comes out: The Transformer 305 Data comes in, transformer comes out: The Estimator 310 13.2 Building a (complete) machine learning pipeline 312 Assembling the final data set with the vector column type 314 Training an ML model using a LogisticRegression classifier 316 13.3 Evaluating and optimizing our model 319 Assessing model accuracy: Confusion matrix and evaluator object 320 ■ True positives vs. false positives: The ROC curve 323 ■ Optimizing hyperparameters with cross- validation 325 13.4 Getting the biggest drivers from our model: Extracting the coefficients 328

CONTENTS ix14 Building custom ML transformers and estimators 331 14.1 Creating your own transformer 332 Designing a transformer: Thinking in terms of Params and transformation 333 ■ Creating the Params of a transformer 335 Getters and setters: Being a nice PySpark citizen 337 ■ Creating a custom transformer’s initialization function 340 ■ Creating our transformation function 341 ■ Using our transformer 343 14.2 Creating your own estimator 344 Designing our estimator: From model to params 345 Implementing the companion model: Creating our own Mixin 347 Creating the ExtremeValueCapper estimator 350 ■ Trying out our custom estimator 352 14.3 Using our transformer and estimator in an ML pipeline 353 Dealing with multiple inputCols 353 ■ In practice: Inserting custom components into an ML pipeline 356 appendix A Solutions to the exercises 361 appendix B Installing PySpark 389 appendix C Some useful Python concepts 408 index 423

preface While computers have been getting more powerful and more capable of chewing though larger data sets, our appetite for consuming data grows much faster. Conse- quently, we built new tools to scale big data jobs across multiple machines. This does not come for free, and early tools were complicated by requiring users to manage not only the data program, but also the health and performance of the cluster of machines themselves. I recall trying to scale my own programs, only to be faced with the advice to “just sample your data set and get on with your day.” PySpark changes the game. Starting with the popular Python programming lan- guage, it provides a clear and readable API to manipulate very large data sets. Still, while in the driver’s seat, you write code as if you were dealing with a single machine. PySpark sits at the intersection of powerful, expressive, and versatile. Through a pow- erful multidimensional data model, you can build your data programs with a clear path to scalability, regardless of the data size. I fell in love with PySpark while working as a data scientist for building credit risk models. On the cusp of migrating our models to a new big data environment, we needed to devise a plan to intelligently convert our data products while “keeping the lights on.” As the self-appointed Python guy, I got tasked to help the team become familiar with PySpark and help accelerate the transition. This love grew exponentially as I got the chance to work with a myriad of clients on different use cases. The com- mon thread? Big data and big problems, all solvable through a powerful data model. One caveat: most of the material available for learning Spark focused on Scala and Java, with Python developers left transliterating the code to their favorite programmingxi

PREFACExiilanguage. I started writing this book to promote PySpark as a great tool for data ana- lysts. In a fortunate turn of events, the Spark project really promoted Python as a first-class citizen. Now, more than ever, you have a powerful tool for scaling your data programs. And big data, once tamed, really feels powerful.

acknowledgments Although my name is on the cover, this book has been a tremendous team effort, and I want to take the time to thank those who helped me along the way. First and foremost, I want to thank my family. Writing a book is a lot of work, and with this work comes a lot of complaining. Simon, Catherine, Véronique, Jean, merci du fond du coeur pour votre soutien. Je vous aime énormément. Regina, in a way, you’ve were my very first PySpark student. Through your leader- ship, you literally changed everything for me career-wise. I will forever cherish the time we worked together, and I feel lucky our paths crossed when they did. I want to thank Renata Pompas, who allowed me to use a color palette made under her supervision for the diagrams in my book. I am color-blind, and finding a set of safe colors to use that would please me and be consistent was helpful during book development. If the figures look good to you, thank her (and the fine Manning graphic designers). If they look bad, blame it on me. Thank you to my team at EPAM, with a special shout-out to Zac, James, Nasim, Vahid, Dmitrii, Yuriy, Val, Robert, Aliaksandra, Ihor, Pooyan, Artem, Volha, Ekaterina, Sergey, Sergei, Siarhei, Kseniya, Artemii, Anatoly, Yuliya, Nadzeya, Artsiom, Denis, Yevhen, Sofiia, Roman, Mykola, Lisa, Gaurav, Megan, and so many more. From the day I announced that I was writing a book to when I wrote these words, I felt supported and encouraged. Thank you to the Laivly team, Jeff, Rod, Craig, Jordan, Abu, Brendan, Daniel, Guy, and Reid, for the opportunity to continue the adventure. I promise you that the future is bright.xiii

ACKNOWLEDGMENTSxiv A warm thank you to those who believed in my “use PySpark, you’ll be grateful you did” mantra. There are too many folks to be exhaustive here, but I want to give a shout out to Mark Derry, Uma Gopinath, Tom Everett, Dhrun Lauwers, Milena Kumurdjieva, Shahid Amlani, Sam Diab, Chris Wagner, JV Eng, Chris Purtill, Naveen Pothayath, Vish Tipirneni, and Patrick Kurkiewicz. During the writing of the book, I had the joy to geek out on PySpark with some fine podcast producers: Brian at Test and Code (https://testandcode.com/), Lior and Michael at WHAT the Data?! (https://podcast.whatthedatapodcast.com/), and Ben at Profitable Python (https://anchor.fm/profitablepythonfm). I am so humbled and grate- ful that you invited me to exchange with you. Thank you Alexey Grigorev for having me in your Book of the Week club on Slack—what an awesome community you’ve built! I want to thank readers who provided comments on the manuscript during devel- opment, as well as the reviewers who provided excellent feedback: Alex Lucas, David Cronkite, Dianshuang Wu, Gary Bake, Geoff Clark, Gustavo Patino, Igor Vieira, Javier Collado Cabeza, Jeremy Loscheider, Josh Cohen, Kay Engelhardt, Kim Falk, Michael Kareev, Mike Jensen, Patrick A. Mol, Paul Fornia, Peter Hampton, Philippe Van Ber- gen, Rambabu Posa, Raushan Jha, Sergio Govoni, Sriram Macharla, Stephen Oates, and Werner Nindl. Finally, and most importantly, I want to thank the dream team at Manning that par- ticipated in making this book a reality. There are many folks who made this experi- ence incredible: Marjan Bace, Michael Stephens, Rebecca Rinehart, Bert Bates, Candace Gillhoolley, Radmila Ercegovac, Aleks Dragosavljević, Matko Hrvatin, Chris- topher Kaufmann, Ana Romac, Branko Latincic, Lucas Weber, Stjepan Jureković, Goran Ore, Keri Hales, Michele Mitchell, Melody Dolab, and the rest of the Manning production team. Speaking of Manning, I want to thank the authors of two specific books: Noel Rap- pin and Robin Dunn from wxPython in Action (Manning, 2016; https://www.manning .com/books/wxpython-in-action), as well as Michael Fogus and Chris Houser from The Joy of Clojure (Manning, 2014; https://www.manning.com/books/the-joy-of-clojure -second-edition). These books triggered something in my brain and made me plunge headfirst into programming (and then data science). In a way, they were the initial spark (bad pun intended) that resulted in this book. Finally, I want to highlight the team at Manning that helped me stay accountable on a day-to-day basis and made this book something I am proud of. Arthur Zubarev, I can’t believe we live in the same city and couldn’t meet! Thank you for your excellent feedback and answering my many questions. Alex Ott, I don’t think I could have wished for a better technical advisor. Databricks is incredibly lucky to have you. Last, but certainly not least, I want to thank Marina Michaels for supporting me from the moment I had the idea of writing this book. Writing a book is a lot more difficult than I originally thought, but you made the whole experience enjoyable, formative, and rel- evant. Thank you from the bottom of my heart.

about this book Data Analysis with Python and PySpark teaches you how to use PySpark to conduct your own big data analysis programs. It takes a practical stance on teaching both the how and why of PySpark. You’ll learn about how to effectively ingest, process, and work with data at scale as well as how to reason about your own data transformation code. After reading this book, you should feel comfortable using PySpark to write your own data programs and analyses. Who should read this book This book is structured around increasingly complicated use cases, moving from sim- ple data transformation to machine learning pipelines. We cover the whole cycle, from data ingestion to results consumption, adding more elements with regard to data source consumption and transformation possibilities. This book caters mostly to data analysts, scientists, and engineers who want to scale their Python code to larger data sets. Ideally, you should have written a few data pro- grams, either through your work or while learning to program. You’ll get more out of this book if you already are comfortable using the Python programming language and ecosystem. Spark (and PySpark, naturally) borrows a lot from object-oriented and functional programming. I do not think it’s reasonable to expect complete knowledge of both pro- gramming paradigms just to use big data efficiently. If you understand Python classes, decorators, and higher-order functions, you’ll have a blast using some of the more advanced constructions in the book to bend PySpark to your will. Should those conceptsxv

ABOUT THIS BOOKxvibe foreign to you, I cover them in the context of PySpark throughout the book (when appropriate) and in the appendixes. How this book is organized: A road map The book is divided into three parts. Part 1, “Get Acquainted,” introduces PySpark and its computation model. It also covers building and submitting a simple data pro- gram, focusing on the core operations that you certainly will use in every PySpark pro- gram you create, such as selecting, filtering, joining, and grouping data in a data frame. Part 2, “Get Proficient,” goes further into data transformation by introducing hier- archical data, a key element of scalable data programs in PySpark. We also make our programs more expressive, flexible, and performant through the judicious introduc- tion of SQL code, an exploration of resilient distributed datasets/user-defined func- tions, efficient usage of pandas within PySpark, and window functions. We also explore Spark’s reporting capabilities and resource management to pinpoint potential perfor- mance problems. Finally, Part 3, “Get Confident,” builds on parts 1 and 2 and covers how to build a machine learning program in PySpark. We use our data transformation tool kit to cre- ate and select features before building and evaluating a machine learning pipeline. We finish this part with creating our own machine learning pipeline components, ensuring maximum usability and readability for our ML programs. Parts 1 and 2 have exercises throughout the chapters, as well as at the end of the chapters. Exercises at the end of a section don’t require you to code; you should be able to answer the questions with what you learned. The book was written with the idea of being read cover to cover, using the appen- dixes as needed. Should you want to dig directly into a topic, I still recommend covering part 1 before delving into a specific chapter. Here are the hard and soft dependencies to help you navigate the book more efficiently: Chapter 3 is a direct continuation of chapter 2. Chapter 5 is a direct continuation of chapter 4. Chapter 9 uses some concepts taught in chapter 8, but advanced readers can read it on its own. Chapters 12, 13, and 14 are best read one after the other. About the code This book works best with Spark version 3.1 or 3.2: Spark introduced many new func- tionalities in version 3, and most commercial offerings are now defaulting to this ver- sion. When appropriate, I provide backward-compatible instructions for Spark version 2.3/2.4. I do not recommend Spark 2.2 or below. I also recommend using Python ver- sion 3.6 and above (I used Python 3.8.8 for the book). Installation instructions are available in appendix A.

ABOUT THIS BOOK xvii You can find the companion repository for the book, with data and code, at https:// github.com/jonesberg/DataAnalysisWithPythonAndPySpark. When appropriate, it also contains runnable versions of the programs developed throughout the book, as well as a few optional exercises. In addition, you can get executable snippets of code from the liveBook (online) version of this book at https://livebook.manning.com/book/data- analysis-with-python-and-pyspark. This book contains many examples of source code both in numbered listings and in line with normal text. In both cases, source code is formatted in a fixed-width font like this to separate it from ordinary text. Sometimes code is also in bold to highlight code that has changed from previous steps in the chapter, such as when a new feature adds to an existing line of code. In many cases, the original source code has been reformatted; we’ve added line breaks and reworked indentation to accommodate the available page space in the book. In rare cases, even this was not enough, and listings include line-continuation markers (➥). Additionally, comments in the source code have often been removed from the listings when the code is described in the text. Code annotations accompany many of the listings and highlight important concepts. liveBook discussion forum Purchase of Data Analysis with Python and PySpark includes free access to liveBook, Manning’s online reading platform. Using liveBook’s exclusive discussion features, you can attach comments to the book globally or to specific sections or paragraphs. It’s a snap to make notes for yourself, ask and answer technical questions, and receive help from the author and other users. To access the forum, go to https://livebook .manning.com/book/data-analysis-with-python-and-pyspark/discussion. You can also learn more about Manning’s forums and the rules of conduct at https://livebook .manning.com/#!/discussion. Manning’s commitment to our readers is to provide a venue where a meaningful dialogue between individual readers and between readers and the author can take place. It is not a commitment to any specific amount of participation on the part of the author, whose contribution to the forum remains voluntary (and unpaid). We sug- gest you try asking the author some challenging questions lest his interest stray! The forum and the archives of previous discussions will be accessible from the publisher’s website as long as the book is in print.

about the author JONATHAN RIOUX uses PySpark inside and out on a daily basis. He also teaches large-scale data analysis to data scientists, engi- neers, and data-savvy business analysts. Jonathan spent a decade in various analytical positions in the insurance industry before venturing into the consulting industry as a machine learning and data analysis expert. He currently works as the director of machine learning for Laivly, a company that equips friendly humans with intelligent auto- mations and machine learning to create the best customer experiences on the planet.xviii