Best way to map values using some ID using dplyr?



What I need to do is the following: I have a dataframe with a q1_monetary_unit and value column like this, let’s call it df:

ID     q1_monetary_unit   value
1          -999           1000
2          -999           2000
3        10 US dollars    -888
4        21 euros         -888

Whenever value isn’t answered on my country’s currency, there’s a string that describes the value and the foreign currency. I’ve also got a dataframe that has the value of each currency in my country’s currency like this one:

US dollar    Euro
   780        850

So my end goal is to turn "10 dollars" into 10 * 780 and 21 euros into 21 * 850, and put those values in the value column, ending like this:

ID     value
1        1000
2        2000
3        7800
4        17850

So, working on my original data, after some transformations I managed to get the following dataframe, let’s call it df2:

ID    value
3     7800
4    17850

So, I want to know what’s the easiest way to replace value in my original dataframe when ID corresponds with the IDs in this new dataframe. Tried doing something like

df %>% mutate(value = case_when(id %in% df2$id~ df2$value,
                                                   T ~ value))

But it doesn’t work. I could delete the rows from df and the bind the rows from df2 but that doesn’t sound very efficient. In Python I would use the map() function, but I’m not sure if R’s map works the same way. Any help would be appreciated.


Managed to do it:

turned df2 into a list and then recoded using it:

value_list = as.list(df2$value)
names(value_list ) = df2$id
df = df%>% mutate(value= recode(df$id, !!!value_list , .default=value))

Any better alternatives are also appreciated

Answered By – Juan C

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More