How to combine a column from a xts zoo object to a table object?

Question

I'm having trouble to join two dfs and I believe it occours due to having two diferente objects. Here is my first df:

head(df1)
            ativo
dia             BBAS3.SA    ITSA4.SA    PETR4.SA
  2000-03-31 -0.16925030  0.04819535  0.02141427
  2000-04-28 -0.04720254 -0.09236691 -0.09300770
  2000-05-31 -0.06899136 -0.03948513 -0.02600493

class(df1)
#[1] "table"

This is my second df:

head(df2)
           SELIC
mar 2000   18.85
abr 2000   18.62
mai 2000   18.51

class(df2)
#[1] "xts" "zoo"

I tried to merge it, but I got this:

df3 <- merge(df1, df2)
head(df3)
#       dia    ativo        Freq     SELIC
#1 2000-03-31 BBAS3.SA -0.16925030   18.85
#2 2000-04-28 BBAS3.SA -0.04720254   18.85
#3 2000-05-31 BBAS3.SA -0.06899136   18.85

And I need to have this:

#            ativo
#dia             BBAS3.SA    ITSA4.SA    PETR4.SA  SELIC
#  2000-03-31 -0.16925030  0.04819535  0.02141427  18.85
#  2000-04-28 -0.04720254 -0.09236691 -0.09300770  18.62
#  2000-05-31 -0.06899136 -0.03948513 -0.02600493  18.51

Or this:

#            ativo
#dia             BBAS3.SA    ITSA4.SA    PETR4.SA  SELIC
#  mar 2000   -0.16925030  0.04819535  0.02141427  18.85
#  abr 2000   -0.04720254 -0.09236691 -0.09300770  18.62
#  mai 2000   -0.06899136 -0.03948513 -0.02600493  18.51

Thank you

Data in dput format.

dput(head(df1, 20))

structure(c(-0.16925030161688, -0.0472025393643018, -0.0689913598242851, 
0.141311262642138, -0.0315001304458658, -0.0145050751136923, 
0.0145050751136922, -0.0678475025589349, -0.0260196172650772, 
0, 0.0480340922090001, 0.282644671185294, -0.091151845340713, 
0.00551488285057102, 0.173804037328355, -0.097043771073958, 0.201248689088215, 
-0.0836092984613955, 0.0146170962005332, -0.193997015642139, 
0.0481953506988029, -0.0923669055769145, -0.0394851266704161, 
0.149348020700408, 0.0340890968099324, 0.0753055790307224, -0.0315784758872336, 
-0.149705665792558, -0.00622308912309538, 0, 0.285026811426058, 
-0.037199331538396, -0.108074213231234, -0.0558730868639311, 
0.0826859194325133, 0.0812613806626518, 0, 0.00487103707074807, 
-0.0600197798057272, -0.0804770853805181, 0.0214142733017596, 
-0.0930076959239043, -0.0260049316189392, 0.262819819149602, 
-0.151890530559808, 0.162839251757119, -0.0502434129983582, -0.0341645850441082, 
-0.066119445873884, -0.0347751105378222, 0, 0.174537138064208, 
0.00731111520881221, -0.156186459447952, 0.128301660685536, 0.113194988393825, 
-0.102021552584886, 0.00369697765156146, 0.014652098503586, -0.0696419860052779, 
-0.019803717564094, 0, 0.0444961639551652, 0.0730668010171692, 
-0.0109530737239368, 0.0374915960907066, -0.0941194227900671, 
0.0453306426927274, -0.173274373945029, 0.228535671136248, 0, 
0.0923733553009261, -0.0400062320449435, 0.0101532578824621, 
-0.0204079876556867, 0.0648597665063123, 0.0238683058395199, 
-0.00378154015037378, -0.0288204487996149, -0.0157179109799149
), .Dim = c(20L, 4L), .Dimnames = list(dia = c("2000-03-31", 
"2000-04-28", "2000-05-31", "2000-06-30", "2000-07-31", "2000-08-31", 
"2000-09-29", "2000-10-31", "2000-11-30", "2000-12-28", "2000-12-29", 
"2001-01-31", "2001-02-28", "2001-03-30", "2001-04-30", "2001-05-31", 
"2001-06-29", "2001-07-31", "2001-08-31", "2001-09-28"), ativo = c("BBAS3.SA", 
"ITSA4.SA", "PETR4.SA", "VALE3.SA")), class = "table")

dput(head(df2, 20))

structure(c(18.85, 18.62, 18.51, 18.04, 16.85, 16.52, 16.56, 
16.6, 16.51, 16.19, 15.49, 15.2, 15.39, 16.02, 16.43, 17.28, 
18.57, 19, 19.06, 19.06), class = c("xts", "zoo"), .indexCLASS = "yearmon", tclass = "yearmon", .indexTZ = "UTC", tzone = "UTC", index = structure(c(951868800, 
954547200, 957139200, 959817600, 962409600, 965088000, 967766400, 
970358400, 973036800, 975628800, 978307200, 980985600, 983404800, 
986083200, 988675200, 991353600, 993945600, 996624000, 999302400, 
1001894400), tzone = "UTC", tclass = "yearmon"), .Dim = c(20L, 
1L), .Dimnames = list(NULL, "retorno"))
> 

Answer 1

The following code does not produce an object of class "table" but it otherwise has the format you want.

library(xts)
library(tidyverse)

df1 %>%
  as.data.frame() %>%
  mutate(dia = as.Date(dia),
         dia = as.yearmon(dia)) %>%
  group_by(dia, ativo) %>%
  summarise(Freq = sum(Freq)) %>%
  ungroup() %>%
  pivot_wider(
    id_cols = dia,
    names_from = ativo,
    values_from = Freq
  ) %>%
  left_join(df2 %>% cbind.data.frame(dia = index(.), .), by = "dia")

Another option, this time producing a "table" is

df1 %>%
  as.data.frame() %>%
  mutate(dia = as.Date(dia),
         dia = as.yearmon(dia)) %>%
  group_by(dia, ativo) %>%
  summarise(Freq = sum(Freq)) %>%
  ungroup() %>%
  pivot_wider(
    id_cols = dia,
    names_from = ativo,
    values_from = Freq
  ) %>%
  left_join(df2 %>% cbind.data.frame(dia = index(.), .), by = "dia") %>%
  pivot_longer(
    cols = -dia,
    names_to = "ativo",
    values_to = "value"
  ) %>%
  xtabs(value ~ dia + ativo, data = .)