How To add number if given in bracket and put calculated value under given catogeries-CodePudding

I have a text file. This text file has data similar to the example given here down. I would like process the data in R in such a way that all value given in bracket should be added and keep the value under one catogeries as given in example. Kindly help, how to import and process the text file, to get my desire results, I am new to programming.
text file look like given below

Carbohydrate metabolism
00010 Glycolysis / Gluconeogenesis (27)
00020 Citrate cycle (TCA cycle) (22)
00030 Pentose phosphate pathway (19)
Energy metabolism
00190 Oxidative phosphorylation (68)
00710 Carbon fixation in photosynthetic organisms (16)
00720 Carbon fixation pathways in prokaryotes (10)

I nedd output in dtatfram, which should look like after adding values given in bracket under catogeris

V1                       V2
Carbohydrate metabolism  68
Energy metabolism        94

CodePudding user response：

This is tricky because you essentially have two dataframes stacked together. One way to achieve your goal is to 1) create a grouping variable if metabolism is energy or carbohydrate, 2) split up the string into the name of energy and the value (which is stuck inside parentheses, so we also need to get rid of those parentheses), and 3) use summarize() to sum everything up by group.

library(tidyverse)

tt <- read_delim("
Carbohydrate metabolism
00010 Glycolysis / Gluconeogenesis (27)
00020 Citrate cycle (TCA cycle) (22)
00030 Pentose phosphate pathway (19)
Energy metabolism
00190 Oxidative phosphorylation (68)
00710 Carbon fixation in photosynthetic organisms (16)
00720 Carbon fixation pathways in prokaryotes (10)", 
                 col_names = c("id", "name"))

tt <- tt %>%
  # create a grouping variable to "divide" your two dataframes
  mutate(meta = as.character(replace(id, str_detect(id, "^[:digit:] $"), NA))) %>%
  fill(meta, .direction = "down") %>%
  # get rid of "column name" stuck in middle of dataframe
  filter(name != "metabolism") %>%
  # split up name of the metabolism and the value by the parenthesis
  extract("name", c("char", "value"), "(\\D*)(\\d.*)") %>%
  # get rid of parenthesis by subtracting last character in the column "value"
  mutate(value = as.numeric(substring(value, 1, nchar(value)-1))) %>% 
  # sum up by grouping variable
  group_by(meta) %>%
  summarise(sumvalue = sum(value))

print(tt)
# A tibble: 2 × 2
  meta         sumvalue
  <chr>           <dbl>
1 Carbohydrate       68
2 Energy             94