Fill rows of columns of a DataFrame with 0s based on the column name

Question

I have a DataFrame that looks like so:

dput(head(f))

structure(list(`2021-04-01` = c(18.75, 18.8047425327496, 19.044178996207, 
19.1751973213021, 19.1689792828825, 19.5836158822698), `2021-05-01` = c(18.68, 
18.6054569055353, 18.8313151379181, 19.0204171560939, 19.0789341312536, 
19.5202197400974), `2021-06-01` = c(18.7, 18.6167441350011, 18.8507363020792, 
19.0240964484369, 19.0811699993501, 19.5312231113571), `2021-07-01` = c(18.74, 
18.6232367708829, 18.8951394692142, 19.0857033425782, 19.1976392153446, 
19.6658807214903), `2021-08-01` = c(18.77, 18.644593579267, 18.9246150091022, 
19.1032894987568, 19.2156342675263, 19.6849727955065), `2021-09-01` = c(18.84, 
18.7266061665193, 19.0136981423636, 19.2080462372435, 19.3252661741782, 
19.7976573506291), `2021-10-01` = c(19.16, 19.0429325189963, 
19.2868666432121, 19.5302122400335, 19.6246896164923, 20.0574681959085
), `2021-11-01` = c(19.61, 19.4885636931401, 19.7281581945936, 
19.9833632850547, 20.1125365664892, 20.5253519746824), `2021-12-01` = c(20.49, 
20.3817436029844, 20.615343551369, 20.8552877862877, 20.9919455460469, 
21.3954384388117), `2022-01-01` = c(20.48, 20.3740323095769, 
20.6359913586072, 20.8916278650574, 21.0735209988655, 21.4729020165058
), `2022-02-01` = c(20.61, 20.5005105056777, 20.7692639443274, 
21.0278974843904, 21.2088372694747, 21.6274189250732), `2022-03-01` = c(20.01, 
19.8989327908198, 20.160435596222, 20.4165374436547, 20.5841189948464, 
20.9775903556065), `2022-04-01` = c(17.33, 17.2371485625473, 
17.4547245416883, 17.5005672257715, 17.6708376845902, 18.1363806857805
), `2022-05-01` = c(16.98, 16.8871485729785, 17.1047245517176, 
17.1505672343433, 17.320837688241, 17.7863806850577), `2022-06-01` = c(16.93, 
16.8371485710296, 17.0547245439537, 17.100567227402, 17.270837681622, 
17.7363806838834), `2022-07-01` = c(16.55, 16.4602929774512, 
16.679186983992, 16.72087791148, 16.902375852915, 17.3788582533972
), `2022-08-01` = c(16.55, 16.4602929762459, 16.6791869839035, 
16.7208779112979, 16.902375853404, 17.3788582563241), `2022-09-01` = c(16.63, 
16.5371485669301, 16.7547245454607, 16.8005672316863, 16.9708376873097, 
17.4363806848043), `2022-10-01` = c(17.55, 17.4565144059899, 
17.6932969707138, 17.7610505877454, 17.9658483820753, 18.3789383274342
), `2022-11-01` = c(17.95, 17.8565144105445, 18.0932969766868, 
18.1610505955585, 18.3658483847519, 18.7789383271028), `2022-12-01` = c(18.83, 
18.746506350021, 18.9775786073436, 19.0456532330963, 19.247708755873, 
19.6618961429746), `2023-01-01` = c(18.85, 18.7565144062112, 
18.9932969711003, 19.0610505896545, 19.2658483811053, 19.6789383251607
), `2023-02-01` = c(18.9, 18.8065144004764, 19.0432969669948, 
19.1110505845328, 19.3158483743015, 19.7289383179684), `2023-03-01` = c(18.48, 
18.3965063458801, 18.6275786028996, 18.695653229726, 18.8977087489342, 
19.3118961401579), `2023-04-01` = c(16.45, 16.3946104511309, 
16.6014635966272, 16.5836894226683, 16.7780479423448, 17.1192896129417
), `2023-05-01` = c(16.1, 16.0446104494817, 16.2514635937641, 
16.2336894172376, 16.4280479394591, 16.7692896149847), `2023-06-01` = c(16.05, 
15.9946104513846, 16.2014635962793, 16.1836894234373, 16.3780479477376, 
16.7192896151668), `2023-07-01` = c(15.68, 15.6196427933565, 
15.8264798722289, 15.8067013586247, 15.9929523803558, 16.3360049618321
), `2023-08-01` = c(15.68, 15.6196427956788, 15.8264798738901, 
15.8067013650241, 15.9929523892458, 16.3360049695611), `2023-09-01` = c(15.75, 
15.6946104485846, 15.9014635974283, 15.8836894236607, 16.0780479442838, 
16.4192896139878), `2023-10-01` = c(16.55, 16.4859287021049, 
16.6972792014036, 16.6652734109894, 16.9082634547469, 17.2600362319714
), `2023-11-01` = c(16.95, 16.88772137434, 17.1001895977296, 
17.0660375905701, 17.3119906079332, 17.6598028036498), `2023-12-01` = c(17.83, 
17.7629779733904, 17.9769203281466, 17.9457670813362, 18.1919876279738, 
18.5322466212441), `2024-01-01` = c(17.85, 17.7877213736662, 
18.0001895966288, 17.9660375873591, 18.21199060611, 18.5598028001363
), `2024-02-01` = c(17.9, 17.8377213696917, 18.0501895967882, 
18.0160375825238, 18.2619906037143, 18.609802799337), `2024-03-01` = c(17.48, 
17.412977978269, 17.6269203306822, 17.5957670842671, 17.8419876327419, 
18.182246625778), `2024-04-01` = c(15.58, 15.6320820495314, 15.9040941973079, 
16.0011191104772, 16.1344022617966, 16.2335337117727), `2024-05-01` = c(15.23, 
15.282082046754, 15.5540941922692, 15.6511191067781, 15.7844022615365, 
15.8835337091116), `2024-06-01` = c(15.18, 15.2320820475369, 
15.5040941967701, 15.6011191055654, 15.7344022621257, 15.8335337059841
), `2024-07-01` = c(14.8, 14.8510913081696, 15.1150995970647, 
15.2182582392206, 15.34485846568, 15.4485814541912), `2024-08-01` = c(14.8, 
14.8510913086602, 15.1150995968972, 15.2182582375734, 15.3448584628037, 
15.4485814528923), `2024-09-01` = c(14.88, 14.9320820482739, 
15.2040941957414, 15.3011191040817, 15.434402262001, 15.5335337090296
), `2024-10-01` = c(15.48, 15.4841139854442, 15.5870431340295, 
15.6417314142646, 15.8049227959112, 16.098072929477), `2024-11-01` = c(15.88, 
15.8841139866634, 15.9870431368691, 16.0417314145131, 16.2049227962264, 
16.4980729322541), `2024-12-01` = c(16.75, 16.7489599312631, 
16.8369242831981, 16.8966205174003, 17.071500700515, 17.3712327127006
), `2025-01-01` = c(16.78, 16.6562480763831, 16.6057902890351, 
16.4748384688119, 16.6420566625675, 16.8092903223808), `2025-02-01` = c(16.83, 
16.7062480758168, 16.6557902869362, 16.5248384644428, 16.69205666146, 
16.8592903220325), `2025-03-01` = c(16.4, 16.2779097817421, 16.2307811943544, 
16.1021665690717, 16.2687265649343, 16.4311314730022), `2025-04-01` = c(15.73, 
15.7087537504016, 15.6075842903385, 15.5299411837982, 15.7125519597493, 
15.8306868005935), `2025-05-01` = c(15.38, 15.3587537511295, 
15.257584289102, 15.1799411833166, 15.3625519604448, 15.480686801248
), `2025-06-01` = c(15.33, 15.3087537532081, 15.2075842896311, 
15.129941184956, 15.312551964791, 15.4306868025988), `2025-07-01` = c(14.95, 
14.9219729698033, 14.8130638959243, 14.7435847446705, 14.9181912547184, 
15.0389778390791), `2025-08-01` = c(14.95, 14.9219729696512, 
14.8130638953738, 14.7435847412341, 14.9181912530557, 15.0389778362589
), `2025-09-01` = c(15.03, 15.0087537477099, 14.9075842830852, 
14.8299411740066, 15.0125519532892, 15.1306867930961), `2025-10-01` = c(15.75, 
15.7563564789682, 15.6174493875532, 15.5606783126084, 15.7533723756061, 
15.8531515873088), `2025-11-01` = c(16.15, 16.1563564821382, 
16.0174493913382, 15.9606783194065, 16.1533723777501, 16.2531515879125
), `2025-12-01` = c(17.03, 17.0272458979667, 16.8737455455617, 
16.8203287006477, 17.0225669443998, 17.1255973103374), Date = c("2021-04-01", 
"2021-04-02", "2021-04-03", "2021-04-04", "2021-04-05", "2021-04-06"
)), row.names = c(NA, 6L), class = "data.frame")

