Extracting numbers after a word and storing the results as a new line separator "\n"

user_8959427 0
Viewed 39

I have some text data which looks like:

text
1  to $2.00 on an ongoing basis. the ongoing eps guidance excludes both a 68 cent-per-share charge associated with the establishment of the solutia-related reserve and a tax benefit of
2     wheat and barley business. on a reported basis, eps is in the range of $1.56 to $1.71 per share for the full fiscal year. (for a reconciliation of ongoing... eps was 4.56 to 4.98
3                                    the year ago quarter while 2004 full year eps was $.93, up 7.7% from 2003. return on equity was 21.7% for the fourth quarter and 20.4% for the full

I am trying to extract some information from it. I want to extract the first number after the word eps. I can do the following:

data %>% 
  mutate(
    firstNumberAfterWord = str_match_all(text, "eps\\D*(\\d+)")
  )

Which gives:

                 firstNumberAfterWord
1 eps guidance excludes both a 68, 68
2        eps is in the range of $1, 1    # This is wrong. It should be "$1.56 to $1.71"
3                    eps was $.93, 93

Which does not do what I want since it pulls 68, 1 and 93 but the 1 is incorrect. I have looked at map_chr(myWordColumn, str_c, collapse = "\n"), to first collapse it and then extract the words but without luck.

I want to extract the first digits after the eps word (all occurrences of the eps word where each occurrence is separated by a "\n" separator.

Expected output would be to have a new column which contains:

$.93 # since this comes after the part "eps was $.93"
68 # since it comes after "eps guidance excludes both a 68"
$1.56 to $ 1.71 # "eps is in the range of $1.56 to $1.71" # On a new line for this observation
 4.56 to 4.98 # eps was 4.56 to 4.98

Which all come after the eps word.

Data:

    data <- data.frame(
  text = c(" to $2.00 on an ongoing basis. the ongoing eps guidance excludes both a 68 cent-per-share charge associated with the establishment of the solutia-related reserve and a tax benefit of", 
           " wheat and barley business. on a reported basis, eps is in the range of $1.56 to $1.71 per share for the full fiscal year. (for a reconciliation of ongoing... eps was 4.56 to 4.98",
           " the year ago quarter while 2004 full year eps was $.93, up 7.7% from 2003. return on equity was 21.7% for the fourth quarter and 20.4% for the full"
  )
)
r regex
1 Answers

I suggest using the following pattern:

\beps\b\D*?(\p{Sc}?\d*\.?\d+(?:\s*(?:to|[\xAD\p{Pd}])\s*\d*\.?\d+)?)

See the regex demo. Details

  • \beps\b - a whole word eps
  • \D*? - 0 or more non-digit chars, as few as possible
  • (\p{Sc}?\d*\.?\d+(?:\s*(?:to|\p{Pd})\s*\d*\.?\d+)?) - Group 1:
    • \p{Sc}? - an optional currency symbol
    • \d*\.?\d+ - an integer or float number
    • (?:\s*(?:to|[\xAD\p{Pd}])\s*\d*\.?\d+)? - an optional occurrence of
      • \s* - 0 or more whitespaces
      • (?:to|[\xAD\p{Pd}]) - either to or any Unicode dash
      • \s* - 0 or more whitespaces
      • \d*\.?\d+ - an integer or float number

See an R demo:

library(stringr)
data <- data.frame(
  text = c(" to $2.00 on an ongoing basis. the ongoing eps guidance excludes both a 68 cent-per-share charge associated with the establishment of the solutia-related reserve and a tax benefit of", 
           " wheat and barley business. on a reported basis, eps is in the range of $1.56 to $1.71 per share for the full fiscal year. (for a reconciliation of ongoing... eps was 4.56 to 4.98",
           " the year ago quarter while 2004 full year eps was $.93, up 7.7% from 2003. return on equity was 21.7% for the fourth quarter and 20.4% for the full"
  )
)
res <- str_match_all(data$text, "\\beps\\b\\D*?(\\p{Sc}?\\d*\\.?\\d+(?:\\s*(?:to|[\\xAD\\p{Pd}])\\s*\\d*\\.?\\d+)?)")
data$result <- lapply(res, function(x) paste(x[,-1], collapse="\n"))

Output:

text
1  to $2.00 on an ongoing basis. the ongoing eps guidance excludes both a 68 cent-per-share charge associated with the establishment of the solutia-related reserve and a tax benefit of
2     wheat and barley business. on a reported basis, eps is in the range of $1.56 to $1.71 per share for the full fiscal year. (for a reconciliation of ongoing... eps was 4.56 to 4.98
3                                    the year ago quarter while 2004 full year eps was $.93, up 7.7% from 2003. return on equity was 21.7% for the fourth quarter and 20.4% for the full
               result
1                  68
2 $1.56\n4.56 to 4.98
3                $.93
user_38329700
Related