Pandas - Get Values From Multindex Columns

I have the following dataframe df: H,Nu,City,Code,Code2 0.965392,15,Madrid,es,es 0.920614,15,Madrid,it,es 0.726219,16,Madrid,tn,es 0.739119,17,Madrid,fr,es 0.789923,55,Dublin,mt,en

Solution 1:

You can groupby on 'City' and 'Code2', call first on this and reset the index resulting in the following:

In [172]:
gp = df.groupby(['City','Code2'])['H'].first().reset_index()
gp

Out[172]:
     City Code2         H
0  Dublin    en  0.7899231  Madrid    es  0.9653922  Milano    it  0.789923

Then perform a left merge on your original df and select the 'H_y' column, the name comes from the fact that the columns clash and ffill this:

In [173]:
df['HCode'] = df.merge(gp, left_on=['City', 'Code'], right_on=['City', 'Code2'], how='left')['H_y'].ffill()
df

Out[173]:
           H  Nu    City Code Code2     HCode
00.96539215  Madrid   es    es  0.96539210.92061415  Madrid   it    es  0.96539220.72621916  Madrid   tn    es  0.96539230.73911917  Madrid   fr    es  0.96539240.78992355  Dublin   mt    en  0.96539250.69923957  Dublin   en    en  0.78992360.89046268  Dublin   ar    en  0.78992370.74686368  Dublin   pt    en  0.78992380.78992355  Milano   it    it  0.78992390.69923957  Milano   es    it  0.789923100.89046268  Milano   ar    it  0.789923110.74686368  Milano   pt    it  0.789923

Result of merge to show what it produces:

In[165]:
df.merge(gp, left_on=['City', 'Code'], right_on=['City', 'Code2'])['H_y']Out[165]:
00.96539210.78992320.789923Name: H_y, dtype: float64

EDIT

OK, IIUC you can group as before but then filter the group where 'Code2' equals 'Code' and then use this to merge against:

In [200]:
gp = df.groupby('City')
mask = gp.apply(lambda x: x['Code2'] == x['Code'])
lookup = df.loc[mask[mask].reset_index(level=0).index]
lookup

Out[200]:
          H  Nu    City Code Code2
50.69923957  Dublin   en    en
00.96539215  Madrid   es    es
80.78992355  Milano   it    it

In [202]:
df['HCode'] = df.merge(lookup, left_on=['City', 'Code'], right_on=['City', 'Code2'], how='left')['H_y'].ffill()
df

Out[202]:
           H  Nu    City Code Code2     HCode
00.96539215  Madrid   es    es  0.96539210.92061415  Madrid   it    es  0.96539220.72621916  Madrid   tn    es  0.96539230.73911917  Madrid   fr    es  0.96539240.78992355  Dublin   mt    en  0.96539250.69923957  Dublin   en    en  0.69923960.89046268  Dublin   ar    en  0.69923970.74686368  Dublin   pt    en  0.69923980.78992355  Milano   it    it  0.78992390.69923957  Milano   es    it  0.789923100.89046268  Milano   ar    it  0.789923110.74686368  Milano   pt    it  0.789923