Find the unique repeat customers in a year. A repeat customer is that unique customer who does shopping in the same store more than once in the same calendar year. Ex. For year 2021, customer D shops more than once in store 3 and customer O shops more than once in store 1. Hence, there are 2 repeat customers for year 2021. For year 2024, O shops more than once in stores 1 and 2. Hence, count is 1 as it is the same customer who shopped in two different stores more than once. The problem asks for unique customer count.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 636
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Identify Yearly Repeat Customers with Power Query
Power Query solution 1 for Identify Yearly Repeat Customers, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.Transform(Table.ToRows(A), each {Date.Year(_{0}), _{1} & "-" & _{2}}),
C = List.Zip(B),
D = List.Distinct(C{0}),
E = List.RemoveNulls(
List.TransformMany(
D,
each List.Distinct(C{1}),
(x, y) =>
if List.Count(List.Select(B, (v) => (v{0} = x) and (v{1} = y))) > 1 then
{x, Text.AfterDelimiter(y, "-")}
else
null
)
),
F = List.Transform(
D,
(x) =>
[
a = List.Distinct(List.Zip(List.Select(E, each _{0} = x)){1}),
Count = List.Count(a),
Customers = Text.Combine(List.Sort(a), ", "),
Year = x
][[Year], [Count], [Customers]]
),
G = Table.FromRecords(F)
in
GPower Query solution 2 for Identify Yearly Repeat Customers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grp = Table.Group(
Source,
"Date",
{
{
"A",
each
let
a = _,
b = Table.Group(a, {"Customer", "Store"}, {"B", each Table.RowCount([Customer])}),
c = List.Sort(List.Distinct(Table.SelectRows(b, each [B] > 1)[Customer])),
d = Table.FromRows({{List.Count(c), Text.Combine(c, ", ")}}, {"Count", "Customers"})
in
d
}
},
0,
(a, b) => Number.From(Date.Year(a) <> Date.Year(b))
)[[Date], [A]],
Exp = Table.ExpandTableColumn(Grp, "A", Table.ColumnNames(Grp[A]{0})),
Sol = Table.TransformColumns(Exp, {"Date", each Date.Year(_)})
in
SolPower Query solution 3 for Identify Yearly Repeat Customers, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(Source, "Year", each Date.Year([Date])),
Group1 = Table.Group(AddCol, {"Year", "Customer", "Store"}, {"Count", each Table.RowCount(_)}),
Group2 = Table.Group(
Group1,
"Year",
{
"Data",
each [
a = Table.SelectRows(_, (x) => x[Count] > 1),
b = List.Distinct(List.Sort(a[Customer])),
c = [Count = List.Count(b), Customers = Text.Combine(b, ", ")]
][c]
}
),
Result = Table.ExpandRecordColumn(Group2, "Data", {"Count", "Customers"})
in
ResultPower Query solution 4 for Identify Yearly Repeat Customers, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
InsertYear = Table.AddColumn(Source, "Year", each Date.Year([Date])),
Group = Table.Group(InsertYear, {"Year", "Store", "Customer"}, {"Count", each Table.RowCount(_)}),
Res = Table.Group(
Table.SelectRows(Group, each [Count] > 1),
"Year",
{{"Count", Rec[B]}, {"Customers", Rec[C]}}
),
Rec = [
A = each List.Sort(List.Distinct([Customer])),
B = each List.Count(A(_)),
C = each Text.Combine(A(_), ", ")
]
in
ResPower Query solution 5 for Identify Yearly Repeat Customers, proposed by Ankur Sharma:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Store", type text}, {"Customer", type text}}),
ExtractedYear = Table.TransformColumns(#"Changed Type",{{"Date", Date.Year}}),
GroupedByYrStCus = Table.Group(ExtractedYear, {"Date", "Store", "Customer"}, {{"Count", each Table.RowCount(_)}}),
GreaterThan1 = Table.SelectRows(GroupedByYrStCus, each [Count] > 1),
RemDupByYrCust = Table.Distinct(GreaterThan1, {"Date", "Customer"}),
GroupedByYr = Table.Group(RemDupByYrCust, {"Date"}, {{"Count", each Table.RowCount(_)}, {"Customer", each Text.Combine(List.Sort(_[Customer]), ", ")}})
in
GroupedByYr
Best Wishes!
Power Query solution 6 for Identify Yearly Repeat Customers, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = Table.TransformColumns(Source, {"Date", each Date.Year(_)}),
Result = Table.Combine(
Table.Group(
Transform,
"Date",
{
{
"New",
each [
Lst = Table.SelectRows(
Table.Group(_, {"Customer", "Store"}, {{"Count", each Table.RowCount(_)}}),
(f) => f[Count] > 1
)[Customer],
fin = Table.FromRows(
{
{
_[Date]{0},
List.Count(List.Distinct(Lst)),
Text.Combine(List.Sort(List.Distinct(Lst)), ", ")
}
},
{"Year", "Count", "Customers"}
)
][fin]
}
}
)[New]
)
in
ResultPower Query solution 7 for Identify Yearly Repeat Customers, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Prob"]}[Content],
Year = Table.RenameColumns(
Table.TransformColumns(Source, {{"Date", Date.Year}}),
{"Date", "Year"}
),
Grp = Table.Group(Year, {"Year", "Store"}, {{"Grp", each _}}),
FxA = (t) =>
let
A = List.Distinct(
Table.SelectRows(t, (m) => List.Count(List.Select(t[Customer], each _ = m[Customer])) > 1)[
Customer
]
)
in
A,
Func = Table.ExpandListColumn(Table.TransformColumns(Grp, {"Grp", each FxA(_)}), "Grp"),
NoNull = Table.Distinct(
Table.RemoveColumns(Table.SelectRows(Func, each ([Grp] <> null)), "Store")
),
Result = Table.Group(
NoNull,
{"Year"},
{
{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type},
{"Customers", each Text.Combine(_[Grp], ", ")}
}
)
in
ResultPower Query solution 8 for Identify Yearly Repeat Customers, proposed by Alexandre Garcia:
let
H = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
P = Table.TransformColumns(H, {"Date", each Date.Year(_)}),
L = {List.Count, List.Distinct},
C = Table.Pivot(
P,
List.Sort(L{1}(P[Customer])),
"Customer",
"Store",
(x) => L{0}(x) > L{0}(L{1}(x))
),
M = List.TransformMany(
Table.ToRows(C),
each {List.Transform(List.PositionOf(_, true, 2), each Table.ColumnNames(C){_})},
(x, y) => {x{0}, L{0}(y), Text.Combine(y, ", ")}
),
S = Table.FromRows(M, {"Year", "Count", "Customers"})
in
SPower Query solution 9 for Identify Yearly Repeat Customers, proposed by Nicolas Micot:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
#"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Type modifié" = Table.TransformColumnTypes(
#"En-têtes promus",
{{"Date", type date}, {"Store", type text}, {"Customer", type text}}
),
#"Année insérée" = Table.AddColumn(#"Type modifié", "Year", each Date.Year([Date]), Int64.Type),
#"Lignes groupées" = Table.Group(
#"Année insérée",
{"Year", "Store", "Customer"},
{{"Nb visite", each Table.RowCount(_), Int64.Type}}
),
#"Lignes filtrées" = Table.SelectRows(#"Lignes groupées", each [Nb visite] > 1),
#"Lignes groupées1" = Table.Group(
#"Lignes filtrées",
{"Year"},
{
{"Count", each Table.RowCount(_), Int64.Type},
{"Customers", each Text.Combine([Customer], ", "), type nullable text}
}
)
in
#"Lignes groupées1"Solving the challenge of Identify Yearly Repeat Customers with Excel
Excel solution 1 for Identify Yearly Repeat Customers, proposed by Bo Rydobon 🇹🇭:
=LET(g,GROUPBY(HSTACK(YEAR(A3:A90),C3:C90,B3:B90),A3:A90,ROWS,,0),h,UNIQUE(FILTER(TAKE(g,,2),DROP(g,,3)>1)),DROP(GROUPBY(TAKE(h,,1),DROP(h,,1),HSTACK(ROWS,ARRAYTOTEXT),,0),1))Excel solution 2 for Identify Yearly Repeat Customers, proposed by John V.:
=LET(
y,
YEAR(
A3:A90
),
c,
C3:C90,
d,
y&B3:B90&c,
u,
UNIQUE,
n,
BYROW(
N(
d=TOROW(
d
)
),
SUM
),
GROUPBY(
y,
c,
HSTACK(
LAMBDA(
i,
ROWS(
u(
i
)
)
),
LAMBDA(
i,
ARRAYTOTEXT(
u(
SORT(
i
)
)
)
)
),
,
0,
,
n>1
)
)Excel solution 3 for Identify Yearly Repeat Customers, proposed by 🇰🇷 Taeyong Shin:
=LET(g,GROUPBY(HSTACK(YEAR(A3:A90),B3:C90),C3:C90,ROWS,,0),F,LAMBDA(fn,LAMBDA(x,fn(UNIQUE(SORT(x))))),DROP(GROUPBY(TAKE(g,,1),INDEX(g,,3),HSTACK(F(ROWS),F(ARRAYTOTEXT)),,0,,TAKE(g,,-1)>1),1))Excel solution 4 for Identify Yearly Repeat Customers, proposed by Kris Jaganah:
=LET(a,
YEAR(
A3:A90
),
b,
B3:B90,
c,
C3:C90,
d,
UNIQUE(
a
),
e,
MAP(a,
b,
c,
LAMBDA(x,
y,
z,
SUM((a=x)*(b=y)*(c=z)))),
REDUCE({"Year",
"Count",
"Customers"},
d,
LAMBDA(u,
v,
VSTACK(u,
LET(p,
SORT(UNIQUE(FILTER(c,
(a=v)*(e>1)))),
HSTACK(
v,
COUNTA(
p
),
ARRAYTOTEXT(
p
)
))))))Excel solution 5 for Identify Yearly Repeat Customers, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_date,
A3:A90,
_store,
B3:B90,
_customer,
C3:C90,
_grp1,
GROUPBY(
HSTACK(
YEAR(
_date
),
_store,
_customer
),
_customer,
ROWS,
0,
0
),
_grp2,
GROUPBY(
TAKE(
_grp1,
,
1
),
INDEX(
_grp1,
,
3
),
HSTACK(
ROWS,
LAMBDA(
a,
ARRAYTOTEXT(
UNIQUE(
a
)
)
)
),
0,
0,
,
TAKE(
_grp1,
,
-1
) > 1
),
_rtrn,
DROP(
_grp2,
1
),
_rtrn
)Excel solution 6 for Identify Yearly Repeat Customers, proposed by Timothée BLIOT:
=LET(A,A3:A90,B,B3:C90,J,LAMBDA(n,TEXTJOIN(", ",,n)),F,FILTER,DROP(REDUCE(0,SEQUENCE(5,,2021),LAMBDA(w,v,LET(C,F(B,YEAR(A)=v),D,SORT(UNIQUE(TEXTSPLIT(J(TOCOL(MAP(SEQUENCE(3),LAMBDA(x,LET(E,F(TAKE(C,,-1),TAKE(C,,1)="S"&x),J(UNIQUE(F(E,MAP(E,LAMBDA(y,SUM(--(y=E))))>1)))))),3)),,", "))),VSTACK(w,HSTACK(v,ROWS(D),J(D)))))),1))Excel solution 7 for Identify Yearly Repeat Customers, proposed by Hussein SATOUR:
=LET(W,
CHOOSECOLS,
b,
IFERROR(
YEAR(
A3:C90
),
A3:C90
),
a,
BYROW(
b,
CONCAT
),
c,
FILTER(b,
MAP(a,
LAMBDA(x,
SUM((a=x)*1)))>1),
d,
UNIQUE(
W(
c,
1,
3
)
),
GROUPBY(
W(
d,
1
),
W(
d,
2
),
HSTACK(
COUNTA,
ARRAYTOTEXT
)
))Excel solution 8 for Identify Yearly Repeat Customers, proposed by Oscar Mendez Roca Farell:
=LET(H,HSTACK,U,UNIQUE,F,GROUPBY,g,F(H(YEAR(A3:A90),B3:C90),C3:C90,ROWS,,0),DROP(F(TAKE(g,,1),INDEX(g,,3),H(LAMBDA(n,ROWS(U(n))),LAMBDA(n,ARRAYTOTEXT(SORT(U(n))))),,0,,DROP(g,,3)>1),1))Excel solution 9 for Identify Yearly Repeat Customers, proposed by Duy Tùng:
=LET(a,
YEAR(
A3:A90
),
b,
B3:B90,
c,
C3:C90,
U,
UNIQUE,
DROP(GROUPBY(a,
c,
HSTACK(
LAMBDA(
x,
ROWS(
U(
x
)
)
),
LAMBDA(
x,
ARRAYTOTEXT(
SORT(
U(
x
)
)
)
)
),
,
0,
,
MAP(a,
b,
c,
LAMBDA(x,
y,
z,
SUM((a=x)*(b=y)*(c=z))))>1),
1))Excel solution 10 for Identify Yearly Repeat Customers, proposed by Sunny Baggu:
=LET(
_y,
YEAR(
A3:A90
),
_uy,
UNIQUE(
_y
),
REDUCE(
{"Year",
"Count",
"Customers"},
_uy,
LAMBDA(x,
y,
VSTACK(
x,
LET(
_st,
FILTER(
B3:C90,
_y = y
),
_a,
UNIQUE(
TAKE(
_st,
,
-1
)
),
_b,
TOROW(
UNIQUE(
TAKE(
_st,
,
1
)
)
),
_st1,
TAKE(
_st,
,
1
),
_st2,
TAKE(
_st,
,
-1
),
_f,
BYROW(
N(
MAKEARRAY(
ROWS(
_a
),
COLUMNS(
_b
),
LAMBDA(r,
c,
SUM((_st1 = INDEX(
_b,
,
c
)) * (_st2 = INDEX(
_a,
r,
))))
) > 1
),
LAMBDA(
s,
SUM(
s
)
)
),
HSTACK(
y,
SUM(
N(
_f <> 0
)
),
ARRAYTOTEXT(
SORT(
FILTER(
_a,
_f
)
)
)
)
)
)
)
)
)Excel solution 11 for Identify Yearly Repeat Customers, proposed by LEONARD OCHEA 🇷🇴:
=LET(
a,
A3:A90,
b,
B3:B90,
c,
C3:C90,
y,
YEAR(
a
),
d,
y&b&c,
m,
BYROW(
N(
d=TOROW(
d
)
),
SUM
),
U,
UNIQUE,
GROUPBY(
y,
c,
HSTACK(
LAMBDA(
w,
COUNTA(
U(
w
)
)
),
LAMBDA(
x,
ARRAYTOTEXT(
SORT(
U(
x
)
)
)
)
),
,
0,
,
m>1
)
)Excel solution 12 for Identify Yearly Repeat Customers, proposed by Md. Zohurul Islam:
=LET(u,A3:A90,v,B3:B90,w,C3:C90,z,HSTACK(YEAR(u),v,w),hdr,HSTACK("Year","Count","Customers"),a,GROUPBY(z,w,COUNTA,0,0),
b,CHOOSECOLS(FILTER(a,DROP(a,,3)>1),1,3),c,UNIQUE(DROP(b,,-1)),d,REDUCE(hdr,c,LAMBDA(x,y,LET(p,SORT(UNIQUE(FILTER(TAKE(b,,-1),DROP(b,,-1)=y))),q,COUNTA(p),s,HSTACK(y,q,ARRAYTOTEXT(p)),VSTACK(x,s)))),d)Excel solution 13 for Identify Yearly Repeat Customers, proposed by Pieter de B.:
=LET(
a,
SORT(
A3:C90,
3
),
c,
CHOOSECOLS,
g,
GROUPBY(
HSTACK(
YEAR(
c(
a,
1
)
),
c(
a,
2
),
c(
a,
3
)
),
c(
a,
3
),
ROWS,
,
0
),
x,
GROUPBY(
c(
g,
1
),
c(
g,
3
),
HSTACK(
ROWS,
ARRAYTOTEXT
),
,
0,
,
c(
g,
4
)>1
),
DROP(
GROUPBY(
c(
g,
1
),
c(
g,
3
),
HSTACK(
LAMBDA(
x,
ROWS(
UNIQUE(
x
)
)
),
LAMBDA(
x,
ARRAYTOTEXT(
UNIQUE(
x
)
)
)
),
,
0,
,
c(
g,
4
)>1
),
1
)
)Excel solution 14 for Identify Yearly Repeat Customers, proposed by Hamidi Hamid:
=LET(g,GROUPBY(HSTACK(YEAR(A3:A90),B3:C90),C3:C90,COUNTA,,0),f,MAP(UNIQUE(YEAR(A3:A90)),LAMBDA(a,TEXTJOIN(",",,UNIQUE(SORT(FILTER(CHOOSECOLS(g,3),(TAKE(g,,1)=a)*(TAKE(g,,-1)>1))))))),HSTACK(UNIQUE(YEAR(A3:A90)),LEN(SUBSTITUTE(f,",","")),f))Excel solution 15 for Identify Yearly Repeat Customers, proposed by Asheesh Pahwa:
=LET(
d,
YEAR(
A3:A90
),
u,
UNIQUE(
d
),
REDUCE(
E2:G2,
u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
B3:C90,
d=y
),
t,
TAKE(
f,
,
1
),
_u,
UNIQUE(
t
),
_t,
TEXTJOIN(
",",
1,
UNIQUE(
MAP(
_u,
LAMBDA(
a,
LET(
fl,
FILTER(
DROP(
f,
,
1
),
t=a
),
un,
UNIQUE(
fl
),
m,
MAP(
un,
LAMBDA(
v,
COUNTA(
FILTER(
fl,
fl=v,
""
)
)>1
)
),
ARRAYTOTEXT(
FILTER(
un,
m,
""
)
)
)
)
)
)
),
HSTACK(
y,
MAP(
_t,
LAMBDA(
a,
COUNTA(
TEXTSPLIT(
a,
","
)
)
)
),
_t
)
)
)
)
)
)Excel solution 16 for Identify Yearly Repeat Customers, proposed by Ankur Sharma:
=LET(g, GROUPBY, u, UNIQUE, j, TEXTJOIN, k, TAKE,
d, HSTACK(YEAR(A3:A90), B3:C90),
a_1, g(d, k(d, , -1), COUNTA, , 0),
WRAPROWS(
TEXTSPLIT(
j(" - ", ,
g(k(a_1, , 1), CHOOSECOLS(a_1, 3), LAMBDA(z, j(" - ", , COUNTA(SORT(u(z))), ARRAYTOTEXT(SORT(u(z))))), , 0, , k(a_1, , -1) > 1)),
" - "),
3))Excel solution 17 for Identify Yearly Repeat Customers, proposed by Meganathan Elumalai:
=LET(
g,
GROUPBY(
HSTACK(
YEAR(
A3:A90
),
B3:C90
),
C3:C90,
ROWS,
,
0
),
DROP(
GROUPBY(
TAKE(
g,
,
1
),
INDEX(
g,
,
3
),
HSTACK(
LAMBDA(
x,
ROWS(
UNIQUE(
x
)
)
),
LAMBDA(
n,
TEXTJOIN(
", ",
,
SORT(
UNIQUE(
n
)
)
)
)
),
0,
0,
,
TAKE(
g,
,
-1
)>1
),
1
)
)Excel solution 18 for Identify Yearly Repeat Customers, proposed by Imam Hambali:
=LET(
d,
YEAR(
A3:A90
),
s,
B3:B90,
c,
C3:C90,
g,
GROUPBY(
HSTACK(
d,
s,
c
),
d&s&c,
COUNTA,
0,
0
),
cc,
CHOOSECOLS,
u,
UNIQUE(
SORT(
FILTER(
HSTACK(
cc(
g,
1
),
cc(
g,
3
)
),
cc(
g,
-1
)>1
),
{1,
2},
{1,
1}
)
),
VSTACK(
{"Date",
"Store",
"Customer"},
DROP(
GROUPBY(
cc(
u,
1
),
cc(
u,
2
),
HSTACK(
COUNTA,
ARRAYTOTEXT
),
0,
0
),
1
)
)
)Solving the challenge of Identify Yearly Repeat Customers with Python
Python solution 1 for Identify Yearly Repeat Customers, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "636 Repeat Customers in a Year.xlsx"
input = pd.read_excel(path, usecols="A:C", skiprows=1, nrows=88)
test = pd.read_excel(path, usecols="E:G", skiprows=1, nrows=5)
input['Year'] = pd.DatetimeIndex(input['Date']).year
repeat_customers = (input.groupby(['Year', 'Customer', 'Store'])
.size()
.reset_index(name='n')
.query('n > 1')
.groupby('Year')
.agg(Count=('Customer', 'nunique'),
Customers=('Customer', lambda x: ', '.join(sorted(x.unique()))))
.reset_index())
print(all(test == repeat_customers)) # True
Python solution 2 for Identify Yearly Repeat Customers, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_636 - Repeat Customers in a Year.xlsx'
df = pd.read_excel(io=file_path, usecols='A:C', skiprows=1)
# Perform data manipulation
df = (
df
.assign(Year = df.Date.dt.year)
.groupby(['Year', 'Customer', 'Store']).agg(Count = ('Date', 'count'))
.reset_index()[lambda x: x.Count > 1]
.groupby('Year').agg(
Count = ('Customer', lambda x: len(x.unique())),
Customers = ('Customer', lambda x: ', '.join(sorted(x.unique())))
)
.reset_index()
)
df
Solving the challenge of Identify Yearly Repeat Customers with Python in Excel
Python in Excel solution 1 for Identify Yearly Repeat Customers, proposed by Alejandro Campos:
df = xl("A2:C90", headers=True)
df['Year'] = pd.to_datetime(df['Date'], format='%d/%m/%Y').dt.year
repeat_customers = df.groupby(['Year', 'Store', 'Customer']).size().reset_index(name='Count').query('Count > 1')
unique_repeat_customers_per_year = repeat_customers.groupby('Year')['Customer'].apply(lambda x: ', '.join(x.unique())).reset_index(name='Customer')
unique_repeat_customers_per_year['Count'] = unique_repeat_customers_per_year['Customer'].str.count(', ') + 1
unique_repeat_customers_per_year
Python in Excel solution 2 for Identify Yearly Repeat Customers, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A2:C90", True)
result = (
df.groupby([df["Date"].dt.year, "Store", "Customer"])
.size()
.reset_index(name="Count")
.query("Count > 1")
.drop_duplicates(subset=["Date", "Customer"])
.groupby("Date")
.agg(
Count=("Count", "size"), Customers=("Customer", lambda x: ", ".join(sorted(x)))
)
.reset_index()
.rename(columns={"Date": "Year"})
)
Python in Excel solution 3 for Identify Yearly Repeat Customers, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A2:C90", True)
grp = (
df.groupby([df["Date"].dt.year, "Store", "Customer"])
.size()
.reset_index(name="Count")
)
fltr = grp[grp["Count"] > 1].drop_duplicates(["Date", "Customer"])
result = (
fltr.groupby("Date")
.agg(
Count=("Count", "size"), Customers=("Customer", lambda x: ", ".join(sorted(x)))
)
.reset_index()
)
result = result.rename(columns={"Date": "Year"})
result
Python in Excel solution 4 for Identify Yearly Repeat Customers, proposed by Seokho MOON:
df["Year"] = df["Date"].dt.year
res = (
df.groupby(["Year", "Store", "Customer"])
.filter(lambda x: len(x) > 1)
.groupby(["Year"])
.agg(
Count=("Customer", lambda x: x.nunique()),
Customers=("Customer", lambda x: ", ".join(sorted(x.unique())))
)
.reset_index()
)
Solving the challenge of Identify Yearly Repeat Customers with R
R solution 1 for Identify Yearly Repeat Customers, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/636 Repeat Customers in a Year.xlsx"
input = read_excel(path, range = "A2:C90")
test = read_excel(path, range = "E2:G7")
repeat_customers = input %>%
mutate(Year = year(Date)) %>%
summarise(n = n(), .by = c(Year, Customer,Store)) %>%
filter(n > 1) %>%
summarise(
Count = n_distinct(Customer),
Customers = paste0(unique(sort(Customer)), collapse = ", "),
.by = c(Year))
all.equal(repeat_customers, test)
#> [1] TRUE
R solution 2 for Identify Yearly Repeat Customers, proposed by Seokho MOON:
R
library(tidyverse)
df %>%
mutate(Year = year(Date)) %>%
group_by(Year, Store, Customer) %>%
filter(n() > 1) %>%
ungroup() %>%
group_by(Year) %>%
summarise(
Count = n_distinct(Customer),
Customers = paste(sort(unique(Customer)), collapse = ", ")
)
&&