Concatenate outputs while filtering for request (hard to explain, got good reprex)

Hi there,

I was wondering on how to "concatenate" outputs into one.
Basically I'm trying to combine info from 2 columns into 1 for more insight.

My idea is to try and combine the counts per individual.

# Manually built a subset and altered names, wonder if  there was a way to this, like get a subset that I can output in a form for reprex.

y <-  tibble(
  `Job Status` = c(
    "INV","INV","INV","CMP","INV","INV","INV","INV","INV","CMP","INV","INV","CMP","CMP","CMP","CMP","CMP","CMP","CMP","CMP",
    "CMP","CMP","CMP","CMP","CMP","CMP","CMP","CMP","CMP","CMP","CMP","CMP","CMP","CMP","CMP","CMP","CMP","WRK","CMP","CMP",
    "CMP","INV","INV","INV","CMP","CMP","CMP","CMP","CMP","CMP","CMP","WRK","CMP","WRK","CMP","INV","INV","WHL","INV","WRK",
    "INV","INV","INV","INV","CMP","CMP","INV","INV","INV","INV","INV","CMP","CMP","CMP","WRK","CMP","INV","INV","INV","INV",
    "INV","INV","CMP","WRK","INV","CMP","INV","INV","INV","INV","INV","WRK","WHL","INV","WRK","CMP","RDD","CMP","INV","INV",
    "INV","INV","INV","INV","CMP","CMP","INV","WHL","INV","CMP","INV","INV","INV","INV","INV","CMP","CMP","CMP","CMP","INV",
    "WRK","WRK","WRK","INV","CMP","INV","INV","INV","CMP","INV","WRK","CMP","INV","WRK","CMP","INV","CMP","INV","WRK","CMP",
    "INV","INV","INV","INV","INV","CMP","INV","INV","INV","WRK","INV","INV","INV","INV","INV","INV","CMP","WRK","WRK","WRK",
    "WRK","INV","INV","CMP","WRK","INV","INV","INV","INV"),
  `Quarters` = c(
    "2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2",
    "2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2",
    "2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2",
    "2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2",
    "2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2",
    "2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2",
    "2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2",
    "2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2",
    "2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2",
    "2020.2","2020.2","2020.2","2020.2","2020.2","2020.2","2020.2"
  ),
  `Total Income (Recognized+Unrecognized REV+WIP)` = c("5009.42","6963.82","6963.82","7591.93","12976.85","10073.85","13736.85","10073.85","13736.85","6985.85","4777.37","4597.37","4078.64","3828.64","3837.13","4965.87","6985.85","9702.55","9383.55","9122.73","9122.73","26030.1","4597.37","1980.7","1980.7","1986.21","1986.21","4036","3263.5","3263.5","3263.5","3263.5","3263.5","5015.42","3263.5","13306.85","13306.85","4395","3435.88","4419.64","4443.64","9046.79","10145.58","2765.7","3356.13","1934.94","1934.94","2218.37","2765.67","2218.28","1990.46","2440.86","4030.2","3965","26533.5","5228.72","4918.07","1404.6","7556.1","4065","4858.44","9702.55","2860","3747","2589.27","6057.85","17661.91","17758.7","4709.43","6057.85","6057.85","4709.43","12910.12","1738.98","4195","10096.25","5971.76","6214.14","1977.62","3214.64","3214.64","2894.92","3399.42","1374.54","8907.25","3567.12","3719.42","3870.68","4091.62","1949.5","2043.14","2215.16","1854","3514.43","0","1603.12","937.88","621.06","161.05","5750.5","205","115","3518.83","4279.26","205","75","73765.5","6096.5","100","75","15049.75","14870.65","100","100","829.54","3265.67","0","0","5763","6542.1","3815","0","4868.33","3645.02","4597.37","7335","5604.25","2587.75","150","615","4134","1437.89","2940.73","1985","3399.42","415","4290","4578","17688","724.24","105","185","22756.97","5971.76","5971.76","410.32","7335","1623.42","1626.54","2112.12","639.5","1154","100","100","100","526.5","1990","450","3100","3200","2842.5","105","75","75","2115","100","100","100","100"),
  `Total Expense (Recognized+Unrecognized CST+ACR)` = c("5009.42","6963.82","6963.82","7591.93","12976.85","10073.85","13736.85","10073.85","13736.85","6985.85","4777.37","4597.37","4078.64","3828.64","3837.13","4965.87",
    "6985.85","9702.55","9383.55","9122.73","9122.73","26030.1","4597.37","1980.7","1980.7","1986.21","1986.21","4036","3263.5","3263.5","3263.5","3263.5","3263.5","5015.42",
    "3263.5","13306.85","13306.85","4395","3435.88","4419.64","4443.64","9046.79","10145.58","2765.7","3356.13","1934.94","1934.94","2218.37","2765.67","2218.28","1990.46",
    "2440.86","4030.2","3965","26533.5","5228.72","4918.07","1404.6","7556.1","4065","4858.44","9702.55","2860","3747","2589.27","6057.85","17661.91","17758.7","4709.43",
    "6057.85","6057.85","4709.43","12910.12","1738.98","4195","10096.25","5971.76","6214.14","1977.62","3214.64","3214.64","2894.92","3399.42","1374.54","8907.25","3567.12",
    "3719.42","3870.68","4091.62","1949.5","2043.14","2215.16","1854","3514.43","0","1603.12","937.88","621.06","161.05","5750.5","205","115","3518.83","4279.26","205","75",
    "73765.5","6096.5","100","75","15049.75","14870.65","100","100","829.54","3265.67","0","0","5763","6542.1","3815","0","4868.33","3645.02","4597.37","7335","5604.25","2587.75",
    "150","615","4134","1437.89","2940.73","1985","3399.42","415","4290","4578","17688","724.24","105","185","22756.97","5971.76","5971.76","410.32","7335","1623.42","1626.54",
    "2112.12","639.5","1154","100","100","100","526.5","1990","450","3100","3200","2842.5","105","75","75","2115","100","100","100","100"
  ),
  
  `Job Profit` = c("556","555","555","457","362","-3101","562","-3101","562","386","796","616","748","465","465","575","386","502","506","506","506","1985","616","387","387","387","387","506","502","502","502","502","502","1568","502","556","556","235","465","449","457","431","351","347","347","337","337","387","347","447","387","180","440","325","449","315","492","397.5","314.88","425","606","502","91","744","321","315","500","404.56","376","315","315","376","561","297.88","697","428","347","347","347","347","347","515","506","415","267","665","826","480","513","140","547","0","50","684.73","-39","593.94","312","140","140","190","135","115","140","145.5","135","75","7449.55","539","100","75","1480","1462","100","100","317","847","0","0","1497.88","376","527","-2228.19","543","453","616","75","912","321","150","177.97","436","675.37","468","1914.65","506","385","56.85","553.76","268","175","105","185","326","304","304","218.89","75","75","75","450","100","75","100","100","100","368.5","1360","0","1363","25.76","409","105","75","75","632","100","100","100","-1104"),
  
  `Job Operator` = c("Starlord","Groot","Groot","Bart","Bart","Groot","Bart","Bart",
                     "Bart","Bart","Bart","Bart","Bart","Bart","Bart","Starlord",
                     "Bart","Bart","Bart","Groot","Bart","Bart","Bart","Bart",
                     "Bart","Bart","Bart","Bart","Bart","Bart","Bart","Bart",
                     "Bart","Bart","Bart","Groot","Bart","Sgt Price","Bart","Bart",
                     "Bart","Bart","Bart","Bart","Bart","Bart","Bart","Bart",
                     "Bart","Bart","Bart","Sgt Price","Bart","Starlord","Bart","Bart",
                     "Bart","Sgt Price","Bart","Starlord","Bart","Bart","Bond, James","Bart",
                     "Bart","Bart","Bart","Bart","Bart","Bart","Bart","Bart",
                     "Bart","Starlord","Starlord","Bart","Bart","Bart","Bart","Bart",
                     "Bart","Bart","Bart","Sgt Price","Starlord","Money Penny","Starlord","Bart",
                     "Bart","Starlord","Bart","Bart","Sgt Price","Jon Doe12","Bond, James","Jon Doe17",
                     "Money Penny","Groot","Starlord","Bart","Starlord","Groot","Groot","Groot","Sgt Price",
                     "Money Penny","Bond, James","Jon Doe12","Sgt Price","Money Penny","Bond, James","Bond, James","Sgt Price",
                     "Sgt Price","Sgt Price","Bart","Harry Potter","Harry Potter","Bond, James","Bart","Bart",
                     "Bond, James","Jon Doe10","Bart","Bart","Sgt Price","Bart","Bart","Jon Doe10",
                     "Bond, James","Bart","Bond, James","Bart","Bond, James","Bart","Bond, James","Bond, James","Bond, James" ,
                     "Bond, James","Bond, James","Sgt Price","Sgt Price","Bart","Bart","Bart","Jon Doe10" ,
                     "Sgt Price","Sgt Price","Sgt Price","Money Penny","Sgt Price","Sgt Price",
                     "Sgt Price","Sgt Price","Sgt Price","Groot","Groot","Starlord","Bond, James",
                     "Bond, James","Money Penny","Sgt Price","Sgt Price","Money Penny","Starlord","Sgt Price",
                     "Sgt Price","Sgt Price","Sgt Price" 
                     ),
  
  `Job.Recog.Date` = c(
                    "2020-04-19","2020-04-20","2020-04-20","2020-04-13","2020-04-22","2020-04-19","2020-04-19","2020-04-19","2020-04-19","2020-04-21","2020-04-10",
                    "2020-04-10","2020-04-28","2020-04-12","2020-04-12","2020-04-15","2020-04-21","2020-04-14","2020-04-11","2020-04-17","2020-04-19","2020-04-13",
                    "2020-04-11","2020-04-10","2020-04-10","2020-04-10","2020-04-10","2020-04-19","2020-04-11","2020-04-11","2020-04-11","2020-04-11","2020-04-11",
                    "2020-04-12","2020-04-11","2020-04-20","2020-04-20","2020-04-22","2020-04-29","2020-04-19","2020-04-22","2020-04-22","2020-04-14","2020-04-13",
                    "2020-04-13","2020-04-18","2020-04-18","2020-04-17","2020-04-13","2020-04-17","2020-04-18","2020-04-22","2020-04-29","2020-04-28","2020-04-21",
                    "2020-04-28","2020-04-21","2020-04-10","2020-04-20","2020-04-28","2020-04-21","2020-04-21","2020-04-13","2020-04-21","2020-04-19","2020-04-14",
                    "2020-04-11","2020-04-19","2020-04-13","2020-04-14","2020-04-14","2020-04-13","2020-04-12","2020-04-11","2020-04-21","2020-04-21","2020-04-13",
                    "2020-04-17","2020-04-20","2020-04-20","2020-04-20","2020-04-14","2020-04-17","2020-04-21","2020-04-13","2020-04-15","2020-04-21","2020-04-21",
                    "2020-04-11","2020-04-20","2020-04-23","2020-04-15","2020-04-16","2020-04-11","2020-04-13","2020-04-16","2020-04-19","2020-04-13","2020-04-23",
                    "2020-04-16","2020-04-19","2020-04-11","2020-04-15","2020-04-23","2020-04-17","2020-04-18","2020-04-22","2020-04-21","2020-04-18","2020-04-23",
                    "2020-04-21","2020-04-21","2020-04-23","2020-04-18","2020-04-23","2020-04-24","2020-04-11","2020-04-12","2020-04-16","2020-04-26","2020-04-26",
                    "2020-04-26","2020-04-14","2020-04-28","2020-04-25","2020-04-25","2020-04-27","2020-04-26","2020-04-19","2020-04-11","2020-04-25","2020-04-20",
                    "2020-04-25","2020-04-24","2020-04-25","2020-04-11","2020-04-11","2020-04-11","2020-04-21","2020-04-11","2020-04-25","2020-04-25","2020-04-27",
                    "2020-04-27","2020-04-27","2020-04-19","2020-04-25","2020-04-26","2020-04-27","2020-04-25","2020-04-22","2020-04-22","2020-04-29","2020-04-29",
                    "2020-04-30","2020-04-20","2020-04-17","2020-04-29","2020-04-23","2020-04-23","2020-04-25","2020-04-28","2020-04-28","2020-04-26","2020-04-28",
                    "2020-04-26","2020-04-25","2020-04-26","2020-04-25"
                  ),
  `Ship.ID` = c(
    "S00047098","S00047548","S00047549","S00047579","S00047635","S00047639","S00047640","S00047641","S00047642","S00047691","S00047741","S00047742","S00047744",
    "S00047746","S00047747","S00047748","S00047784","S00047810","S00047858","S00047879","S00047880","S00047897","S00047900","S00047901","S00047902","S00047903",
    "S00047904","S00047919","S00047920","S00047921","S00047922","S00047923","S00047924","S00047926","S00047953","S00047954","S00047955","S00047956","S00047973",
    "S00047974","S00047975","S00047982","S00047984","S00048004","S00048006","S00048007","S00048008","S00048010","S00048011","S00048012","S00048013","S00048035",
    "S00048036","S00048037","S00048038","S00048082","S00048086","S00048119","S00048131","S00048134","S00048135","S00048136","S00048152","S00048163","S00048164",
    "S00048187","S00048188","S00048190","S00048191","S00048192","S00048193","S00048194","S00048197","S00048219","S00048223","S00048231","S00048241","S00048243",
    "S00048244","S00048245","S00048246","S00048252","S00048270","S00048272","S00048280","S00048281","S00048291","S00048304","S00048305","S00048384","S00048447",
    "S00048486","S00048518","S00048521","S00048567","S00048571","S00048598","S00048600","S00048602","S00048604","S00048629","S00048630","S00048650","S00048687",
    "S00048690","S00048694","S00048700","S00048701","S00048702","S00048713","S00048715","S00048717","S00048718","S00048719","S00048786","S00047898","S00047942",
    "S00047947","S00047970","S00048001","S00048002","S00048030","S00048071","S00048075","S00048085","S00048120","S00048132","S00048133","S00048158","S00048179",
    "S00048189","S00048202","S00048221","S00048260","S00048269","S00048285","S00048346","S00048347","S00048348","S00048362","S00048432","S00048438","S00048453",
    "S00048454","S00048455","S00048459","S00048472","S00048496","S00048497","S00048502","S00048506","S00048507","S00048511","S00048512","S00048513","S00048519",
    "S00048529","S00048572","S00048597","S00048635","S00048647","S00048695","S00048696","S00048699","S00048739","S00048740","S00048742","S00048744","S00048781"
  ),

  ) 


