how to pivot the table

2 views (last 30 days)
Kanakaiah Jakkula
Kanakaiah Jakkula on 5 Jan 2016
Commented: Peter Perkins on 17 Jan 2016
I have the below table: Table1:
Index GroupName RcpName
1 A pr.vf001K
2 C pr.vb002K
3 D pr.vk003K
4 E pr.vb004K
5 A pr.vb005K
6 B pr.vf006K
7 C pr.va007K
8 D pr.vb008K
9 F pr.vb009K
10 C pr.vb007K
11 E pr.vg010K
12 B pr.vf006K
13 A pr.vf001K
14 D pr.vb013K
15 E pr.vb004K
16 C pr.vb007K
17 B pr.vf09K
18 D pr.vb013K
19 B pr.vb19K
20 B pr.vf006K
21 E pr.vb004K
22 F pr.vb023K
23 D pr.vb013K
24 E pr.vb010K
25 A
26 G Tv_pr.vf22k_ff01_hy004
Step1: I want to break the RcpNames at pr., and second '_' (underscore): after performing this, the above table become as below:
Index GroupName RcpName
1 A vf001K
2 C vb002K
3 D vk003K
4 E vb004K
5 A vb005K
6 B vf006K
7 C va007K
8 D vb008K
9 F vb009K
10 C vb007K
11 E vg010K
12 B vf006K
13 A vf001K
14 D vb013K
15 E vb004K
16 C vb007K
17 B vf09K
18 D vb013K
19 B vb19K
20 B vf006K
21 E vb004K
22 F vb023K
23 D vb013K
24 E vb010K
25 A pr12k_mm
26 G vf22k_ff01
I want to pivote this table, i.e Table2(pivoting to get unique RcpNames), and get the maximum index of for each RcpName (after pivoting,now only exist unique RcpNames, so duplicacy of Indexes) , and get the corresponding GroupName based on index (here I use vlookup in excel).
Finally I want the output as below:
A 25 pr12k_mm
C 7 va007K
C 2 vb002K
E 21 vb004K
A 5 vb005K
C 16 vb007K
D 8 vb008K
F 9 vb009K
E 24 vb010K
D 23 vb013K
F 22 vb023K
B 19 vb19K
A 13 vf001K
B 20 vf006K
B 17 vf09K
G 26 vf22k_ff01
E 11 vg010K
D 3 vk003K
Please kindly help on this, many many thanks in advance.
  1 Comment
Sivakumaran Chandrasekaran
you can break after pr.
i am not sure about removal of underscore

Sign in to comment.

Answers (1)

Peter Perkins
Peter Perkins on 5 Jan 2016
A much shorter example would make your question easier to understand.
I don't think this has anything to do with "pivoting" in the usual sense. It seems to me that you want to do two things:
1) Strip off leading and trailing parts of strings. You can use strsplit or rexexprep for that.
2) Find the last occurrence of eah unique value. You can use the second output of unique for that.
Hope this helps.
Peter Perkins
Peter Perkins on 17 Jan 2016
See the doc for unique. You're looking for the second output in Sean's example. BTW, rather than 'legacy', I'd suggest 'last' (although either would work):
[C,IA,IC] = unique(A,'rows',OCCURRENCE) specify which index is returned
in IA in the case of repeated values (or rows) in A. The default value
is OCCURENCE = 'first', which returns the index of the first occurrence
of each repeated value (or row) in A, while OCCURRENCE = 'last' returns
the index of the last occurrence of each repeated value (or row) in A.

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!