Cookbook¶

In [1]: df = pd.DataFrame(np.random.randn(6,1), index=pd.date_range('2013-08-01', periods=6, freq='B'), columns=list('A'))

In [2]: df.ix[3,'A'] = np.nan

In [3]: df
Out[3]: 
                   A
2013-08-01  0.469112
2013-08-02 -0.282863
2013-08-05 -1.509059
2013-08-06       NaN
2013-08-07  1.212112
2013-08-08 -0.173215

In [4]: df.reindex(df.index[::-1]).ffill()
Out[4]: 
                   A
2013-08-08 -0.173215
2013-08-07  1.212112
2013-08-06  1.212112
2013-08-05 -1.509059
2013-08-02 -0.282863
2013-08-01  0.469112

cumsum reset at NaN values

Replace¶

Using replace with backrefs

Grouping¶

The grouping docs.

Basic grouping with apply

Using get_group

Apply to different items in a group

Expanding Apply

Replacing values with groupby means

Sort by group with aggregation

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

In [5]: df = pd.DataFrame(
   ...:      {u'line_race': [10L, 10L, 8L, 10L, 10L, 8L],
   ...:       u'beyer': [99L, 102L, 103L, 103L, 88L, 100L]},
   ...:      index=[u'Last Gunfighter', u'Last Gunfighter', u'Last Gunfighter',
   ...:             u'Paynter', u'Paynter', u'Paynter']); df
   ...: 
Out[5]: 
                 beyer  line_race
Last Gunfighter     99         10
Last Gunfighter    102         10
Last Gunfighter    103          8
Paynter            103         10
Paynter             88         10
Paynter            100          8

In [6]: df['beyer_shifted'] = df.groupby(level=0)['beyer'].shift(1)

In [7]: df
Out[7]: 
                 beyer  line_race  beyer_shifted
Last Gunfighter     99         10            NaN
Last Gunfighter    102         10             99
Last Gunfighter    103          8            102
Paynter            103         10            NaN
Paynter             88         10            103
Paynter            100          8             88

Expanding Data¶

Alignment and to-date

Rolling Computation window based on values instead of counts

Rolling Mean by Time Interval

Splitting¶

Splitting a frame

Pivot¶

The Pivot docs.

Partial sums and subtotals

Frequency table like plyr in R

Apply¶

Turning embedded lists into a multi-index frame

Rolling apply with a DataFrame returning a Series

Rolling apply with a DataFrame returning a Scalar

Timeseries¶

Between times

Using indexer between time

Vectorized Lookup

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

Resampling¶

The Resample docs.

TimeGrouping of values grouped across time

TimeGrouping #2

Using TimeGrouper and another grouping to create subgroups, then apply a custom function

Resampling with custom periods

Resample intraday frame without adding new days

Resample minute data

Resample with groupby

Merge¶

The Concat docs. The Join docs.

emulate R rbind

Self Join

How to set the index and join

KDB like asof join

Join with a criteria based on the values

Plotting¶

The Plotting docs.

Make Matplotlib look like R

Setting x-axis major and minor labels

Plotting multiple charts in an ipython notebook

Creating a multi-line plot

Plotting a heatmap

Annotate a time-series plot

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

In [8]: df = pd.DataFrame(
   ...:     {u'stratifying_var': np.random.uniform(0, 100, 20),
   ...:      u'price': np.random.normal(100, 5, 20)}
   ...: )
   ...: 

In [9]: df[u'quartiles'] = pd.qcut(
   ...:     df[u'stratifying_var'],
   ...:     4,
   ...:     labels=[u'0-25%', u'25-50%', u'50-75%', u'75-100%']
   ...: )
   ...: 

In [10]: df.boxplot(column=u'price', by=u'quartiles')
Out[10]: <matplotlib.axes.AxesSubplot at 0xaa2d2d0c>

Data In/Out¶

Performance comparison of SQL vs HDF5

CSV¶

The CSV docs

read_csv in action

appending to a csv

Reading a csv chunk-by-chunk

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

Inferring dtypes from a file

Dealing with bad lines

Dealing with bad lines II

Reading CSV with Unix timestamps and converting to local timezone

Write a multi-row index CSV without writing duplicates

Parsing date components in multi-columns is faster with a format

In [30]: i = pd.date_range('20000101',periods=10000)

