MATLAB Answers

0

How to pivot the table

Asked by Mekala balaji on 11 Jun 2019
Latest activity Edited by Peter Perkins
on 12 Jun 2019
Hi,
I have below data,
ID Date cK_1 ck_k2 ck_4
Gat_pt01 2019-06-01 pass pass miss
HAY_2t_001 2019-06-01 undefined pass pass
VBA_jkl_78 2019-06-05 pass pass pass
HAY_2t_001 2019-06-05 pass miss pass
VBA_jkl_78 2019-06-06 pass pass pass
Gat_pt01 2019-06-06 pass pass miss
I want to pivote by ID & by date
My dessired output as below:
ID Date cK_1 ck_k2 ck_4
Pass Miss Undefined Pass Miss Undefined Pass Miss Undefined
Gat_pt01
01-06-2019 1 0 0 1 0 0 0 1 0
06-06-2019 1 0 0 1 0 0 0 1 0
HAY_2t_001
01-06-2019 0 0 1 1 0 0 1 0 0
05-06-2019 1 0 0 0 1 0 1 0 0
VBA_jkl_78
05-06-2019 1 0 0 1 0 0 1 0 0
06-06-2019 1 0 0 1 0 0 1 0 0

  0 Comments

Sign in to comment.

1 Answer

Peter Perkins
Answer by Peter Perkins
on 12 Jun 2019
Edited by Peter Perkins
on 12 Jun 2019
 Accepted Answer

At first glance, this seems like you could be using the unstack function, and it may be that you can. But you are a bit out of model for that, because the data type is changing. It's more like you are converting each of the categorical variables in a table into what statisticans would call dummy variables (and if you have the Statistics and Machine Learning Toolbox, you could use dummyvars). Here's something that might help get you started:
>> cK_1 = categorical(["p";"m";missing;"p";"p";"m"])
cK_1 =
6×1 categorical array
p
m
<undefined>
p
p
m
>> cK_1 = array2table([cK_1=="p" cK_1=="m" ismissing(cK_1)],'VariableNames',["pass" "miss" "undefined"])
cK_1 =
6×3 table
pass miss undefined
_____ _____ _________
true false false
false true false
false false true
true false false
true false false
false true false
>> ID = ["a";"b";"c";"b";"c";"a"];
>> Date = datetime(2019,6,[1;1;5;5;6;6]);
>> t = table(ID,Date,cK_1)
t =
6×3 table
ID Date cK_1
pass miss undefined
___ ___________ ___________________________
"a" 01-Jun-2019 true false false
"b" 01-Jun-2019 false true false
"c" 05-Jun-2019 false false true
"b" 05-Jun-2019 true false false
"c" 06-Jun-2019 true false false
"a" 06-Jun-2019 false true false

  0 Comments

Sign in to comment.