MATLAB Answers

How to pivot the table

17 views (last 30 days)
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.

Accepted Answer

Peter Perkins
Peter Perkins on 12 Jun 2019
Edited: Peter Perkins on 12 Jun 2019
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.

More Answers (0)


Translated by