Understand lists structure

Question

I have R code that is connected to my PostgresDB.

It gives me for every table exactly one row with one column of type Boolean

res <- lapply(all_tables,
              function(table){
                sqlStatement <- 
                  paste("SELECT CASE WHEN MAX(date) = current_date-1 then TRUE else FALSE end as x from "
                        ,table)
                dbGetQuery(con, sqlStatement)
              })
names(res) <- all_tables
res

The result is somewhat satisfying:

datawarehouse.table1
     x
1 TRUE

datawarehouse.table2
      x
1 FALSE

datawarehouse.table3
      x
1 FALSE

What I actually need is something like this dataframe:

table                  valid
datawarehouse.table1   TRUE
....

What I don't understand are those x's and those 1's.

But when I do typeof(x) for the elements in res, it says 'list'. How can a list have columns and row numbers? — Johnny Banana, Oct 23 '18 at 10:16
Because you have a list of dataframes. You can convert a list of dataframes to a single dataframe using the approach [here](https://stackoverflow.com/questions/2851327/convert-a-list-of-data-frames-into-one-data-frame), or you can use [dplyr/purrr](https://stackoverflow.com/questions/49076510/r-dplyr-convert-a-list-of-dataframes-into-a-single-organized-dataframe). — slackline, Oct 23 '18 at 10:22
Thanks in advance. Since I didn't understand its structure, I wasn't able to google properly. — Johnny Banana, Oct 23 '18 at 11:10

Oliver Baumann · Accepted Answer · 2018-10-23T12:50:05.393

1

As slackline pointed out, 1 is the row number. The x's are due to you executing this SQL statement:

SELECT CASE WHEN MAX(date) = current_date-1 then TRUE else FALSE end as x from table

Do you see that as x in there? That is the x you are looking for.

You could turn your result, which is a list of dataframes, into one dataframe like this:

# this is the `res` from your example, with some example data
res <- list("datawarehouse.table1" = data.frame("x" = c(TRUE)),
            "datawarehouse.table2" = data.frame("x" = c(FALSE)),
            "datawarehouse.table3" = data.frame("x" = c(TRUE)))

# the names of the list items should be the values in the new column
table_names <- list("table" = names(res))

res <- do.call(rbind, res)
# get rid of the row names, will have integer indexes instead
rownames(res) <- NULL

# add a new column with the table names
# you could use stringr::str_extract to only pull out the portion you need
res<- cbind(res, table_names)

edited Oct 23 '18 at 12:50

answered Oct 23 '18 at 10:14

Oliver Baumann

2,209
1
10
26

Thank you very much, exactly what I was searching for. Man, I really forgot about that x in my statement - Flüchtigkeitsfehler, Oliver! :-) Danke! – Johnny Banana Oct 23 '18 at 10:17
1

Glad to have been able to help! Bitte accept the Antwort if you don't mind ;-) – Oliver Baumann Oct 23 '18 at 10:19
Hi, one little question. The result looks satisfying, but the table names are no "real" column - they don't have a name. This becomes problematic, because when I use the new table (I saved the result from do.call() into res_2) with sqldf and use select * on it, it gives me only the boolean column "x" back, but not the tablename column. Any idea? :-) – Johnny Banana Oct 23 '18 at 12:27
You can rename columns of a dataframe with `colnames(res_2) <- 'x'` (this will give the column the name `x` you can however call it whatever you want). – slackline Oct 23 '18 at 12:43
@JohnnyBanana, I edited the answer, see if that helps! – Oliver Baumann Oct 23 '18 at 12:50

Understand lists structure

1 Answers1