I have a first dataFrame which looks like this

              value  origin
id
Loss_2000_0  611264    2000
Loss_2000_1  644563    2000
Loss_2000_2  555245    2000
Loss_2000_3  572451    2000
Loss_2001_0  496761    2001
...             ...     ...
Loss_2014_1   68247    2014
Loss_2014_2   33820    2014
Loss_2014_3   63882    2014
Loss_2014_4   39578    2014
Loss_2014_5   51357    2014

And I have a second dataFrame

           LDF
ult   1.273796
2000  1.045484
2001  1.066983
2002  1.094516
2003  1.068604
2004  1.112227
2005  1.098801
2006  1.093077
2007  1.123588
2008  1.135084
2009  1.182663
2010  1.202442
2011  1.229210
2012  1.355516
2013  1.386031
2014  2.122421

I would like to create a third dataFrame similar to the first, but in which the value is multiplied by 2.122421 if origin is 2014, by 1.386031 if origin is 2013, etc.

CodePudding user response：

Try this:

import pandas as pd
data1 = [
    ("Loss_2000_0",611264,2000),
    ("Loss_2000_1",644563,2000),
    ("Loss_2000_2",555245,2000),
    ("Loss_2000_3",572451,2000),
    ("Loss_2001_0",496761,2001),
    ("Loss_2014_1", 68247,2014),
    ("Loss_2014_2", 33820,2014),
    ("Loss_2014_3", 63882,2014),
    ("Loss_2014_4", 39578,2014),
    ("Loss_2014_5", 51357,2014)
]

data2 = [
    (2000, 1.273796),
    (2001, 1.045484),
    (2002, 1.066983),
    (2003, 1.094516),
    (2004, 1.068604),
    (2005, 1.112227),
    (2006, 1.098801),
    (2007, 1.093077),
    (2008, 1.123588),
    (2009, 1.135084),
    (2010, 1.182663),
    (2011, 1.202442),
    (2012, 1.229210),
    (2013, 1.355516),
    (2014, 1.386031),
]
df1 = pd.DataFrame(data1, columns=["id", "value", "origin"])
df2 = pd.DataFrame(data2, columns=["ult", "LDF"])
df3 = pd.merge(df1, df2, left_on='origin', right_on='ult')
df3['loss'] = df3['LDF'] * df3['value'] 
df3

Output

CodePudding user response：

Given the index your using and stealing the data from Hussain Bohra you can adapt Quang Hoang to your needs.

Data

import pandas as pd

data1 = [
    ("Loss_2000_0",611264,2000),
    ("Loss_2000_1",644563,2000),
    ("Loss_2000_2",555245,2000),
    ("Loss_2000_3",572451,2000),
    ("Loss_2001_0",496761,2001),
    ("Loss_2014_1", 68247,2014),
    ("Loss_2014_2", 33820,2014),
    ("Loss_2014_3", 63882,2014),
    ("Loss_2014_4", 39578,2014),
    ("Loss_2014_5", 51357,2014)
]

data2 = [
    (2000, 1.273796),
    (2001, 1.045484),
    (2002, 1.066983),
    (2003, 1.094516),
    (2004, 1.068604),
    (2005, 1.112227),
    (2006, 1.098801),
    (2007, 1.093077),
    (2008, 1.123588),
    (2009, 1.135084),
    (2010, 1.182663),
    (2011, 1.202442),
    (2012, 1.229210),
    (2013, 1.355516),
    (2014, 1.386031),
]
df1 = pd.DataFrame(data1, 
                   columns=["id", "value", "origin"])\
        .set_index("id") # has you want id as index
df2 = pd.DataFrame(data2, columns=["ult", "LDF"])

Now you want a new df from df1 let's call it df3 and we can now multiply

df3 = df1.copy()
df3["value"] = df3["origin"].map(df2.set_index("ult")["LDF"]) * df3["value"]

What we are doing with map is basically create a dictionary with year as key and LDF as value.