Skip to main content
`Ctrl`+`K`

# 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.

## 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  -50

### If-then…

An if-then on one column

    In [3]: df.loc[df.AAA >= 5, "BBB"] = -1

    In [4]: df
    Out[4]:
       AAA  BBB  CCC
    0    4   10  100
    1    5   -1   50
    2    6   -1  -30
    3    7   -1  -50

An if-then with assignment to 2 columns:

    In [5]: df.loc[df.AAA >= 5, ["BBB", "CCC"]] = 555

    In [6]: df
    Out[6]:
       AAA  BBB  CCC
    0    4   10  100
    1    5  555  555
    2    6  555  555
    3    7  555  555

Add another line with different logic, to do the -else

    In [7]: df.loc[df.AAA < 5, ["BBB", "CCC"]] = 2000

    In [8]: df
    Out[8]:
       AAA   BBB   CCC
    0    4  2000  2000
    1    5   555   555
    2    6   555   555
    3    7   555   555

Or use pandas where after you’ve set up a mask

    In [9]: df_mask = pd.DataFrame(
       ...:     {"AAA": [True] * 4, "BBB": [False] * 4, "CCC": [True, False] * 2}
       ...: )
       ...:

    In [10]: df.where(df_mask, -1000)
    Out[10]:
       AAA   BBB   CCC
    0    4 -1000  2000
    1    5 -1000 -1000
    2    6 -1000   555
    3    7 -1000 -1000

if-then-else using NumPy’s where()

    In [11]: df = pd.DataFrame(
       ....:     {"AAA": [4, 5, 6, 7], "BBB": [10, 20, 30, 40], "CCC": [100, 50, -30, -50]}
       ....: )
       ....:

    In [12]: df
    Out[12]:
       AAA  BBB  CCC
    0    4   10  100
    1    5   20   50
    2    6   30  -30
    3    7   40  -50

    In [13]: df["logic"] = np.where(df["AAA"] > 5, "high", "low")

    In [14]: df
    Out[14]:
       AAA  BBB  CCC logic
    0    4   10  100   low
    1    5   20   50   low
    2    6   30  -30  high
    3    7   40  -50  high

### Splitting

Split a frame with a boolean criterion

    In [15]: df = pd.DataFrame(
       ....:     {"AAA": [4, 5, 6, 7], "BBB": [10, 20, 30, 40], "CCC": [100, 50, -30, -50]}
       ....: )
       ....:

    In [16]: df
    Out[16]:
       AAA  BBB  CCC
    0    4   10  100
    1    5   20   50
    2    6   30  -30
    3    7   40  -50

    In [17]: df[df.AAA <= 5]
    Out[17]:
       AAA  BBB  CCC
    0    4   10  100
    1    5   20   50

    In [18]: df[df.AAA > 5]
    Out[18]:
       AAA  BBB  CCC
    2    6   30  -30
    3    7   40  -50

### Building criteria

Select with multi-column criteria

    In [19]: df = pd.DataFrame(
       ....:     {"AAA": [4, 5, 6, 7], "BBB": [10, 20, 30, 40], "CCC": [100, 50, -30, -50]}
       ....: )
       ....:

    In [20]: df
    Out[20]:
       AAA  BBB  CCC
    0    4   10  100
    1    5   20   50
    2    6   30  -30
    3    7   40  -50

…and (without assignment returns a Series)

    In [21]: df.loc[(df["BBB"] < 25) & (df["CCC"] >= -40), "AAA"]
    Out[21]:
    0    4
    1    5
    Name: AAA, dtype: int64

…or (without assignment returns a Series)

    In [22]: df.loc[(df["BBB"] > 25) | (df["CCC"] >= -40), "AAA"]
    Out[22]:
    0    4
    1    5
    2    6
    3    7
    Name: AAA, dtype: int64

…or (with assignment modifies the DataFrame.)

    In [23]: df.loc[(df["BBB"] > 25) | (df["CCC"] >= 75), "AAA"] = 999

    In [24]: df
    Out[24]:
       AAA  BBB  CCC
    0  999   10  100
    1    5   20   50
    2  999   30  -30
    3  999   40  -50

Select rows with data closest to certain value using argsort

    In [25]: df = pd.DataFrame(
       ....:     {"AAA": [4, 5, 6, 7], "BBB": [10, 20, 30, 40], "CCC": [100, 50, -30, -50]}
       ....: )
       ....:

    In [26]: df
    Out[26]:
       AAA  BBB  CCC
    0    4   10  100
    1    5   20   50
    2    6   30  -30
    3    7   40  -50

    In [27]: aValue = 43.0

    In [28]: df.loc[(df.CCC - aValue).abs().argsort()]
    Out[28]:
       AAA  BBB  CCC
    1    5   20   50
    0    4   10  100
    2    6   30  -30
    3    7   40  -50

Dynamically reduce a list of criteria using a binary operators

    In [29]: df = pd.DataFrame(
       ....:     {"AAA": [4, 5, 6, 7], "BBB": [10, 20, 30, 40], "CCC": [100, 50, -30, -50]}
       ....: )
       ....:

    In [30]: df
    Out[30]:
       AAA  BBB  CCC
    0    4   10  100
    1    5   20   50
    2    6   30  -30
    3    7   40  -50

    In [31]: Crit1 = df.AAA <= 5.5

    In [32]: Crit2 = df.BBB == 10.0

    In [33]: Crit3 = df.CCC > -40.0

One could hard code:

    In [34]: AllCrit = Crit1 & Crit2 & Crit3

…Or it can be done with a list of dynamically built criteria

    In [35]: import functools

    In [36]: CritList = [Crit1, Crit2, Crit3]

    In [37]: AllCrit = functools.reduce(lambda x, y: x & y, CritList)

    In [38]: df[AllCrit]
    Out[38]:
       AAA  BBB  CCC
    0    4   10  100

## Selection

### DataFrames

The indexing docs.

Using both row labels and value conditionals

    In [39]: df = pd.DataFrame(
       ....:     {"AAA": [4, 5, 6, 7], "BBB": [10, 20, 30, 40], "CCC": [100, 50, -30, -50]}
       ....: )
       ....:

    In [40]: df
    Out[40]:
       AAA  BBB  CCC
    0    4   10  100
    1    5   20   50
    2    6   30  -30
    3    7   40  -50

    In [41]: df[(df.AAA <= 6) & (df.index.isin([0, 2, 4]))]
    Out[41]:
       AAA  BBB  CCC
    0    4   10  100
    2    6   30  -30

Use loc for label-oriented slicing and iloc positional slicing GH 2904

    In [42]: df = pd.DataFrame(
       ....:     {"AAA": [4, 5, 6, 7], "BBB": [10, 20, 30, 40], "CCC": [100, 50, -30, -50]},
       ....:     index=["foo", "bar", "boo", "kar"],
       ....: )
       ....:

