How can I summarize multiple string values by a column value in R?

OK, so this question isn’t as simple as the title may sound. I’ve got a table that’s structued like this:

| Brand | First Name | Last Name | Amount  | e-mail              |
|-------|------------|-----------|---------|---------------------|
| A     | John       | Smith     | 920 USD | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="ee848186809d83879a86ae8b838f8782c08d8183">[email protected]</a> |
| A     | Mary       | Smith     | 650 USD | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="0e646166607d63677a664e6b636f6762206d6163">[email protected]</a> |
| A     | Margaret   | Smith     | 400 USD | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d2b8bdbabca1bfbba6ba92b7bfb3bbbefcb1bdbf">[email protected]</a> |
| B     | Eric       | Davis     | 120 USD | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="84eee0e5f2edf7c4e1e9e5ede8aae7ebe9">[email protected]</a>    |
| B     | Wanda      | Davis     | 500 USD | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="f9939d988f908ab99c94989095d79a9694">[email protected]</a>    |
| B     | Jean       | Davis     | 300 USD | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="412b252037283201242c20282d6f222e2c">[email protected]</a>    |
| A     | Daniel     | Barnes    | 400 USD | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="32565053405c574172575f535b5e1c515d5f">[email protected]</a>   |

What I’m ultimately trying to do is generate emails to be sent to inform customers of their credit balance, and in the above example, I’d like to send one email to [email protected] that says something like “You have credits with Brand A. John Smith has 920 USD, Mary Smith has 650 USD, Margaret Smith has 400 USD.”

I don’t need to get all the way there with this question, but what I would like to do is have one row for each e-mail which somehow includes the information for each row with that email. Maybe some kind of generated concatenated field? It seems simple in theory, but in practice I’m having a tough time coming up with how exactly to do this in R. Any help would be much appreciated!

Bonus: I’m also fairly experienced with MySQL, so if there’s a better way to do it in SQL, that’d be great!

Edit: Dput output (with names and emails edited)

structure(list(BRAND = c("R", "C", "C", "C", "C", "R", "R", "C", 
"C", "C"), GUEST_S_LAST_NAME = c("Stockman", "Ericson", "Ericson", 
"Alcin", "Andrews", "Smith", "Smith", "Brown", "Brown", "Brown"
), GUEST_S_FIRST_NAME = c("Margaret", "Abraham", "Naomi", "Dina", 
"Arthur", "Laura", "Alan", "Gregory", "Marina", "Viktoria"), 
    COMPENSATIONAMOUNT_OR_PERCENT = c("920 USD", "1363 USD", 
    "1363 USD", "452 USD", "452 USD", "250 USD", "250 USD", "1019 USD", 
    "1019 USD", "323 USD"), EXPIRATION_DATE = c("04/30/2022 12:00:00 00 am", 
    "12/31/2021 12:00:00 00 am", "12/31/2021 12:00:00 00 am", 
    "12/31/2021 12:00:00 00 am", "12/31/2021 12:00:00 00 am", 
    "04/30/2022 12:00:00 00 am", "04/30/2022 12:00:00 00 am", 
    "12/31/2021 12:00:00 00 am", "12/31/2021 12:00:00 00 am", 
    "12/31/2021 12:00:00 00 am"), EMAIL = c("<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="bbded6dad2d78afbded6dad2d795d8d4d6">[email protected]</a>", 
    "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="0e6b636f67623c4e6b636f6762206d6163">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6d08000c04015f2d08000c0401430e0200">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="31545c50585d0271545c50585d1f525e5c">[email protected]</a>", 
    "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="056068646c6936456068646c692b666a68">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="a5c0c8c4ccc991e5c0c8c4ccc98bc6cac8">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="f7929a969e9bc3b7929a969e9bd994989a">[email protected]</a>", 
    "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="73161e121a1f4633161e121a1f5d101c1e">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="2c49414d4540196c49414d4540024f4341">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="5f3a323e36336a1f3a323e3633713c3032">[email protected]</a>"
    )), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"
))

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Here is my approach with dplyr:

library(dplyr)

