Search code examples
rreplacepositionrowna

replace NA depending on column position

I have this table enter image description here

I would like the NAs in columns 2:6 to be "Ag", and those in 7:12 to be "Non-Ag". I was thinking of approaching it like: if NA and in column between 2 and 6 then "Ag" else "Non-Ag" but keep running into errors.

The dput is:

structure(list(X1 = c("Treatment", NA, "Long Term Arm", NA, "Short Term Arm", 
NA), X2 = c(NA, "# Ent.", "4.36", "[2.93]", "0.49", "[2.6]"), 
    X3 = c(NA, "Rev.", "8521.72", "[5523.36]", "10174.38∗∗", 
    "[5136.73]"), X4 = c("Ag", "Costs", "5277.01", "[3785.31]", 
    "5966.48∗", "[3464.9]"), X5 = c(NA, "Net-Rev.", "2137.42", 
    "[4615.71]", "4368.46", "[4033.6]"), X6 = c(NA, "Assets", 
    "29779.09∗∗∗", "[10833.07]", "15558.74∗", "[8977.02]"
    ), X7 = c(NA, "# Ent.", "5.57∗∗∗", "[1.81]", "2.90∗∗", 
    "[1.35]"), X8 = c(NA, "Rev.", "52857.68∗∗", "[22776.31]", 
    "13003.09", "[14778.92]"), X9A = c("Non-Ag", "Costs", "26778.28∗", 
    "[15747.31]", "2530.93", "[9819.96]"), X9B = c(NA, "Net-Rev.", 
    "26088.63∗∗", "[11245.63]", "10456.25", "[6713.02]"), 
    X12 = c(NA, "Assets", "6271.58", "[6082.45]", "883.07", "[4328.38]"
    )), row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"
))
Solution

  • I would subset based on which values are missing using is.na, and assign with "Ag" or "Non-Ag" separately for clarity. This also assumes you are looking to replace the NA in row 1.

    df[1, 2:6][is.na(df[1, 2:6])] <- "Ag"
df[1, 7:12][is.na(df[1, 7:12])] <- "Non-Ag"