In the question table, a list of patients is provided who are scheduled to visit the doctor for consultations and surgery. We aim to extract the list of patient IDs, categorized by gender, who revisit after their surgery. For example, Patient P-05 was visited on February 17, 2024, following surgery on February 7, and appears in the results. However, Patient P-13 did not undergo surgery, and Patient P-07 did not return after their surgery.
📌 Challenge Details and Links
Challenge Number: 42
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Revisit After Surgery! with Power Query
Power Query solution 1 for Revisit After Surgery!, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ReType = Table.TransformColumnTypes(Source, {{"Date", type date}}),
Group = Table.Group(
ReType,
{"Patient-ID"},
{
{
"All",
each _,
type table [
Date = nullable date,
#"Patient-ID" = text,
#"Gander#(tab)" = text,
Referral = text
]
}
}
),
AddMeetsCond = Table.AddColumn(
Group,
"MeetsConditions",
each [
a = [All],
b = Table.SelectRows(a, each [Referral] = "Surgery"),
c = List.Min(b[Date]),
d = Table.SelectRows(a, each [Date] > c)
][d]
),
Filter = Table.RemoveColumns(Table.SelectRows(AddMeetsCond, each ([MeetsConditions] <> 0)), "All"),
Expand = Table.SelectRows(
Table.ExpandTableColumn(
Filter,
"MeetsConditions",
{"Patient-ID", "Gander#(tab)"},
{"Patient ID", "Gender"}
),
each [Gender] <> null
),
Regroup = Table.Group(
Expand,
{"Gender"},
{
{"Post Surgery Visits", each Table.RowCount(List.Distinct(_)), Int64.Type},
{"Patient ID", each [Patient ID], type list}
}
),
Extract = Table.TransformColumns(
Regroup,
{"Patient ID", each Text.Combine(List.Transform(List.Distinct(_), Text.From), ", "), type text}
)
in
ExtractPower Query solution 2 for Revisit After Surgery!, proposed by Aditya Kumar Darak 🇮🇳:
let
// header spelling Gender corrected
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
Group = Table.Group (
Source,
{ "Gender", "Patient-ID" },
{
"R",
each [
C = Text.Combine ( [Referral] ),
R = Text.Contains ( C, "VisitSurgeryVisit" )
or Text.Contains ( C, "VisitSurgerySurgery" )
][R]
}
),
Filter = Table.SelectRows ( Group, each [R] ),
Return = Table.Group (
Filter,
"Gender",
{ { "Count", Table.RowCount }, { "Patient", each Text.Combine ( [#"Patient-ID"], ", " ) } }
)
in
ReturnPower Query solution 3 for Revisit After Surgery!, proposed by Aditya Kumar Darak 🇮🇳:
let
// header spelling Gender corrected
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
Group = Table.Group (
Source,
{ "Gender", "Patient-ID" },
{
"R",
each [
P = List.PositionOf ( [Referral], "Surgery" ) + 1,
R = Table.RowCount ( _ ) > ( if P = 0 then 99 else P )
][R]
}
),
Filter = Table.SelectRows ( Group, each [R] ),
Return = Table.Group (
Filter,
"Gender",
{ { "Count", Table.RowCount }, { "Patient", each Text.Combine ( [#"Patient-ID"], ", " ) } }
)
in
ReturnPower Query solution 4 for Revisit After Surgery!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.SelectRows(Table.Group(Source, {"Patient-ID"}, {{"Gender", each
let
a = Table.Skip(_, each [Referral]<>"Surgery"),
b = Table.Skip(a, 1)
in Table.ToColumns(b){2}{0}?}}), each [Gender]<>null),
Sol = Table.Group(Group, {"Gender"}, {{"No of re-visit after surgery", each Table.RowCount(_)}, {"Patient ID", each Text.Combine([#"Patient-ID"], ", ")}})
in
SolPower Query solution 5 for Revisit After Surgery!, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Date", type date}, {"Patient-ID", type text}, {"Gender", type text}, {"Referral", type text}}
),
#"Merged Queries" = Table.NestedJoin(
Table.SelectRows(#"Changed Type", each ([Referral] = "Surgery")),
{"Patient-ID"},
#"Changed Type",
{"Patient-ID"},
"Filtered Rows",
JoinKind.LeftOuter
),
VisitsAfterSurgery = List.RemoveNulls(
Table.AddColumn(
#"Merged Queries",
"Custom",
each Table.Min(
Table.SelectRows([Filtered Rows], (row) => row[Referral] = "Visit" and row[Date] > [Date]),
{"Date"}
)
)[Custom]
),
TblFromRecords = Table.FromRecords(VisitsAfterSurgery),
#"Grouped Rows" = Table.Group(
TblFromRecords,
{"Gender"},
{
{"Number of Visits", each Table.RowCount(_), Int64.Type},
{"Patient ID", each Text.Combine([#"Patient-ID"], ", "), Int64.Type}
}
)
in
#"Grouped Rows"Solving the challenge of Revisit After Surgery! with Excel
Excel solution 1 for Revisit After Surgery!, proposed by Bo Rydobon 🇹🇭:
=LET(a,GROUPBY(SORT(C2:D27,,,1),E2:E27,LAMBDA(x,--IFNA(MATCH("S*",x,)<ROWS(x),0)),1,0),VSTACK(I2:K2,DROP(GROUPBY(TAKE(a,,1),SORT(DROP(a,,1),,,1),HSTACK(SUM,ARRAYTOTEXT),0,0,-1,DROP(a,,2)),1)))Excel solution 2 for Revisit After Surgery!, proposed by محمد حلمي:
=LET(r,
REDUCE(0,
UNIQUE(
C3:C27
),
LAMBDA(a,
v,
LET(x,
FILTER(
D3:E27,
C3:C27=v
),
i,
SCAN(
0,
DROP(
x,
,
1
)=E3, LAMBDA(
a,
d,
IF(
d,
a+d,
0
)
)
),
r,
ROWS(
i
),
m,
MAX(
i
),VSTACK(a,
IF((r>2)*(r>m),
HSTACK(
@x,
m,
v
)))))),REDUCE(
P2:R2,
D4:D5,
LAMBDA(
a,
v, LET(
d,
FILTER(
DROP(
r,
,
1
),
TAKE(
r,
,
1
)=v
),
VSTACK(
a,
HSTACK(
v,
MAX(
TAKE(
d,
,
1
)
),
TEXTJOIN(
", ",
,
DROP(
d,
,
1
)
)
)
)
)
)
))Excel solution 3 for Revisit After Surgery!, proposed by 🇵🇪 Ned Navarrete C.:
=LET(
d,
C3:C27,
g,
D3:D27,
u,
UNIQUE(
d
),
r,
FILTER(
u,
MAP(
u,
LAMBDA(
p,
LET(
m,
FILTER(
E3:E27,
d=p
),
n,
CONCAT(
LEFT(
m
)
),
SUM(
--ISNUMBER(
FIND(
{"SV",
"SS"},
n
)
)
)
)
)
)
),
x,
INDEX(
g,
XMATCH(
r,
d
)
),
REDUCE(
I2:K2,
UNIQUE(
g
),
LAMBDA(
c,
v,
LET(
i,
FILTER(
r,
x=v
),
VSTACK(
c,
HSTACK(
v,
ROWS(
i
),
ARRAYTOTEXT(
i
)
)
)
)
)
)
)Excel solution 4 for Revisit After Surgery!, proposed by Oscar Mendez Roca Farell:
=LET(
u,
UNIQUE(
C3:C27
),
m,
MAP(
u,
LAMBDA(
a,
COUNT(
FIND(
"SV",
CONCAT(
REPT(
LEFT(
E3:E27
),
C3:C27=a
)
)
)
)
)
),
t,
FILTER(
UNIQUE(
C3:D27
),
m
),
REDUCE(
I2:K2,
UNIQUE(
D3:D27
),
LAMBDA(
i,
x,
LET(
r,
REPT(
DROP(
t,
,
-1
),
DROP(
t,
,
1
)=x
),
VSTACK(
i,
HSTACK(
x,
SUM(
N(
r>""
)
),
TEXTJOIN(
", ",
,
r
)
)
)
)
)
)
)Excel solution 5 for Revisit After Surgery!, proposed by Julian Poeltl:
=LET(T,
B2:E27,
TT,
DROP(
T,
1,
1
),
I,
TAKE(
TT,
,
1
),
R,
TAKE(
TT,
,
-1
),
G,
CHOOSECOLS(
TT,
2
),
UP,
SORT(
UNIQUE(
I
)
),
V,
IFERROR(MAP(UP,
LAMBDA(A,
LET(F,
FILTER(
R,
I=A
),
SUM(--(DROP(
F,
XMATCH(
"Surgery",
F
)
)="Visit"))))),
0),
U,
XLOOKUP(
UP,
I,
G
)&","&V,
TR,
TEXTSPLIT(
TEXTJOIN(
";",
,
UNIQUE(
U
)
),
",",
";"
),
FT,
FILTER(
TR,
TAKE(
TR,
,
-1
)*1>0
),
PID,
BYROW(
FT,
LAMBDA(
A,
TEXTJOIN(
",",
,
FILTER(
UP,
U=TEXTJOIN(
",",
,
A
)
)
)
)
),
VSTACK(
HSTACK(
"Gender",
"No of re-visit after surgery",
"Patient ID"
),
HSTACK(
FT,
PID
)
))Excel solution 6 for Revisit After Surgery!, proposed by Kris Jaganah:
=LET(a,
B3:B27,
b,
C3:C27,
c,
D3:D27,
d,
E3:E27,
e,
UNIQUE(
b
),
f,
XLOOKUP(
e&"Surgery",
b&d,
a,
0
),
g,
XLOOKUP(
e,
b,
c
),
h,
IF(f>0,
MAP(e,
f,
LAMBDA(x,
y,
COUNT(FILTER(a,
(b=x)*(a>y),
"")))),
0),
DROP(
GROUPBY(
g,
e,
HSTACK(
COUNTA,
ARRAYTOTEXT
),
0,
0,
-1,
h>0
),
1
))Excel solution 7 for Revisit After Surgery!, proposed by John Jairo Vergara Domínguez:
=LET(
g,
GROUPBY(
C3:D27,
E3:E27,
CONCAT,
,
0
),
z,
DROP(
g,
,
2
),
DROP(
GROUPBY(
INDEX(
g,
,
2
),
TAKE(
g,
,
1
),
HSTACK(
ROWS,
ARRAYTOTEXT
),
,
0,
-1,
LEN(
z
)>6+IFERROR(
FIND(
"Surgery",
z
),
99
)
),
1
)
)Excel solution 8 for Revisit After Surgery!, proposed by Sunny Baggu:
=LET(
_p,
SORT(
UNIQUE(
C3:C27
)
), _cri,
MAP( _p, LAMBDA(
x,
LET(
_a,
FILTER(
E3:E27,
C3:C27 = x
),
_b,
XMATCH(
"Surgery",
_a
),
IFERROR(
OR(
INDEX(
_a,
_b + 1
) = {"Surgery",
"Visit"}
),
FALSE
)
)
) ), _g,
XLOOKUP(
_p,
C3:C27,
D3:D27
), _ug,
UNIQUE(
_g
), _c1,
MAP(_ug,
LAMBDA(a,
SUM((_g = a) * _cri))), _c2,
MAP(_ug,
LAMBDA(b,
ARRAYTOTEXT(FILTER(_p,
_cri * (_g = b))))), HSTACK(
_ug,
_c1,
_c2
)
)Excel solution 9 for Revisit After Surgery!, proposed by Hussein SATOUR:
=TEXTSPLIT(CONCAT(MAP(G3:G4,
LAMBDA(y,
LET(p,
C3:C27,
a,
FILTER(
p,
D3:D27=y
),
b,
MAP(a,
LAMBDA(x,
AND(SUM((a=x)*1)>1,
IFERROR(
XMATCH(
"Surgery",
DROP(
FILTER(
E3:E27,
p=x
),
-1
)
),
0
)>0))),
c,
UNIQUE(
FILTER(
a,
b
)
),
COUNTA(
c
)&"|"&TEXTJOIN(
",",
,
c
)&"/")))),
"|",
"/",
1)Excel solution 10 for Revisit After Surgery!, proposed by Josh Brodrick:
=LET(
pt,
SORT(
UNIQUE(
B3:C27,
1,
TRUE
)
),vol,
MAP(
CHOOSECOLS(
pt,
1
),
LAMBDA(
x,
TEXTAFTER(
CONCAT(
CHOOSECOLS(
SORT(
FILTER(
$A$3:$D$27,
$B$3:$B$27=x
),
1
),
-1
)
),
"Surgery",
,
,
,
""
)
)
),r,
DROP(
UNIQUE(
FILTER(
HSTACK(
pt,
vol
),
CHOOSECOLS(
HSTACK(
pt,
vol
),
3
)<>""
)
),
,
-1
),m,
HSTACK("Male",
COUNTA(
DROP(
FILTER(
r,
CHOOSECOLS(
r,
2
)<>"Female"
),
,
-1
)
),
TEXTJOIN(",",
,
(DROP(
FILTER(
r,
CHOOSECOLS(
r,
2
)<>"Female"
),
,
-1
)))),f,
HSTACK("Female",
COUNTA(
DROP(
FILTER(
r,
CHOOSECOLS(
r,
2
)="Female"
),
,
-1
)
),
TEXTJOIN(",",
,
(DROP(
FILTER(
r,
CHOOSECOLS(
r,
2
)="Female"
),
,
-1
)))),VSTACK(
{"Gender",
"No of revisit after surgery",
"Patient ID"},
m,
f
))Excel solution 11 for Revisit After Surgery!, proposed by Rayan Saud:
=LET(
id,
UNIQUE(
C3:C27
), g,
D3:D27, Ug,
UNIQUE(
g
), Pg,
XLOOKUP(
id,
C3:C27,
g,
0,
0
), l,
TEXTSPLIT( TEXTAFTER(
MAP(
id,
LAMBDA(
x,
TEXTJOIN(
",",
,
FILTER(
E3:E27,
C3:C27 = x
)
)
)
),
"Surgery,",
1
), "," ), v,
MAP(
l,
LAMBDA(
x,
IF(
ISERROR(
x
),
0,
COUNTA(
x
)
)
)
), HSTACK(
Ug, MAP(
Ug,
LAMBDA(
x,
SUM(
FILTER(
v,
Pg = x
)
)
)
), MAP(Ug,
LAMBDA(x,
TEXTJOIN(", ",
,
FILTER(id,
((Pg = x)) * (v <> 0)))))
)
)Solving the challenge of Revisit After Surgery! with Python
Python solution 1 for Revisit After Surgery!, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
input = pd.read_excel('CH-042 Revisit after surgury.xlsx', usecols="B:E", skiprows=1, nrows = 27)
test = pd.read_excel('CH-042 Revisit after surgury.xlsx', usecols="I:K", skiprows=1, nrows = 2)
result = input.groupby(['Patient-ID', 'Gandert']).apply(lambda x: ' -> '.join(x['Referral'])).reset_index(name='seq')
result = result[result['seq'].str.contains('Surgery ->')]
result = result.groupby('Gandert').agg({'seq': 'count', 'Patient-ID': lambda x: ', '.join(sorted(x))}).rename(columns={'seq': 'No of re-visit after surgery', 'Patient-ID': 'Patient ID'}).reset_index()
result = result.rename(columns={'Gandert': 'Gender'}).sort_values(by= 'Gender', ascending=False).reset_index(drop=True)
print(result.equals(test)) # TrueSolving the challenge of Revisit After Surgery! with R
R solution 1 for Revisit After Surgery!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel('files/CH-042 Revisit after surgury.xlsx', range = "B2:E27")
test = read_excel('files/CH-042 Revisit after surgury.xlsx', range = "I2:K4")
result = input %>%
group_by(`Patient-ID`, Gander) %>%
arrange(Date) %>%
summarise(seq = paste0(Referral, collapse = ' -> ')) %>%
ungroup() %>%
filter(str_detect(seq, "Surgery -> ")) %>%
summarise('No of re-visit after surgery' = n() %>% as.numeric(),
'Patient ID' = paste0(sort(`Patient-ID`), collapse = ', '),
.by = Gander) %>%
select(Gender = Gander, everything())