How do I split text into a new row based on a match in R?

Question

I have the following dataframe (a smaller sample):


|     |Date       |Signs       |Horoscope                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |Newspaper      |
|:----|:----------|:-----------|:----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|:--------------|
|1937 |14-11-2020 |CAPRICORN   |You may have difficulty making sense of the world today. Perhaps the odd things that people say will seem to lack meaning - or the facts will be at variance with what you had come to expect. But that's often the case at this point in the lunar cycle. IN PICS | Ayodhya witnesses Deepotsav grandeur as over 5 lakh diyas light up Saryu banks on Diwali eve AQUARIUS Your planetary ruler, Saturn, has benefited from a long series of harmonious aspects, which means that you should be in a relatively strong and secure position. At any rate, circumstances are on your side, mainly because you can stick to your guns. |Indian Express |
|1938 |13-11-2020 |TAURUS      |Think very carefully about your long-term hopes and wishes, and make sound and sensible plans to get what you want. It's a day to listen to advice, read instructions, and generally pay heed to people who know what they're talking about. They, in their turn, may find that your ideas are a lot more creative than they realised. READ | AG nod for contempt case against Kunal Kamra over tweets                                                                                                                                                                                                                              |Indian Express |

I would like to split this text and add a new row everytime there is AQUARIUS found. The above dataframe should instead look like this:

|     |Date       |Signs       |Horoscope                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |Newspaper      |
|:----|:----------|:-----------|:----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|:--------------|
|1937 |14-11-2020 |CAPRICORN   |You may have difficulty making sense of the world today. Perhaps the odd things that people say will seem to lack meaning - or the facts will be at variance with what you had come to expect. But that's often the case at this point in the lunar cycle. IN PICS | Ayodhya witnesses Deepotsav grandeur as over 5 lakh diyas light up Saryu banks on Diwali eve 
|1938 |14-11-2020 |AQUARIUS    |Your planetary ruler, Saturn, has benefited from a long series of harmonious aspects, which means that you should be in a relatively strong and secure position. At any rate, circumstances are on your side, mainly because you can stick to your guns. |Indian Express |
|1939 |13-11-2020 |TAURUS      |Think very carefully about your long-term hopes and wishes, and make sound and sensible plans to get what you want. It's a day to listen to advice, read instructions, and generally pay heed to people who know what they're talking about. They, in their turn, may find that your ideas are a lot more creative than they realised. READ | AG nod for contempt case against Kunal Kamra over tweets                                                                                                                                                                                                                              |Indian Express |

I tried writing the following code using strsplit()

h <- strsplit(horoscopes$Horoscope, split = "AQUARIUS")

h <- data.frame(Date = horoscopes$Date, Horoscope = unlist(h), Signs = horoscopes$Signs)

But I get the following error:

Error in data.frame(Date = horoscopes$Date, Horoscope = unlist(h), Signs = horoscopes$Signs) : 
  arguments imply differing number of rows: 11323, 11858

DPUT:

structure(list(Date = c("14-11-2020", "14-11-2020", "14-11-2020", 
"14-11-2020", "13-11-2020", "13-11-2020", "13-11-2020", "13-11-2020", 
"13-11-2020", "13-11-2020", "13-11-2020", "13-11-2020"), Signs = c("LIBRA", 
"SCORPIO", "SAGITTARIUS", "CAPRICORN", "TAURUS", "GEMINI", "LEO", 
"VIRGO", "LIBRA", "SCORPIO", "SAGITTARIUS", "CAPRICORN"), Horoscope = c(" If money appears to be in short supply the reason may lie in recent mismanagement, but the underlying causes seem to stretch way back to the distant past and a host of attitudes to finances that were unfortunately foisted upon you. You'll soon have a chance to make your own way. READ | Suraj Pe Mangal Bhari review: Loud, unfunny movie ", 
" It seems that loved ones are now ready to move forward but you occupy an important role in their affairs and must be ready to confer your approval, support and blessing. As far as your own affairs are concerned, make money matters your priority. Perhaps you actually need to make more money! ", 
" The Moon cruises into a highly promising position. Thus, by the time you wake up, the vague traumas of recent days should be a thing of the past and you'll be free to enjoy the weekend in peace. You could even pick up a bargain or make an unexpected profit. ", 
" You may have difficulty making sense of the world today. Perhaps the odd things that people say will seem to lack meaning - or the facts will be at variance with what you had come to expect. But that's often the case at this point in the lunar cycle. IN PICS | Ayodhya witnesses Deepotsav grandeur as over 5 lakh diyas light up Saryu banks on Diwali eve AQUARIUS Your planetary ruler, Saturn, has benefited from a long series of harmonious aspects, which means that you should be in a relatively strong and secure position. At any rate, circumstances are on your side, mainly because you can stick to your guns. ", 
" Think very carefully about your long-term hopes and wishes, and make sound and sensible plans to get what you want. It's a day to listen to advice, read instructions, and generally pay heed to people who know what they're talking about. They, in their turn, may find that your ideas are a lot more creative than they realised. READ | AG nod for contempt case against Kunal Kamra over tweets ", 
" Today's lunar patterns offer you all the chances you need to deal with routine chores and tasks. This may sound rather dull, but get as much as you can under your belt today and you'll thank your good sense at the weekend. By the way, if you want to slip out of an obligation, next week could give you the chance. CANCER Mercury's relationship with Venus favours sensible people doing sensible things in sensible ways. It also enables you to do the practical work necessary to get your dreams off the drawing board. First of all, though, you'll have to pay the costs, and these could be greater than expected. ", 
" If you enjoy intrigue and behind-the-scenes manipulation, as we all do to some degree or another, you may do your best now. There is much that you can do to help other people without them knowing about it. I'm sure you'll get your thanks in a few days' time. READ | Explained: India likely to enter technical recession, what does this mean? ", 
" You have refused to buckle under the strain, and all credit to you for your resolve and resilience. Personally, and professionally, the world is your oyster, so you may approach the immediate future with great confidence. If you still have any doubts, consult the experts one last time. ", 
" Your stars are generally optimistic and advantageous, so if you are anxious to get off the treadmill at work, now would seem to be a good time to do it. Find ways to lighten up! Over the next few days, you will be free to question your professional priorities  with positive results. ", 
" You are much too sensible to blame the stars for anything that goes wrong. Yet now that the Moon is gracing you with its benevolent rays you must realise that the celestial configuration shares some responsibility for your major achievements. ", 
" There's definitely an undercurrent in the air today. You're never happy when emotions get intense and feelings run high, and that is just what is likely to happen. You'll probably be left in the dark regarding a number of significant questions - just for now. READ | Krunal Pandya questioned at Mumbai airport over luxury watches ", 
" Your financial affairs certainly look set to prosper. Bear in mind the following general principles: luxuries are better buys than necessities and pleasure will be more profitable than pain. In love, it's time to mend fences and heal a rift. Be generous in victory! AQUARIUS Anyone who tells you that you're incompetent, inefficient and irresponsible is likely to be sent away with a flea in their ear. And quite right, too. Why should people pick on you just because you have your own way of doing things? There's one answer: none! READ | Asif Basra found hanging at Dharamshala residence "
), Newspaper = c("Indian Express", "Indian Express", "Indian Express", 
"Indian Express", "Indian Express", "Indian Express", "Indian Express", 
"Indian Express", "Indian Express", "Indian Express", "Indian Express", 
"Indian Express")), class = "data.frame", row.names = 1934:1945)

