How to divide the columns of an xts object by the columns of another xts object by its column name in R

user_14403266 0
Viewed 101

I have two xts objects, df1 and df2 that look this way, re

spectively:

df1:

           |Argentina   |Peru  |Chile  |Colombia    |Brasil   |
----------------------------------------------------------------
2017-01-01 |4           |12    |16     |8           |32       |
2018-01-01 |14          |18    |6      |4           |82       |
2019-01-01 |64          |22    |14     |18          |52       |
2020-01-01 |24          |80    |36     |6           |14       |
2021-01-01 |34          |70    |36     |60          |74

|

df2:

           |Argentina   |Colombia  |Chile  |Peru  |Brasil    |
----------------------------------------------------------------
2017-01-01 |1           |1         |16     |12     |2        |
2018-01-01 |7           |2         |3      |2      |41       |
2019-01-01 |16          |18        |1      |11     |13       |
2020-01-01 |8           |2         |3      |40     |7        |
2021-01-01 |17          |30        |6      |35     |74       |

And I want to divide each element of each column in df1 by the element in the same date in the corresponding column (by country, i.e. by column name) in df2. For example divide each element of the column "Chile" in df1 by the corresponding element by date of the column "Chile" in df2 to obtain the dataframe df3:

           |Argentina   |Peru  |Chile  |Colombia    |Brasil   |
----------------------------------------------------------------
2017-01-01 |4           |1     |1      |8           |16       |
2018-01-01 |2           |9     |2      |2           |2        |
2019-01-01 |4           |2     |14     |1           |4        |
2020-01-01 |3           |2     |12     |3           |2        |
2021-01-01 |2           |2     |6      |2           |1        |

It occurs to me to do df1/df2 but as the columns in the xts do not have the same order, the result I get does not make sense.... Could you help me?

data-cleaning xts time-series r
1 Answers

The trick is to get the data in the same order by sorting the column names and use this to select the columns in order. This only works if all the column names are the same in both xts objects.

Note: I have renamed the df1 and df2 objects to df1_xts and df2_xts to show that we are dealing with xts objects and not data.frames to avoid any confusion.

# select the column names of both xts objects, via sort 
# and use matrix calculations to do the rest.
df1_xts[,sort(names(df1_xts))] / df2_xts[,sort(names(df2_xts))]

           Argentina Brasil Chile Colombia Peru
2017-01-01         4     16     1        8    1
2018-01-01         2      2     2        2    9
2019-01-01         4      4    14        1    2
2020-01-01         3      2    12        3    2
2021-01-01         2      1     6        2    2
user_49851760
Related