There are 2 explicit slicing methods, with a third general case

1. Positional-oriented (Python slicing style : exclusive of end)

2. Label-oriented (Non-Python slicing style : inclusive of end)

3. General (Either slicing style : depends on if the slice contains labels or positions)

    In [43]: df.loc["bar":"kar"]  # Label
    Out[43]:
         AAA  BBB  CCC
    bar    5   20   50
    boo    6   30  -30
    kar    7   40  -50

    # Generic
    In [44]: df[0:3]
    Out[44]:
         AAA  BBB  CCC
    foo    4   10  100
    bar    5   20   50
    boo    6   30  -30

    In [45]: df["bar":"kar"]
    Out[45]:
         AAA  BBB  CCC
    bar    5   20   50
    boo    6   30  -30
    kar    7   40  -50

Ambiguity arises when an index consists of integers with a non-zero start or non-unit increment.

    In [46]: data = {"AAA": [4, 5, 6, 7], "BBB": [10, 20, 30, 40], "CCC": [100, 50, -30, -50]}

    In [47]: df2 = pd.DataFrame(data=data, index=[1, 2, 3, 4])  # Note index starts at 1.

    In [48]: df2.iloc[1:3]  # Position-oriented
    Out[48]:
       AAA  BBB  CCC
    2    5   20   50
    3    6   30  -30

    In [49]: df2.loc[1:3]  # Label-oriented
    Out[49]:
       AAA  BBB  CCC
    1    4   10  100
    2    5   20   50
    3    6   30  -30

Using inverse operator (~) to take the complement of a mask

    In [50]: df = pd.DataFrame(
       ....:     {"AAA": [4, 5, 6, 7], "BBB": [10, 20, 30, 40], "CCC": [100, 50, -30, -50]}
       ....: )
       ....:

    In [51]: df
    Out[51]:
       AAA  BBB  CCC
    0    4   10  100
    1    5   20   50
    2    6   30  -30
    3    7   40  -50

    In [52]: df[~((df.AAA <= 6) & (df.index.isin([0, 2, 4])))]
    Out[52]:
       AAA  BBB  CCC
    1    5   20   50
    3    7   40  -50

How to randomly extract n contiguous rows in a pandas DataFrame?

    In [53]: df = pd.DataFrame({
       ....:     'A': range(10),
       ....:     'B': range(10),
       ....:     'C': range(10)
       ....: })
       ....:

    In [54]: window_size = 5

    In [55]: start_idx = np.random.randint(0, len(df) - window_size + 1)

    In [56]: df.iloc[start_idx:start_idx+window_size]
    Out[56]:
       A  B  C
    1  1  1  1
    2  2  2  2
    3  3  3  3
    4  4  4  4
    5  5  5  5

### New columns

Efficiently and dynamically creating new columns using DataFrame.map (previously named applymap)

    In [57]: df = pd.DataFrame({"AAA": [1, 2, 1, 3], "BBB": [1, 1, 2, 2], "CCC": [2, 1, 3, 1]})

    In [58]: df
    Out[58]:
       AAA  BBB  CCC
    0    1    1    2
    1    2    1    1
    2    1    2    3
    3    3    2    1

    In [59]: source_cols = df.columns  # Or some subset would work too

    In [60]: new_cols = [str(x) + "_cat" for x in source_cols]

    In [61]: categories = {1: "Alpha", 2: "Beta", 3: "Charlie"}

    In [62]: df[new_cols] = df[source_cols].map(categories.get)

    In [63]: df
    Out[63]:
       AAA  BBB  CCC  AAA_cat BBB_cat  CCC_cat
    0    1    1    2    Alpha   Alpha     Beta
    1    2    1    1     Beta   Alpha    Alpha
    2    1    2    3    Alpha    Beta  Charlie
    3    3    2    1  Charlie    Beta    Alpha

Keep other columns when using min() with groupby

    In [64]: df = pd.DataFrame(
       ....:     {"AAA": [1, 1, 1, 2, 2, 2, 3, 3], "BBB": [2, 1, 3, 4, 5, 1, 2, 3]}
       ....: )
       ....:

    In [65]: df
    Out[65]:
       AAA  BBB
    0    1    2
    1    1    1
    2    1    3
    3    2    4
    4    2    5
    5    2    1
    6    3    2
    7    3    3

Method 1 : idxmin() to get the index of the minimums

    In [66]: df.loc[df.groupby("AAA")["BBB"].idxmin()]
    Out[66]:
       AAA  BBB
    1    1    1
    5    2    1
    6    3    2

Method 2 : sort then take first of each

    In [67]: df.sort_values(by="BBB").groupby("AAA", as_index=False).first()
    Out[67]:
       AAA  BBB
    0    1    1
    1    2    1
    2    3    2

Notice the same results, with the exception of the index.

## Multiindexing

The multindexing docs.

Creating a MultiIndex from a labeled frame

    In [68]: df = pd.DataFrame(
       ....:     {
       ....:         "row": [0, 1, 2],
       ....:         "One_X": [1.1, 1.1, 1.1],
       ....:         "One_Y": [1.2, 1.2, 1.2],
       ....:         "Two_X": [1.11, 1.11, 1.11],
       ....:         "Two_Y": [1.22, 1.22, 1.22],
       ....:     }
       ....: )
       ....:

    In [69]: df
    Out[69]:
       row  One_X  One_Y  Two_X  Two_Y
    0    0    1.1    1.2   1.11   1.22
    1    1    1.1    1.2   1.11   1.22
    2    2    1.1    1.2   1.11   1.22

    # As Labelled Index
    In [70]: df = df.set_index("row")

    In [71]: df
    Out[71]:
         One_X  One_Y  Two_X  Two_Y
    row
    0      1.1    1.2   1.11   1.22
    1      1.1    1.2   1.11   1.22
    2      1.1    1.2   1.11   1.22

    # With Hierarchical Columns
    In [72]: df.columns = pd.MultiIndex.from_tuples([tuple(c.split("_")) for c in df.columns])

    In [73]: df
    Out[73]:
         One        Two
           X    Y     X     Y
    row
    0    1.1  1.2  1.11  1.22
    1    1.1  1.2  1.11  1.22
    2    1.1  1.2  1.11  1.22

    # Now stack & Reset
    In [74]: df = df.stack(0).reset_index(1)

    In [75]: df
    Out[75]:
        level_1     X     Y
    row
    0       One  1.10  1.20
    0       Two  1.11  1.22
    1       One  1.10  1.20
    1       Two  1.11  1.22
    2       One  1.10  1.20
    2       Two  1.11  1.22

    # And fix the labels (Notice the label 'level_1' got added automatically)
    In [76]: df.columns = ["Sample", "All_X", "All_Y"]

    In [77]: df
    Out[77]:
        Sample  All_X  All_Y
    row
    0      One   1.10   1.20
    0      Two   1.11   1.22
    1      One   1.10   1.20
    1      Two   1.11   1.22
    2      One   1.10   1.20
    2      Two   1.11   1.22

