I have a table that is downloaded from Excel. The structure looks like this:
excel_table <- tribble(
~hour, ~day, ~value_1, ~value_2, ~value_3, ~value_4, ~day, ~value_1, ~value_2, ~value_3, ~value_4, ~day, ~value_1, ~value_2, ~value_3, ~value_4,
"10am", "11-03-2021", 2, 3, 4, 5, "11-10-2021", 2, 3, 4, 5, "11-17-2021", 2, 3, 4, 5,
"11am", "11-03-2021", 2, 3, 4, 5, "11-10-2021"2, 3, 4, 5, "11-17-2021", 2, 3, 4, 5,
"12pm", "11-03-2021", 2, 3, 4, 5, "11-10-2021"2, 3, 4, 5, "11-17-2021", 2, 3, 4, 5,
"1pm", "11-03-2021", 2, 3, 4, 5, "11-10-2021"2, 3, 4, 5, "11-17-2021", 2, 3, 4, 5,
"2pm", "11-03-2021", 2, 3, 4, 5, "11-10-2021"2, 3, 4, 5, "11-17-2021", 2, 3, 4, 5,
"3pm", "11-03-2021", 2, 3, 4, 5, "11-10-2021"2, 3, 4, 5, "11-17-2021", 2, 3, 4, 5,
"4pm", "11-03-2021", 2, 3, 4, 5, "11-10-2021"2, 3, 4, 5, "11-17-2021", 2, 3, 4, 5,
"5pm", "11-03-2021", 2, 3, 4, 5, "11-10-2021"2, 3, 4, 5, "11-17-2021", 2, 3, 4, 5,
"6pm", "11-03-2021", 2, 3, 4, 5, "11-10-2021"2, 3, 4, 5, "11-17-2021", 2, 3, 4, 5,
"7pm", "11-03-2021", 2, 3, 4, 5, "11-10-2021"2, 3, 4, 5, "11-17-2021", 2, 3, 4, 5,
"8pm", "11-03-2021", 2, 3, 4, 5, "11-10-2021"2, 3, 4, 5, "11-17-2021", 2, 3, 4, 5
)
This is the output that I am looking for:
excel_table <- tribble(
~hour, ~day, ~value_1, ~value_2, ~value_3, ~value_4,
"10am", "11-03-2021", 2, 3, 4, 5,
"11am", "11-03-2021", 2, 3, 4, 5,
"12pm", "11-03-2021", 2, 3, 4, 5,
"1pm", "11-03-2021", 2, 3, 4, 5,
"2pm", "11-03-2021", 2, 3, 4, 5,
"3pm", "11-03-2021", 2, 3, 4, 5,
"4pm", "11-03-2021", 2, 3, 4, 5,
"5pm", "11-03-2021", 2, 3, 4, 5,
"6pm", "11-03-2021", 2, 3, 4, 5,
"7pm", "11-03-2021", 2, 3, 4, 5,
"8pm", "11-03-2021", 2, 3, 4, 5,
"10am", "11-10-2021", 2, 3, 4, 5,
"11am", "11-10-2021", 2, 3, 4, 5,
"12pm", "11-10-2021", 2, 3, 4, 5,
"1pm", "11-10-2021", 2, 3, 4, 5,
"2pm", "11-10-2021", 2, 3, 4, 5,
"3pm", "11-10-2021", 2, 3, 4, 5,
"4pm", "11-10-2021", 2, 3, 4, 5,
"5pm", "11-10-2021", 2, 3, 4, 5,
"6pm", "11-10-2021", 2, 3, 4, 5,
"7pm", "11-10-2021", 2, 3, 4, 5,
"8pm", "11-10-2021", 2, 3, 4, 5,
"10am", "11-17-2021", 2, 3, 4, 5,
"11am", "11-17-2021", 2, 3, 4, 5,
"12pm", "11-17-2021", 2, 3, 4, 5,
"1pm", "11-17-2021", 2, 3, 4, 5,
"2pm", "11-17-2021", 2, 3, 4, 5,
"3pm", "11-17-2021", 2, 3, 4, 5,
"4pm", "11-17-2021", 2, 3, 4, 5,
"5pm", "11-17-2021", 2, 3, 4, 5,
"6pm", "11-17-2021", 2, 3, 4, 5,
"7pm", "11-17-2021", 2, 3, 4, 5,
"8pm", "11-17-2021", 2, 3, 4, 5,
)
My first attempts were using tidyr::gather or tidyr::pivot_longer but that didn't get a good result and I won't reproduce that attempt here because it wasn't the right approach. Then it occurred to me that I could just cut off the columns into new dataframes and then use rbind() or dplyr::bind_rows() to stack the rows on top of each other where the columns match. So I started down that road but it's not such a good road to go down because I was timing myself and it would take way too long. The table I'm working with has more than the three dates; it has many years worth of data.
Is there a solution where I can restructure this data? I'm looking to preserve the first six columns and then stack the next five columns on the bottom of the rows, and then the next five on the bottom of that (and also I'm hoping to repeat the first column that says 'hour' all the way down)