I have a table I imported into R using the ocr function from tesseract which I need to format into a table that can be used to create graphical representations from.
I have done a bunch of different transformations on the data and so far have the first 3 columns that are needed for my final output.
My output for the table so far looks like this:
> table_df2
V1 month_list us_total
1 2020 December 6.7
2 2021 January 6.3
3 2021 February 6.2
4 2021 March 6
5 2021 April 6.1
6 2021 May 5.8
7 2021 June 5.9
8 2021 July 5.4
9 2021 August 5.2
10 2021 September 4.8
11 2021 October 4.6
12 2021 November 4.2
13 2021 December 3.9
"V1" will need to be renamed as "Year", "month_list" as "Month", and "us_total" as "U.S. Totals"
I also have cleaned all of the values in the remaining part of the table and have the following dataframe output for these:
> dput(values)
structure(list(table_df = c("5.6", "5.8", "14.4", "8.5", "10.5",
"24.2", "9.1", "8.9", "16.9", "5.1", "5.5", "14.5", "8.5", "9.4",
"17.3", "8.8", "7.7", "17.4", "5.2", "5.3", "13.1", "8.9", "10.2",
"19.8", "8.5", "7.7", "17.3", "5", "5.2", "11.8", "8.7", "9.8",
"18.1", "7.3", "7.5", "16.3", "4.8", "5.3", "11.1", "8.6", "10.2",
"18.9", "7.5", "7.5", "17", "4.8", "5.1", "8.8", "8.2", "9.8",
"12.1", "7.4", "6.7", "14.2", "5", "5.2", "9", "8.5", "10", "9.3",
"7.9", "6.6", "13.2", "", "4.5", "4.9", "8.2", "7.6", "8.4",
"13.3", "6.7", "6.2", "10.8", "4.3", "4.4", "9.7", "7.8", "9.0",
"17.4", "6", "5.6", "14.9", "3.7", "4.2", "10.6", "7.2", "7.9",
"14.6", "5.5", "5.6", "17.5", "3.8", "3.6", "10.3", "6.8", "8.2",
"16.0", "5.6", "5", "15.6", "3.7", "3.3", "9", "4.9", "7.2",
"22", "5.3", "4.5", "12.1", "3.1", "3.0", "8.6", "6.2", "7",
"21.0", "4.9", "4.2", "12.2")), row.names = c(NA, -118L), class = "data.frame")
These values need to make up 9 columns to the right of U.S. Total column in my current output, where the first 9 values are for 2020 December, the next 9 are for 2021 January row, etc.
If I could get this to work correctly, the final output would look like this table (in dataframe format):
I was able to get this to work using the matrix function.
my_matrix <- matrix(as.numeric(values), ncol = 9, byrow = TRUE)
values_df <- data.frame(my_matrix)
colnames(values_df) <- c("W.F", "W.M", "W.16-19", "B.F", "B.M", "B.16-19",
"HL.F", "HL.M", "HL.16-19")