### Arithmetic

Performing arithmetic with a MultiIndex that needs broadcasting

    In [78]: cols = pd.MultiIndex.from_tuples(
       ....:     [(x, y) for x in ["A", "B", "C"] for y in ["O", "I"]]
       ....: )
       ....:

    In [79]: df = pd.DataFrame(np.random.randn(2, 6), index=["n", "m"], columns=cols)

    In [80]: df
    Out[80]:
              A                   B                   C
              O         I         O         I         O         I
    n  0.952516  0.035680  0.974184  0.719101  0.184765  0.619326
    m -1.138680 -0.641576  0.443212 -0.110240 -0.166778  0.501113

    In [81]: df = df.div(df["C"], level=1)

    In [82]: df
    Out[82]:
        A       B       C
        O   I   O   I   O   I
    n NaN NaN NaN NaN NaN NaN
    m NaN NaN NaN NaN NaN NaN

### Slicing

Slicing a MultiIndex with xs

    In [83]: coords = [("AA", "one"), ("AA", "six"), ("BB", "one"), ("BB", "two"), ("BB", "six")]

    In [84]: index = pd.MultiIndex.from_tuples(coords)

    In [85]: df = pd.DataFrame([11, 22, 33, 44, 55], index, ["MyData"])

    In [86]: df
    Out[86]:
            MyData
    AA one      11
       six      22
    BB one      33
       two      44
       six      55

To take the cross section of the 1st level and 1st axis the index:

    # Note : level and axis are optional, and default to zero
    In [87]: df.xs("BB", level=0, axis=0)
    Out[87]:
         MyData
    one      33
    two      44
    six      55

…and now the 2nd level of the 1st axis.

    In [88]: df.xs("six", level=1, axis=0)
    Out[88]:
        MyData
    AA      22
    BB      55

Slicing a MultiIndex with xs, method #2

    In [89]: import itertools

    In [90]: index = list(itertools.product(["Ada", "Quinn", "Violet"], ["Comp", "Math", "Sci"]))

    In [91]: headr = list(itertools.product(["Exams", "Labs"], ["I", "II"]))

    In [92]: indx = pd.MultiIndex.from_tuples(index, names=["Student", "Course"])

    In [93]: cols = pd.MultiIndex.from_tuples(headr)  # Notice these are un-named

    In [94]: data = [[70 + x + y + (x * y) % 3 for x in range(4)] for y in range(9)]

    In [95]: df = pd.DataFrame(data, indx, cols)

    In [96]: df
    Out[96]:
                   Exams     Labs
                       I  II    I  II
    Student Course
    Ada     Comp      70  71   72  73
            Math      71  73   75  74
            Sci       72  75   75  75
    Quinn   Comp      73  74   75  76
            Math      74  76   78  77
            Sci       75  78   78  78
    Violet  Comp      76  77   78  79
            Math      77  79   81  80
            Sci       78  81   81  81

    In [97]: All = slice(None)

    In [98]: df.loc["Violet"]
    Out[98]:
           Exams     Labs
               I  II    I  II
    Course
    Comp      76  77   78  79
    Math      77  79   81  80
    Sci       78  81   81  81

    In [99]: df.loc[(All, "Math"), All]
    Out[99]:
                   Exams     Labs
                       I  II    I  II
    Student Course
    Ada     Math      71  73   75  74
    Quinn   Math      74  76   78  77
    Violet  Math      77  79   81  80

    In [100]: df.loc[(slice("Ada", "Quinn"), "Math"), All]
    Out[100]:
                   Exams     Labs
                       I  II    I  II
    Student Course
    Ada     Math      71  73   75  74
    Quinn   Math      74  76   78  77

    In [101]: df.loc[(All, "Math"), ("Exams")]
    Out[101]:
                     I  II
    Student Course
    Ada     Math    71  73
    Quinn   Math    74  76
    Violet  Math    77  79

    In [102]: df.loc[(All, "Math"), (All, "II")]
    Out[102]:
                   Exams Labs
                      II   II
    Student Course
    Ada     Math      73   74
    Quinn   Math      76   77
    Violet  Math      79   80

Setting portions of a MultiIndex with xs

### Sorting

Sort by specific column or an ordered list of columns, with a MultiIndex

    In [103]: df.sort_values(by=("Labs", "II"), ascending=False)
    Out[103]:
                   Exams     Labs
                       I  II    I  II
    Student Course
    Violet  Sci       78  81   81  81
            Math      77  79   81  80
            Comp      76  77   78  79
    Quinn   Sci       75  78   78  78
            Math      74  76   78  77
            Comp      73  74   75  76
    Ada     Sci       72  75   75  75
            Math      71  73   75  74
            Comp      70  71   72  73

Partial selection, the need for sortedness GH 2995

### Levels

Prepending a level to a multiindex

Flatten Hierarchical columns

## Missing data

The missing data docs.

Fill forward a reversed timeseries

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

    In [105]: df.loc[df.index[3], "A"] = np.nan

    In [106]: df
    Out[106]:
                       A
    2013-08-01 -0.355322
    2013-08-02 -0.337890
    2013-08-05  0.580967
    2013-08-06       NaN
    2013-08-07  0.057802
    2013-08-08  0.761948

    In [107]: df.bfill()
    Out[107]:
                       A
    2013-08-01 -0.355322
    2013-08-02 -0.337890
    2013-08-05  0.580967
    2013-08-06  0.057802
    2013-08-07  0.057802
    2013-08-08  0.761948

cumsum reset at NaN values

### Replace

Using replace with backrefs

## Grouping

The grouping docs.

Basic grouping with apply

Unlike agg, apply’s callable is passed a sub-DataFrame which gives you access to all the columns

    In [108]: df = pd.DataFrame(
       .....:     {
       .....:         "animal": "cat dog cat fish dog cat cat".split(),
       .....:         "size": list("SSMMMLL"),
       .....:         "weight": [8, 10, 11, 1, 20, 12, 12],
       .....:         "adult": [False] * 5 + [True] * 2,
       .....:     }
       .....: )
       .....:

    In [109]: df
    Out[109]:
      animal size  weight  adult
    0    cat    S       8  False
    1    dog    S      10  False
    2    cat    M      11  False
    3   fish    M       1  False
    4    dog    M      20  False
    5    cat    L      12   True
    6    cat    L      12   True

    # List the size of the animals with the highest weight.
    In [110]: df.groupby("animal").apply(lambda subf: subf["size"][subf["weight"].idxmax()])
    Out[110]:
    animal
    cat     L
    dog     M
    fish    M
    dtype: str

