Reshaping a dataframe with NA values in R

A base R approach,

df2 <- df  # Assigning the df into a new one

To fill the NA values with last occured non NA values by creating a new column Standard,

df2$Standard <- na.omit(df[,1])[cumsum(!is.na(df[,1]))] 

Similarly, after canceling out the names includes Std, by replacing all values with non NA values in About column, finaldf appears.

df2[grepl("Std",df2[,1]),1] <- NA
df2[,1] <- na.omit(df2[,1])[cumsum(!is.na(df2[,1]))] 
finaldf <- df2[!is.na(df2[,"Ratings"]),]

   About Questions Ratings Standard
3    Ram        Q1       7    Std 8
4    Ram        Q2       7    Std 8
5    Ram        Q3       7    Std 8
8   John        Q1       7    Std 9
9   John        Q2       7    Std 9
10  John        Q3       7    Std 9
11  John        Q4       7    Std 9

This is the same part as you did by using the reshape() function.

out <- reshape(finaldf, idvar = "About", v.names = "Ratings", timevar = "Questions", direction = "wide")
out[is.na(out)] <- 0
colnames(out) <- c("About","Standard","Q1","Q2","Q3","Q4")

gives,

  About Standard Q1 Q2 Q3 Q4
3   Ram    Std 8  7  7  7  0
8  John    Std 9  7  7  7  7

Here is a concise and clean tidyverse approach. There are two assumptions that this will work:

1. After a students name there will always follow a string containing "Std" in the next row. (If there are other patterns as well you can extent this approach by adding them to the str_detect call).

2. All other rows of About are NA.

Further, from your expected output it seems like you want to treat missing values in Questions as 0. If you prefer NA instead, you can drop the values_fill argument in pivot_wider.

library(tidyverse)

df <- data.frame("About" = c("Ram","Std 8",NA,NA,NA,"John", "Std 9", NA, NA,NA,NA),
                "Questions" = c(NA,NA,"Q1","Q2","Q3",NA,NA,"Q1","Q2","Q3","Q4"),
                "Ratings" = c(NA,NA,7,7,7,NA,NA,7,7,7,7), stringsAsFactors = FALSE)

df %>%
  mutate(About = ifelse(str_detect(lead(About), "Std") & !is.na(About),
                       paste(About, lead(About)),
                       NA)) %>%
  fill(About) %>% 
  drop_na(Questions) %>% 
  pivot_wider(names_from = Questions,
              values_from = Ratings,
              values_fill = 0
  )

#> # A tibble: 2 x 5
#>   About         Q1    Q2    Q3    Q4
#>   <chr>      <dbl> <dbl> <dbl> <dbl>
#> 1 Ram Std 8      7     7     7     0
#> 2 John Std 9     7     7     7     7

^{Created on 2020-06-13 by the reprex package (v0.3.0)}

Before using reshape or pivot_wider we need to convert our data suitable for such transformation.

library(tidyverse) #for all the awesome packages
library(janitor) #to clean names


df <- data.frame("About" = c("Ram","Std 8",NA,NA,NA,"John", "Std 9", NA, NA,NA,NA),
                 "Questions" = c(NA,NA,"Q1","Q2","Q3",NA,NA,"Q1","Q2","Q3","Q4"),
                 "Ratings" = c(NA,NA,7,7,7,NA,NA,7,7,7,7), stringsAsFactors = FALSE)

df %>%
  as_tibble() -> df # I like to work with tibble

df
#> # A tibble: 11 x 3
#>    About Questions Ratings
#>    <chr> <chr>       <dbl>
#>  1 Ram   <NA>           NA
#>  2 Std 8 <NA>           NA
#>  3 <NA>  Q1              7
#>  4 <NA>  Q2              7
#>  5 <NA>  Q3              7
#>  6 John  <NA>           NA
#>  7 Std 9 <NA>           NA
#>  8 <NA>  Q1              7
#>  9 <NA>  Q2              7
#> 10 <NA>  Q3              7
#> 11 <NA>  Q4              7


#I found I can remove a column out from the above tibble, the below function moves the values to the left if there is a NA

t(apply(df, 1, function(x) c(x[!is.na(x)], x[is.na(x)]))) -> df[] 

df
#> # A tibble: 11 x 3
#>    About Questions Ratings
#>    <chr> <chr>     <chr>  
#>  1 Ram    <NA>     <NA>   
#>  2 Std 8  <NA>     <NA>   
#>  3 Q1    " 7"      <NA>   
#>  4 Q2    " 7"      <NA>   
#>  5 Q3    " 7"      <NA>   
#>  6 John   <NA>     <NA>   
#>  7 Std 9  <NA>     <NA>   
#>  8 Q1    " 7"      <NA>   
#>  9 Q2    " 7"      <NA>   
#> 10 Q3    " 7"      <NA>   
#> 11 Q4    " 7"      <NA>


df %>% 
  clean_names() %>%  # no capitals
  dplyr::select(-ratings) %>% # removing the extra columns
  mutate(questions = questions %>% parse_number()) -> df1 # make the second column numeric


df1
#> # A tibble: 11 x 2
#>    about questions
#>    <chr>     <dbl>
#>  1 Ram          NA
#>  2 Std 8        NA
#>  3 Q1            7
#>  4 Q2            7
#>  5 Q3            7
#>  6 John         NA
#>  7 Std 9        NA
#>  8 Q1            7
#>  9 Q2            7
#> 10 Q3            7
#> 11 Q4            7

# this for loop will get me a vector for the name column which I can use to append it to the df

name <- as.character()
for(i in 1:nrow(df1)){

  if(is.na(df1[i,2])){
    if(is.na(df1[i+1,2])){
      name <- c(name , as.character(df1[i,1]))
    } else {
      name <- c(name, NA)
    }
  } else {
    name <- c(name, NA)
  }

}

name 
#>  [1] "Ram"  NA     NA     NA     NA     "John" NA     NA     NA     NA    
#> [11] NA

name %>% 
  enframe(name = NULL, value = "name") -> name_df #converting vector to tibble

name_df 
#> # A tibble: 11 x 1
#>    name 
#>    <chr>
#>  1 Ram  
#>  2 <NA> 
#>  3 <NA> 
#>  4 <NA> 
#>  5 <NA> 
#>  6 John 
#>  7 <NA> 
#>  8 <NA> 
#>  9 <NA> 
#> 10 <NA> 
#> 11 <NA>

df1 %>% 
  bind_cols(name_df)%>% #binding the new column to the original df
  mutate(std = ifelse(is.na(questions) & is.na(name), about, NA)) %>% # mutating a new column for standard
  fill(name) %>% # this will fill the NA with non NA previous value
  fill(std) %>% 
  drop_na(questions) %>% # dropping unnecessary rows
  pivot_wider(names_from = "about", values_from = "questions") -> final_df # now I can use pivot_wider to get the expected result

final_df
#> # A tibble: 2 x 6
#>   name  std      Q1    Q2    Q3    Q4
#>   <chr> <chr> <dbl> <dbl> <dbl> <dbl>
#> 1 Ram   Std 8     7     7     7    NA
#> 2 John  Std 9     7     7     7     7

^{Created on 2020-06-13 by the reprex package (v0.3.0)}