Conditonal updating a multiindex frame using pandas

Question

I have 2 frames:
frame1
Loc--- Time -- Code -- Value
FRA ----2008 ---- F- ---- 1.224
CAN ----2007 ---- G ------1.99
MEX ----2010 -----I -------3.55
frame2(multi index one)
Country ------Ccy -------2007 ------2008 ------2009
FRANCE------ EURO-------5.225 ------6.299------7.555
CANADA------ CANADIAN---53.65--------4.445-----8.445
MEXICO ------ Peso------15434.154--------14564.3 -----4.455
I would like to convert all columns 2007/2008/... to Euro.
It means that if the Ccy is aleady EURO, it should remain, if not retrieve the exchange rate from frame1 and update the value.
I'm stuck here, tried different ways but nothing satisfactory.

Can you please format your input into something that can be easily copied into a DataFrame? Please see [How to make a good reproducible pandas example](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — ALollz, Feb 19 '19 at 15:35
Is there any other table correlating Loc and Ccy from both dataframes? — Daniel Labbe, Feb 19 '19 at 15:39
Loc is the 3 first characters of Country. country and Ccy are part of the frame2 index, while 2007, 2008, 2009 are columns — Crovish, Feb 19 '19 at 15:41

Daniel Labbe · Accepted Answer · 2019-02-20T08:17:01.227

0

Considering the multindex, and using the method merge to facilitate the operation, the following code:

frame2['Country'] = frame2.index.get_level_values(0)
frame2['Loc'] = frame2.index.get_level_values(0).str[0:3]
frame2['Ccy'] = frame2.index.get_level_values(1)
merged = frame1.merge(frame2)
merged.loc[merged.Ccy != 'EURO', ['2007', '2008', '2009']] = merged.loc[merged.Ccy != 'EURO', ['2007', '2008', '2009']].values * merged.loc[merged.Ccy != 'EURO', ['Value']].values
merged.loc[merged.Ccy != 'EURO', 'Ccy'] = 'EURO'
frame2 = merged[['Country','Ccy','2007','2008','2009']]
frame2.set_index(['Country', 'Ccy'], inplace = True)

Produces the desired output:

EDIT: now updating also the Ccy column with the new currency.

edited Feb 20 '19 at 08:17

answered Feb 19 '19 at 16:02

Daniel Labbe

1,979
3
15
20

Thanks for the reply. But I get this error on the first line 'DataFrame' object has no attribute 'Country' – Crovish Feb 19 '19 at 16:11
@Crovish, I considered the column names as you defined in the question (below). Are they different from this? Frame1: Loc,Time,Code,Value Frame2: Country,Ccy,2007,2008,2009 – Daniel Labbe Feb 19 '19 at 16:16
Frame1 is inline with what you described, but frame 2 has columns 2007,2008,2009. Country and ccy are the index – Crovish Feb 19 '19 at 16:25
Edited to consider the indexes. Take a look, @Crovish. – Daniel Labbe Feb 19 '19 at 16:44
No it doesn't really. As you still have local Ccy. example after conversion, Canada Ccy should be Euro – Crovish Feb 20 '19 at 07:45
Hi @Crovish, I edited the answer to update also the Ccy column. Please, check now. – Daniel Labbe Feb 20 '19 at 08:17

Conditonal updating a multiindex frame using pandas

1 Answers1