Using get_group

    In [111]: gb = df.groupby("animal")

    In [112]: gb.get_group("cat")
    Out[112]:
      animal size  weight  adult
    0    cat    S       8  False
    2    cat    M      11  False
    5    cat    L      12   True
    6    cat    L      12   True

Apply to different items in a group

    In [113]: def GrowUp(x):
       .....:     avg_weight = sum(x[x["size"] == "S"].weight * 1.5)
       .....:     avg_weight += sum(x[x["size"] == "M"].weight * 1.25)
       .....:     avg_weight += sum(x[x["size"] == "L"].weight)
       .....:     avg_weight /= len(x)
       .....:     return pd.Series(["L", avg_weight, True], index=["size", "weight", "adult"])
       .....:

    In [114]: expected_df = gb.apply(GrowUp)

    In [115]: expected_df
    Out[115]:
           size   weight  adult
    animal
    cat       L  12.4375   True
    dog       L  20.0000   True
    fish      L   1.2500   True

Expanding apply

    In [116]: S = pd.Series([i / 100.0 for i in range(1, 11)])

    In [117]: def cum_ret(x, y):
       .....:     return x * (1 + y)
       .....:

    In [118]: def red(x):
       .....:     return functools.reduce(cum_ret, x, 1.0)
       .....:

    In [119]: S.expanding().apply(red, raw=True)
    Out[119]:
    0    1.010000
    1    1.030200
    2    1.061106
    3    1.103550
    4    1.158728
    5    1.228251
    6    1.314229
    7    1.419367
    8    1.547110
    9    1.701821
    dtype: float64

Replacing some values with mean of the rest of a group

    In [120]: df = pd.DataFrame({"A": [1, 1, 2, 2], "B": [1, -1, 1, 2]})

    In [121]: gb = df.groupby("A")

    In [122]: def replace(g):
       .....:     mask = g < 0
       .....:     return g.where(~mask, g[~mask].mean())
       .....:

    In [123]: gb.transform(replace)
    Out[123]:
       B
    0  1
    1  1
    2  1
    3  2

Sort groups by aggregated data

    In [124]: df = pd.DataFrame(
       .....:     {
       .....:         "code": ["foo", "bar", "baz"] * 2,
       .....:         "data": [0.16, -0.21, 0.33, 0.45, -0.59, 0.62],
       .....:         "flag": [False, True] * 3,
       .....:     }
       .....: )
       .....:

    In [125]: code_groups = df.groupby("code")

    In [126]: agg_n_sort_order = code_groups[["data"]].transform("sum").sort_values(by="data")

    In [127]: sorted_df = df.loc[agg_n_sort_order.index]

    In [128]: sorted_df
    Out[128]:
      code  data   flag
    1  bar -0.21   True
    4  bar -0.59  False
    3  foo  0.45   True
    0  foo  0.16  False
    2  baz  0.33  False
    5  baz  0.62   True

Create multiple aggregated columns

    In [129]: rng = pd.date_range(start="2014-10-07", periods=10, freq="2min")

    In [130]: ts = pd.Series(data=list(range(10)), index=rng)

    In [131]: def MyCust(x):
       .....:     if len(x) > 2:
       .....:         return x.iloc[1] * 1.234
       .....:     return pd.NaT
       .....:

    In [132]: mhc = {"Mean": "mean", "Max": "max", "Custom": MyCust}

    In [133]: ts.resample("5min").apply(mhc)
    Out[133]:
                         Mean  Max Custom
    2014-10-07 00:00:00   1.0    2  1.234
    2014-10-07 00:05:00   3.5    4    NaT
    2014-10-07 00:10:00   6.0    7  7.404
    2014-10-07 00:15:00   8.5    9    NaT

    In [134]: ts
    Out[134]:
    2014-10-07 00:00:00    0
    2014-10-07 00:02:00    1
    2014-10-07 00:04:00    2
    2014-10-07 00:06:00    3
    2014-10-07 00:08:00    4
    2014-10-07 00:10:00    5
    2014-10-07 00:12:00    6
    2014-10-07 00:14:00    7
    2014-10-07 00:16:00    8
    2014-10-07 00:18:00    9
    Freq: 2min, dtype: int64

Create a value counts column and reassign back to the DataFrame

    In [135]: df = pd.DataFrame(
       .....:     {"Color": "Red Red Red Blue".split(), "Value": [100, 150, 50, 50]}
       .....: )
       .....:

    In [136]: df
    Out[136]:
      Color  Value
    0   Red    100
    1   Red    150
    2   Red     50
    3  Blue     50

    In [137]: df["Counts"] = df.groupby(["Color"]).transform(len)

    In [138]: df
    Out[138]:
      Color  Value  Counts
    0   Red    100       3
    1   Red    150       3
    2   Red     50       3
    3  Blue     50       1

Shift groups of the values in a column based on the index

    In [139]: df = pd.DataFrame(
       .....:     {"line_race": [10, 10, 8, 10, 10, 8], "beyer": [99, 102, 103, 103, 88, 100]},
       .....:     index=[
       .....:         "Last Gunfighter",
       .....:         "Last Gunfighter",
       .....:         "Last Gunfighter",
       .....:         "Paynter",
       .....:         "Paynter",
       .....:         "Paynter",
       .....:     ],
       .....: )
       .....:

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

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

    In [142]: df
    Out[142]:
                     line_race  beyer  beyer_shifted
    Last Gunfighter         10     99            NaN
    Last Gunfighter         10    102           99.0
    Last Gunfighter          8    103          102.0
    Paynter                 10    103            NaN
    Paynter                 10     88          103.0
    Paynter                  8    100           88.0

Select row with maximum value from each group

    In [143]: df = pd.DataFrame(
       .....:     {
       .....:         "host": ["other", "other", "that", "this", "this"],
       .....:         "service": ["mail", "web", "mail", "mail", "web"],
       .....:         "no": [1, 2, 1, 2, 1],
       .....:     }
       .....: ).set_index(["host", "service"])
       .....:

    In [144]: mask = df.groupby(level=0).agg("idxmax")

    In [145]: df_count = df.loc[mask["no"]].reset_index()

    In [146]: df_count
    Out[146]:
        host service  no
    0  other     web   2
    1   that    mail   1
    2   this    mail   2

Grouping like Python’s itertools.groupby

    In [147]: df = pd.DataFrame([0, 1, 0, 1, 1, 1, 0, 1, 1], columns=["A"])

    In [148]: df["A"].groupby((df["A"] != df["A"].shift()).cumsum()).groups
    Out[148]: {1: [0], 2: [1], 3: [2], 4: [3, 4, 5], 5: [6], 6: [7, 8]}

    In [149]: df["A"].groupby((df["A"] != df["A"].shift()).cumsum()).cumsum()
    Out[149]:
    0    0
    1    1
    2    0
    3    1
    4    2
    5    3
    6    0
    7    1
    8    2
    Name: A, dtype: int64

