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.