How to store data when a string condition of another column is met?

Question

Rabeca Mohammed 2021-10-15

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1564451-how-to-store-data-when-a-string-condition-of-another-column-is-met

评论： Rabeca Mohammed 2021-10-15

采纳的回答： Stephen23

I have imported an excel file, which has three columns: EVENT_ID, BEGIN_DATE, STATE_ABBR.

EVENT_ID BEGIN_DATE STATE_ABBR

1 01-Apr-2000 CT

2 02-Jun-2000 CT

3 13-Aug-2000 CT

4 29-Oct-2000 CT

5 31-Apr-2002 CT

6 06-Jun-2009 CT

7 08-Jan-2011 DE

8 09-Dec-2016 DE

9 11-Feb-2019 MA

10 20-Aug-2020 MD

11 19-Nov-2020 MD

12 22-Apr-2021 MD

. . .

I want to store the number of times (COUNT) a year (YEAR) has repeated in BEGIN_DATE corresponding to STATE_ABBR. In this case, the output should look like:

YEAR COUNT STATE_ABBR

2000 4 CT

2002 1 CT

2009 1 CT

2011 1 DE

2016 1 DE

2019 1 MA

2020 2 MD

2021 1 MD

. . .

Any idea on how to do this?

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Stephen23 2021-10-15

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1564451-how-to-store-data-when-a-string-condition-of-another-column-is-met#answer_809446

SED_FULL_NE.xlsx

T = readtable('SED_FULL_NE.xlsx')
T = 1300×34 table
EVENT_IDCZ_NAME_STRBEGIN_LOCATIONBEGIN_DATEEVENT_TYPEMAGNITUDETOR_F_SCALEDEATHS_DIRECTINJURIES_DIRECTDAMAGE_PROPERTY_NUMDAMAGE_CROPS_NUMSTATE_ABBRCZ_TIMEZONEMAGNITUDE_TYPEEPISODE_IDCZ_TYPECZ_FIPSWFOINJURIES_INDIRECTDEATHS_INDIRECTSOURCEFLOOD_CAUSETOR_LENGTHTOR_WIDTHBEGIN_RANGEBEGIN_AZIMUTHEND_RANGEEND_AZIMUTHEND_LOCATIONEND_DATEEND_TIMEBEGIN_LATBEGIN_LONEND_LAT___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

2897e+06    {'NORTHERN FAIRFIELD (ZONE)' }      {0×0 char}      01-Apr-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1327e+06     {'Z'}        5       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     30-Apr-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2897e+06    {'SOUTHERN NEW LONDON (ZONE)'}      {0×0 char}      01-Apr-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1327e+06     {'Z'}       12       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     30-Apr-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2897e+06    {'SOUTHERN MIDDLESEX (ZONE)' }      {0×0 char}      01-Apr-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1327e+06     {'Z'}       11       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     30-Apr-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2897e+06    {'SOUTHERN NEW HAVEN (ZONE)' }      {0×0 char}      01-Apr-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1327e+06     {'Z'}       10       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     30-Apr-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2897e+06    {'SOUTHERN FAIRFIELD (ZONE)' }      {0×0 char}      01-Apr-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1327e+06     {'Z'}        9       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     30-Apr-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2897e+06    {'NORTHERN NEW LONDON (ZONE)'}      {0×0 char}      01-Apr-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1327e+06     {'Z'}        8       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     30-Apr-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2897e+06    {'NORTHERN MIDDLESEX (ZONE)' }      {0×0 char}      01-Apr-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1327e+06     {'Z'}        7       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     30-Apr-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2897e+06    {'NORTHERN NEW HAVEN (ZONE)' }      {0×0 char}      01-Apr-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1327e+06     {'Z'}        6       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     30-Apr-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2968e+06    {'SOUTHERN FAIRFIELD (ZONE)' }      {0×0 char}      01-May-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1349e+06     {'Z'}        9       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     31-May-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2968e+06    {'SOUTHERN MIDDLESEX (ZONE)' }      {0×0 char}      01-May-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1349e+06     {'Z'}       11       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     31-May-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2968e+06    {'NORTHERN NEW LONDON (ZONE)'}      {0×0 char}      01-May-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1349e+06     {'Z'}        8       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     31-May-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2968e+06    {'SOUTHERN NEW HAVEN (ZONE)' }      {0×0 char}      01-May-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1349e+06     {'Z'}       10       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     31-May-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2968e+06    {'NORTHERN MIDDLESEX (ZONE)' }      {0×0 char}      01-May-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1349e+06     {'Z'}        7       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     31-May-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2968e+06    {'NORTHERN NEW HAVEN (ZONE)' }      {0×0 char}      01-May-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1349e+06     {'Z'}        6       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     31-May-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2968e+06    {'SOUTHERN NEW LONDON (ZONE)'}      {0×0 char}      01-May-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1349e+06     {'Z'}       12       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     31-May-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
2968e+06    {'NORTHERN FAIRFIELD (ZONE)' }      {0×0 char}      01-May-2002    {'Drought'}    {0×0 char}    {0×0 char}           0                 0                    0                    0              {'CT'}        {'EST'}        {0×0 char}      1.1349e+06     {'Z'}        5       {'OKX'}            0                   0           {'OTHER FEDERAL AGENCY'}    {0×0 char}     {0×0 char}    {0×0 char}    {0×0 char}      {0×0 char}      {0×0 char}    {0×0 char}      {0×0 char}     31-May-2002      2359      {0×0 char}    {0×0 char}    {0×0 char}
[G,YEAR,STATE_ABBR] = findgroups(year(T.BEGIN_DATE),T.STATE_ABBR);
COUNT = accumarray(G,ones(numel(G),1));
out = table(YEAR,COUNT,STATE_ABBR) % you can SORTROWS this if required
out = 39×3 table
YEARCOUNTSTATE_ABBR___________________

    4        {'DE'}  
    5        {'MD'}  
   20        {'NJ'}  
   11        {'PA'}  
   20        {'DE'}  
   26        {'MD'}  
   85        {'ME'}  
   60        {'NH'}  
   94        {'NJ'}  
   29        {'NY'}  
   41        {'PA'}  
   24        {'CT'}  
   44        {'DE'}  
   55        {'MD'}  
   61        {'NY'}  
    4        {'DE'}  