### Expanding data

Alignment and to-date

Rolling Computation window based on values instead of counts

Rolling Mean by Time Interval

### Splitting

Splitting a frame

Create a list of dataframes, split using a delineation based on logic included in rows.

    In [150]: df = pd.DataFrame(
       .....:     data={
       .....:         "Case": ["A", "A", "A", "B", "A", "A", "B", "A", "A"],
       .....:         "Data": np.random.randn(9),
       .....:     }
       .....: )
       .....:

    In [151]: dfs = list(
       .....:     zip(
       .....:         *df.groupby(
       .....:             (1 * (df["Case"] == "B"))
       .....:             .cumsum()
       .....:             .rolling(window=3, min_periods=1)
       .....:             .median()
       .....:         )
       .....:     )
       .....: )[-1]
       .....:

    In [152]: dfs[0]
    Out[152]:
      Case      Data
    0    A -0.712964
    1    A -0.443160
    2    A -0.974602
    3    B  1.047704

    In [153]: dfs[1]
    Out[153]:
      Case      Data
    4    A -0.717852
    5    A -1.053898
    6    B -0.019369

    In [154]: dfs[2]
    Out[154]:
      Case      Data
    7    A  0.758887
    8    A  2.340598

### Pivot

The Pivot docs.

Partial sums and subtotals

    In [155]: df = pd.DataFrame(
       .....:     data={
       .....:         "Province": ["ON", "QC", "BC", "AL", "AL", "MN", "ON"],
       .....:         "City": [
       .....:             "Toronto",
       .....:             "Montreal",
       .....:             "Vancouver",
       .....:             "Calgary",
       .....:             "Edmonton",
       .....:             "Winnipeg",
       .....:             "Windsor",
       .....:         ],
       .....:         "Sales": [13, 6, 16, 8, 4, 3, 1],
       .....:     }
       .....: )
       .....:

    In [156]: table = pd.pivot_table(
       .....:     df,
       .....:     values=["Sales"],
       .....:     index=["Province"],
       .....:     columns=["City"],
       .....:     aggfunc="sum",
       .....:     margins=True,
       .....: )
       .....:

    In [157]: table.stack("City")
    Out[157]:
                        Sales
    Province City
    AL       Calgary      8.0
             Edmonton     4.0
             Montreal     NaN
             Toronto      NaN
             Vancouver    NaN
    ...                   ...
    All      Toronto     13.0
             Vancouver   16.0
             Windsor      1.0
             Winnipeg     3.0
             All         51.0

    [48 rows x 1 columns]

Frequency table like plyr in R

    In [158]: grades = [48, 99, 75, 80, 42, 80, 72, 68, 36, 78]

    In [159]: df = pd.DataFrame(
       .....:     {
       .....:         "ID": ["x%d" % r for r in range(10)],
       .....:         "Gender": ["F", "M", "F", "M", "F", "M", "F", "M", "M", "M"],
       .....:         "ExamYear": [
       .....:             "2007",
       .....:             "2007",
       .....:             "2007",
       .....:             "2008",
       .....:             "2008",
       .....:             "2008",
       .....:             "2008",
       .....:             "2009",
       .....:             "2009",
       .....:             "2009",
       .....:         ],
       .....:         "Class": [
       .....:             "algebra",
       .....:             "stats",
       .....:             "bio",
       .....:             "algebra",
       .....:             "algebra",
       .....:             "stats",
       .....:             "stats",
       .....:             "algebra",
       .....:             "bio",
       .....:             "bio",
       .....:         ],
       .....:         "Participated": [
       .....:             "yes",
       .....:             "yes",
       .....:             "yes",
       .....:             "yes",
       .....:             "no",
       .....:             "yes",
       .....:             "yes",
       .....:             "yes",
       .....:             "yes",
       .....:             "yes",
       .....:         ],
       .....:         "Passed": ["yes" if x > 50 else "no" for x in grades],
       .....:         "Employed": [
       .....:             True,
       .....:             True,
       .....:             True,
       .....:             False,
       .....:             False,
       .....:             False,
       .....:             False,
       .....:             True,
       .....:             True,
       .....:             False,
       .....:         ],
       .....:         "Grade": grades,
       .....:     }
       .....: )
       .....:

    In [160]: df.groupby("ExamYear").agg(
       .....:     {
       .....:         "Participated": lambda x: x.value_counts()["yes"],
       .....:         "Passed": lambda x: sum(x == "yes"),
       .....:         "Employed": lambda x: sum(x),
       .....:         "Grade": lambda x: sum(x) / len(x),
       .....:     }
       .....: )
       .....:
    Out[160]:
              Participated  Passed  Employed      Grade
    ExamYear
    2007                 3       2         3  74.000000
    2008                 3       3         0  68.500000
    2009                 3       2         2  60.666667

Plot pandas DataFrame with year over year data

To create year and month cross tabulation:

    In [161]: df = pd.DataFrame(
       .....:     {"value": np.random.randn(36)},
       .....:     index=pd.date_range("2011-01-01", freq="ME", periods=36),
       .....: )
       .....:

    In [162]: pd.pivot_table(
       .....:     df, index=df.index.month, columns=df.index.year, values="value", aggfunc="sum"
       .....: )
       .....:
    Out[162]:
            2011      2012      2013
    1   0.219039  1.053999 -0.121113
    2  -1.235583  0.651981 -0.258742
    3   0.031785  0.280668 -0.706329
    4   0.701683  0.642481  0.402547
    5  -1.557016  0.109001  1.171652
    6  -0.636986 -0.533294  1.387952
    7  -1.238610  0.243981  0.009025
    8   0.179286 -1.037831  0.613766
    9  -1.002278 -1.150016 -0.031653
    10  0.654052 -0.871461  0.543156
    11  1.150663 -0.687693 -0.983195
    12 -1.269741  1.921056 -1.247823

### Apply

