Ggtcf

There are many NA's in my dataset and I need to shift all those cells (at row level) to the left.

Example- my dataframe:

 df=data.frame(x=c("l","m",NA,NA,"p"),y=c(NA,"b","c",NA,NA),z=c("u",NA,"w","x","y"))
 df
 x y z
 1 l <NA> u
 2 m b <NA>
 3 <NA> c w
 4 <NA> <NA> x
 5 p <NA> y

I want the above dataframe converted into this:

 x y z
 1 l u NA
 2 m b NA
 3 c w NA
 4 x <NA> NA
 5 p y NA

Please help.

Thanks.

You can use the standard apply function:

df=data.frame(x=c("l","m",NA,NA,"p"),y=c(NA,"b","c",NA,NA),z=c("u",NA,"w","x","y"))
df2 = as.data.frame(t(apply(df,1, function(x) return(c(x[!is.na(x)],x[is.na(x)]) ) )))
colnames(df2) = colnames(df)

> df
 x y z
1 l <NA> u
2 m b <NA>
3 <NA> c w
4 <NA> <NA> x
5 p <NA> y
> df2
 x y z
1 l u <NA>
2 m b <NA>
3 c w <NA>
4 x <NA> <NA>
5 p y <NA>

Thanks to @Richard Scriven for good observation

A) with is.na and order, lapply and rbind for aggregation

nosort.df<-do.call(rbind,lapply(1:nrow(df),function(x) z=df[x,][order(is.na(df[x,]))];colnames(z)<-c("x","y","z");return(z) ))

> nosort.df
 x y z
1 l u <NA>
2 m b <NA>
3 c w <NA>
4 x <NA> <NA>
5 p y <NA>

B) if sorted rows are required:

with sort, lapply and rbind

sort.df<-do.call(rbind,lapply(1:nrow(df),function(x) z=sort(df[x,],na.last=TRUE);colnames(z)<-c("x","y","z");return(z) ))

> sort.df
 x y z
1 l u <NA>
2 b m <NA>
3 c w <NA>
4 x <NA> <NA>
5 p y <NA> 

If you won't get shorter answer, this should help:

df=data.frame(x=c("l","m",NA,NA,"p"),y=c(NA,"b","c",NA,NA),z=c("u",NA,"w","x","y"))
sapply(df,as.character)


for(i in 1:nrow(df))
 sub <- df[i,c(which(!is.na(df[i,])),which(is.na(df[i,])))] 
 colnames(sub) <- colnames(df)
 df[i,] <- sub

If you don't want to use VBA, you can try the following steps.

1. Select your dataset
2. Replace NA will empty cells
3. press F5 and select blanks ok
4. right click on any of the selection and delete (left)

I hope this helps.