Replace Blank Cells with Character

Replace blank cells with character

An example data frame:

dat <- read.table(text = "
191 ''
282 A
202 ''
210 B")

You can use sub to replace the empty strings with "N":

dat$V2 <- sub("^$", "N", dat$V2)

#    V1 V2
# 1 191  N
# 2 282  A
# 3 202  N
# 4 210  B

How to replace empty cells of a particular column in a list with a character in R

Here is a reproducible example (R Version 4.1.0)

library(tidyverse)

my_list <- list(
  data.frame(a = 1:5, Event = c(6, "", "", 9, ""), c = 11:15), 
  data.frame(a = 1:5, Event = c("", "", "", 8, 9), c = 16:20)
)

lapply(my_list, FUN = \(x) {
  x |> mutate(Event = case_when(Event == "" ~ "No event", TRUE ~ Event))
})

For earlier R versions:

lapply(my_list, FUN = function(x) {
  x %>% mutate(Event = case_when(Event == "" ~ "No event", TRUE ~ Event))
})

Replace blank cell with no in R

Work on the factor levels:

DF <- read.table(text = 'ID  Score  
                 1. A
                 2. " "
                 3. B
                 4. " "
                 5. C', header = TRUE)
levels(DF$Score)[levels(DF$Score) == " "] <- "no"
#  ID Score
#1  1     A
#2  2    no
#3  3     B
#4  4    no
#5  5     C

This is very efficient since there are usually far less factor levels than elements in your vector.

Excel Office Scripts - Replace blank cells with the text null

This worked for me ...

function main(workbook: ExcelScript.Workbook)
{
  let worksheet = workbook.getWorksheet("Source");
  let table = worksheet.getTable("Source");

  let statusColumn = table.getColumnByName("Status");
  let statusColumnRange = statusColumn.getRangeBetweenHeaderAndTotal();

  let emptyStatusCells = statusColumnRange.getSpecialCells(ExcelScript.SpecialCellType.blanks);

  if (emptyStatusCells != undefined) {
    let rangeAreas = emptyStatusCells.getAreas();

    rangeAreas.forEach(range => {
      let values = range.getValues();

      values.forEach(cellValue => {
        cellValue[0] = "null";
      })

      range.setValues(values);
    })
  }

  // Add additional logic here once the blank cells are dealt with.
}

How do you replace blanks with character string in a filtered dataframe in R?

Using grepl:

df_1$bdl[grepl("U", df_1$qual)]="<"

Output:

    qual bdl
1    U   <
2   UJ   <
3  <NA>   <
4    J    
5   U-   <
6   UU   <
7  <NA>    
8    U   <

Replace Blank/empty cell with a string value

You could just use the .Replace Method with the range. It would be much quicker.

Range("AS22:AU" & LastRow).Replace "", "Greens", xlWhole

Replacing blank values (white space) with NaN in pandas

I think df.replace() does the job, since pandas 0.13:

df = pd.DataFrame([
    [-0.532681, 'foo', 0],
    [1.490752, 'bar', 1],
    [-1.387326, 'foo', 2],
    [0.814772, 'baz', ' '],     
    [-0.222552, '   ', 4],
    [-1.176781,  'qux', '  '],         
], columns='A B C'.split(), index=pd.date_range('2000-01-01','2000-01-06'))

# replace field that's entirely space (or empty) with NaN
print(df.replace(r'^\s*$', np.nan, regex=True))

Produces:

                   A    B   C
2000-01-01 -0.532681  foo   0
2000-01-02  1.490752  bar   1
2000-01-03 -1.387326  foo   2
2000-01-04  0.814772  baz NaN
2000-01-05 -0.222552  NaN   4
2000-01-06 -1.176781  qux NaN

---

As Temak pointed it out, use df.replace(r'^\s+$', np.nan, regex=True) in case your valid data contains white spaces.

---

Related Topics