# Ensuring the formats for reprex
y$`Total Income (Recognized+Unrecognized REV+WIP)` <-  as.double(y$`Total Income (Recognized+Unrecognized REV+WIP)`)
y$`Total Expense (Recognized+Unrecognized CST+ACR)` <-  as.double(y$`Total Expense (Recognized+Unrecognized CST+ACR)`)
y$`Job Profit` <-  as.double(y$`Job Profit`)


# Summary.
y %>%group_by(
  `Job Status`,
  `Quarters`) %>%  summarise(
    Tot.Income = currency(sum(`Total Income (Recognized+Unrecognized REV+WIP)`)),
    Tot.Expense = currency(sum(`Total Expense (Recognized+Unrecognized CST+ACR)`)),
    Tot.Profit = currency(sum(`Job Profit`)),
    Mean.Profit = currency(mean(`Job Profit`)),
    Count = n(),
    Job.Ops = paste0(unique(`Job Operator`), collapse = ","),
    ship.id = paste0(`Ship.ID`, collapse = ",")
  ) %>% arrange(desc(`Tot.Profit`)) 

The Output should be, please take a look at the Count & Job.Ops, this is what I wish to "combine/ breakdown" by operator.

# A tibble: 5 x 9
# Groups:   Job Status [5]
  `Job Status` Quarters Tot.Income   Tot.Expense  Tot.Profit  Mean.Profit  Count Job.Ops            ship.id                
  <chr>        <chr>    <S3: format> <S3: format> <S3: forma> <S3: format> <int> <chr>              <chr>                  