user_3732271 · Answer

We need to replicate by the lengths of the list element for 'Date' and 'Signs'

h1 <- data.frame(Date = rep(horoscopes$Date, lengths(h)), 
   Horoscope = unlist(h), Signs = rep(horoscopes$Signs, lengths(h)))

or may use separate_rows from tidyr. Based on the expected output. We are splitting at the 'AQUARIUS' and in the new expanded data, the 'Signs' should be replaced by 'AQUARIUS'. Therefore, create a sequence column (row_number()) before the separate_rows, then after the expansion, grouped by rn and replace the duplicated elements of 'Signs' with 'AQUARIUS'

library(tidyr)
library(dplyr)
horoscopes %>%
   mutate(rn = row_number()) %>%
   separate_rows(Horoscope, sep = "\s+AQUARIUS\s+") %>% 
   group_by(rn) %>% 
   mutate(Signs = replace(Signs, duplicated(Signs), "AQUARIUS")) %>% 
   ungroup

-output

# A tibble: 14 x 5
   Date      Signs      Horoscope                                                                                                                      Newspaper       rn
                                                                                                                                                
 1 14-11-20… LIBRA      " If money appears to be in short supply the reason may lie in recent mismanagement, but the underlying causes seem to stretc… Indian Expr…     1
 2 14-11-20… SCORPIO    " It seems that loved ones are now ready to move forward but you occupy an important role in their affairs and must be ready … Indian Expr…     2
 3 14-11-20… SAGITTARI… " The Moon cruises into a highly promising position. Thus, by the time you wake up, the vague traumas of recent days should b… Indian Expr…     3
 4 14-11-20… CAPRICORN  " You may have difficulty making sense of the world today. Perhaps the odd things that people say will seem to lack meaning -… Indian Expr…     4
 5 14-11-20… AQUARIUS   "Your planetary ruler, Saturn, has benefited from a long series of harmonious aspects, which means that you should be in a re… Indian Expr…     4
 6 13-11-20… TAURUS     " Think very carefully about your long-term hopes and wishes, and make sound and sensible plans to get what you want. It's a … Indian Expr…     5
 7 13-11-20… GEMINI     " Today's lunar patterns offer you all the chances you need to deal with routine chores and tasks. This may sound rather dull… Indian Expr…     6
 8 13-11-20… LEO        " If you enjoy intrigue and behind-the-scenes manipulation, as we all do to some degree or another, you may do your best now.… Indian Expr…     7
 9 13-11-20… VIRGO      " You have refused to buckle under the strain, and all credit to you for your resolve and resilience. Personally, and profess… Indian Expr…     8
10 13-11-20… LIBRA      " Your stars are generally optimistic and advantageous, so if you are anxious to get off the treadmill at work, now would see… Indian Expr…     9
11 13-11-20… SCORPIO    " You are much too sensible to blame the stars for anything that goes wrong. Yet now that the Moon is gracing you with its be… Indian Expr…    10
12 13-11-20… SAGITTARI… " There's definitely an undercurrent in the air today. You're never happy when emotions get intense and feelings run high, an… Indian Expr…    11
13 13-11-20… CAPRICORN  " Your financial affairs certainly look set to prosper. Bear in mind the following general principles: luxuries are better bu… Indian Expr…    12
14 13-11-20… AQUARIUS   "Anyone who tells you that you're incompetent, inefficient and irresponsible is likely to be sent away with a flea in their e… Indian Expr…    12

How do I split text into a new row based on a match in R?

1 Answers