I’m using R to calculate simple ratios in columns and save those values in another table. The columns seen in the str included are every day in January and every hour of every day in January. For the purposes of this project, I have to calculate all of the hours of the month. I’m getting an appropriate table, but all the values are NA, so the logic or my attempt is failing somewhere and I’ve read all I can read. I’ve hit my limit on trying to find the issue and I need help.
library(dplyr)
library(purrr)
RATIO_2019_01_df <- test_df %>%
select(AREA, RAW_STOP_COUNTS, RAW_DEVICE_COUNTS)
#error checking:
#print(names(test_df))
# Loop through the days and calculate ratios
# Note '1:31' in the for loop is the number of days in the month, adjust for each month
for (day in 1:31) {
day_column <- paste0("Day_", sprintf("%02d", day))
# Loop through 24 hours
for (hour in 1:24) {
hour_column <- paste0("Day_", sprintf("%02d", day), "_hr_", sprintf("%02d", hour))
# Calculate the ratio for each hour and add it as a new column in ratio_df
ratio_column <- paste0("Ratio_Day_", sprintf("%02d", day), "_hr_", sprintf("%02d", hour))
RATIO_2019_01_df[[ratio_column]] <- ifelse(test_df[[day_column]] != 0,
RATIO_2019_01_df[[hour_column]] / RATIO_2019_01_df[[day_column]], 0)
#Assign 0 (zero) for instances where the denominator = zero
}
}
Here is the str (1 observation only for space)
structure(list(AREA = 484391216083, RAW_STOP_COUNTS = 13553,
RAW_DEVICE_COUNTS = 2494, Day_01 = 299, Day_02 = 311, Day_03 = 351,
Day_04 = 384, Day_05 = 376, Day_06 = 416, Day_07 = 322, Day_08 = 569,
Day_09 = 456, Day_10 = 490, Day_11 = 489, Day_12 = 381, Day_13 = 264,
Day_14 = 519, Day_15 = 476, Day_16 = 470, Day_17 = 570, Day_18 = 582,
Day_19 = 341, Day_20 = 321, Day_21 = 355, Day_22 = 521, Day_23 = 516,
Day_24 = 491, Day_25 = 533, Day_26 = 377, Day_27 = 363, Day_28 = 509,
Day_29 = 514, Day_30 = 460, Day_31 = 527, Day_1_hr_01 = 16,
Day_1_hr_02 = 13, Day_1_hr_03 = 15, Day_1_hr_04 = 3, Day_1_hr_05 = 6,
Day_1_hr_06 = 4, Day_1_hr_07 = 3, Day_1_hr_08 = 6, Day_1_hr_09 = 5,
Day_1_hr_10 = 7, Day_1_hr_11 = 14, Day_1_hr_12 = 16, Day_1_hr_13 = 16,
Day_1_hr_14 = 15, Day_1_hr_15 = 15, Day_1_hr_16 = 19, Day_1_hr_17 = 24,
Day_1_hr_18 = 24, Day_1_hr_19 = 15, Day_1_hr_20 = 23, Day_1_hr_21 = 11,
Day_1_hr_22 = 13, Day_1_hr_23 = 9, Day_1_hr_24 = 7, Day_2_hr_01 = 5,
Day_2_hr_02 = 3, Day_2_hr_03 = 4, Day_2_hr_04 = 8, Day_2_hr_05 = 5,
Day_2_hr_06 = 2, Day_2_hr_07 = 9, Day_2_hr_08 = 16, Day_2_hr_09 = 9,
Day_2_hr_10 = 18, Day_2_hr_11 = 12, Day_2_hr_12 = 13, Day_2_hr_13 = 12,
Day_2_hr_14 = 13, Day_2_hr_15 = 8, Day_2_hr_16 = 21, Day_2_hr_17 = 22,
Day_2_hr_18 = 39, Day_2_hr_19 = 28, Day_2_hr_20 = 27, Day_2_hr_21 = 16,
Day_2_hr_22 = 11, Day_2_hr_23 = 4, Day_2_hr_24 = 6, Day_3_hr_01 = 5,
Day_3_hr_02 = 1, Day_3_hr_03 = 9, Day_3_hr_04 = 4, Day_3_hr_05 = 5,
Day_3_hr_06 = 3, Day_3_hr_07 = 7, Day_3_hr_08 = 12, Day_3_hr_09 = 10,
Day_3_hr_10 = 9, Day_3_hr_11 = 11, Day_3_hr_12 = 12, Day_3_hr_13 = 12,
Day_3_hr_14 = 12, Day_3_hr_15 = 16, Day_3_hr_16 = 27, Day_3_hr_17 = 25,
Day_3_hr_18 = 35, Day_3_hr_19 = 40, Day_3_hr_20 = 37, Day_3_hr_21 = 19,
Day_3_hr_22 = 24, Day_3_hr_23 = 9, Day_3_hr_24 = 7, Day_4_hr_01 = 7,
Day_4_hr_02 = 6, Day_4_hr_03 = 3, Day_4_hr_04 = 2, Day_4_hr_05 = 3,
Day_4_hr_06 = 6, Day_4_hr_07 = 9, Day_4_hr_08 = 10, Day_4_hr_09 = 8,
Day_4_hr_10 = 8, Day_4_hr_11 = 15, Day_4_hr_12 = 21, Day_4_hr_13 = 28,
Day_4_hr_14 = 26, Day_4_hr_15 = 20, Day_4_hr_16 = 17, Day_4_hr_17 = 25,
Day_4_hr_18 = 55, Day_4_hr_19 = 30, Day_4_hr_20 = 29, Day_4_hr_21 = 17,
Day_4_hr_22 = 21, Day_4_hr_23 = 12, Day_4_hr_24 = 6, Day_5_hr_01 = 8,
Day_5_hr_02 = 9, Day_5_hr_03 = 8, Day_5_hr_04 = 5, Day_5_hr_05 = 5,
Day_5_hr_06 = 5, Day_5_hr_07 = 6, Day_5_hr_08 = 5, Day_5_hr_09 = 9,
Day_5_hr_10 = 12, Day_5_hr_11 = 17, Day_5_hr_12 = 24, Day_5_hr_13 = 27,
Day_5_hr_14 = 15, Day_5_hr_15 = 33, Day_5_hr_16 = 37, Day_5_hr_17 = 27,
Day_5_hr_18 = 20, Day_5_hr_19 = 27, Day_5_hr_20 = 25, Day_5_hr_21 = 18,
Day_5_hr_22 = 14, Day_5_hr_23 = 10, Day_5_hr_24 = 10, Day_6_hr_01 = 13,
Day_6_hr_02 = 7, Day_6_hr_03 = 11, Day_6_hr_04 = 6, Day_6_hr_05 = 9,
Day_6_hr_06 = 6, Day_6_hr_07 = 8, Day_6_hr_08 = 15, Day_6_hr_09 = 11,
Day_6_hr_10 = 16, Day_6_hr_11 = 13, Day_6_hr_12 = 11, Day_6_hr_13 = 26,
Day_6_hr_14 = 45, Day_6_hr_15 = 24, Day_6_hr_16 = 27, Day_6_hr_17 = 32,
Day_6_hr_18 = 25, Day_6_hr_19 = 27, Day_6_hr_20 = 23, Day_6_hr_21 = 17,
Day_6_hr_22 = 16, Day_6_hr_23 = 18, Day_6_hr_24 = 10, Day_7_hr_01 = 7,
Day_7_hr_02 = 5, Day_7_hr_03 = 5, Day_7_hr_04 = 3, Day_7_hr_05 = 2,
Day_7_hr_06 = 3, Day_7_hr_07 = 5, Day_7_hr_08 = 18, Day_7_hr_09 = 13,
Day_7_hr_10 = 14, Day_7_hr_11 = 12, Day_7_hr_12 = 15, Day_7_hr_13 = 25,
Day_7_hr_14 = 16, Day_7_hr_15 = 21, Day_7_hr_16 = 35, Day_7_hr_17 = 30,
Day_7_hr_18 = 39, Day_7_hr_19 = 13, Day_7_hr_20 = 12, Day_7_hr_21 = 6,
Day_7_hr_22 = 9, Day_7_hr_23 = 10, Day_7_hr_24 = 4, Day_8_hr_01 = 5,
Day_8_hr_02 = 3, Day_8_hr_03 = 19, Day_8_hr_04 = 4, Day_8_hr_05 = 5,
Day_8_hr_06 = 5, Day_8_hr_07 = 8, Day_8_hr_08 = 73, Day_8_hr_09 = 56,
Day_8_hr_10 = 18, Day_8_hr_11 = 19, Day_8_hr_12 = 14, Day_8_hr_13 = 13,
Day_8_hr_14 = 21, Day_8_hr_15 = 29, Day_8_hr_16 = 88, Day_8_hr_17 = 45,
Day_8_hr_18 = 34, Day_8_hr_19 = 29, Day_8_hr_20 = 29, Day_8_hr_21 = 20,
Day_8_hr_22 = 19, Day_8_hr_23 = 8, Day_8_hr_24 = 5, Day_9_hr_01 = 3,
Day_9_hr_02 = 2, Day_9_hr_03 = 0, Day_9_hr_04 = 1, Day_9_hr_05 = 4,
Day_9_hr_06 = 7, Day_9_hr_07 = 12, Day_9_hr_08 = 39, Day_9_hr_09 = 44,
Day_9_hr_10 = 9, Day_9_hr_11 = 11, Day_9_hr_12 = 13, Day_9_hr_13 = 12,
Day_9_hr_14 = 15, Day_9_hr_15 = 15, Day_9_hr_16 = 73, Day_9_hr_17 = 47,
Day_9_hr_18 = 48, Day_9_hr_19 = 27, Day_9_hr_20 = 24, Day_9_hr_21 = 24,
Day_9_hr_22 = 13, Day_9_hr_23 = 10, Day_9_hr_24 = 3, Day_10_hr_01 = 2,
Day_10_hr_02 = 2, Day_10_hr_03 = 2, Day_10_hr_04 = 1, Day_10_hr_05 = 2,
Day_10_hr_06 = 3, Day_10_hr_07 = 8, Day_10_hr_08 = 51, Day_10_hr_09 = 36,
Day_10_hr_10 = 6, Day_10_hr_11 = 13, Day_10_hr_12 = 17, Day_10_hr_13 = 18,
Day_10_hr_14 = 17, Day_10_hr_15 = 19, Day_10_hr_16 = 80,
Day_10_hr_17 = 56, Day_10_hr_18 = 57, Day_10_hr_19 = 31,
Day_10_hr_20 = 27, Day_10_hr_21 = 21, Day_10_hr_22 = 8, Day_10_hr_23 = 9,
Day_10_hr_24 = 4, Day_11_hr_01 = 4, Day_11_hr_02 = 5, Day_11_hr_03 = 2,
Day_11_hr_04 = 2, Day_11_hr_05 = 2, Day_11_hr_06 = 1, Day_11_hr_07 = 12,
Day_11_hr_08 = 37, Day_11_hr_09 = 38, Day_11_hr_10 = 16,
Day_11_hr_11 = 10, Day_11_hr_12 = 15, Day_11_hr_13 = 17,
Day_11_hr_14 = 20, Day_11_hr_15 = 28, Day_11_hr_16 = 67,
Day_11_hr_17 = 51, Day_11_hr_18 = 47, Day_11_hr_19 = 28,
Day_11_hr_20 = 25, Day_11_hr_21 = 27, Day_11_hr_22 = 17,
Day_11_hr_23 = 15, Day_11_hr_24 = 3, Day_12_hr_01 = 4, Day_12_hr_02 = 5,
Day_12_hr_03 = 2, Day_12_hr_04 = 7, Day_12_hr_05 = 3, Day_12_hr_06 = 1,
Day_12_hr_07 = 3, Day_12_hr_08 = 10, Day_12_hr_09 = 9, Day_12_hr_10 = 13,
Day_12_hr_11 = 27, Day_12_hr_12 = 20, Day_12_hr_13 = 21,
Day_12_hr_14 = 50, Day_12_hr_15 = 28, Day_12_hr_16 = 16,
Day_12_hr_17 = 19, Day_12_hr_18 = 24, Day_12_hr_19 = 30,
Day_12_hr_20 = 16, Day_12_hr_21 = 24, Day_12_hr_22 = 22,
Day_12_hr_23 = 21, Day_12_hr_24 = 6, Day_13_hr_01 = 5, Day_13_hr_02 = 4,
Day_13_hr_03 = 2, Day_13_hr_04 = 2, Day_13_hr_05 = 2, Day_13_hr_06 = 1,
Day_13_hr_07 = 4, Day_13_hr_08 = 0, Day_13_hr_09 = 7, Day_13_hr_10 = 11,
Day_13_hr_11 = 16, Day_13_hr_12 = 13, Day_13_hr_13 = 20,
Day_13_hr_14 = 16, Day_13_hr_15 = 24, Day_13_hr_16 = 27,
Day_13_hr_17 = 18, Day_13_hr_18 = 22, Day_13_hr_19 = 18,
Day_13_hr_20 = 18, Day_13_hr_21 = 15, Day_13_hr_22 = 13,
Day_13_hr_23 = 4, Day_13_hr_24 = 2, Day_14_hr_01 = 2, Day_14_hr_02 = 2,
Day_14_hr_03 = 0, Day_14_hr_04 = 1, Day_14_hr_05 = 0, Day_14_hr_06 = 3,
Day_14_hr_07 = 8, Day_14_hr_08 = 58, Day_14_hr_09 = 66, Day_14_hr_10 = 15,
Day_14_hr_11 = 9, Day_14_hr_12 = 12, Day_14_hr_13 = 12, Day_14_hr_14 = 17,
Day_14_hr_15 = 25, Day_14_hr_16 = 72, Day_14_hr_17 = 41,
Day_14_hr_18 = 57, Day_14_hr_19 = 36, Day_14_hr_20 = 23,
Day_14_hr_21 = 27, Day_14_hr_22 = 23, Day_14_hr_23 = 5, Day_14_hr_24 = 5,
Day_15_hr_01 = 5, Day_15_hr_02 = 4, Day_15_hr_03 = 3, Day_15_hr_04 = 0,
Day_15_hr_05 = 3, Day_15_hr_06 = 2, Day_15_hr_07 = 4, Day_15_hr_08 = 45,
Day_15_hr_09 = 43, Day_15_hr_10 = 12, Day_15_hr_11 = 7, Day_15_hr_12 = 19,
Day_15_hr_13 = 12, Day_15_hr_14 = 11, Day_15_hr_15 = 21,
Day_15_hr_16 = 80, Day_15_hr_17 = 46, Day_15_hr_18 = 55,
Day_15_hr_19 = 26, Day_15_hr_20 = 20, Day_15_hr_21 = 26,
Day_15_hr_22 = 14, Day_15_hr_23 = 11, Day_15_hr_24 = 7, Day_16_hr_01 = 3,
Day_16_hr_02 = 1, Day_16_hr_03 = 1, Day_16_hr_04 = 1, Day_16_hr_05 = 1,
Day_16_hr_06 = 5, Day_16_hr_07 = 6, Day_16_hr_08 = 41, Day_16_hr_09 = 58,
Day_16_hr_10 = 10, Day_16_hr_11 = 12, Day_16_hr_12 = 15,
Day_16_hr_13 = 15, Day_16_hr_14 = 12, Day_16_hr_15 = 20,
Day_16_hr_16 = 84, Day_16_hr_17 = 51, Day_16_hr_18 = 41,
Day_16_hr_19 = 27, Day_16_hr_20 = 15, Day_16_hr_21 = 24,
Day_16_hr_22 = 16, Day_16_hr_23 = 5, Day_16_hr_24 = 6, Day_17_hr_01 = 2,
Day_17_hr_02 = 1, Day_17_hr_03 = 4, Day_17_hr_04 = 2, Day_17_hr_05 = 3,
Day_17_hr_06 = 3, Day_17_hr_07 = 9, Day_17_hr_08 = 62, Day_17_hr_09 = 47,
Day_17_hr_10 = 13, Day_17_hr_11 = 15, Day_17_hr_12 = 14,
Day_17_hr_13 = 13, Day_17_hr_14 = 29, Day_17_hr_15 = 24,
Day_17_hr_16 = 70, Day_17_hr_17 = 56, Day_17_hr_18 = 53,
Day_17_hr_19 = 51, Day_17_hr_20 = 29, Day_17_hr_21 = 23,
Day_17_hr_22 = 23, Day_17_hr_23 = 14, Day_17_hr_24 = 10,
Day_18_hr_01 = 6, Day_18_hr_02 = 7, Day_18_hr_03 = 8, Day_18_hr_04 = 4,
Day_18_hr_05 = 6, Day_18_hr_06 = 5, Day_18_hr_07 = 15, Day_18_hr_08 = 46,
Day_18_hr_09 = 44, Day_18_hr_10 = 10, Day_18_hr_11 = 13,
Day_18_hr_12 = 20, Day_18_hr_13 = 22, Day_18_hr_14 = 19,
Day_18_hr_15 = 31, Day_18_hr_16 = 79, Day_18_hr_17 = 55,
Day_18_hr_18 = 55, Day_18_hr_19 = 38, Day_18_hr_20 = 36,
Day_18_hr_21 = 20, Day_18_hr_22 = 21, Day_18_hr_23 = 14,
Day_18_hr_24 = 8, Day_19_hr_01 = 7, Day_19_hr_02 = 8, Day_19_hr_03 = 3,
Day_19_hr_04 = 7, Day_19_hr_05 = 5, Day_19_hr_06 = 4, Day_19_hr_07 = 1,
Day_19_hr_08 = 5, Day_19_hr_09 = 13, Day_19_hr_10 = 20, Day_19_hr_11 = 17,
Day_19_hr_12 = 17, Day_19_hr_13 = 20, Day_19_hr_14 = 20,
Day_19_hr_15 = 21, Day_19_hr_16 = 32, Day_19_hr_17 = 37,
Day_19_hr_18 = 22, Day_19_hr_19 = 14, Day_19_hr_20 = 18,
Day_19_hr_21 = 17, Day_19_hr_22 = 17, Day_19_hr_23 = 8, Day_19_hr_24 = 8,
Day_20_hr_01 = 3, Day_20_hr_02 = 7, Day_20_hr_03 = 7, Day_20_hr_04 = 4,
Day_20_hr_05 = 6, Day_20_hr_06 = 2, Day_20_hr_07 = 2, Day_20_hr_08 = 5,
Day_20_hr_09 = 12, Day_20_hr_10 = 14, Day_20_hr_11 = 14,
Day_20_hr_12 = 7, Day_20_hr_13 = 30, Day_20_hr_14 = 30, Day_20_hr_15 = 32,
Day_20_hr_16 = 27, Day_20_hr_17 = 25, Day_20_hr_18 = 21,
Day_20_hr_19 = 21, Day_20_hr_20 = 18, Day_20_hr_21 = 6, Day_20_hr_22 = 14,
Day_20_hr_23 = 8, Day_20_hr_24 = 6, Day_21_hr_01 = 8, Day_21_hr_02 = 3,
Day_21_hr_03 = 1, Day_21_hr_04 = 0, Day_21_hr_05 = 1, Day_21_hr_06 = 2,
Day_21_hr_07 = 0, Day_21_hr_08 = 7, Day_21_hr_09 = 4, Day_21_hr_10 = 19,
Day_21_hr_11 = 16, Day_21_hr_12 = 19, Day_21_hr_13 = 26,
Day_21_hr_14 = 33, Day_21_hr_15 = 30, Day_21_hr_16 = 31,
Day_21_hr_17 = 27, Day_21_hr_18 = 32, Day_21_hr_19 = 28,
Day_21_hr_20 = 19, Day_21_hr_21 = 19, Day_21_hr_22 = 14,
Day_21_hr_23 = 10, Day_21_hr_24 = 6, Day_22_hr_01 = 7, Day_22_hr_02 = 2,
Day_22_hr_03 = 3, Day_22_hr_04 = 2, Day_22_hr_05 = 1, Day_22_hr_06 = 3,
Day_22_hr_07 = 8, Day_22_hr_08 = 53, Day_22_hr_09 = 53, Day_22_hr_10 = 16,
Day_22_hr_11 = 15, Day_22_hr_12 = 15, Day_22_hr_13 = 10,
Day_22_hr_14 = 23, Day_22_hr_15 = 32, Day_22_hr_16 = 64,
Day_22_hr_17 = 48, Day_22_hr_18 = 53, Day_22_hr_19 = 34,
Day_22_hr_20 = 28, Day_22_hr_21 = 19, Day_22_hr_22 = 16,
Day_22_hr_23 = 11, Day_22_hr_24 = 5, Day_23_hr_01 = 2, Day_23_hr_02 = 4,
Day_23_hr_03 = 7, Day_23_hr_04 = 0, Day_23_hr_05 = 2, Day_23_hr_06 = 4,
Day_23_hr_07 = 7, Day_23_hr_08 = 62, Day_23_hr_09 = 40, Day_23_hr_10 = 11,
Day_23_hr_11 = 10, Day_23_hr_12 = 13, Day_23_hr_13 = 19,
Day_23_hr_14 = 26, Day_23_hr_15 = 28, Day_23_hr_16 = 70,
Day_23_hr_17 = 41, Day_23_hr_18 = 54, Day_23_hr_19 = 35,
Day_23_hr_20 = 31, Day_23_hr_21 = 21, Day_23_hr_22 = 14,
Day_23_hr_23 = 13, Day_23_hr_24 = 2, Day_24_hr_01 = 2, Day_24_hr_02 = 2,
Day_24_hr_03 = 3, Day_24_hr_04 = 1, Day_24_hr_05 = 2, Day_24_hr_06 = 4,
Day_24_hr_07 = 10, Day_24_hr_08 = 52, Day_24_hr_09 = 39,
Day_24_hr_10 = 13, Day_24_hr_11 = 18, Day_24_hr_12 = 11,
Day_24_hr_13 = 14, Day_24_hr_14 = 12, Day_24_hr_15 = 25,
Day_24_hr_16 = 58, Day_24_hr_17 = 42, Day_24_hr_18 = 65,
Day_24_hr_19 = 48, Day_24_hr_20 = 21, Day_24_hr_21 = 23,
Day_24_hr_22 = 11, Day_24_hr_23 = 8, Day_24_hr_24 = 7, Day_25_hr_01 = 4,
Day_25_hr_02 = 3, Day_25_hr_03 = 4, Day_25_hr_04 = 3, Day_25_hr_05 = 4,
Day_25_hr_06 = 2, Day_25_hr_07 = 4, Day_25_hr_08 = 44, Day_25_hr_09 = 52,
Day_25_hr_10 = 13, Day_25_hr_11 = 15, Day_25_hr_12 = 12,
Day_25_hr_13 = 31, Day_25_hr_14 = 16, Day_25_hr_15 = 35,
Day_25_hr_16 = 81, Day_25_hr_17 = 47, Day_25_hr_18 = 42,
Day_25_hr_19 = 26, Day_25_hr_20 = 27, Day_25_hr_21 = 35,
Day_25_hr_22 = 11, Day_25_hr_23 = 16, Day_25_hr_24 = 6, Day_26_hr_01 = 7,
Day_26_hr_02 = 7, Day_26_hr_03 = 3, Day_26_hr_04 = 6, Day_26_hr_05 = 5,
Day_26_hr_06 = 7, Day_26_hr_07 = 9, Day_26_hr_08 = 7, Day_26_hr_09 = 9,
Day_26_hr_10 = 17, Day_26_hr_11 = 26, Day_26_hr_12 = 23,
Day_26_hr_13 = 18, Day_26_hr_14 = 23, Day_26_hr_15 = 24,
Day_26_hr_16 = 32, Day_26_hr_17 = 25, Day_26_hr_18 = 24,
Day_26_hr_19 = 22, Day_26_hr_20 = 23, Day_26_hr_21 = 14,
Day_26_hr_22 = 16, Day_26_hr_23 = 17, Day_26_hr_24 = 13,
Day_27_hr_01 = 10, Day_27_hr_02 = 10, Day_27_hr_03 = 8, Day_27_hr_04 = 7,
Day_27_hr_05 = 5, Day_27_hr_06 = 5, Day_27_hr_07 = 10, Day_27_hr_08 = 10,
Day_27_hr_09 = 11, Day_27_hr_10 = 8, Day_27_hr_11 = 12, Day_27_hr_12 = 19,
Day_27_hr_13 = 33, Day_27_hr_14 = 28, Day_27_hr_15 = 21,
Day_27_hr_16 = 34, Day_27_hr_17 = 24, Day_27_hr_18 = 31,
Day_27_hr_19 = 23, Day_27_hr_20 = 17, Day_27_hr_21 = 12,
Day_27_hr_22 = 12, Day_27_hr_23 = 7, Day_27_hr_24 = 6, Day_28_hr_01 = 6,
Day_28_hr_02 = 5, Day_28_hr_03 = 0, Day_28_hr_04 = 1, Day_28_hr_05 = 9,
Day_28_hr_06 = 2, Day_28_hr_07 = 5, Day_28_hr_08 = 48, Day_28_hr_09 = 44,
Day_28_hr_10 = 12, Day_28_hr_11 = 11, Day_28_hr_12 = 18,
Day_28_hr_13 = 13, Day_28_hr_14 = 22, Day_28_hr_15 = 36,
Day_28_hr_16 = 70, Day_28_hr_17 = 45, Day_28_hr_18 = 48,
Day_28_hr_19 = 33, Day_28_hr_20 = 28, Day_28_hr_21 = 17,
Day_28_hr_22 = 20, Day_28_hr_23 = 9, Day_28_hr_24 = 7, Day_29_hr_01 = 4,
Day_29_hr_02 = 3, Day_29_hr_03 = 2, Day_29_hr_04 = 2, Day_29_hr_05 = 2,
Day_29_hr_06 = 3, Day_29_hr_07 = 7, Day_29_hr_08 = 61, Day_29_hr_09 = 27,
Day_29_hr_10 = 9, Day_29_hr_11 = 14, Day_29_hr_12 = 14, Day_29_hr_13 = 15,
Day_29_hr_14 = 34, Day_29_hr_15 = 38, Day_29_hr_16 = 73,
Day_29_hr_17 = 45, Day_29_hr_18 = 52, Day_29_hr_19 = 33,
Day_29_hr_20 = 27, Day_29_hr_21 = 17, Day_29_hr_22 = 23,
Day_29_hr_23 = 3, Day_29_hr_24 = 6, Day_30_hr_01 = 3, Day_30_hr_02 = 0,
Day_30_hr_03 = 0, Day_30_hr_04 = 4, Day_30_hr_05 = 0, Day_30_hr_06 = 1,
Day_30_hr_07 = 4, Day_30_hr_08 = 49, Day_30_hr_09 = 48, Day_30_hr_10 = 14,
Day_30_hr_11 = 10, Day_30_hr_12 = 16, Day_30_hr_13 = 14,
Day_30_hr_14 = 12, Day_30_hr_15 = 20, Day_30_hr_16 = 61,
Day_30_hr_17 = 40, Day_30_hr_18 = 43, Day_30_hr_19 = 35,
Day_30_hr_20 = 28, Day_30_hr_21 = 22, Day_30_hr_22 = 20,
Day_30_hr_23 = 7, Day_30_hr_24 = 9, Day_31_hr_01 = 9, Day_31_hr_02 = 7,
Day_31_hr_03 = 8, Day_31_hr_04 = 10, Day_31_hr_05 = 8, Day_31_hr_06 = 11,
Day_31_hr_07 = 7, Day_31_hr_08 = 54, Day_31_hr_09 = 40, Day_31_hr_10 = 12,
Day_31_hr_11 = 11, Day_31_hr_12 = 16, Day_31_hr_13 = 15,
Day_31_hr_14 = 20, Day_31_hr_15 = 22, Day_31_hr_16 = 80,
Day_31_hr_17 = 44, Day_31_hr_18 = 41, Day_31_hr_19 = 30,
Day_31_hr_20 = 17, Day_31_hr_21 = 20, Day_31_hr_22 = 27,
Day_31_hr_23 = 7, Day_31_hr_24 = 11), row.names = c(NA, -1L
), class = c("tbl_df", "tbl", "data.frame"))
8
It’s fine to output in wide format, what’s challenging is to analyze in wide format. Your problem will be simpler if you can get the data (preferably upstream) into longer format. If it were in long format, you’d just use df |> mutate(ratio = hr_val / day_val).
If you only have the data in wide format, and you need to output to wide format, I think it’s still simpler to reshape long, do your calcs, and then reshape wide again.
Reshape long:
library(tidyverse)
df_long <- df |> # df = dput data in question
pivot_longer(-(AREA:RAW_DEVICE_COUNTS))
Now we have a long table where some rows relate to daily totals and others are hourly numbers. I’ll split those into separate tables, since each “days” value should be linked to 24 “hours” values. That sounds like a join.
(Here, I also convert the day number (e.g. Day_1 to 1) so it will more easily match up with columns like Day_01_hr_01, which pads the day count when it’s a single digit.)
df_days <- df_long |>
filter(str_length(name) <= 6) |>
rename(day = name, day_val = value) |>
mutate(across(day, parse_number))
df_hours <- df_long |>
filter(str_length(name) > 6) |>
separate(name, into = c("day", "hour"), sep = "(?<=[0-9])_") |>
mutate(across(day:hour, parse_number)) |>
rename(hr_val = value)
With all those preliminaries out of the way to get our data into two “tidy” (aka “3rd normal form”) tables, it’s very simple to combine the two and calculate the ratio:
df_hours |>
left_join(df_days) |>
mutate(ratio = hr_val / day_val)
I’m unclear on what the required output format is, but I presume some variation of using pivot_wider would suffice. For example, adding this after the code above:
... |>
select(-(hr_val:day_val)) |>
pivot_wider(names_from = c(day, hour),
names_glue = "Day_{day}_hr_{hour}",
values_from = ratio)`
…yields a wide table of ratios like this:
AREA RAW_STOP_COUNTS RAW_DEVICE_COUNTS Day_1_hr_1 Day_1_hr_2 Day_1_hr_3 Day_1_hr_4 Day_1_hr_5 Day_1_hr_6 Day_1_hr_7 Day_1_hr_8 Day_1_hr_9 Day_1_hr_10
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 484391216083 13553 2494 0.0535 0.0435 0.0502 0.0100 0.0201 0.0134 0.0100 0.0201 0.0167 0.0234
Two problems:
1: Presumably you have a typo in your for loop on line 11 in the ifelse statement:
RATIO_2019_01_df[[ratio_column]] <- ifelse(test_df[[day_column]] != 0,
RATIO_2019_01_df[[hour_column]] / RATIO_2019_01_df[[day_column]], 0)
should be:
RATIO_2019_01_df[[ratio_column]] <- ifelse(test_df[[day_column]] != 0,
test_df[[hour_column]] / test_df[[day_column]], 0)
(i.e. change RATIO_2019_01_df to test_df).
2: On line 6, in the first sprintf call:
hour_column <- paste0("Day_", sprintf("%02d", day), "_hr_", sprintf("%02d", hour))
can be simplified to:
hour_column <- paste0("Day_", day, "_hr_", sprintf("%02d", hour))
since your column names are “Day_1_hr_01”, “Day_1_hr_02”, etc. There’s only 1 digit after “Day_” for digits < 10 (not “Day_01_hr_01”, etc.).
With these changes, your code returns:
AREA RAW_STOP_COUNTS RAW_DEVICE_COUNTS Ratio_Day_01_hr_01 Ratio_Day_01_hr_02 Ratio_Day_01_hr_03 Ratio_Day_01_hr_04 Ratio_Day_01_hr_05 Ratio_Day_01_hr_06 Ratio_Day_01_hr_07
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 484391216083 13553 2494 0.0535 0.0435 0.0502 0.0100 0.0201 0.0134 0.0100
1