Rolling apply to organize - Turning embedded lists into a MultiIndex frame

    In [163]: df = pd.DataFrame(
       .....:     data={
       .....:         "A": [[2, 4, 8, 16], [100, 200], [10, 20, 30]],
       .....:         "B": [["a", "b", "c"], ["jj", "kk"], ["ccc"]],
       .....:     },
       .....:     index=["I", "II", "III"],
       .....: )
       .....:

    In [164]: def SeriesFromSubList(aList):
       .....:     return pd.Series(aList)
       .....:

    In [165]: df_orgz = pd.concat(
       .....:     {ind: row.apply(SeriesFromSubList) for ind, row in df.iterrows()}
       .....: )
       .....:

    In [166]: df_orgz
    Out[166]:
             0     1     2     3
    I   A    2     4     8  16.0
        B    a     b     c   NaN
    II  A  100   200   NaN   NaN
        B   jj    kk   NaN   NaN
    III A   10  20.0  30.0   NaN
        B  ccc   NaN   NaN   NaN

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

    In [167]: df = pd.DataFrame(
       .....:     data=np.random.randn(2000, 2) / 10000,
       .....:     index=pd.date_range("2001-01-01", periods=2000),
       .....:     columns=["A", "B"],
       .....: )
       .....:

    In [168]: df
    Out[168]:
                       A         B
    2001-01-01  0.000171 -0.000047
    2001-01-02 -0.000059 -0.000025
    2001-01-03 -0.000055  0.000094
    2001-01-04 -0.000019 -0.000077
    2001-01-05  0.000018  0.000007
    ...              ...       ...
    2006-06-19  0.000027 -0.000117
    2006-06-20  0.000129 -0.000073
    2006-06-21  0.000061 -0.000188
    2006-06-22 -0.000005 -0.000152
    2006-06-23  0.000083 -0.000141

    [2000 rows x 2 columns]

    In [169]: def gm(df, const):
       .....:     v = ((((df["A"] + df["B"]) + 1).cumprod()) - 1) * const
       .....:     return v.iloc[-1]
       .....:

    In [170]: s = pd.Series(
       .....:     {
       .....:         df.index[i]: gm(df.iloc[i: min(i + 51, len(df) - 1)], 5)
       .....:         for i in range(len(df) - 50)
       .....:     }
       .....: )
       .....:

    In [171]: s
    Out[171]:
    2001-01-01    0.001861
    2001-01-02    0.001441
    2001-01-03    0.002351
    2001-01-04    0.000715
    2001-01-05    0.000781
                    ...
    2006-04-30    0.002773
    2006-05-01    0.002503
    2006-05-02    0.002038
    2006-05-03   -0.000297
    2006-05-04   -0.000995
    Length: 1950, dtype: float64

Rolling apply with a DataFrame returning a Scalar

Rolling Apply to multiple columns where function returns a Scalar (Volume Weighted Average Price)

    In [172]: rng = pd.date_range(start="2014-01-01", periods=100)

    In [173]: df = pd.DataFrame(
       .....:     {
       .....:         "Open": np.random.randn(len(rng)),
       .....:         "Close": np.random.randn(len(rng)),
       .....:         "Volume": np.random.randint(100, 2000, len(rng)),
       .....:     },
       .....:     index=rng,
       .....: )
       .....:

    In [174]: df
    Out[174]:
                    Open     Close  Volume
    2014-01-01  2.219966 -1.267996    1575
    2014-01-02  0.992552  0.014528    1776
    2014-01-03 -0.010015  0.705241    1773
    2014-01-04 -1.178787 -0.281064     267
    2014-01-05  0.033486 -0.696814    1580
    ...              ...       ...     ...
    2014-04-06  0.512575  1.507745     194
    2014-04-07  1.522977 -0.645314    1943
    2014-04-08 -1.484474 -2.004731     951
    2014-04-09  1.397273 -0.283128    1262
    2014-04-10 -1.003720  1.057274    1596

    [100 rows x 3 columns]

    In [175]: def vwap(bars):
       .....:     return (bars.Close * bars.Volume).sum() / bars.Volume.sum()
       .....:

    In [176]: window = 5

    In [177]: s = pd.concat(
       .....:     [
       .....:         (pd.Series(vwap(df.iloc[i: i + window]), index=[df.index[i + window]]))
       .....:         for i in range(len(df) - window)
       .....:     ]
       .....: )
       .....:

    In [178]: s.round(2)
    Out[178]:
    2014-01-06   -0.27
    2014-01-07    0.06
    2014-01-08    0.05
    2014-01-09   -0.22
    2014-01-10   -0.07
                  ...
    2014-04-06   -0.48
    2014-04-07   -0.44
    2014-04-08   -0.15
    2014-04-09   -0.98
    2014-04-10   -0.64
    Length: 95, dtype: float64

## Timeseries

### Time-distance weighted rolling aggregations

A decay-weighted aggregation can be applied inside a time-based rolling window. The weight depends on how far each observation is from the most recent timestamp in the window. This is useful when more recent values should contribute more to rolling statistics than older ones, particularly where observations are not evenly spaced in time.

This approach relies on `rolling.apply` and recomputes weights for each window, so it may be slow for large datasets.

    df = pd.DataFrame(
        {
            "timestamp": [
                "2024-01-01 01:00",
                "2024-01-01 02:00",
                "2024-01-01 05:00",
                "2024-01-01 05:00",
                "2024-01-01 07:00",
                "2024-01-01 08:00",
            ],
            "value": [12, 13, 20, 26, 24, 27],
        }
    )

    df["timestamp"] = pd.to_datetime(df["timestamp"])
    df = df.set_index("timestamp")

    def decay_weighted_mean(values, alpha=0.1):
        timestamps = values.index
        age_hours = (timestamps.max() - timestamps).total_seconds() / 3600
        weights = np.exp(-alpha * age_hours)
        return (weights * values).sum() / weights.sum()

    result = (
        df["value"]
        .rolling("7h")
        .apply(decay_weighted_mean)
    )

Between times

Using indexer between time

Constructing a datetime range that excludes weekends and includes only certain times

Vectorized Lookup

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

    In [179]: dates = pd.date_range("2000-01-01", periods=5)

    In [180]: dates.to_period(freq="M").to_timestamp()
    Out[180]:
    DatetimeIndex(['2000-01-01', '2000-01-01', '2000-01-01', '2000-01-01',
                   '2000-01-01'],
                  dtype='datetime64[us]', freq=None)

### 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 Timeseries

Grouping using a MultiIndex

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

Resampling with custom periods

Resample intraday frame without adding new days

Resample minute data

Resample with groupby

## Merge

The Join docs.

Concatenate two dataframes with overlapping index (emulate R rbind)

    In [181]: rng = pd.date_range("2000-01-01", periods=6)

    In [182]: df1 = pd.DataFrame(np.random.randn(6, 3), index=rng, columns=["A", "B", "C"])

    In [183]: df2 = df1.copy()

Depending on df construction, `ignore_index` may be needed

    In [184]: df = pd.concat([df1, df2], ignore_index=True)

    In [185]: df
    Out[185]:
               A         B         C
    0  -0.758783  0.030403 -1.690042
    1   0.120074  0.951482 -0.141065
    2   0.328092  0.021672 -0.352088
    3  -0.444963 -0.057350  0.890862
    4   0.279513  0.852785 -0.853224
    5   1.682849 -0.889863  0.463793
    6  -0.758783  0.030403 -1.690042
    7   0.120074  0.951482 -0.141065
    8   0.328092  0.021672 -0.352088
    9  -0.444963 -0.057350  0.890862
    10  0.279513  0.852785 -0.853224
    11  1.682849 -0.889863  0.463793

