how to pivot the table

Question

Kanakaiah Jakkula 2016-1-5

1
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/262702-how-to-pivot-the-table

评论： Peter Perkins 2016-1-17

I have the below table: Table1:

Index  GroupName  RcpName
A          pr.vf001K
C          pr.vb002K
D          pr.vk003K
E          pr.vb004K
A          pr.vb005K
B          pr.vf006K
C          pr.va007K
D          pr.vb008K
F          pr.vb009K
C          pr.vb007K
E          pr.vg010K
B          pr.vf006K
A          pr.vf001K
D          pr.vb013K
E          pr.vb004K
C          pr.vb007K
B          pr.vf09K
D          pr.vb013K
B          pr.vb19K
B          pr.vf006K
E          pr.vb004K
F          pr.vb023K
D          pr.vb013K
E          pr.vb010K
A          Tv_pr12k_mm_ty004.vg
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
A  vf001K
C  vb002K
D  vk003K
E  vb004K
A  vb005K
B  vf006K
C  va007K
D  vb008K
F  vb009K
C  vb007K
E  vg010K
B  vf006K
A  vf001K
D  vb013K
E  vb004K
C  vb007K
B  vf09K
D  vb013K
B  vb19K
B  vf006K
E  vb004K
F  vb023K
D  vb013K
E  vb010K
A  pr12k_mm
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 个评论
显示 -1更早的评论隐藏 -1更早的评论

Sivakumaran Chandrasekaran 2016-1-5

you can break after pr.

i am not sure about removal of underscore

请先登录，再进行评论。

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

Answer 1

Peter Perkins 2016-1-5

2
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/262702-how-to-pivot-the-table#answer_205137

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更早的评论隐藏 2更早的评论

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 2016-1-17

在 MATLAB Online 中打开

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.