Grouping Data: How do I group all the rows with the same number on the second column together?

18 views (last 30 days)
:How do I group all the rows with the same number on the second column together? For example grouping all those with a one to then find the average of those with a 1 in the second colunm.
1997 1 235413 0 56430 448407 740250 2820.197904
1997 2 226329 0 52965 426243 705537 2805.185346
1997 3 261795 0 64159 499926 825880 2790.517432
1997 4 270906 0 67822 527115 865843 2779.191129
1997 5 269135 0 66941 502926 839002 2769.086904
1997 6 262200 0 68581 492315 823096 2759.273342
1997 7 268685 0 71522 520628 860835 2753.40779
1997 8 238558 0 64915 493336 796809 2735.052627
1997 9 246241 0 68066 459213 773520 2721.821639
1997 10 275398 0 77965 546641 900004 2718.294436
1997 11 252037 0 73838 498377 824252 2711.816183
1997 12 240379 0 74221 535088 849688 2651.859427
1998 1 173367 0 56590 456694 686651 2587.061022
1998 2 196494 0 60210 537412 794116 2554.380082
1998 3 229674 0 66700 592665 889039 2556.63575
1998 4 233200 0 67194 599109 899503 2548.15965
1998 5 226549 0 63414 570527 860490 2558.14556
1998 6 223851 0 63242 562463 849556 2564.411316
1998 7 235736 0 63983 603327 903046 2566.032396
1998 8 237364 0 62228 628813 928405 2561.433972
1998 9 258592 0 65429 647055 971076 2551.114591
1998 10 222548 0 55989 569520 848057 2544.76801
1998 11 246428 0 60838 615332 922598 2552.04413
1998 12 257512 0 61813 662672 981997 2559.692372
1999 1 226445 0 57052 628256 911753 2560.080488
1999 2 196260 0 47529 530347 774136 2552.762668
1999 3 247219 0 63824 673819 984862 2543.781406
1999 4 244103 0 60783 662524 967410 2540.799132
1999 5 235134 0 56804 636119 928057 2548.471442
1999 6 253875 0 63670 705486 1023031 2560.255326
1999 7 248908 0 60256 693820 1002984 2568.232887
1999 8 224760 0 52608 635848 913216 2540.906859
1999 9 231963 0 55625 631757 919345 2535.686753
1999 10 247660 0 61029 706631 1015320 2513.524785
1999 11 252708 0 62071 714227 1029006 2515.774877
1999 12 257023 0 61062 727393 1045478 2521.265456
2000 1 228316 0 58629 710006 996951 2505.014103
2000 2 221573 0 54955 685999 962527 2496.075126
2000 3 251094 0 65356 778953 1095403 2484.422238
2000 4 218070 0 56326 666432 940828 2494.562128
2000 5 242391 0 60709 733977 1037077 2505.949649
2000 6 238478 0 61695 736251 1036424 2489.716644
2000 7 245916 0 60708 700058 1006682 2482.699541
2000 8 249354 0 59721 773834 1082909 2473.567473
2000 9 214452 0 56188 671461 942101 2444.968959
2000 10 225464 0 60359 774119 1059942 2446.465597
2000 11 240569 0 64109 781953 1086631 2453.869965
2000 12 228419 0 66258 704274 998951 2447.909492
2001 1 234307 0 59496 620782 914585 2420.10026
2001 2 243962 0 58970 624346 927278 2410.870448
2001 3 255743 0 63339 659277 978359 2387.268754
2001 4 276692 0 59991 532462 869145 2381.766606
2001 5 259985 0 61007 586766 907758 2380.814347
2001 6 257642 0 61866 568413 887921 2370.797882
2001 7 258214 0 61776 562402 882392 2368.762423
2001 8 260221 0 61570 587314 909105 2357.85578
2001 9 262143 0 65300 537150 864593 2356.089485
2001 10 254047 0 64993 533818 852858 2350.620934
2001 11 280425 0 70766 579676 930867 2362.333004
2001 12 268842 0 66314 549417 884573 2356.430994
2002 1 264016 0 69907 581815 915738 2340.649836
2002 2 212440 0 53309 454381 720130 2333.603063
2002 3 258689 0 69704 551671 880064 2315.733147
2002 4 260556 0 69676 549634 879866 2303.154373
2002 5 275551 0 68311 542487 886349 2292.483026
2002 6 278648 0 53109 415394 747151 2292.900751
2002 7 277763 0 64514 530472 872749 2302.050591
2002 8 274923 0 63183 526666 864772 2289.429688
2002 9 249902 0 67318 509354 826574 2275.36978
2002 10 274205 0 74403 586649 935257 2279.361745
2002 11 269979 0 75414 578939 924332 2274.075467
2002 12 253549 0 70902 548616 873067 2263.61155
2003 1 245969 0 71294 551015 868278 2251.987864
2003 2 227790 0 65477 532824 826091 2240.121565
2003 3 251242 0 78109 580007 909358 2209.298208
2003 4 251487 0 77074 571997 900558 2209.828321
2003 5 244574 0 71973 543598 860145 2211.310126
2003 6 239853 0 71724 531380 842957 2214.510147
2003 7 242291 0 76863 559708 878862 2210.053146
2003 8 203244 0 57461 479625 740330 2181.695435
2003 9 199022 0 59391 457046 715459 2160.789771
2003 10 237309 0 70745 568173 876227 2158.412136
2003 11 216826 0 64839 549057 830722 2158.163903
2003 12 217264 0 64716 621943 903923 2148.030788
2004 1 168227 0 50816 528764 747807 2133.139642
2004 2 182960 0 54082 587397 824439 2124.539492
2004 3 198505 0 60734 630607 889846 2103.658836
2004 4 194692 0 52022 601939 848653 2109.393288
2004 5 179939 0 45415 561656 787010 2110.314685
2004 6 194585 0 50378 624559 869522 2110.287663
2004 7 257072 0 52025 551968 861065 1896.568924
2004 8 280278 0 60198 622521 962997 1879.430636
2004 9 278718 0 62551 577645 918914 1873.685615
2004 10 314644 0 71150 679204 1064998 1858.02237
2004 11 329553 0 75280 694792 1099625 1834.169868
2004 12 300409 0 97169 636187 1033765 2107.720925
2005 1 301104 3 70462 662139 1033708 1793.975237
2005 2 260541 0 58017 551779 870337 1786.381673
2005 3 333673 0 78187 711545 1123405 1770.89338
2005 4 315602 0 73731 675194 1064527 1758.54623
2005 5 330875 0 77529 698492 1106896 1744.424984
2005 6 344132 0 80263 678685 1103080 1730.90686
2005 7 313905 0 74146 613446 1001497 1715.214001
2005 8 329697 0 75853 648179 1053729 1699.005699
2005 9 331977 0 82116 625701 1039794 1668.439488
2005 10 319063 0 81388 618718 1019169 1651.6143
2005 11 360966 0 92425 683201 1136592 1643.241852
2005 12 358595 0 90418 666503 1115516 1644.389052
2006 1 328801 7 87088 625533 1041429 1634.781275
2006 2 315516 0 82781 618278 1016575 1639.63791
2006 3 359886 0 96535 684301 1140722 1633.018507
2006 4 323647 0 87204 621165 1032016 1632.989798
2006 5 337380 1252 89630 651086 1079348 1634.972544
2006 6 332332 2800 89224 635979 1060335 1640.123054
2006 7 315687 3188 81447 588748 989070 1637.80255
2006 8 350606 3437 86952 670949 1111944 1634.193923
2006 9 351032 4312 88635 650478 1094457 1632.282397
2006 10 312221 4348 79857 583722 980148 1638.395432
2006 11 365046 5587 92868 670501 1134002 1646.605222
2006 12 334166 5203 80679 611752 1031800 1651.479899
2007 1 348136 7603 86661 677466 1119866 1751.024465
2007 2 303331 8436 70486 589522 971775 1880.346839
2007 3 341073 10565 83961 660700 1096299 1870.802327
2007 4 334213 11313 83538 665713 1094777 1866.042565
2007 5 345887 11393 83858 686354 1127492 1868.440653
2007 6 314214 11148 77616 621270 1024248 1868.290757
2007 7 324117 11620 79161 641966 1056864 1863.619671
2007 8 336283 12173 78802 674415 1101673 1864.704123
2007 9 266573 9500 64800 511849 852722 1855.163322
2007 10 348560 12744 86202 699168 1146674 1854.13665
2007 11 347451 15180 85820 691316 1139767 1854.402167
2007 12 293285 19982 71285 594071 978623 1857.227915

Answers (1)

Adam Danz
Adam Danz on 8 Nov 2019
Edited: Adam Danz on 12 Nov 2019
For a single group's mean
mu = mean(m(m(:,2)==1,3));
% 1 2 3 4
% 1: the variable name of your matix
% 2: column number containing the group variable
% 3: group value
% 4: column to be averaged
For all groups means
muAll = splitapply(@mean, m(:,3), m(:,2))
% 1 4 1 2
% Same notes as above

Categories

Find more on Modeling in Help Center and File Exchange

Tags

Products

Community Treasure Hunt

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

Start Hunting!