Self Join of a DataFrame GH 2996

    In [186]: df = pd.DataFrame(
       .....:     data={
       .....:         "Area": ["A"] * 5 + ["C"] * 2,
       .....:         "Bins": [110] * 2 + [160] * 3 + [40] * 2,
       .....:         "Test_0": [0, 1, 0, 1, 2, 0, 1],
       .....:         "Data": np.random.randn(7),
       .....:     }
       .....: )
       .....:

    In [187]: df
    Out[187]:
      Area  Bins  Test_0      Data
    0    A   110       0 -1.533246
    1    A   110       1  1.406591
    2    A   160       0 -1.175524
    3    A   160       1 -1.905383
    4    A   160       2 -0.197435
    5    C    40       0 -0.831425
    6    C    40       1 -0.138160

    In [188]: df["Test_1"] = df["Test_0"] - 1

    In [189]: pd.merge(
       .....:     df,
       .....:     df,
       .....:     left_on=["Bins", "Area", "Test_0"],
       .....:     right_on=["Bins", "Area", "Test_1"],
       .....:     suffixes=("_L", "_R"),
       .....: )
       .....:
    Out[189]:
      Area  Bins  Test_0_L    Data_L  Test_1_L  Test_0_R    Data_R  Test_1_R
    0    A   110         0 -1.533246        -1         1  1.406591         0
    1    A   160         0 -1.175524        -1         1 -1.905383         0
    2    A   160         1 -1.905383         0         2 -0.197435         1
    3    C    40         0 -0.831425        -1         1 -0.138160         0

How to set the index and join

KDB like asof join

Join with a criteria based on the values

Using searchsorted to merge based on values inside a range

## Plotting

The Plotting docs.

Make Matplotlib look like R

Setting x-axis major and minor labels

Plotting multiple charts in an IPython Jupyter 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 [190]: df = pd.DataFrame(
       .....:     {
       .....:         "stratifying_var": np.random.uniform(0, 100, 20),
       .....:         "price": np.random.normal(100, 5, 20),
       .....:     }
       .....: )
       .....:

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

    In [192]: df.boxplot(column="price", by="quartiles")
    Out[192]: <Axes: title={'center': 'price'}, xlabel='quartiles'>

