Format table with frequency of unique combination
1 次查看(过去 30 天)
显示 更早的评论
I have a table T, a part of this looks like:
Code Number1 Date Number2
____ _______ __________ _______
a 1 03-02-2017 18.4191
a 1 17-02-2017 4.4187
a 1 17-02-2017 4.4187
a 1 01-05-2017 8.2921
b 2 01-05-2017 8.2921
b 1 01-05-2017 3.6584
b 2 01-05-2017 3.6584
b 1 04-01-2017 44.4493
b 1 04-01-2017 51.2154
c 1 04-01-2017 56.1046
Now I want to make a new table with the codes as columnnames, the dates as rownames and the frequency of this combination in the table. In this case it would look like:
a b c
___ ___ ___
04-1-2017 NaN 2 1
03-2-2017 1 NaN NaN
17-2-2017 2 NaN NaN
01-5-2017 1 3 NaN
I have already calculated the frequency by:
[C,~,ib] = unique(T(:,[1 3]));
Frequency = histc(ib, 1:height(C));
But how can I format this table?
- The column 'Code' is categorical and ' Date' is datetime
0 个评论
采纳的回答
Peter Perkins
2018-3-1
This is the classic application of unstack, with the subtlety that you aren't actually unstacking the data, you're counting instances. So you need to use length as the aggregation function. I'm gonna demonstrate using a timetable, which isn't crucial, but still, these are timestamped data.
>> t = readtable('tmp1.csv');
>> t.Code = categorical(t.Code);
>> t.Date = datetime(t.Date,'InputFormat','dd-MM-yyyy')
t =
10×4 table
Code Number1 Date Number2
____ _______ ___________ _______
a 1 03-Feb-2017 18.419
a 1 17-Feb-2017 4.4187
a 1 17-Feb-2017 4.4187
a 1 01-May-2017 8.2921
b 2 01-May-2017 8.2921
b 1 01-May-2017 3.6584
b 2 01-May-2017 3.6584
b 1 04-Jan-2017 44.449
b 1 04-Jan-2017 51.215
c 1 04-Jan-2017 56.105
>> tt = table2timetable(t,'RowTimes','Date')
tt =
10×3 timetable
Date Code Number1 Number2
___________ ____ _______ _______
03-Feb-2017 a 1 18.419
17-Feb-2017 a 1 4.4187
17-Feb-2017 a 1 4.4187
01-May-2017 a 1 8.2921
01-May-2017 b 2 8.2921
01-May-2017 b 1 3.6584
01-May-2017 b 2 3.6584
04-Jan-2017 b 1 44.449
04-Jan-2017 b 1 51.215
04-Jan-2017 c 1 56.105
>> ttu = unstack(tt,'Number1','Code','AggregationFunction',@length)
ttu =
4×3 timetable
Date a b c
___________ ___ ___ ___
03-Feb-2017 1 NaN NaN
17-Feb-2017 2 NaN NaN
01-May-2017 1 3 NaN
04-Jan-2017 NaN 2 1
更多回答(0 个)
另请参阅
产品
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!