1 CMP          2020.2   $302,445.06  $302,445.06  $31,364.81  $482.54         65 Bart,Starlord,Gro~ S00047579,S00047691,S0~
2 INV          2020.2   $455,451.88  $455,451.88  $27,438.45  $342.98         80 Starlord,Groot,Ba~ S00047098,S00047548,S0~
3 WRK          2020.2   $68,960.51   $68,960.51   $6,578.22   $328.91         20 Sgt Price,Starlor~ S00047956,S00048035,S0~
4 WHL          2020.2   $9,355.10    $9,355.10    $986.50     $328.83          3 Sgt Price,Jon Doe~ S00048119,S00048518,S0~
5 RDD          2020.2   $937.88      $937.88      $312.00     $312.00          1 Money Penny        S00048598   

Interpreting the output for example, Quarter 2020.2 has 65 (Ship.ID under CMP status)
Now I could generate a new table to break who has how many under what status like such:

> y %>% group_by(`Job Operator`,
+                `Quarters`,
+                `Job Status`) %>% count() %>% print(n = Inf)
# A tibble: 24 x 4
# Groups:   Job Operator, Quarters, Job Status [24]
   `Job Operator` Quarters `Job Status`     n
   <chr>          <chr>    <chr>        <int>
 1 Bart           2020.2   CMP             45
 2 Bart           2020.2   INV             38
 3 Bart           2020.2   WRK              3
 4 Bond, James    2020.2   CMP              4
 5 Bond, James    2020.2   INV              7
 6 Bond, James    2020.2   WRK              6
 7 Groot          2020.2   CMP              4
 8 Groot          2020.2   INV              7
 9 Harry Potter   2020.2   CMP              2
