To append values to an existing dataset in R

[ksasi2k3]

I have a table resulting from a sql query, so each time I run the query in R it will generate values for each category. I had a for loop for running this SQL query.
Result will be like this.
Example:
1st loop:
Name Age
A 22
B 24

2nd loop:
Name Age
C 25
D 26

3rd Loop
Name Age
T 28
S 26

I need to Append this in a single tabel:
After Looping all data to in a single table:
Name Age
A 22
B 24
C 25
D 26
T 28
S 26

I tried using rbind

df2<-rbind(df1,currTableDF)

but I am not getting desired result, any help would be greatly appreciated.

Thanks
Ksasi2k3

[mishabalyasin]

Instead of for loop you can use purrr::map_dfr that will create a dataframe in the end for you. Without code it's difficult to say more than this, but the change should be straightforward.

[ksasi2k3]

@mishabalyasin

purrr::map_dfr

Thank you Mishabalyasin.
Here is my code.

library("RODBC")
library(sqldf)
library(formattable)
library(qicharts2)
library(ggplot2)
library(R2PPT)
library(qcc)
library(RDCOMClient)
library(flextable)
library(data.table)

df2<-c('123','248')

df4<-c("Supplier2","Supplier1","Supplier3")

for (i in df2)
{
  for (j in df4)
  {
    
    dbhandle <- odbcDriverConnect('driver={SQL Server};server=AAAA;database=TT;trusted_connection=true')
    currTableSQL<-paste0("with tab1
                         as
                         (
                         Select * from Table1 where end_dt>=DATEADD(DAY,1,EOMONTH(getdate(),-6)) and t.id = '",i,"' and s.Supplier = '",j,"'  and c.part='TT'  and t.Type = 1")
    currTableDF<-sqlQuery(dbhandle,currTableSQL)
    print(currTableDF)
    currTableDF$Week
    # substring(currTableDF$Week,12)
    class(currTableDF$Week)
    df1<-data.frame()
    df2<-rbind(df1,currTableDF)
    
    # setDT(df2)
    # setkeyv(df2,grep("index",names(df2),v=T))
    # unique(df2)
    # print(df2)
  }
}

For each loop I am generating a dataframe called"currTableDF". I am trying to create empty dataframe named "df1" and now I need to load each iteration data from "currTableDF" to "df1" and name it as "df2" a new dataframe. It should have all values of each iteration.

Main thing I need is to update values of each iteration to a single dataframe.

[mishabalyasin]

I don't have connection to your database, so below is a bit of pseudo-code of how I would approach this problem:

library(tidyverse)

df2 <- c('123','248')
df4 <- c("Supplier2","Supplier1","Supplier3")

input <- purrr::cross_df(list(times = df2, suppliers = df4))

#dbhandle <- odbcDriverConnect('driver={SQL Server};server=AAAA;database=TT;trusted_connection=true')

purrr::map2_chr(input$times, input$suppliers, function(x, y){
  sql <- glue::glue("with tab1 as (Select * from Table1 
             where end_dt>=DATEADD(DAY,1,EOMONTH(getdate(),-6)) 
             and t.id = '{x}' 
             and s.Supplier = '{y}'  
             and c.part='TT'  
             and t.Type = 1")
  
  # sqlQuery(dbhandle, currTableSQL)
})
#> [1] "with tab1 as (Select * from Table1 \nwhere end_dt>=DATEADD(DAY,1,EOMONTH(getdate(),-6)) \nand t.id = '123' \nand s.Supplier = 'Supplier2'  \nand c.part='TT'  \nand t.Type = 1"
#> [2] "with tab1 as (Select * from Table1 \nwhere end_dt>=DATEADD(DAY,1,EOMONTH(getdate(),-6)) \nand t.id = '248' \nand s.Supplier = 'Supplier2'  \nand c.part='TT'  \nand t.Type = 1"
#> [3] "with tab1 as (Select * from Table1 \nwhere end_dt>=DATEADD(DAY,1,EOMONTH(getdate(),-6)) \nand t.id = '123' \nand s.Supplier = 'Supplier1'  \nand c.part='TT'  \nand t.Type = 1"
#> [4] "with tab1 as (Select * from Table1 \nwhere end_dt>=DATEADD(DAY,1,EOMONTH(getdate(),-6)) \nand t.id = '248' \nand s.Supplier = 'Supplier1'  \nand c.part='TT'  \nand t.Type = 1"
#> [5] "with tab1 as (Select * from Table1 \nwhere end_dt>=DATEADD(DAY,1,EOMONTH(getdate(),-6)) \nand t.id = '123' \nand s.Supplier = 'Supplier3'  \nand c.part='TT'  \nand t.Type = 1"
#> [6] "with tab1 as (Select * from Table1 \nwhere end_dt>=DATEADD(DAY,1,EOMONTH(getdate(),-6)) \nand t.id = '248' \nand s.Supplier = 'Supplier3'  \nand c.part='TT'  \nand t.Type = 1"

^{Created on 2019-06-28 by the reprex package (v0.3.0)}

As you can see, it's possible to generate 6 SQL queries and what you need to do is to change purrr::map2_chr to purrr::map2_dfr and uncomment actual SQL query.

[ksasi2k3]

Thank you Mishabalyasin..

Sure I will try the code. In meantime I used same "rbind" to solve this issue.
Its kind of declaring the empty dataframe out of the loop and use "rbind" to solve this.

Thank you very much for your effort. I think its "purrr::map" new one and a new learning. The main objective of this process is, I am trying to represent different Suppliers in a single chart.

I need to represent "Supplier1","Supplier2", "Supplier3" in a single chart with different color. Any idea....

[andresrcs]

I really don't understand why you need a for-loop for this, if you want to end up with just one dataframe, why you don't simply use the in operator in your sql query?, something like this

Select 
	* 
from 
	Table1 
where 
	end_dt >= DATEADD(DAY,1,EOMONTH(getdate(),-6)) and
	t.id in ('123','248') and
	s.Supplier in ('Supplier2','Supplier1','Supplier3') and
	c.part='TT' and
	t.Type = 1

This will give you all the combinations on a single table eliminating the need for iterate and bind.