23

I have 2 DataFrames which I would like to merge. I have looked at the documentation and tried to perform the following operation but an getting confused as to how to do it. Like I said I have 2 DataFrames:

df1:

      id        name  type currency
0  BTA.S   Applewood  Hard      GBp
1  VOD.S    Softwood  Soft      GBp

and 

df2:

   id
BTA.S    301.221525
VOD.S    213.791400

and I would like to return:

      id        name  type currency       price
0  BTA.S   Applewood  Hard      GBp  301.221525
1  VOD.S    Softwood  Soft      GBp  213.791400

Where the price column from the df2 is merged with df1. (Just to let you know there will be alot more wood types by the time I've finished).

I have tried a few methods of doing this:

Result = df1.merge(df2[['*.S']], left_on='id', right_index=True)

where I met the exception:

ValueError: can not merge DataFrame with instance of type <class 'pandas.core.series.Series'>

and 

Result = pd.concat([Df1, Df2], axis=1, ignore_index=True)

where I get the exception:

ValueError: labels ['type'] not contained in axis

But I am getting confused.

halfer
  • 19,824
  • 17
  • 99
  • 186
Stacey
  • 4,825
  • 17
  • 58
  • 99

4 Answers4

44

The error message indicates that df2 is of type pd.Series. You need to convert df2 .to_frame() as .merge() needs a pd.DataFrame() input (see docs):

df1.merge(df2[['*.S']].to_frame(), left_on='id', right_index=True)

while you probably also just could:

df1.merge(df2.to_frame(), left_on='id', right_index=True)

Alternatively, you can use pd.DataFrame.join() which accepts a pd.Series.

Stefan
  • 41,759
  • 13
  • 76
  • 81
  • One may have to transform the frame to match the column. For e.g. `a1 = [['10', '1.2', '4.2'], ['15', '70', '0.03'], ['8', '5', '0']]` `a2 = [['10', '1.2', '4.2'], ['1', '0', '0.06'], ['6', '4', '3']]` `df1 = pd.DataFrame(a1, columns=['one', 'two', 'three'])` `df2 = pd.DataFrame(a2, columns=['one', 'two', 'three'])` `pd.merge(df1, df2.loc[0].to_frame().T, on=['one', 'one'], how='inner')` – Pramit Jan 29 '19 at 08:58
6

This error means that one of your objects is not a pandas Data Frame.

ValueError: can not merge DataFrame with instance of type <class 'pandas.core.series.Series'>

To prove this to yourself,

print(type(df2))

And that should output pandas.core.series.Series

To achieve your desired result,

df2 = df2.to_frame().reset_index()
df2.columns = ['id', 'price']
df1.merge(df2)

Outputs:

    id  name    type    currency    price
0   BTA.S   Applewood   Hard    GBp     301.221525
1   VOD.S   Softwood    Soft    GBp     213.791400
michael_j_ward
  • 4,369
  • 1
  • 24
  • 25
2

You can simply add df2 (which is a Series, not a DataFrame) as a new column

df['price']=df2
Alex Monras
  • 198
  • 2
  • 10
0

use to_frame() or updata your pandas;

join Series with Dataframe is accepted in new pandas version

wa007
  • 105
  • 8