Pandas: trouble understanding how merge works

pandas, python

Solution

From docs:

on: Columns (names) to join on. Must be found in both the left and right DataFrame objects. If not passed and left_index and right_index are False, the intersection of the columns in the DataFrames will be inferred to be the join keys

I don't know why this is not in the docstring, but it explains your problem.

You can either give `left_index` and `right_index`:

In : pnd.merge(freq, hist, right_index=True, left_index=True)
Out:
        freq  count
series
0       0.01      1
1       0.04      4
2       0.14     14
3       0.12     12
4       0.21     21
5       0.14     14
6       0.17     17
7       0.07      7
8       0.05      5
9       0.01      1
10      0.01      1
11      0.03      3

Or you can make your index a column and use `on`:

In : freq2 = freq.reset_index()

In : hist2 = hist.reset_index()

In : pnd.merge(freq2, hist2, on='series')
Out:
    series  freq  count
0        0  0.01      1
1        1  0.04      4
2        2  0.14     14
3        3  0.12     12
4        4  0.21     21
5        5  0.14     14
6        6  0.17     17
7        7  0.07      7
8        8  0.05      5
9        9  0.01      1
10      10  0.01      1
11      11  0.03      3

Alternatively and more simply, `DataFrame` has `join` method which does exactly what you want:

In : freq.join(hist)
Out:
        freq  count
series
0       0.01      1
1       0.04      4
2       0.14     14
3       0.12     12
4       0.21     21
5       0.14     14
6       0.17     17
7       0.07      7
8       0.05      5
9       0.01      1
10      0.01      1
11      0.03      3

Problem

I'm doing something wrong with merge and I can't understand what it is. I've done the following to estimate a histogram of a series of integer values: ``` import pandas as pnd import numpy as np series = pnd.Series(np.random.poisson(5, size = 100)) tmp = {"series" : series, "count" : np.ones(len(series))} hist = pnd.DataFrame(tmp).groupby("series").sum() freq = (hist / hist.sum()).rename(columns = {"count" : "freq"}) ``` If I print `hist` and `freq` this is what I get: ``` > print hist count series 0 2 1 4 2 13 3 15 4 12 5 16 6 18 7 7 8 8 9 3 10 1 11 1 > print freq freq series 0 0.02 1 0.04 2 0.13 3 0.15 4 0.12 5 0.16 6 0.18 7 0.07 8 0.08 9 0.03 10 0.01 11 0.01 ``` They're both indexed by `"series"` but if I try to merge: ``` > df = pnd.merge(freq, hist, on = "series") ``` I get a `KeyError: 'no item named series'` exception. If I omit `on = "series"` I get a `IndexError: list index out of range` exception. I don't get what I'm doing wrong. May be "series" is an index and not a column so I must do it differently?

Original source