## 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 GH 2886

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()`:

    In [193]: for i in range(3):
       .....:     data = pd.DataFrame(np.random.randn(10, 4))
       .....:     data.to_csv("file_{}.csv".format(i))
       .....:

    In [194]: files = ["file_0.csv", "file_1.csv", "file_2.csv"]

    In [195]: result = pd.concat([pd.read_csv(f) for f in files], ignore_index=True)

You can use the same approach to read all files matching a pattern. Here is an example using `glob`:

    In [196]: import glob

    In [197]: import os

    In [198]: files = glob.glob("file_*.csv")

    In [199]: result = pd.concat([pd.read_csv(f) for f in files], ignore_index=True)

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

    In [200]: i = pd.date_range("20000101", periods=10000)

    In [201]: df = pd.DataFrame({"year": i.year, "month": i.month, "day": i.day})

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

    In [203]: %timeit pd.to_datetime(df.year * 10000 + df.month * 100 + df.day, format='%Y%m%d')
       .....: ds = df.apply(lambda x: "%04d%02d%02d" % (x["year"], x["month"], x["day"]), axis=1)
       .....: ds.head()
       .....: %timeit pd.to_datetime(ds)
       .....:
    3.82 ms +- 1.03 ms per loop (mean +- std. dev. of 7 runs, 100 loops each)
    2.13 ms +- 408 us per loop (mean +- std. dev. of 7 runs, 1,000 loops each)

#### Skip row between header and data

    In [204]: data = """;;;;
       .....:  ;;;;
       .....:  ;;;;
       .....:  ;;;;
       .....:  ;;;;
       .....:  ;;;;
       .....: ;;;;
       .....:  ;;;;
       .....:  ;;;;
       .....: ;;;;
       .....: date;Param1;Param2;Param4;Param5
       .....:     ;m²;°C;m²;m
       .....: ;;;;
       .....: 01.01.1990 00:00;1;1;2;3
       .....: 01.01.1990 01:00;5;3;4;5
       .....: 01.01.1990 02:00;9;5;6;7
       .....: 01.01.1990 03:00;13;7;8;9
       .....: 01.01.1990 04:00;17;9;10;11
       .....: 01.01.1990 05:00;21;11;12;13
       .....: """
       .....:

##### Option 1: pass rows explicitly to skip rows

    In [205]: from io import StringIO

    In [206]: pd.read_csv(
       .....:     StringIO(data),
       .....:     sep=";",
       .....:     skiprows=[11, 12],
       .....:     index_col=0,
       .....:     parse_dates=True,
       .....:     header=10,
       .....: )
       .....:
    Out[206]:
                         Param1  Param2  Param4  Param5
    date
    1990-01-01 00:00:00       1       1       2       3
    1990-01-01 01:00:00       5       3       4       5
    1990-01-01 02:00:00       9       5       6       7
    1990-01-01 03:00:00      13       7       8       9
    1990-01-01 04:00:00      17       9      10      11
    1990-01-01 05:00:00      21      11      12      13

##### Option 2: read column names and then data

    In [207]: pd.read_csv(StringIO(data), sep=";", header=10, nrows=10).columns
    Out[207]: Index(['date', 'Param1', 'Param2', 'Param4', 'Param5'], dtype='str')

    In [208]: columns = pd.read_csv(StringIO(data), sep=";", header=10, nrows=10).columns

    In [209]: pd.read_csv(
       .....:     StringIO(data), sep=";", index_col=0, header=12, parse_dates=True, names=columns
       .....: )
       .....:
    Out[209]:
                         Param1  Param2  Param4  Param5
    date
    1990-01-01 00:00:00       1       1       2       3
    1990-01-01 01:00:00       5       3       4       5
    1990-01-01 02:00:00       9       5       6       7
    1990-01-01 03:00:00      13       7       8       9
    1990-01-01 04:00:00      17       9      10      11
    1990-01-01 05:00:00      21      11      12      13

### SQL

The SQL docs

Reading from databases with SQL

### Excel

The Excel docs

Reading from a filelike handle

Modifying formatting in XlsxWriter output

Loading only visible sheets GH 19842#issuecomment-892150745

### 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 GH 3032

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

Large Data work flows

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

Groupby on an HDFStore with low group density

Groupby on an HDFStore with high group density

Hierarchical queries on an HDFStore

Counting with an 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 [210]: df = pd.DataFrame(np.random.randn(8, 3))

    In [211]: store = pd.HDFStore("test.h5")

    In [212]: store.put("df", df)

    # you can store an arbitrary Python object via pickle
    In [213]: store.get_storer("df").attrs.my_attribute = {"A": 10}

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

You can create or load an HDFStore in-memory by passing the `driver` parameter to PyTables. Changes are only written to disk when the HDFStore is closed.

    In [215]: store = pd.HDFStore("test.h5", "w", driver="H5FD_CORE")

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

    In [217]: store["test"] = df

    # only after closing the store, data is written to disk:
    In [218]: store.close()

### 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:

    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 = pd.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 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:

    In [219]: df = pd.DataFrame(np.random.random(size=(100, 5)))

    In [220]: corr_mat = df.corr()

    In [221]: mask = np.tril(np.ones_like(corr_mat, dtype=np.bool_), k=-1)

    In [222]: corr_mat.where(mask)
    Out[222]:
              0         1         2        3   4
    0       NaN       NaN       NaN      NaN NaN
    1 -0.002635       NaN       NaN      NaN NaN
    2  0.066117 -0.038354       NaN      NaN NaN
    3  0.047939 -0.045638  0.090354      NaN NaN
    4 -0.149592 -0.029750 -0.147658 -0.09218 NaN

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.

    In [223]: def distcorr(x, y):
       .....:     n = len(x)
       .....:     a = np.zeros(shape=(n, n))
       .....:     b = np.zeros(shape=(n, n))
       .....:     for i in range(n):
       .....:         for j in range(i + 1, n):
       .....:             a[i, j] = abs(x[i] - x[j])
       .....:             b[i, j] = abs(y[i] - y[j])
       .....:     a += a.T
       .....:     b += b.T
       .....:     a_bar = np.vstack([np.nanmean(a, axis=0)] * n)
       .....:     b_bar = np.vstack([np.nanmean(b, axis=0)] * n)
       .....:     A = a - a_bar - a_bar.T + np.full(shape=(n, n), fill_value=a_bar.mean())
       .....:     B = b - b_bar - b_bar.T + np.full(shape=(n, n), fill_value=b_bar.mean())
       .....:     cov_ab = np.sqrt(np.nansum(A * B)) / n
       .....:     std_a = np.sqrt(np.sqrt(np.nansum(A ** 2)) / n)
       .....:     std_b = np.sqrt(np.sqrt(np.nansum(B ** 2)) / n)
       .....:     return cov_ab / std_a / std_b
       .....:

    In [224]: df = pd.DataFrame(np.random.normal(size=(100, 3)))

    In [225]: df.corr(method=distcorr)
    Out[225]:
              0         1         2
    0  1.000000  0.210138  0.141308
    1  0.210138  1.000000  0.152878
    2  0.141308  0.152878  1.000000

## Timedeltas

The Timedeltas docs.

Using timedeltas

    In [226]: import datetime

    In [227]: s = pd.Series(pd.date_range("2012-1-1", periods=3, freq="D"))

    In [228]: s - s.max()
    Out[228]:
    0   -2 days
    1   -1 days
    2    0 days
    dtype: timedelta64[us]

    In [229]: s.max() - s
    Out[229]:
    0   2 days
    1   1 days
    2   0 days
    dtype: timedelta64[us]

    In [230]: s - datetime.datetime(2011, 1, 1, 3, 5)
    Out[230]:
    0   364 days 20:55:00
    1   365 days 20:55:00
    2   366 days 20:55:00
    dtype: timedelta64[us]

    In [231]: s + datetime.timedelta(minutes=5)
    Out[231]:
    0   2012-01-01 00:05:00
    1   2012-01-02 00:05:00
    2   2012-01-03 00:05:00
    dtype: datetime64[us]

    In [232]: datetime.datetime(2011, 1, 1, 3, 5) - s
    Out[232]:
    0   -365 days +03:05:00
    1   -366 days +03:05:00
    2   -367 days +03:05:00
    dtype: timedelta64[us]

    In [233]: datetime.timedelta(minutes=5) + s
    Out[233]:
    0   2012-01-01 00:05:00
    1   2012-01-02 00:05:00
    2   2012-01-03 00:05:00
    dtype: datetime64[us]

Adding and subtracting deltas and dates

    In [234]: deltas = pd.Series([datetime.timedelta(days=i) for i in range(3)])

    In [235]: df = pd.DataFrame({"A": s, "B": deltas})

    In [236]: df
    Out[236]:
               A      B
    0 2012-01-01 0 days
    1 2012-01-02 1 days
    2 2012-01-03 2 days

    In [237]: df["New Dates"] = df["A"] + df["B"]

    In [238]: df["Delta"] = df["A"] - df["New Dates"]

    In [239]: df
    Out[239]:
               A      B  New Dates   Delta
    0 2012-01-01 0 days 2012-01-01  0 days
    1 2012-01-02 1 days 2012-01-03 -1 days
    2 2012-01-03 2 days 2012-01-05 -2 days

    In [240]: df.dtypes
    Out[240]:
    A             datetime64[us]
    B            timedelta64[us]
    New Dates     datetime64[us]
    Delta        timedelta64[us]
    dtype: object

Another example

Values can be set to NaT using np.nan, similar to datetime

    In [241]: y = s - s.shift()

    In [242]: y
    Out[242]:
    0      NaT
    1   1 days
    2   1 days
    dtype: timedelta64[us]

    In [243]: y[1] = np.nan

    In [244]: y
    Out[244]:
    0      NaT
    1      NaT
    2   1 days
    dtype: timedelta64[us]

## 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:

    In [245]: def expand_grid(data_dict):
       .....:     rows = itertools.product(*data_dict.values())
       .....:     return pd.DataFrame.from_records(rows, columns=data_dict.keys())
       .....:

    In [246]: df = expand_grid(
       .....:     {"height": [60, 70], "weight": [100, 140, 180], "sex": ["Male", "Female"]}
       .....: )
       .....:

    In [247]: df
    Out[247]:
        height  weight     sex
    0       60     100    Male
    1       60     100  Female
    2       60     140    Male
    3       60     140  Female
    4       60     180    Male
    5       60     180  Female
    6       70     100    Male
    7       70     100  Female
    8       70     140    Male
    9       70     140  Female
    10      70     180    Male
    11      70     180  Female

## Constant Series

To assess if a series has a constant value, we can check if `series.nunique() <= 1`. However, a more performant approach, that does not count all unique values first, is:

    In [248]: v = s.to_numpy()

    In [249]: is_constant = v.shape[0] == 0 or (s[0] == s).all()

This approach assumes that the series does not contain missing values. For the case that we would drop NA values, we can simply remove those values first:

    In [250]: v = s.dropna().to_numpy()

    In [251]: is_constant = v.shape[0] == 0 or (s[0] == s).all()

If missing values are considered distinct from any other value, then one could use:

    In [252]: v = s.to_numpy()

    In [253]: is_constant = v.shape[0] == 0 or (s[0] == s).all() or not pd.notna(v).any()

(Note that this example does not disambiguate between `np.nan`, `pd.NA` and `None`)