CODEWITHSUNDEEP
rreplacena
Alex Bădoi
Alex Bădoi

Reputation: 830

How to replace columns containing NA with the contents of the previous column?

I have a large dataframe with random columns which contain NA values. It looks like this: 

     2002-06-26 2002-06-27   2002-06-28   2002-07-01 2002-07-02   2002-07-03   2002-07-05
1  US1718711062         NA BMG4388N1065 US0116591092         NA AN8068571086 GB00BYMT0J19
2  US9837721045         NA US0025671050 US03662Q1058         NA BMG3223R1088 US0097281069
3                       NA US00847J1051 US06652V2088         NA BMG4388N1065 US0305061097
4                       NA US04351G1013 US1046741062         NA BMG7496G1033 US03836W1036
5                       NA US2925621052 US1431301027         NA CA88157K1012 US06652V2088
6                       NA US34988V1061 US1897541041         NA CH0044328745 US1547604090
7                       NA US3596941068 US2053631048         NA GB00B5BT0K07 US1778351056
8                       NA US4180561072 US2567461080         NA IE00B5LRLL25 US1999081045
9                       NA US4198791018 US2925621052         NA IE00B8KQN827 US3498531017
10                      NA US45071R1095 US3989051095         NA IE00BGH1M568 US42222N1037

I need a code which identifies and fills out the NA columns with the contents of the previous column. So for example column "2002-06-27" should contain "US1718711062" and "US9837721045". The NA columns are at irregular intervals.

Columns are also of random length some only containing one element so I think the best way to identify columns with no values is to look at the first row like so:

row.has.na <- which(is.na(data[1,]))

[1] 2 5

Upvotes: 0

Views: 86

Answers (2)

Cath
Cath

Reputation: 24074

To complete my comment: as you have already computed row.has.na, the vector of indices for the NA column, here is a way to use it and get what you need:

data[, row.has.na] <- data[, row.has.na - 1]

Upvotes: 3

slamballais
slamballais

Reputation: 3235

This should work. Note that this also works if two (or more) NA columns are next to each other. Maybe there is a way around the while-loop, but...

# Create some data
data <- data.frame(col1 = 1:10, col2 = NA, col3 = 10:1, col4 = NA, col5 = NA, col6 = NA)

# Find which columns contain NA in the first row
col_NA <- which(is.na(data[1,]))

# Select the previous columns
col_replace <- col_NA - 1

# Check if any NA columns are next to each other and fix it: 
while(any(diff(col_replace) == 1)){
    ind <- which(diff(col_replace) == 1) + 1
    col_replace[ind] <- col_replace[ind] - 1
}

# Replace the NA columns with the previous columns
data[,col_NA] <- data[,col_replace]

   col1 col2 col3 col4 col5 col6
1     1    1   10   10   10   10
2     2    2    9    9    9    9
3     3    3    8    8    8    8
4     4    4    7    7    7    7
5     5    5    6    6    6    6
6     6    6    5    5    5    5
7     7    7    4    4    4    4
8     8    8    3    3    3    3
9     9    9    2    2    2    2
10   10   10    1    1    1    1

Upvotes: 2

Related Questions