your_data %>%
  group_by(BRAND, EMAIL) %>%
  summarize(text = paste0(
    sprintf("You have credits with Brand %s. ", BRAND),
    paste(sprintf("%s %s has %s",
                  GUEST_S_FIRST_NAME,
                  GUEST_S_LAST_NAME,
                  COMPENSATIONAMOUNT_OR_PERCENT),
          collapse = ", "), "."))

Returns:

# A tibble: 10 x 3
# Groups:   BRAND, EMAIL [5]
   BRAND EMAIL         text                                                     
   <chr> <chr>         <chr>                                                    
 1 C     <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="f1949c90989dc3b1949c90989d">[email protected]</a>… You have credits with Brand C. Abraham Ericson has 1363 …
 2 C     <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="553038343c3967153038343c39">[email protected]</a>… You have credits with Brand C. Abraham Ericson has 1363 …
 3 C     <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="3a5f575b5356097a5f575b5356">[email protected]</a>… You have credits with Brand C. Dina Alcin has 452 USD, A…
 4 C     <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="c1a4aca0a8adf281a4aca0a8ad">[email protected]</a>… You have credits with Brand C. Dina Alcin has 452 USD, A…
 5 C     <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="284d454941441d684d45494144">[email protected]</a>… You have credits with Brand C. Gregory Brown has 1019 US…
 6 C     <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="583d353931346d183d35393134">[email protected]</a>… You have credits with Brand C. Gregory Brown has 1019 US…
 7 C     <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="84e1e9e5ede8b1c4e1e9e5ede8">[email protected]</a>… You have credits with Brand C. Gregory Brown has 1019 US…
 8 R     <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="7c19111d15104d3c19111d1510">[email protected]</a>… You have credits with Brand R. Margaret Stockman has 920…
 9 R     <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="26434b474f4a1266434b474f4a">[email protected]</a>… You have credits with Brand R. Laura Smith has 250 USD, …
10 R     <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="b7d2dad6dedb83f7d2dad6dedb">[email protected]</a>… You have credits with Brand R. Laura Smith has 250 USD, …
# Data used:
your_data <- structure(list(BRAND = c("R", "C", "C", "C", "C", "R", "R", "C", "C", "C"), GUEST_S_LAST_NAME = c("Stockman", "Ericson", "Ericson", "Alcin", "Andrews", "Smith", "Smith", "Brown", "Brown", "Brown"), GUEST_S_FIRST_NAME = c("Margaret", "Abraham", "Naomi", "Dina", "Arthur", "Laura", "Alan", "Gregory", "Marina", "Viktoria"), COMPENSATIONAMOUNT_OR_PERCENT = c("920 USD", "1363 USD", "1363 USD", "452 USD", "452 USD", "250 USD", "250 USD", "1019 USD", "1019 USD", "323 USD"), EXPIRATION_DATE = c("04/30/2022 12:00:00 00 am", "12/31/2021 12:00:00 00 am", "12/31/2021 12:00:00 00 am", "12/31/2021 12:00:00 00 am", "12/31/2021 12:00:00 00 am", "04/30/2022 12:00:00 00 am", "04/30/2022 12:00:00 00 am", "12/31/2021 12:00:00 00 am", "12/31/2021 12:00:00 00 am", "12/31/2021 12:00:00 00 am"), EMAIL = c("<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="fa9f979b9396cbba9f979b9396d4999597">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6f0a020e06035d2f0a020e0603410c0002">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="c7a2aaa6aeabf587a2aaa6aeabe9a4a8aa">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="e88d85898184dba88d85898184c68b8785">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="7d18101c14114e3d18101c1411531e1210">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="9afff7fbf3f6aedafff7fbf3f6b4f9f5f7">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6d08000c0401592d08000c0401430e0200">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="77121a161e1b4237121a161e1b5914181a">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="dbbeb6bab2b7ee9bbeb6bab2b7f5b8b4b6">[email protected]</a>", "<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="9ffaf2fef6f3aadffaf2fef6f3b1fcf0f2">[email protected]</a>")), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"))


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x