CODEWITHSUNDEEP
pythonpandasdataframe
Chapo
Chapo

Reputation: 2543

pandas join on multi index with criteria on one level

I have df1 indexed with 3 levels and df2 indexed with the same 3 levels. Let's assume date,name,criteria are the 3 columns by which those 2 dataframes are indexed.

I would like to "join" df2 to df1 such that for each level of index a_date,a_name,a_criteria in df1, the values that are joined from df2 match a_date,a_name,specific_criteria where specific_criteria is a constant value.

I end up doing something like the below and was wondering if there was a more elegant way ?

new_df1= pd.DataFrame()
for group_name, group_df in df1.groupby(level=[0, 1]):
    index_level = group_name + ("0M","field_v1",)
    group_df["bla2"] = df2.loc[index_level, "bla2"]
    new_df1 = pd.concat([new_df1, group_df])

Sample data :

df1:

date    asset   tenor   bla value
1/3/2005    AEX 12M 100 0.155346154
1/3/2005    AEX 12M 102.5   0.150401253
1/3/2005    AEX 12M 105 0.142983901
1/3/2005    AEX 12M 107.5   0.137154123
1/3/2005    AEX 12M 110 0.133267604
1/3/2005    AEX 6M  112.5   0.130425064
1/3/2005    AEX 6M  115 0.126161253
1/3/2005    AEX 6M  117.5   0.12344318
1/3/2005    AEX 6M  120 0.121631132
1/3/2005    AEX 6M  80  0.210254154

df2:

tenor   field   bla2    date
0M  field_v1    351.91  1/3/2005
0M  field_v2    351.91  1/3/2005
0M  field_v3    347.7   1/3/2005

I would like to obtain the following result_df1 (bla2 value (351.91) for (1/3/2005,AEX,0M,field_v1") in df2 for all (1/3/2005,AEX) lines in df1 :

date    asset   tenor   bla value   bla2
1/3/2005    AEX 12M 100 0.155346154 351.91
1/3/2005    AEX 12M 102.5   0.150401253 351.91
1/3/2005    AEX 12M 105 0.142983901 351.91
1/3/2005    AEX 12M 107.5   0.137154123 351.91
1/3/2005    AEX 12M 110 0.133267604 351.91
1/3/2005    AEX 6M  112.5   0.130425064 351.91
1/3/2005    AEX 6M  115 0.126161253 351.91
1/3/2005    AEX 6M  117.5   0.12344318  351.91
1/3/2005    AEX 6M  120 0.121631132 351.91
1/3/2005    AEX 6M  80  0.210254154 351.91

Upvotes: 1

Views: 83

Answers (2)

jezrael
jezrael

Reputation: 863226

For me working filter by DataFrame.xs and then DataFrame.join:

df22 = df2.xs(('0M','field_v1'), level=[2,3])
new_df1 = df1.join(df22)

Upvotes: 1

Chapo
Chapo

Reputation: 2543

In case it's useful to someone, you can in fact do

df1["tenor2"] = "0M"
df1["field"] = "field_v1"
df1.set_index(["date","tenor2","field"]
df2.set_index(["date","tenor","field"]
df1.join(df2)

EDIT : better solution by @jezrael

Upvotes: 0

Related Questions