CODEWITHSUNDEEP
rdataframe
Neil
Neil

Reputation: 8247

how to extract cells from r dataframe and add as a new row

I have a following dataframe in r

Names        X_1          X_2          X_3           X_4
Name         Sagar II    Booster
Location     India       No            Discharge     Open
Depth        19.5        start         End
DOC          3.2         FPL           64 
Qunatity     234         SPL           50

Now I want to extract certain cells and their corresponding values in next cell. My desired dataframe would be

Names        Values
Name         Sagar II
Location     India 
Discharge    Open
Depth        19.5  
DOC          3.2 
FPL          64 
SPL          50

How can I do it in r?

Upvotes: 0

Views: 73

Answers (2)

www
www

Reputation: 39154

A solution from base R.

# Create example data frame
dt <- read.table(text = "Names        X_1          X_2          X_3           X_4
Name         Sagar II    Booster
                 Location     India       No            Discharge     Open
                 Depth        19.5        start         End
                 DOC          3.2         FPL           64 
                 Qunatity     234         SPL           50",
                 stringsAsFactors = FALSE, header = TRUE, fill = TRUE)

# A list of target keys
target_key <- c("Name", "Location", "Discharge", "Depth", "DOC", "FPL", "SPL")

# A function to extract value based on key and create a new data frame
extract_fun <- function(key, df = dt){
  Row <- which(apply(dt, 1, function(x) key %in% x))
  Col <- which(apply(dt, 2, function(x) key %in% x))
  df2 <- data.frame(Names = key, Values = df[Row, Col + 1],
                    stringsAsFactors = FALSE)
  df2$Values <- as.character(df2$Values)
  return(df2)
}

# Apply the extract_fun
ext_list <- lapply(target_key, extract_fun)

# Combine all data frame
dt_final <- do.call(rbind, ext_list)

dt_final
      Names Values
1      Name  Sagar
2  Location  India
3 Discharge   Open
4     Depth   19.5
5       DOC    3.2
6       FPL     64
7       SPL     50

Upvotes: 1

acylam
acylam

Reputation: 18681

Might not be the most efficient, but works for your example:

library(dplyr)

key_value = function(extraction){
  temp = matrix(NA, nrow = length(extraction), ncol = 2)
  temp[,1] = extraction

  for(ii in 1:nrow(temp)){
    index = df %>%
      as.matrix %>%
      {which(. == extraction[ii], arr.ind = TRUE)}

    temp[ii, 2] = index %>% {df[.[1], .[2]+1]}
  }

  return(data.frame(Names = temp[,1], Values = temp[,2]))
}

Result:

> vec = c("Name", "Location", "Discharge", "Depth", "DOC", "FPL", "SPL")

> key_value(vec)
      Names  Values
1      Name SagarII
2  Location   India
3 Discharge    Open
4     Depth    19.5
5       DOC     3.2
6       FPL      64
7       SPL      50

Data:

df = read.table(text = "Names        X_1          X_2          X_3           X_4
                Name         SagarII    Booster   NA    NA
                Location     India       No            Discharge     Open
                Depth        19.5        start         End   NA
                DOC          3.2         FPL           64   NA
                Qunatity     234         SPL           50   NA", header = TRUE, stringsAsFactors = FALSE)

Upvotes: 1

Related Questions