Python | Python Pivot Tables: Analyzing Data Across Two Variables

Python Why Python is best for Data Sciences Python Efficient Code Data Types For Data Science Working With CSV Counter built-in class most_common() - collections module OrderedDict power feature - subclass namedtuple is a powerful tool From String to datetime DateTime Components TimeZone in Action TimeDelta - Time Travel with timedelta Parsing time with pendulum Data Manipulation with Pandas Creating DataFrames with Pandas Creating DataFrames with Dictionaries in Pandas DataFrame With CSV File Summary statistics Summarizing numerical data Summarizing dates The .agg() method Summaries on multiple columns Multiple summaries Cumulative sum Cumulative statistics Dropping duplicate names Dropping duplicate pairs Summaries by group Multiple grouped summaries Grouping by multiple variables Many groups, many summaries Pivot tables Group by to pivot table Different statistics in a pivot table Multiple statistics in pivot table Pivot on two variables Filling missing values in pivot tables Summing with pivot tables Explicit indexes Slicing lists Sort the index before slice Slicing the outer index level Slicing the inner index levels badly Slicing the inner index levels correctly Slicing columns Slice twice Slicing by dates Slicing by partial dates Subsetting by row/column number Slicing - .loc[] + slicing is a power combo The axis argument Calculating summary stats across columns Visualizing data Histograms Bar plots Line plots Rotating axis labels Scatter plots Layering plots Plot with Legend Plot with Transparency Avocados Missing values Detecting missing values Detecting any missing values with .isna().any() Detecting any missing values Counting missing values Plotting missing values Removing missing values Replacing missing values List of dictionaries - by row Dictionary of lists - by column DataFrame manipulation Built-in functions Defining a function Function parameters Return values from functions Docstrings Multiple Parameters and Return Values Basic ingredients of a function Global vs. local scope Nested functions Returning functions Using nonlocal Default and flexible arguments Lambda functions Anonymous functions Introduction to error handling The float() function Passing an incorrect argument Passing valid arguments Passing invalid arguments Errors and exceptions Errors and exceptions - 2 What is iterate Iterating with a for loop Iterators vs. iterables Iterating over iterables: next() Iterating at once with * Iterating with dictionaries Iterating with file connections Using enumerate() enumerate() and unpack Using zip() zip() and unpack Print zip with * Using iterators to load large files into memory Loading data in chunks Iterating over data Populate a list with a for loop A list comprehension For loop And List Comprehension List comprehension with range() Nested loops Conditionals in comprehensions Dict comprehensions Generator expressions List comprehensions vs. generators Conditionals in generator expressions Build generator function Using generator function Generators for the large data limit Build a generator function Using pandas read_csv iterator for streaming data Building with builtins Built-in function: range() with Efficient Code Built-in function: enumerate() with Efficient Code Built-in function: map() with Efficient Code The power of NumPy arrays with Efficient Code NumPy array broadcasting NumPy array boolean indexing Why should we time our code? Using %timeit %timeit output Specifying number loops Using %timeit in line magic mode Using %timeit in cell magic mode Saving output Comparing times Code profiling for runtime %lprun output Code profilling for memory usage %mprun output Efficiently Combining, Counting, and iterating Combining objects Combining objects with zip Counting with loop collections.Counter() The itertools module Combinations with loop itertools.combinations() Comparing objects with loops Set method difference Set method symmetric difference Set method union Uniques with sets Beneifits of eleiminating loops Eliminate loops with NumPy Moving calculations above a loop Using holistic conversions Introduction to pandas DataFrame iteration Calculating win percentage Adding win percentage to DataFrame Iterating with .iloc Iterating with .iterrows() .itertuples() Iterating with .itertuples() pandas .apply() method Dates in Python Attributes of a date Finding the weekday of a date Math with Dates Incrementing variables += Turning dates into strings ISO 8601 format with Exmples Format strftime Adding time to the mix Replacing parts of a datetime Printing datetimes Parsing datetimes with strptime Working with durations Creating timedeltas Negative timedeltas UTC offsets Adjusting timezone vs changing tzinfo Time zone database Starting Daylight Saving Time Ending Daylight Saving Time Reading date and time data in Pandas Loading datetimes with parse_dates Timezone-aware arithmetic Summarizing datetime data in pandas Additional datetime methods in Pandas Timezones in Pandas All datetime operations in Pandas All parts of Pandas Additional datetime methods in Pandas Introduction to string manipulation Concatenation Indexing Slicing Stride String operations Adjusting cases Splitting Joining Stripping characters Finding and replacing Finding substrings Index function Counting occurrences Replacing substrings Positional formatting string formatting Methods for formatting Positional formatting Reordering values Named placeholders Format specifier Formatting datetime Formatted string literal - f-strings Type conversion Index lookups Escape sequences Inline operations Calling functions Template method Substitution The re module Supported metacharacters Repeated characters Quantifiers in re module Regex metacharacters Special characters OR operator in re Module OR operand in re module Greedy vs. nongreedy matching Grouping and capturing re module Pipe | re module Non-capturing groups Backreferences Numbered groups Named groups Lookaround Look-ahead Positive look-ahead Negative look-ahead Look-behind Positive look-behind Negative look-behind Web Scraping With Python Slashes and Brackets in web scrapping Introduction to the scrapy Selector Setting up a Selector Selecting Selectors Extracting Data from a SelectorList CSS Locators Attributes in CSS Selectors with CSS Text Extraction Crawl A Classy Spider Docstrings Docstring formats Don't repeat yourself (DRY) Pass by assignment Immutable or Mutable? Using context managers The "yield" keyword Nested contexts Two ways to define a context manager Handling errors Functions as objects Functions as variables Lists and dictionaries of functions Referencing a function Functions as arguments Defining a function inside another function Functions as return values The global keyword The nonlocal keyword Attaching nonlocal variables to nested functions Closures and deletion Closures and overwriting Definitions - nested function Definitions - nonlocal variables Decorators decorator look like? The double_args decorator Time a function Using timer() When to use decorators with timer() Decorators and metadata The timer decorator Access to the original function Decorators that take arguments run_n_times() A decorator factory Timeout(): a real world example Querying Python interpreter's memory usage Allocating memory for an array Allocating memory for a computation Querying array memory Usage Querying DataFrame memory usage Using pd.read_csv() with chunksize Examining a chunk Filtering a chunk Chunking & filtering together Using pd.concat() Plotting the filtered results Managing Data with Generators Filtering in a list comprehension Filtering & summing with generators Examining consumed generators Reading many files Examining a sample DataFrame Aggregating with Generators Computing the fraction of long trips Delaying Computation with Dask Composing functions Deferring computation with `delayed` Visualizing a task graph Renaming decorated functions Using decorator @-notation Deferring Computation with Loops Aggregating with delayed Functions Computing fraction of long trips with `delayed` functions Chunking Arrays in Dask Working with Numpy arrays Working with Dask arrays Aggregating in chunks Aggregating with Dask arrays Dask array methods/attributes Timing array computations Computing with Multidimensional Arrays A Numpy array of time series data Reshaping time series data Reshaping: Getting the order correct! Using reshape: Row- & column-major ordering Indexing in multiple dimensions Aggregating multidimensional arrays Broadcasting rules Connecting with Dask HDF5 format (Hierarchical Data Format version 5) Extracting Dask array from HDF5 Aggregating while ignoring NaNs Producing a visualization of data_dask Stacking arrays Stacking one-dimensional arrays Stacking two-dimensional arrays Putting array blocks together Analyzing Earthquake Data Using HDF5 files for analyzing earthquake data Extracting Dask array from HDF5 for Analyzing Earthquake Data Aggregating while ignoring NaNs for Analyzing Earthquake Data Producing a visualization of data_dask for Analyzing Earthquake Data Stacking arrays for Analyzing Earthquake Data Stacking one-dimensional arrays for Analyzing Earthquake Data Stacking two-dimensional arrays for Analyzing Earthquake Data Putting array blocks together for Analyzing Earthquake Data Using Dask DataFrames Reading CSV For Dask DataFrames Reading multiple CSV files For Dask DataFrames Building delayed pipelines Compatibility with Pandas API Timing DataFrame Operations Timing I/O & computation: Pandas Is Dask or Pandas appropriate? Building Dask Bags & Globbing Sequences to bags Reading text files Glob expressions Using Python's glob module Functional Approaches using Dask Bags Functional programming Functional programming - Using map Functional programming - Using Filter Functional Approaches - Using dask.bag.map Functional Approaches - Using dask.bag.filter Functional Approaches - Using .str & string methods JSON data files Using json module JSON Files into Dask Bags Plucking values Merging DataFrames Dask DataFrame pipelines Repeated reads & performance Using persistence Python, data science, & software engineering Software engineering concepts Django Introduction Datatypes Lists Combining Lists Finding and Removing Elements in a List Iterating and Sorting Tuples Zipping and Unpacking More Unpacking in Loops Enumerating positions Sets for Unordered and Unique Data with Tuples in Python Set Creating Sets in Python: Harnessing the Power of Unique Collections Modifying Sets in Python: Adding and Removing Elements with Ease Removing Data from Sets in Python: Streamlining Set Operations Exploring Set Operations in Python: Uncovering Similarities among Sets Set Operations in Python: Unveiling Differences among Sets Exploring Dictionaries in Python: A Key-Value Data Structure Creating and Looping Through Dictionaries in Python: A Comprehensive Guide Safely Finding Values in Python Dictionaries: A Guide to Avoiding Key Errors Safely Finding Values in Python Dictionaries: Advanced Techniques for Key Lookup Dictionaries-Working with Nested Data in Python: Exploring Hierarchical Structures Adding and Extending Python Dictionaries: Flexible Data Manipulation Popping and Deleting from Python Dictionaries: Managing Key-Value Removal Working with Dictionaries More Pythonically: Efficient Data Manipulation Checking Dictionaries for Data: Effective Data Validation in Python Working with CSV Files in Python: Simplify Data Processing and Analysis Creating a Dictionary from a File in Python: Simplify Data Mapping and Access Counting Made Easy in Python: Harness the Power of Counting Techniques Exploring the Collections Module in Python: Enhance Data Structures and Operations Understanding the Counter Class in Python: Simplify Counting and Frequency Analysis Working with Dictionaries of Unknown Structure using defaultdict in Python Advanced Usage of defaultdict in Python for Flexible Data Handling Maintaining Dictionary Order with OrderedDict in Python Harnessing the Power of OrderedDict's Advanced Features in Python Unleashing the Power of namedtuple in Python Leveraging the Power of namedtuples in Python Working with Datetime Components and Current Time in Python Exploring Datetime Components in Python Understanding "now" in Python's Datetime Module Exploring Timezones in Python's Datetime Module Time Travel in Python: Adding and Subtracting Time HELP! Libraries to Make Python Development Easier Parsing Time with Pendulum: Simplify Your Date and Time Operations Timezone Hopping with Pendulum: Seamlessly Manage Time across Different Timezones Humanizing Differences: Making Time Intervals More Readable with Pendulum

