Converting Data, Class Character to Numeric w/ NAs in R-CodePudding

There are many similar threads related to my question but I've tried the solutions and have not been able to resolve my problem.

I have written a function to summarize and reformat my data but when I want to format and round the numerical results I get the following error: Error in vapply(x, format, "", big.mark = big.mark, big.interval = big.interval, : values must be length 1, but FUN(X[[1]]) result is length 6

I assume the issue is related to the NAs, but when I try to reclassify the data from character to numeric I get the following error: In Tables(df, x) : NAs introduced by coercion

I created a sample dataset which reproduces my issue, along with my code:

library(dplyr, tidyr)

df<-data.frame(Date=c("Sep 2005","May 2005","Sep 2004","Sep 2005","May 2005","Sep 2004"),Group=c("A.A","B.A","A.A","B.B","A.B","B.C"),Category=c("Total","Total","Total","Total","Total","Total"),Cost=c(100025,51580.75,0,357450.25,73040,1754400))


x<-c("A.A","A.B","B.A","B.B","B.C","Total")

Tables<-function(data,x){
  data2<-bind_rows(data %>% group_by(Group,Date)%>% dplyr::summarise(Amt=sum(Cost)),
                   data %>% group_by(Category,Date)%>%dplyr::summarise(Amt=sum(Cost))%>%dplyr::rename(Group = Category)) %>%
    arrange(Date,Group) %>%pivot_wider(names_from = Date,values_from = Amt)%>% slice(match(x, Group))
  
  data2<-data2%>%mutate(QoQ=((as.numeric(.[[4]])-as.numeric(.[[3]]))/as.numeric(.[[3]])))
  
  
  data2[2]<-prettyNum(data2[2],digits=0,big.mark=",",scientific=F)
}

df2<-as.data.frame(Tables(df,x))

Ideally I would be reformatting columns 2-4 but I'm experimenting with just column 2 for now. I've tried including the following in my function:

data[2]<-as.double(as.character(data[2]))
  data[[2]]<-as.numeric(data[[2]])
  data[2]<-as.numeric(unlist(data[2]))

CodePudding user response：

# "smart" class conversion
df3[] <- lapply(df3, type.convert, as.is = TRUE)
df3
#> # A tibble: 6 x 4
#> # Groups:   Group [6]
#>   Group `May 2005` `Sep 2004` `Sep 2005`
#>   <chr>      <dbl>      <int>      <dbl>
#> 1 A.A          NA           0    100025 
#> 2 A.B       73040          NA        NA 
#> 3 B.A       51581.         NA        NA 
#> 4 B.B          NA          NA    357450.
#> 5 B.C          NA     1754400        NA 
#> 6 Total    124621.    1754400    457475.

# Confirm
lapply(df3, class)
#> $Group
#> [1] "character"
#> 
#> $`May 2005`
#> [1] "numeric"
#> 
#> $`Sep 2004`
#> [1] "integer"
#> 
#> $`Sep 2005`
#> [1] "numeric"
Created on 2021-10-21 by the reprex package (v2.0.1)

Data:

library(dplyr)
library(tidyr)

df<-data.frame(
  Date=c("Sep 2005","May 2005","Sep 2004","Sep 2005","May 2005","Sep 2004"),
  Group=c("A.A","B.A","A.A","B.B","A.B","B.C"),
  Category=c("Total","Total","Total","Total","Total","Total"),
  Cost=c(100025,51580.75,0,357450.25,73040,1754400))

x<-c("A.A","A.B","B.A","B.B","B.C","Total")

df2 <- bind_rows(
  df %>%
    group_by(Group, Date) %>%
    summarise(Amt = sum(Cost)),
  df %>%
    group_by(Category, Date) %>%
    summarise(Amt = sum(Cost)) %>%
    rename(Group = Category))

df3 <- df2 %>%
  arrange(Date, Group) %>%
  pivot_wider(names_from = Date, values_from = Amt) %>%
  dplyr::slice(match(x, Group))