Pivot the data as shown.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 667
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Pivot Data To Columns with Power Query
Power Query solution 1 for Pivot Data To Columns, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.Sort(A, each Date.DayOfWeek([Date])),
C = Table.AddColumn(B, "Month-Day", each Date.Month([Date])),
D = Table.TransformColumns(C, {"Date", each Text.Start(Date.DayOfWeekName(_), 3)}),
E = Table.Pivot(D, List.Distinct(D[Date]), "Date", "Sales", List.Sum),
F = Table.TransformColumns(
E,
{"Month-Day", each Text.Start(Date.MonthName(Date.From("1/" & Text.From(_) & "/2025")), 3)}
)
in
FPower Query solution 2 for Pivot Data To Columns, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Days = List.Distinct(
List.Transform(
List.Sort(Source[Date], each Date.DayOfWeek(_)),
(x) => Text.Start(Date.DayOfWeekName(x), 3)
)
),
Split = Table.SplitColumn(
Source,
"Date",
each {Text.Start(Date.MonthName(_), 3), Text.Start(Date.DayOfWeekName(_), 3)},
{"Month-Day", "Day"}
),
Type = Table.TransformColumnTypes(Split, {{"Sales", type text}}),
Group = Table.Group(Type, {"Month-Day", "Day"}, {{"All", each Text.Combine([Sales], ",")}}),
Pivot = Table.Pivot(Group, Days, "Day", "All"),
Final = Table.Sort(Pivot, {{each Date.From("1-" & [#"Month-Day"] & "- 2025"), Order.Ascending}})
in
FinalPower Query solution 3 for Pivot Data To Columns, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
Grp = Table.Combine(Table.Group(Origen, "Date", {{"A", each
let
a = _,
b = Table.AddColumn(a, "B", each Text.Start(Date.DayOfWeekName([Date], "en-US"),3)),
c = Table.TransformColumns(b, {"Date", each Date.Month(_)})
in c}},0, (x,y)=> Number.From(Date.Month(x)<>Date.Month(y)))[A]),
Dates = {"Mon","Tue","Wed","Thu","Fri","Sat"},
Pivot = Table.Pivot(Grp, Dates, "B", "Sales", each Text.Combine(List.Transform(_, Text.From), ", ")),
Sol = Table.TransformColumns(Pivot, {"Date", each Text.Start(Date.MonthName(hashtag#date(2024,_,1), "en-US"),3)})
in
Sol
Power Query solution 4 for Pivot Data To Columns, proposed by Seokho MOON:
let
Source = Table.Sort(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{each Date.DayOfWeek([Date])}
),
TransCol = Table.TransformColumns(Source, {"Date", Fun1}),
Fun1 = each [
#"Month-Day" = Date.ToText(DateTime.Date(_), "MMM"),
D = Date.ToText(DateTime.Date(_), "ddd")
],
Expand = Table.ExpandRecordColumn(TransCol, "Date", {"Month-Day", "D"}),
Pivot = Table.Pivot(Expand, List.Distinct(Expand[D]), "D", "Sales", Fun2),
Fun2 = each Text.Combine(List.Transform(_, Text.From), ", "),
Res = Table.Sort(Pivot, {each Date.From([#"Month-Day"] & "1")})
in
ResPower Query solution 5 for Pivot Data To Columns, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
L = List.Transform,
fx = (a) => Text.Start(Date.DayOfWeekName(a), 3),
Weekday = List.Distinct(
List.Transform(List.Sort(Source[Date], each Date.DayOfWeek(_)), (x) => fx(x))
),
Group = Table.Group(
Source,
"Date",
L(
Weekday,
(f) => {
f,
each Text.Combine(L(Table.SelectRows(_, (c) => fx(c[Date]) = f)[Sales], Text.From), ", ")
}
),
0,
(x, y) => Value.Compare(Date.MonthName(x), Date.MonthName(y))
),
Result = Table.RenameColumns(Group, {"Date", "Month-Day"})
in
ResultPower Query solution 6 for Pivot Data To Columns, proposed by Antriksh Sharma:
let
Source = Table.TransformColumnTypes(Table, {"Sales", Text.Type}),
Month = Table.RenameColumns(
Table.Distinct(
Table.TransformColumns(
Table.Sort(Source, each [Date])[[Date]],
{"Date", each Text.Start(Date.MonthName(_), 3), type text}
)
),
{"Date", "Month"}
),
Weekday = List.Distinct(
List.Transform(
List.Sort(Source[Date], each Date.DayOfWeek(_)),
each Text.Start(Date.DayOfWeekName(_), 3)
)
),
Result = List.Accumulate(
Weekday,
Month,
(s, c) =>
Table.AddColumn(
s,
c,
(x) =>
Text.Combine(
Table.SelectRows(
Source,
(y) =>
Text.Start(Date.MonthName(y[Date]), 3)
= x[Month] and Text.Start(Date.DayOfWeekName(y[Date]), 3)
= c
)[Sales],
", "
),
type text
)
)
in
ResultPower Query solution 7 for Pivot Data To Columns, proposed by Peter Krkos:
let
F = each Text.Start(Date.DayOfWeekName(_, "en-US"), 3),
Days = List.Buffer(List.Transform(List.Dates(hashtag#date(2025,3,3), 7, hashtag#duration(1,0,0,0)), F)),
T = Table.Combine(Table.Group(Source, "Date", {{"T", each
List.Accumulate(Days, hashtag#table(type table[#"Month-Day"=text], {{Text.Proper(Date.ToText(_{0}[Date], "MMM"))}}), (s,c)=>
Table.AddColumn(s, c, (r)=>
let a = List.Transform(Table.SelectRows(_, (x)=> F(x[Date]) = c)[Sales], Text.From)
in if List.IsEmpty(a) then null else Text.Combine(a, ", "), type text)),
type table}}, 0,
(x,y)=> Value.Compare(Date.Month(y), Date.Month(x)))[T]),
RemovedBlankCols = Table.RemoveColumns(T, Table.SelectRows(Table.TransformColumnTypes(Table.Profile(T),
List.Transform({"Count", "NullCount"}, (x)=> {x, Int64.Type})), each [Count] = [NullCount])[Column])
in
RemovedBlankCols
Power Query solution 8 for Pivot Data To Columns, proposed by Alexandre Garcia:
let
H = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
P = (x, y) => DateTime.ToText(x, [Format = y, Culture = "en-US"]),
L = Table.SplitColumn(
H,
"Date",
(x) => {Date.DayOfWeek(x, 0), P(x, "ddd"), P(x, "MMM")},
{"x", "y", "Month-Day"}
),
C = Table.Combine(
Table.Group(
L,
"Month-Day",
{
"x",
each Table.Pivot(
Table.RemoveColumns(_, {"x"}),
List.Distinct(Table.Sort(L, "x")[y]),
"y",
"Sales",
each Text.Combine(List.Transform(_, Text.From), ", ")
)
}
)[x]
)
in
CPower Query solution 9 for Pivot Data To Columns, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
getValues = Table.ExpandRecordColumn(
Table.TransformColumns(
Source,
{
{
"Date",
each
let
d = Date.From(_)
in
[
MonthNo = Date.Month(d),
#"Month-Day" = Text.Start(Date.MonthName(d, "en-US"), 3),
Day = Text.Start(Date.DayOfWeekName(d, "en-US"), 3)
]
}
}
),
"Date",
{"MonthNo", "Month-Day", "Day"}
),
Result = Table.RemoveColumns(
Table.Pivot(
getValues,
{"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"},
"Day",
"Sales",
List.Sum
),
{"MonthNo"}
)
in
ResultSolving the challenge of Pivot Data To Columns with Excel
Excel solution 1 for Pivot Data To Columns, proposed by Bo Rydobon 🇹🇭:
=LET(d,A2:A21,DROP(PIVOTBY(TEXT(d,{"mm","mmm"}),HSTACK(WEEKDAY(d),TEXT(d,"ddd")),B2:B21,ARRAYTOTEXT,,0,,0),1,1))Excel solution 2 for Pivot Data To Columns, proposed by Rick Rothstein:
=LET(d,A2:A21,s,B2:B21,m,UNIQUE(TEXT(d,"mmm")),w,SEQUENCE(,6,2),HSTACK(VSTACK("Month-Day",m),REDUCE(TEXT(w,"ddd"),SEQUENCE(COUNTA(m)),LAMBDA(a,x,VSTACK(a,BYCOL(IF(WEEKDAY(d)=w*(MONTH(d)=x),s,""),LAMBDA(c,TEXTJOIN(", ",,c))))))))Excel solution 3 for Pivot Data To Columns, proposed by John V.:
=LET(
d,
A2:A21,
p,
DROP(
PIVOTBY(
TEXT(
d,
{"m",
"mmm"}
),
HSTACK(
WEEKDAY(
d
),
TEXT(
d,
"ddd"
)
),
B2:B21,
ARRAYTOTEXT,
,
0,
,
0
),
1,
1
),
IF(
TAKE(
p,
,
1
)&TAKE(
p,
1
)="",
"Month-Day",
p
)
)Excel solution 4 for Pivot Data To Columns, proposed by Kris Jaganah:
=LET(
a,
A2:A21,
b,
DROP(
PIVOTBY(
HSTACK(
MONTH(
a
),
TEXT(
a,
"mmm"
)
),
HSTACK(
WEEKDAY(
a
),
TEXT(
a,
"ddd"
)
),
B2:B21,
SUM,
,
0,
,
0
),
1,
1
),
IF(
SCAN(
,
b,
CONCAT
)="",
"Month-Day",
b
)
)Excel solution 5 for Pivot Data To Columns, proposed by Julian Poeltl:
=LET(D,A2:A21,S,B2:B21,WD,WEEKDAY(D,1),M,TEXT(D,"MMM"),UM,UNIQUE(M),UWD,TOROW(TEXT(UNIQUE(SORT(WD)),"DDD")),VSTACK(HSTACK("Month-Day",UWD),HSTACK(UM,MAP(UWD&UM,LAMBDA(A,ARRAYTOTEXT(IFERROR(FILTER(S,TEXT(WD,"DDD")&M=A),"")))))))Excel solution 6 for Pivot Data To Columns, proposed by Hussein SATOUR:
=LET(
d,
A2:A21,
DROP(
PIVOTBY(
HSTACK(
MONTH(
d
),
TEXT(
d,
"mmm"
)
),
HSTACK(
WEEKDAY(
d
),
TEXT(
d,
"ddd"
)
),
B2:B21,
ARRAYTOTEXT,
,
0,
,
0
),
1,
1
)
)Excel solution 7 for Pivot Data To Columns, proposed by Duy Tùng:
=LET(a,A2:A21,u,PIVOTBY(TEXT(a,"mmm"),TEXT(a,"ddd"),B2:B21,ARRAYTOTEXT,,0,,0),IF(TAKE(u,1)&TAKE(u,,1)="","Month-Day",u))Excel solution 8 for Pivot Data To Columns, proposed by Sunny Baggu:
=LET(
_wd, WEEKDAY(A2:A21),
_m, MONTH(A2:A21),
_um, UNIQUE(_m),
_un, UNIQUE(TEXT(A2:A21, "mmm")),
_d, SEQUENCE(, 6, 2),
_v, MAP(
_um & _d,
LAMBDA(a, ARRAYTOTEXT(FILTER(B2:B21, a = _m & _wd, "")))
),
VSTACK(
HSTACK("Month-Day", TOROW(UNIQUE(SORTBY(TEXT(A2:A21, "ddd"), _wd)))),
HSTACK(_un, _v)
)
)Excel solution 9 for Pivot Data To Columns, proposed by Anshu Bantra:
= to_df(REF("A1:B21"))
df['Date'] = pd.to_datetime(df['Date'], format='%d-%m-%Y')
df['Month'] = df['Date'].dt.month_name().str[:3]
df['Weekday'] = df['Date'].dt.day_name().str[:3]
df['Month_Num'] = df['Date'].dt.month
df['Day_num'] = df['Date'].dt.weekday
pivot = df.pivot_table(index='Month', columns='Weekday', values='Sales', aggfunc=lambda x: ','.join(x.astype(str)), fill_value='')
pivot = pivot.reindex(df.sort_values(by=['Month_Num'])['Month'].unique())
pivot[list(df.sort_values(by=['Day_num'])['Weekday'].unique())Excel solution 10 for Pivot Data To Columns, proposed by Anshu Bantra:
=LET(
dates_, A2:A21,
vals_, B2:B21,
months_, SORTBY(TEXT(dates_, "MMM"), MONTH(dates_)),
days_, SORTBY(TEXT(dates_, "DDD"), WEEKDAY(dates_, 2)),
MAKEARRAY(
COUNTA(UNIQUE(months_)), COUNTA(UNIQUE(days_)),
LAMBDA(
x, y,
ARRAYTOTEXT(
FILTER(
vals_,
( (MONTH(dates_) = x) * (WEEKDAY(dates_, 2) = y) ), ""
)
)
)
)
)Excel solution 11 for Pivot Data To Columns, proposed by Md. Zohurul Islam:
=LET(
u,
A2:A21,
v,
B2:B21,
a,
HSTACK(
MONTH(
u
),
TEXT(
u,
"mmm"
)
),
b,
HSTACK(
WEEKDAY(
u
),
TEXT(
u,
"ddd"
)
),
c,
TOROW(
DROP(
UNIQUE(
SORT(
b,
1,
1
)
),
,
1
)
),
d,
DROP(
UNIQUE(
a
),
,
1
),
e,
MAP(
d&c,
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
v,
DROP(
a,
,
1
)&DROP(
b,
,
1
)=x,
""
)
)
)
),
f,
HSTACK(
VSTACK(
"Month-Day",
d
),
VSTACK(
c,
e
)
),
f
)Excel solution 12 for Pivot Data To Columns, proposed by Hamidi Hamid:
=LET(
x,
HSTACK(
MONTH(
A2:A21
)&"/"&TEXT(
A2:A21,
"mmm"
),
WEEKDAY(
A2:A21,
2
)&"/"&TEXT(
A2:A21,
"dddd"
)
),
t,
PIVOTBY(
TAKE(
x,
,
1
),
TAKE(
x,
,
-1
),
B2:B21,
SUM,
,
0,
,
0
),
IFERROR(
IF(
ISTEXT(
t
),
TEXTAFTER(
t,
"/",
),
t
),
""
)
)Excel solution 13 for Pivot Data To Columns, proposed by Asheesh Pahwa: &
=LET(
dt,
A2:A21,
t,
TEXT(
dt,
"mmm"
),
m,
UNIQUE(
t
),
d,
TEXT(
dt,
"ddd"
),
u,
UNIQUE(
d
),
w,
UNIQUE(
WEEKDAY(
dt,
11
)
),
s,
TOROW(
SORTBY(
u,
w
)
),
c,
m&"-"&s,
VSTACK(
HSTACK(
D2,
s
),
HSTACK(
m,
MAP(
c,
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
B2:B21,
t&"-"&d=x,
""
)
)
)
)
)
)
)Excel solution 14 for Pivot Data To Columns, proposed by Eric Laforce:
=LET(
d,
tData[Date],
p,
PIVOTBY(
HSTACK(
MONTH(
d
),
TEXT(
d,
"mmm"
)
),
HSTACK(
WEEKDAY(
d
),
TEXT(
d,
"jjj"
)
),
tData[Sales],
SUM,
,
0,
,
0
),
DROP(
p,
1,
1
)
)
NB weekday format = "jjj" (FR) instead of "ddd" (US)Excel solution 15 for Pivot Data To Columns, proposed by ferhat CK:
=LET(t,TEXT,a,PIVOTBY((1&t(A2:A21,"mmm"))*1,WEEKDAY(A2:A21),B2:B21,MAX,,0,,0),b,DROP(IF(a=INDEX(a,,1),t(a,"mmm"),a),1),VSTACK(IF(TAKE(a,1)="","Month-Day",t(TAKE(a,1),"ddd")),b))Excel solution 16 for Pivot Data To Columns, proposed by Charles Roldan:
=DROP(
PIVOTBY(
TEXT(
A2:A21,
{"mm",
"mmm"}
),
TEXT(
WEEKDAY(
A2:A21
),
{"dd",
"ddd"}
),
B2:B21,
ARRAYTOTEXT,
,
0,
,
0
),
1,
1
)Excel solution 17 for Pivot Data To Columns, proposed by Jaroslaw Kujawa:
=LET(a;A2:A21;b;PIVOTBY(MONTH(a);WEEKDAY(a);OFFSET(a;;1);LAMBDA(x;TEXTJOIN(", ";;x));;0;;0);c;HSTACK(TEXT("1-"&TAKE(b;;1);"mmm");DROP(VSTACK(TEXT(TAKE(b;1);"ddd");DROP(b;1));;1));IF(c<>"1-";c;"Month-Day"))Excel solution 18 for Pivot Data To Columns, proposed by Meganathan Elumalai:
=DROP(PIVOTBY(TEXT(A2:A21,{"mm","mmm"}),HSTACK(WEEKDAY(A2:A21),TEXT(A2:A21,"ddd")),B2:B21,ARRAYTOTEXT,0,0,,0,),1,1)Excel solution 19 for Pivot Data To Columns, proposed by Imam Hambali:
=LET(
cc,
CHOOSECOLS,
d,
TEXT(
A2:A21,
{"mmm",
"m",
"ddd"}
),
wd,
WEEKDAY(
A2:A21,
2
),
p,
PIVOTBY(
cc(
d,
2
),
wd,
B2:B21,
SUM,
0,
0,
,
0
),
x,
XLOOKUP(
cc(
p,
1
),
cc(
d,
2
),
cc(
d,
1
),
""
),
y,
XLOOKUP(
DROP(
TAKE(
p,
1
),
,
1
),
wd,
cc(
d,
3
)
),
HSTACK(
x,
VSTACK(
y,
DROP(
p,
1,
1
)
)
)
)Excel solution 20 for Pivot Data To Columns, proposed by Victor Momoh (MVP, MOS, R.Eng):
=LET(a,A2:A21,DROP(PIVOTBY(HSTACK(MONTH(a),TEXT(a,"mmm")),HSTACK(WEEKDAY(a),TEXT(a,"ddd")),B2:B21,ARRAYTOTEXT,,0,,0),1,1))Excel solution 21 for Pivot Data To Columns, proposed by Erdit Qendro:
=LET(ar,A2:B21,dtL,TAKE(ar,,1),dtV,TAKE(ar,,-1),
pivot,DROP(PIVOTBY(HSTACK(MONTH(dtL),
TEXT(dtL,"mmm")),WEEKDAY(dtL),
dtV,LAMBDA(a,TEXTJOIN(", ",,a)),0,0,,0),,1),
frow,HSTACK("Month-Day",TRANSPOSE(UNIQUE(SORTBY(TEXT(dtL,"ddd"),WEEKDAY(dtL))))),
result,VSTACK(frow,DROP(pivot,1)),result)Excel solution 22 for Pivot Data To Columns, proposed by Cary Ballard, DML:
=LET(d,A2:A21,DROP(PIVOTBY(HSTACK(TEXT(d, "m"), TEXT(d, "mmm")), HSTACK(WEEKDAY(d), TEXT(d, "ddd")), B2:B21, SUM, , 0, 2, 0), 1, 1))Excel solution 23 for Pivot Data To Columns, proposed by Hussain Ali Nasser:
=LET(d, A2:A21, s, B2:B21, m, TEXT(d, "mmm"), dy, TEXT(d, "ddd"), p, PIVOTBY(HSTACK(MONTH(d), m), HSTACK(WEEKDAY(d), dy), s, ARRAYTOTEXT, , 0, , 0), DROP(p, 1, 1))Excel solution 24 for Pivot Data To Columns, proposed by Fausto Bier:
=LET(
r,
A2:A21,
p,
PIVOTBY(
TEXT(
r,
"m/mmm"
),
TEXT(
WEEKDAY(
r,
1
),
"g/ggg"
),
B2:B21,
ARRAYTOTEXT,
,
0,
,
0
),
MAKEARRAY(
ROWS(
p
),
COLUMNS(
p
),
LAMBDA(
r,
c,
LET(
i,
INDEX(
p,
r,
c
),
IFS(
r*c=1,
"Mounth-Day",
OR(
r=1,
c=1
),
PROPER(
TEXTAFTER(
i,
"/",
,
,
,
i
)
),
TRUE,
i
)
)
)
)
)Solving the challenge of Pivot Data To Columns with Python
Python solution 1 for Pivot Data To Columns, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "667 Pivot Problem.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=21)
test = pd.read_excel(path, usecols="D:J", skiprows=1, nrows=9)
test.update(test.select_dtypes(include=[np.number]).applymap(lambda x: str(int(x)) if not pd.isna(x) else np.NaN))
input['Month-Day'] = input['Date'].dt.strftime('%b')
input['wday'] = input['Date'].dt.strftime('%a')
result = input.drop(columns=['Date']).astype({'Month-Day': 'category', 'wday': 'category'})
result = result.groupby(['Month-Day', 'wday'], observed=False)['Sales'].apply(lambda x: ', '.join(map(str, x))).unstack().reset_index()
result = result[['Month-Day', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']]
month_order = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
result['Month-Day'] = pd.Categorical(result['Month-Day'], categories=month_order, ordered=True)
result = result.sort_values('Month-Day').reset_index(drop=True)
result['Month-Day'] = result['Month-Day'].astype(str)
print(result.equals(test)) # True
Python solution 2 for Pivot Data To Columns, proposed by Luan Rodrigues:
import pandas as pd
import calendar
file = "Excel_Challenge_667 - Pivot Problem.xlsx"
df = pd.read_excel(file,usecols='A:B')
dias = list(calendar.day_abbr)
df['Dia'] = pd.to_datetime(df['Date']).dt.day_name().str[:3]
df['Mes'] = pd.to_datetime(df['Date']).dt.month_name().str[:3]
del df['Date']
pvt = df.pivot_table(
index="Mes",
columns='Dia',
values='Sales',
aggfunc=lambda x: ', '.join(map(str, x)),sort=False
).reindex(columns=dias).reset_index()
print(pvt.dropna(axis=1, how='all'))
Python solution 3 for Pivot Data To Columns, proposed by Abdallah Ally:
import pandas as pd
from calendar import day_abbr, month_abbr
file_path = 'Excel_Challenge_667 - Pivot Problem.xlsx'
df = pd.read_excel(io=file_path, usecols='A:B')
# Perform data manipulation
df = (
df
.assign(
Month_Day = df['Date'].dt.strftime('%b'),
Day = df['Date'].dt.strftime('%a'),
Sales = df.Sales.map(str)
)
.groupby(['Month_Day', 'Day']).Sales.agg(', '.join)
.reset_index()
.pivot(index='Month_Day', columns='Day', values='Sales')
.rename_axis('', axis=1)
.reset_index()
.fillna('')
.rename(columns={'Month_Day': 'Month-Day'})
)
df = df[['Month-Day'] + sorted(df.columns[1:], key=lambda x: list(day_abbr).index(x))]
df = df.sort_values(
by='Month-Day',
ignore_index=True,
key=lambda x: [list(month_abbr).index(x) for x in df['Month-Day']]
)
df
Solving the challenge of Pivot Data To Columns with Python in Excel
Python in Excel solution 1 for Pivot Data To Columns, proposed by Alejandro Campos:
df = xl("A1:B21", headers=True)
df["Date"] = pd.to_datetime(df["Date"], format="%d/%m/%Y")
df["Month-Day"] = df["Date"].dt.strftime("%b")
pivot = df.pivot_table(index="Month-Day", columns=df["Date"].dt.strftime("%a"), values="Sales", aggfunc=lambda x: ', '.join(map(str, x)))
pivot = pivot.reindex(columns=["Mon", "Tue", "Wed", "Thu", "Fri", "Sat"]).reset_index().rename_axis(None, axis=1)
pivot.sort_values("Month-Day", key=lambda x: pd.to_datetime(x, format="%b"), ignore_index=True).fillna("")
Python in Excel solution 2 for Pivot Data To Columns, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A1:B21", True)
df["Month"] = df["Date"].dt.strftime("%b")
df["Day"] = df["Date"].dt.strftime("%a")
wk = [
(pd.Timestamp("1900-01-01") + pd.Timedelta(days=i)).strftime("%a") for i in range(7)
]
pivot = df.pivot_table("Sales", "Month", "Day", lambda x: ", ".join(map(str, x)), "")
pivot = (
pivot.reindex(columns=[i for i in wk if i in pivot.columns])
.reset_index()
.rename_axis("", axis=1)
.rename(columns={"Month": "Month-Day"})
.sort_values(
"Month-Day", key=lambda x: pd.to_datetime(x, format="%b"), ignore_index=True
)
)
Solving the challenge of Pivot Data To Columns with R
R solution 1 for Pivot Data To Columns, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/667 Pivot Problem.xlsx"
input = read_excel(path, range = "A1:B21")
test = read_excel(path, range = "D2:J11")
result = input %>%
mutate(`Month-Day` = month(Date, label = TRUE, abbr = TRUE, locale = "en"),
wday = wday(Date, label = TRUE, abbr = TRUE, week_start = 1, locale = "en")) %>%
select(-Date) %>%
mutate(across(c(`Month-Day`, wday), as.factor)) %>%
summarise(Sales = paste(Sales, collapse = ", "), .by = c('Month-Day', wday)) %>%
pivot_wider(names_from = wday, values_from = Sales, names_sort = TRUE) %>%
mutate(`Month-Day` = as.character(`Month-Day`))
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&&