4 个评论
显示 2更早的评论隐藏 2更早的评论

Stephen23 2021-10-15

编辑：Stephen23 2021-10-15

COUNT = accumarray(G,ones(numel(G),1));

counts the ones corresponding to each group in G:

https://www.mathworks.com/help/matlab/ref/accumarray.html

[G,YEAR,STATE_ABBR] = findgroups(T.BEGIN_DATE.Year,T.STATE_ABBR);

returns a group number for each unique pair of data-points in the two input arguments.

https://www.mathworks.com/help/matlab/ref/findgroups.html

Rabeca Mohammed 2021-10-15

Got it! Thank you so much!

请先登录，再进行评论。

Answer 2

KSSV 2021-10-15

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1564451-how-to-store-data-when-a-string-condition-of-another-column-is-met#answer_809246

编辑：KSSV 2021-10-15

T = readtable('https://in.mathworks.com/matlabcentral/answers/uploaded_files/768171/SED_FULL_NE.xlsx') ; 
id = [1 4 12] ; 
T = T(:,id) ;
thedates = T.(2) ;
[theyear,ia,ib]=unique(year(thedates)) ;
count=accumarray(ib,1) ; 
state_abbr = T.(3)(ia) ; 
T = table(theyear,count,state_abbr)
T = 12×3 table
    theyear    count    state_abbr
    _______    _____    __________

     2000        40       {'DE'}  
     2001       355       {'DE'}  
     2002       184       {'CT'}  
     2005         9       {'DE'}  
     2007        76       {'DE'}  
     2008        96       {'DE'}  
     2010        59       {'MD'}  
     2012        96       {'CT'}  
     2015        39       {'NJ'}  
     2016       156       {'CT'}  
     2017        53       {'CT'}  
     2020       137       {'CT'}  

10 个评论
显示 8更早的评论隐藏 8更早的评论

Rabeca Mohammed 2021-10-15

Could you please explain the following codes?

[year,ia,ib]=unique(thedates.Year) ;

count=accumarray(ib,1);

state_abbr = T.(3)(ia) ;

What are ia, ib? Why is ib in the accumarray function? Why is 1 in it too? What is T.(3)(ia)?

Rabeca Mohammed 2021-10-15

My bad, the ouput is wrong. In the Excel file, for the year 2000 (BEGIN_DATE = 2000), DE (STATE_ABBR = DE) has only 4 entries. So, count should be 4, not 40. COUNTs of other STATE_ABBRs are also inconsistent to the excel file.

请先登录，再进行评论。