In [31]: df = pd.DataFrame(dict(year = i.year, month = i.month, day = i.day))

In [32]: df.head()
Out[32]:
   day  month  year
0    1      1  2000
1    2      1  2000
2    3      1  2000
3    4      1  2000
4    5      1  2000

In [33]: %timeit pd.to_datetime(df.year*10000+df.month*100+df.day,format='%Y%m%d')
100 loops, best of 3: 7.08 ms per loop

# simulate combinging into a string, then parsing
In [34]: ds = df.apply(lambda x: "%04d%02d%02d" % (x['year'],x['month'],x['day']),axis=1)

In [35]: ds.head()
Out[35]:
0    20000101
1    20000102
2    20000103
3    20000104
4    20000105
dtype: object

In [36]: %timeit pd.to_datetime(ds)
1 loops, best of 3: 488 ms per loop

SQL¶

The SQL docs

Reading from databases with SQL

Excel¶

The Excel docs

Reading from a filelike handle

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

Deduplicating 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

Large Data work flows

Reading in a sequence of files, then providing a global unique index to a store while appending

Groupby on a HDFStore

Hierarchical queries on a HDFStore

Counting with 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

In [11]: df = DataFrame(np.random.randn(8,3))

In [12]: store = HDFStore('test.h5')

In [13]: store.put('df',df)

# you can store an arbitrary python object via pickle
In [14]: store.get_storer('df').attrs.my_attribute = dict(A = 10)

In [15]: store.get_storer('df').attrs.my_attribute
Out[15]: {'A': 10}

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,

#include <stdio.h>
#include <stdint.h>

typedef struct _Data
{
    int32_t count;
    double avg;
    float scale;
} Data;

int main(int argc, const char *argv[])
{
    size_t n = 10;
    Data d[n];

    for (int i = 0; i < n; ++i)
    {
        d[i].count = i;
        d[i].avg = i + 1.0;
        d[i].scale = (float) i + 2.0f;
    }

    FILE *file = fopen("binary.dat", "wb");
    fwrite(&d, sizeof(Data), n, file);
    fclose(file);

    return 0;
}

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:

import numpy as np
from pandas import DataFrame

names = 'count', 'avg', 'scale'

# note that the offsets are larger than the size of the type because of
# struct padding
offsets = 0, 8, 16
formats = 'i4', 'f8', 'f4'
dt = np.dtype({'names': names, 'offsets': offsets, 'formats': formats},
              align=True)
df = DataFrame(np.fromfile('binary.dat', dt))

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 msgpack, both of which are supported by pandas’ IO facilities.

Computation¶

Numerical integration (sample-based) of a time series

Miscellaneous¶

The Timedeltas docs.

Operating with timedeltas

Create timedeltas with date differences

Adding days to dates in a dataframe

Aliasing Axis Names¶

To globally provide aliases for axis names, one can define these 2 functions:

In [16]: def set_axis_alias(cls, axis, alias):
   ....:      if axis not in cls._AXIS_NUMBERS:
   ....:          raise Exception("invalid axis [%s] for alias [%s]" % (axis, alias))
   ....:      cls._AXIS_ALIASES[alias] = axis
   ....: 

Table Of Contents

Search

Cookbook¶

Idioms¶

Selection¶

MultiIndexing¶

Arithmetic¶

Slicing¶

Sorting¶

Levels¶

panelnd¶

Missing Data¶

Replace¶

Grouping¶

Expanding Data¶

Splitting¶

Pivot¶

Apply¶

Timeseries¶

Resampling¶

Merge¶

Plotting¶

Data In/Out¶

CSV¶

SQL¶

Excel¶

HDFStore¶

Binary Files¶

Computation¶

Miscellaneous¶

Aliasing Axis Names¶

Navigation

Table Of Contents

Search

Cookbook¶

Idioms¶

Selection¶

MultiIndexing¶

Arithmetic¶

Slicing¶

Sorting¶

Levels¶

panelnd¶

Missing Data¶

Replace¶

Grouping¶

Expanding Data¶

Splitting¶

Pivot¶

Apply¶

Timeseries¶

Resampling¶

Merge¶

Plotting¶

Data In/Out¶

CSV¶

SQL¶

Excel¶

HDFStore¶

Binary Files¶

Computation¶

Miscellaneous¶

Aliasing Axis Names¶

Navigation