how to pivot the table

1 次查看（过去 30 天）
Kanakaiah Jakkula
评论： Peter Perkins 2016-1-17
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 Tv_pr12k_mm_ty004.vg
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:
Table2:
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 个评论
Sivakumaran Chandrasekaran
Sivakumaran Chandrasekaran 2016-1-5
you can break after pr.
i am not sure about removal of underscore

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Peter Perkins
Peter Perkins 2016-1-5
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.
  4 个评论
显示 2更早的评论
Kanakaiah Jakkula
Kanakaiah Jakkula 2016-1-6
编辑：Kanakaiah Jakkula 2016-1-6
Sir,
I also want to store the group name for each corresponding RcpName (which is unique, and lastly occured). it giving some error:
??? Error using ==> cell.unique at 29 Unrecognized option.
Error in ==> Pivot at 12 [uv,idxlast] = unique(T.RCP,'legacy'); Sincerely,
Peter Perkins
Peter Perkins 2016-1-17
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.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Common Operations 的更多信息

标签

产品

Community Treasure Hunt

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

Start Hunting!

Translated by