89

I'm trying to inner join DataFrame A to DataFrame B and am running into an error.

Here's my join statement:

merged = DataFrameA.join(DataFrameB, on=['Code','Date'])

And here's the error:

ValueError: len(left_on) must equal the number of levels in the index of "right"

I'm not sure the column order matters (they aren't truly "ordered" are they?), but just in case, the DataFrames are organized like this:

DataFrameA:  Code, Date, ColA, ColB, ColC, ..., ColG, ColH (shape: 80514, 8 - no index)
DataFrameB:  Date, Code, Col1, Col2, Col3, ..., Col15, Col16 (shape: 859, 16 - no index)

Do I need to correct my join statement? Or is there another, better way to get the intersection (or inner join) of these two DataFrames?

smci
  • 32,567
  • 20
  • 113
  • 146
Ian Joyce
  • 1,039
  • 1
  • 7
  • 12
  • 1
    Just something to point out, technically all dfs and series and panels for that matter will have an index, it may not be one you've set but there is always one, probably int64 starting from 0. – EdChum Jan 30 '15 at 09:13
  • totally right. I just wasn't sure how to say that succinctly. Standard index? Default index? – Ian Joyce Jan 30 '15 at 15:52

2 Answers2

143

use merge if you are not joining on the index:

merged = pd.merge(DataFrameA,DataFrameB, on=['Code','Date'])

Follow up to question below:

Here is a reproducible example:

import pandas as pd
# create some timestamps for date column
i = pd.to_datetime(pd.date_range('20140601',periods=2))

#create two dataframes to merge
df = pd.DataFrame({'code': ['ABC','EFG'], 'date':i,'col1': [10,100]})
df2 = pd.DataFrame({'code': ['ABC','EFG'], 'date':i,'col2': [10,200]})

#merge on columns (default join is inner)
pd.merge(df, df2, on =['code','date'])

This results is:

    code    col1    date    col2
0   ABC     10      2014-06-01  10
1   EFG     100     2014-06-02  200

What happens when you run this code?

JAB
  • 12,401
  • 6
  • 45
  • 50
26

Here is another way of performing join. Unlike the answer verified, this is a more general answer applicable to all other types of join.

Inner Join

inner join can also be performed by explicitly mentioning it as follows in how:

pd.merge(df1, df2, on='filename', how='inner')

The same methodology aplies for the other types of join:

OuterJoin

pd.merge(df1, df2, on='filename', how='outer')

Left Join

pd.merge(df1, df2, on='filename', how='left')

Right Join

pd.merge(df1, df2, on='filename', how='right')
Jeru Luke
  • 20,118
  • 13
  • 80
  • 87