left join with for loop

Question

I need answer for

no fac date count

1 fac1 2015-01 3
2 fac1 2016-01 5
3 fac1 2017-01 7
4 fac2 2015-01 9
5 fac2 2017-01 11
6 fac3 2016-01 13
7 fac4 2015-01 15
8 fac5 2017-01 17

To make

date     fac1 fac2 fac3 fac4 fac5
2015-01     3    9    0   15    0
2016-01     5    0   13    0    0
2017-01     7   11    0    0   17

like this.

I'm trying to use left join but this error occurs.

# first Data
Data0 <-mid[1:31,]

# Separate Data by factor
for (i in 1:72) {
  assign(paste0("Data", i), subset(mid, midnames %in% mid_names[i])) 
}

# Make first dataset
df3 <- Data0
df3$date[is.na(df3$date)] <- Sys.time()
df3[is.na(df3)]<-0

#leftjoin Datas by factor
for (i in 1:72) {
  df3 <- sqldf(paste("SELECT *
                FROM df3
                   LEFT OUTER JOIN Data",i," USING(year,month,date)",sep=""))
  df3[is.na(df3)]<-0

}

Error: Cannot pass NA to dbQuoteIdentifier()

In addition: Warning message: In field_types[] <- field_types[names(data)] : number of items to replace is not a multiple of replacement length

If you are using `dcast`, then `library(reshape);dcast(df1, date~fac, value.var = 'count', fill = 0)` or with `base R` `xtabs(count~date + fac, df1)` — akrun, Nov 06 '17 at 07:11

score 0 · Accepted Answer · answered Nov 06 '17 at 07:08

Your query is off; you need a standard pivot query here:

SELECT
    date,
    MAX(CASE WHEN fac = 'fac1' THEN count END) AS fac1,
    MAX(CASE WHEN fac = 'fac2' THEN count END) AS fac2,
    MAX(CASE WHEN fac = 'fac3' THEN count END) AS fac3,
    MAX(CASE WHEN fac = 'fac4' THEN count END) AS fac4,
    MAX(CASE WHEN fac = 'fac5' THEN count END) AS fac5
FROM yourTable
GROUP BY
    date

I assume that you have a data frame which contains the data in your first piece of sample information.

left join with for loop

1 Answers1