Increment through a dataframe containing numeric, character, and date columns to individually clean columns in R?

user_16709730 0
Viewed 94

I have a dataframe, let's say it's:

name = c("Joe", "Tim", "Sally")
code = c(43, NA, 19)
address = c("123 street Rd.", "911 Emergency Ln.", "NULL")
date = as.Date(c("2021-11-02", "", "2021-03-29"))

data <- data.frame(name, code, address, date)

and I want to clean the dataset by changing numeric columns to NaN (unless this makes it difficult to graph), character columns to "not provided", and date values to NA or whatever is suggested.

I have been trying to use an if statement inside a for loop like:

columns <- colnames(data)

for (column in columns){
    if (is.numeric(data[column]){
       data[column][is.na(data[column])] = 0}
    elseif (is.numeric(data[column]){
       data[column][is.character(data[column])] = "not provided"}
    else data[column][is.date(data[column])] = "1900-1-1"

but this is not working the way I thought it should. I am looking for something that looks similar to:

name code address date
Joe 43 123 street Rd. 2021-11-02
Tim Nan not provided 1901-1-1
Sally 19 911 Emergency Ln. 2021-03-29

I am learning about handling large datasets (>1 million rows) using tidyverse, but I am struggling on this type of problem. How can I clean data in a way that results in what I'm looking for?

r
1 Answers

1) fix - Have fixed the code below making as few changes as possible. Note that data["name"] results in a data frame with one column whereas data[["name"]] is the column itself. From the input it seems that missing character strings are denoted by the character string "NULL".

name = c("Joe", "Tim", "Sally")
code = c(43, NA, 19)
address = c("123 street Rd.", "911 Emergency Ln.", "NULL")
date = as.Date(c("2021-11-02", "", "2021-03-29"))

data <- data.frame(name, code, address, date)

columns <- names(data)
for (column in columns){
    if (is.numeric(data[[column]])) {
        data[[column]][is.na(data[[column]])] = 0
    } else if (is.character(data[[column]])) {
        data[[column]][data[[column]] == "NULL"] = "not provided"
    } else if (inherits(data[[column]], "Date")) {
        data[[column]][is.na(data[[column]])] = "1900-1-1"
    }
}
data
##    name code           address       date
## 1   Joe   43    123 street Rd. 2021-11-02
## 2   Tim    0 911 Emergency Ln. 1900-01-01
## 3 Sally   19      not provided 2021-03-29

2) lapply - Shorter would be to iterate over the columns performing the replacements on each and then using replace to convert the resulting list back to a data frame.

Convert <- function(x) {
  if (is.numeric(x)) x[is.na(x)] <- 0
  else if (is.character(x)) x[x == "NULL"] <- "not provided"
  else if (inherits(x, "Date")) x[is.na(x)] <- "1900-1-1"
  x
}
data2 <- replace(data, TRUE, lapply(data, Convert))

3) dplyr/tidyr - Using dplyr/tidyr it could be done like this:

library(dplyr)
library(tidyr)

data2 <- data %>%
 mutate(across(where(is.numeric), replace_na, 0)) %>%
 mutate(across(where(is.character), ~ replace(., . == "NULL", "not provided"))) %>%
 mutate(across(where(~ inherits(., "Date")), replace_na, "1900-01-01"))

4) collapse - It could also be done using ftransformv in the collapse package

library(collapse)

data2 <- data |>
  ftransformv(is.numeric, \(x) replace(x, is.na(x), 0)) |>
  ftransformv(is.character, \(x) replace(x, x == "NULL", "not provided")) |>
  ftransformv(\(x) inherits(x, "Date"), \(x) replace(x, is.na(x), "1900-01-01"))
user_5165480
Related