10 Jon Doe10      2020.2   CMP              2
11 Jon Doe10      2020.2   WRK              1
12 Jon Doe12      2020.2   INV              1
13 Jon Doe12      2020.2   WHL              1
14 Jon Doe17      2020.2   CMP              1
15 Money Penny    2020.2   CMP              4
16 Money Penny    2020.2   RDD              1
17 Money Penny    2020.2   WRK              2
18 Sgt Price      2020.2   CMP              1
19 Sgt Price      2020.2   INV             21
20 Sgt Price      2020.2   WHL              2
21 Sgt Price      2020.2   WRK              3
22 Starlord       2020.2   CMP              2
23 Starlord       2020.2   INV              6
24 Starlord       2020.2   WRK              5

Wondering how to "combine" these two but for each Operator:

    Count = n(),
    Job.Ops = paste0(unique(`Job Operator`), collapse = ",")

To get something like so:

A tibble: 5 x 9
# Groups:   Job Status [5]
  `Job Status` Quarters Tot.Income   Tot.Expense  Tot.Profit  Mean.Profit  Count Job.Ops+IndvCount            ship.id                
  <chr>        <chr>    <S3: format> <S3: format> <S3: forma> <S3: format> <int> <chr>              <chr>                  
1 CMP          2020.2   $302,445.06  $302,445.06  $31,364.81  $482.54         65 Bart_46,Starlord_2,Gro~ S00047579,S00047691,S0~
2 INV          2020.2   $455,451.88  $455,451.88  $27,438.45  $342.98         80 Starlord,Groot,Ba~ S00047098,S00047548,S0~
3 WRK          2020.2   $68,960.51   $68,960.51   $6,578.22   $328.91         20 Sgt Price,Starlor~ S00047956,S00048035,S0~
4 WHL          2020.2   $9,355.10    $9,355.10    $986.50     $328.83          3 Sgt Price,Jon Doe~ S00048119,S00048518,S0~
5 RDD          2020.2   $937.88      $937.88      $312.00     $312.00          1 Money Penny        S00048598  