As you can see I have a column that is called Date with Dates from 2021-04-01 until 2025-12-31. I also have columns, one for each month between these Dates. So a column for April 2021, May 2021, ..., December 2025.

What I want is to optimize the nested for loop that I am using (maybe with dplyr) below:

  for(i in  1:nrow(f)):
    for(j in 1:ncol(f)):
      if(as.character(f$Date[i]) != colnames(f)[j]):
        f[i, j] <- 0

I tried to do it using the comment's suggestions and it worked, but somehow it is slower:

  require(tidyr)
  long <- f %>%
    gather(Month_Product, Price, -c(Date)) %>%
    filter(Month_Product == as.character(floor_date(ymd(Date), unit = "month")))%>%
    spread(Month_Product, Price) %>%
    mutate_all(~replace(., is.na(.), 0)) %>%
    mutate(SPOT = rowSums(across(where(is.numeric)))) %>%
    dplyr::select(Date, SPOT)

So if I were to take only the head of the result DataFrame it would look have the column of 2021-04-01 with values since the first 6 Date Values are in the month of 2021-04-01 and the other columns values would all be equal to 0.

I hope this is clear I could provide further clarification if needed.

Thank you.

It would be better to [reshape wide-to-long](https://stackoverflow.com/q/2185252/680068), then compare the dates. — zx8754, Jun 04 '21 at 09:53
Glad it helped, you are welcome, feel free to submit your final solution below as an answer. — zx8754, Jun 04 '21 at 10:02
I used the solution in your post but it is weirdly much slower !! do you have any ideas ? or should i just post both codes and ask for someone to help me optimize the original ? — user9396820, Jun 12 '21 at 12:50
Yes, please post an example data `dput(head(myData))`: and your current solution below as an answer. — zx8754, Jun 14 '21 at 08:32

zx8754 · Accepted Answer · 2021-06-16T07:48:13.023

Base solution, with the help of lubridate package to get the floor date.

library(lubridate) # floor_date

# get index where floor date of "Date" doesn't match column name
ix <- matrix(
  rep(head(colnames(f), -1), each = nrow(f)),
  nrow = nrow(f)) != as.character(floor_date(ymd(f$Date), unit = "month"))

# assign 0, based on index, excluding the last "Date" column
f[, -ncol(f)][ ix ] <- 0

# then row sums
data.frame(f$Date,
           SPOT = rowSums(f[, -ncol(f)]))
#         Date     SPOT
# 1 2021-04-01 18.75000
# 2 2021-04-02 18.80474
# 3 2021-04-03 19.04418
# 4 2021-04-04 19.17520
# 5 2021-04-05 19.16898
# 6 2021-04-06 19.58362

A bit more clarification: get column names excluding last "Date" column, then repeat each as many as number of rows, then convert to matrix. Then compare this matrix's each column with the floor of "Date" column. This will give us logical (TRUE/FALSE) matrix, which we then use as index to assign zero. And lastly, sum by rows, excluding last "Date" column.

Thank you I will check if it is optimized and will get back to you — user9396820, Jun 16 '21 at 19:01

Fill rows of columns of a DataFrame with 0s based on the column name

1 Answers1