Trouble when data was read from a postgreSQL database-CodePudding

I have a dataset in a postgreSQL database that I am reading into an R script for manipulation through ODBC connection. I realized that computations with the dataset are erroring out due to data coming as "list" rather than "numeric". When I downloaded the data to Excel (csv) first and then read into the same R script, all is well. I tried using the following code to try to unlist and convert to numeric the column of interest, I get the error code that follows - I don't know if this is because the column has many "NULL"s and they aren't being recognized as NULL when I tried to eliminate them with the first 2 lines shown below? OR am I doing something else wrong? Is there a better way to get the data through ODBC-postgreSQL without running into this issue or correct better?

  all_data2 <- all_data_plant %>%
    filter(!is.na(!!rlang::sym(test_par[j])))
  
all_data2[test_par[j]] <- as.numeric(unlist(all_data2[test_par[j]]))

Error: Assigned data as.numeric(unlist(all_data2[test_par[j]])) must be compatible with existing data. x Existing data has 17661 rows. x Assigned data has 3504 rows. i Only vectors of size 1 are recycled. Run rlang::last_error() to see where the error occurred.

Attached a sample of the data in Excel and here is what it looks like in R:

Your table should look like this:

And here's the code I used to import data in RStudio:

library(DBI)
library(RODBCDBI)
library(odbc)
odbc <- dbConnect(odbc::odbc(), dsn = "PostgreSQL")

df <- dbGetQuery(odbc, 'SELECT * FROM table1')

>class(df)
[1] "data.frame"

And here's the output of the query:

              codetime water_stucco_ratio stiffening_back_slate_coat
1  2021-01-04 02:32:00             1.1017                         NA
2  2021-01-05 02:32:00             1.1059                         NA
3  2021-01-06 02:32:00                 NA                         NA
4  2021-01-07 02:32:00             1.0922                         NA
5  2021-01-08 02:32:00                 NA                         NA
6  2021-01-09 02:32:00                 NA                         NA
7  2021-01-10 02:32:00             1.1835                         NA
8  2021-01-11 02:32:00                 NA                         NA
9  2021-01-12 02:32:00             1.1454                         NA
10 2021-01-13 02:32:00                 NA                         NA
11 2021-01-14 02:32:00                 NA                         NA
12 2021-01-15 02:32:00             1.1039                         NA
13 2021-01-16 02:32:00                 NA                         NA
14 2021-01-17 02:32:00             1.1042                         NA
15 2021-01-18 02:32:00                 NA                         NA
16 2021-01-19 02:32:00                 NA                         NA
17 2021-01-20 02:32:00                 NA                         NA
   stiffening_face_slate_coat stiffening_slurry weight_dry
1                          NA                NA         NA
2                          NA                NA         NA
3                          NA              16.2         NA
4                          NA                NA         NA
5                          NA              19.2         NA
6                          NA                NA       1432
7                          NA                NA         NA
8                          NA              15.0         NA
9                          NA                NA         NA
10                         NA              15.0         NA
11                         NA                NA       1478
12                         NA                NA         NA
13                         NA              16.2         NA
14                         NA                NA         NA
15                         NA              18.0         NA
16                         NA                NA       1463
17                         NA                NA       1463

Note: R is going to treat NULL as NA as it treats as not a number. Now you can perform all the manipulations you want to on the data in R.

CodePudding user response：

After connecting to the postgreSQL from R and uploading the data, the data looks like shown in the image. enter image description here