As seen above, I've manually added the count and operator name (just for Bart and Starlord in 2020.2 Quarter, cuz I'm lazy) , separated by an underscore to exemplify what I would like to achieve.

Any suggestions ?
Thank you for your time an assistance.

Best regards,
LF.

I think it's just a matter of creating the Job.Ops+IndvCount variable before summarization. Below I've just modified the Job Operator variable in-place but you can name it differently if required.

# Summary.
y %>% 
  group_by(`Job Operator`, `Quarters`, `Job Status`) %>% 
  mutate(n = n()) %>% 
  ungroup() %>% 
  mutate(`Job Operator` = paste(`Job Operator`, n, sep = "_")) %>% 
  group_by(`Job Status`,`Quarters`) %>% 
  summarise(Tot.Income = currency(sum(`Total Income (Recognized+Unrecognized REV+WIP)`)),
            Tot.Expense = currency(sum(`Total Expense (Recognized+Unrecognized CST+ACR)`)),
            Tot.Profit = currency(sum(`Job Profit`)),
            Mean.Profit = currency(mean(`Job Profit`)),
            Count = n(),
            Job.Ops = paste0(unique(`Job Operator`), collapse = ","),
            ship.id = paste0(`Ship.ID`, collapse = ",")) %>% 
  arrange(desc(`Tot.Profit`)) %>% 
  print(width = Inf)