Pivot on two variables

To pivot on two variables, you can pass a list of variables to the index or columns parameter in the pivot_table() method. Here's an example:

import pandas as pd

# create a pandas DataFrame

df = pd.DataFrame({'Region': ['North', 'North', 'South', 'South', 'West', 'West'],

'Month': ['Jan', 'Feb', 'Jan', 'Feb', 'Jan', 'Feb'],

'Salesperson': ['Alice', 'Bob', 'Charlie', 'Dave', 'Eve', 'Frank'],

'Sales': [100, 200, 150, 50, 75, 125],

'Profit': [20, 50, 30, 10, 15, 25]})

# create a pivot table that summarizes the sales and profit data by region and month

pivot = pd.pivot_table(df, values=['Sales', 'Profit'], index=['Region', 'Month'], columns=['Salesperson'], aggfunc='sum')

print(pivot)

This will create a pivot table that summarizes the sales and profit data by region and month. The resulting output will be:

Profit Sales

Salesperson Alice Bob Charlie Dave Eve Frank Alice Bob Charlie Dave Eve Frank

Region Month

North Feb 50.0 200 NaN NaN NaN NaN 200 200 NaN NaN NaN NaN

Jan 20.0 100 NaN NaN NaN NaN 100 200 NaN NaN NaN NaN

South Feb NaN NaN 10.0 50 NaN NaN NaN NaN 50.0 50 NaN NaN

Jan NaN NaN 30.0 NaN NaN NaN NaN NaN 150.0 NaN NaN NaN

West Feb NaN NaN NaN NaN 25 25 NaN NaN NaN NaN 75 125

Jan NaN NaN NaN NaN 15 NaN NaN NaN NaN NaN 75 NaN

The resulting pivot table shows the total sales and profit for each salesperson broken down by region and month. The index parameter is passed a list of variables that specifies the rows to group by, and the columns parameter is passed a variable that specifies the columns to group by. You can pass any number of variables to group by to the index and columns parameters.