Pandas: Merge dataframe and series based on index

Question

I created following dataframe priceearning_byyear, which is still incomplete:

Afterwards, I would like to "insert"/merge following series price into the above df:

The serie has one row (2021) too much, which I would like to remove when inserting into the above df.

My attempt is:

priceearning_byyear.merge(price.to_frame(), left_index=True)

But I got following error:

MergeError: Must pass right_on or right_index=True

If I use right_index=True, I'll get :

MergeError: Must pass left_on or left_index=True

I don't get it, why it doesn't work. Thank you for any pointer:-)

this post may help stackoverflow.com/a/23660378/13273054

kiranr
– kiranr

2021-03-21 19:56:12 +00:00
Commented Mar 21, 2021 at 19:56 — kiranr
– kiranr, Commented Mar 21, 2021 at 19:56

Fei Yao · Accepted Answer · 2021-03-21 22:04:45Z

3

You are almost there. Just pass left_index and right_index simultaneously to get the desired effect as below.

# Reproduce your data
import pandas as pd
priceearning_byyear = pd.DataFrame(dict(year=[2016,2017,2018,2019,2020], eps=[2.09,2.32,3.00,2.99,3.31])).set_index('year')
price = pd.Series([28.95,42.31,39.44,73.41,132.69,119.99], index=[2016,2017,2018,2019,2020,2021])
price.name = 'Close'
price.index.name='year'

# Merge priceearning_byyear and price by using their indexes
priceearning_byyear.merge(price,left_index=True,right_index=True)

answered Mar 21, 2021 at 22:04

Fei Yao

1,78213 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Clare-Anne Modestflower · Accepted Answer · 2021-03-21 20:37:47Z

1

You need to specify the column which you want to merge on in priceearning_byyear.

data1 = {"year":[x for x in range(2016, 2020)], "eps":[x for x in range(2,12,2)]}
data2 = pd.Series(data = {2016: 1, 2017: 2, 2018: 3, 2019: 4, 2020: 5, 2021: 6})

df1 = pd.DataFrame(data1)
df1.merge(data2.to_frame(name='price'), how='left', left_on="year", right_index=True)

result

answered Mar 21, 2021 at 20:37

Clare-Anne Modestflower

263 bronze badges

Comments

GSA · Accepted Answer · 2022-06-11 09:20:21Z

0

Another example I run into recently. The index of my dataframe was datetime, and my series was integers. What I did was write a small function to merge the dataframe and series for me in the following steps:

first, by converting to the series to a dataframe, and setting its index using the dataframe's index, and
second, merging the two dataframes and renaming the series' column.

Note: The function assumes that the dataframe and series have the same length.

def merge_df_series(x, y):
    """
    Merge dataframe and series on index
    and rename series column.
    """
    series = y.to_frame().set_index(x.index)
    return x.merge(series, left_index=True, right_index=True).rename(columns={0: 'y'})

edited Jun 11, 2022 at 9:20

answered Jun 11, 2022 at 9:04

GSA

8451 gold badge9 silver badges17 bronze badges

Collectives™ on Stack Overflow

Pandas: Merge dataframe and series based on index

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related