#> # A tibble: 5 x 9
#> # Groups:   Job Status [5]
#>   `Job Status` Quarters Tot.Income  Tot.Expense Tot.Profit Mean.Profit Count
#>   <chr>        <chr>    <formttbl>  <formttbl>  <formttbl> <formttbl>  <int>
#> 1 CMP          2020.2   $302,445.06 $302,445.06 $31,364.81 $482.54        65
#> 2 INV          2020.2   $455,451.88 $455,451.88 $27,438.45 $342.98        80
#> 3 WRK          2020.2   $68,960.51  $68,960.51  $6,578.22  $328.91        20
#> 4 WHL          2020.2   $9,355.10   $9,355.10   $986.50    $328.83         3
#> 5 RDD          2020.2   $937.88     $937.88     $312.00    $312.00         1
#>   Job.Ops                                                                       
#>   <chr>                                                                         
#> 1 Bart_45,Starlord_2,Groot_4,Money Penny_4,Jon Doe17_1,Sgt Price_1,Harry Potter~
#> 2 Starlord_6,Groot_7,Bart_38,Bond, James_7,Jon Doe12_1,Sgt Price_21             
#> 3 Sgt Price_3,Starlord_5,Bart_3,Bond, James_6,Jon Doe10_1,Money Penny_2         
#> 4 Sgt Price_2,Jon Doe12_1                                                       
#> 5 Money Penny_1                                                                 
#>   ship.id                                                                       
#>   <chr>                                                                         
#> 1 S00047579,S00047691,S00047744,S00047746,S00047747,S00047748,S00047784,S000478~
#> 2 S00047098,S00047548,S00047549,S00047635,S00047639,S00047640,S00047641,S000476~
#> 3 S00047956,S00048035,S00048037,S00048134,S00048223,S00048272,S00048486,S000485~
#> 4 S00048119,S00048518,S00048701                                                 
#> 5 S00048598
1 Like

Thank you!
Learned something very useful today, being able to implement this in many ways!

Thank you for your time and assistance!
LF.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.