Cookbook
This is a repository for short and sweet examples and links for useful pandas recipes. We encourage users to add to this documentation.
Adding interesting links and/or inline examples to this section is a great First Pull Request.
Simplified, condensed, new-user friendly, in-line examples have been inserted where possible to augment the Stack-Overflow and GitHub links. Many of the links contain expanded information, above what the in-line examples offer.
Pandas (pd) and Numpy (np) are the only two abbreviated imported modules. The rest are kept explicitly imported for newer users.
These examples are written for Python 3. Minor tweaks might be necessary for earlier python versions.
Idioms
These are some neat pandas idioms
if-then/if-then-else on one column, and assignment to another one or more columns:
In [1]: df = pd.DataFrame({'AAA': [4, 5, 6, 7],
...: 'BBB': [10, 20, 30, 40],
...: 'CCC': [100, 50, -30, -50]})
...:
In [2]: df
Out[2]:
AAA BBB CCC
0 4 10 100
1 5 20 50
2 6 30 -30
3 7 40 -50if-then…
An if-then on one column
An if-then with assignment to 2 columns:
Add another line with different logic, to do the -else
Or use pandas where after you’ve set up a mask
if-then-else using numpy’s where()
Splitting
Split a frame with a boolean criterion
Building criteria
Select with multi-column criteria
…and (without assignment returns a Series)
…or (without assignment returns a Series)
…or (with assignment modifies the DataFrame.)
Select rows with data closest to certain value using argsort
Dynamically reduce a list of criteria using a binary operators
One could hard code:
…Or it can be done with a list of dynamically built criteria
Selection
DataFrames
The indexing docs.
Using both row labels and value conditionals
Use loc for label-oriented slicing and iloc positional slicing
There are 2 explicit slicing methods, with a third general case
Positional-oriented (Python slicing style : exclusive of end)
Label-oriented (Non-Python slicing style : inclusive of end)
General (Either slicing style : depends on if the slice contains labels or positions)
Ambiguity arises when an index consists of integers with a non-zero start or non-unit increment.
Using inverse operator (~) to take the complement of a mask
New columns
Efficiently and dynamically creating new columns using applymap
Keep other columns when using min() with groupby
Method 1 : idxmin() to get the index of the minimums
Method 2 : sort then take first of each
Notice the same results, with the exception of the index.
MultiIndexing
The multindexing docs.
Creating a MultiIndex from a labeled frame
Arithmetic
Performing arithmetic with a MultiIndex that needs broadcasting
Slicing
To take the cross section of the 1st level and 1st axis the index:
…and now the 2nd level of the 1st axis.
Slicing a MultiIndex with xs, method #2
Setting portions of a MultiIndex with xs
Sorting
Sort by specific column or an ordered list of columns, with a MultiIndex
Partial selection, the need for sortedness;
Levels
Prepending a level to a multiindex
Missing data
The missing data docs.
Fill forward a reversed timeseries
Replace
Grouping
The grouping docs.
Unlike agg, apply’s callable is passed a sub-DataFrame which gives you access to all the columns
Apply to different items in a group
Replacing some values with mean of the rest of a group
Sort groups by aggregated data
Create multiple aggregated columns
Create a value counts column and reassign back to the DataFrame
Shift groups of the values in a column based on the index
Select row with maximum value from each group
Grouping like Python’s itertools.groupby
Expanding data
Rolling Computation window based on values instead of counts
Splitting
Create a list of dataframes, split using a delineation based on logic included in rows.
Pivot
The Pivot docs.
Frequency table like plyr in R
Plot pandas DataFrame with year over year data
To create year and month cross tabulation:
Apply
Rolling apply to organize - Turning embedded lists into a MultiIndex frame
Rolling apply with a DataFrame returning a Series
Rolling Apply to multiple columns where function calculates a Series before a Scalar from the Series is returned
Rolling apply with a DataFrame returning a Scalar
Rolling Apply to multiple columns where function returns a Scalar (Volume Weighted Average Price)
Timeseries
Constructing a datetime range that excludes weekends and includes only certain times
Aggregation and plotting time series
Turn a matrix with hours in columns and days in rows into a continuous row sequence in the form of a time series. How to rearrange a Python pandas DataFrame?
Dealing with duplicates when reindexing a timeseries to a specified frequency
Calculate the first day of the month for each entry in a DatetimeIndex
Resampling
The Resample docs.
Using Grouper instead of TimeGrouper for time grouping of values
Time grouping with some missing values
Valid frequency arguments to Grouper
Using TimeGrouper and another grouping to create subgroups, then apply a custom function
Resampling with custom periods
Resample intraday frame without adding new days
Merge
The Concat docs. The Join docs.
Append two dataframes with overlapping index (emulate R rbind)
Depending on df construction, ignore_index may be needed
Join with a criteria based on the values
Using searchsorted to merge based on values inside a range
Plotting
The Plotting docs.
Setting x-axis major and minor labels
Plotting multiple charts in an ipython notebook
Annotate a time-series plot #2
Generate Embedded plots in excel files using Pandas, Vincent and xlsxwriter
Boxplot for each quartile of a stratifying variable
Data In/Out
Performance comparison of SQL vs HDF5
CSV
The CSV docs
Reading only certain rows of a csv chunk-by-chunk
Reading the first few lines of a frame
Reading a file that is compressed but not by gzip/bz2 (the native compressed formats which read_csv understands). This example shows a WinZipped file, but is a general application of opening the file within a context manager and using that handle to read. See here
Reading CSV with Unix timestamps and converting to local timezone
Write a multi-row index CSV without writing duplicates
Reading multiple files to create a single DataFrame
The best way to combine multiple files into a single DataFrame is to read the individual frames one by one, put all of the individual frames into a list, and then combine the frames in the list using pd.concat():
You can use the same approach to read all files matching a pattern. Here is an example using glob:
Finally, this strategy will work with the other pd.read_*(...) functions described in the io docs.
Parsing date components in multi-columns
Parsing date components in multi-columns is faster with a format
Skip row between header and data
Option 1: pass rows explicitly to skip rows
Option 2: read column names and then data
SQL
The SQL docs
Reading from databases with SQL
Excel
The Excel docs
Reading from a filelike handle
Modifying formatting in XlsxWriter output
HTML
Reading HTML tables from a server that cannot handle the default request header
HDFStore
The HDFStores docs
Simple queries with a Timestamp Index
Managing heterogeneous data using a linked multiple table hierarchy
Merging on-disk tables with millions of rows
Avoiding inconsistencies when writing to a store from multiple processes/threads
De-duplicating a large store by chunks, essentially a recursive reduction operation. Shows a function for taking in data from csv file and creating a store by chunks, with date parsing as well. See here
Creating a store chunk-by-chunk from a csv file
Appending to a store, while creating a unique index
Reading in a sequence of files, then providing a global unique index to a store while appending
Groupby on a HDFStore with low group density
Groupby on a HDFStore with high group density
Hierarchical queries on a HDFStore
Troubleshoot HDFStore exceptions
Setting min_itemsize with strings
Using ptrepack to create a completely-sorted-index on a store
Storing Attributes to a group node
Binary files
pandas readily accepts NumPy record arrays, if you need to read in a binary file consisting of an array of C structs. For example, given this C program in a file called main.c compiled with gcc main.c -std=gnu99 on a 64-bit machine,
the following Python code will read the binary file 'binary.dat' into a pandas DataFrame, where each element of the struct corresponds to a column in the frame:
Note
The offsets of the structure elements may be different depending on the architecture of the machine on which the file was created. Using a raw binary file format like this for general data storage is not recommended, as it is not cross platform. We recommended either HDF5 or parquet, both of which are supported by pandas’ IO facilities.
Computation
Numerical integration (sample-based) of a time series
Correlation
Often it’s useful to obtain the lower (or upper) triangular form of a correlation matrix calculated from DataFrame.corr(). This can be achieved by passing a boolean mask to where as follows:
The method argument within DataFrame.corr can accept a callable in addition to the named correlation types. Here we compute the distance correlation matrix for a DataFrame object.
Timedeltas
The Timedeltas docs.
Adding and subtracting deltas and dates
Values can be set to NaT using np.nan, similar to datetime
Aliasing axis names
To globally provide aliases for axis names, one can define these 2 functions:
Creating example data
To create a dataframe from every combination of some given values, like R’s expand.grid() function, we can create a dict where the keys are column names and the values are lists of the data values:
Reference : https://pandas.pydata.org/pandas-docs/stable/user_guide/cookbook.html
Last updated