Calculating mean across a column and then subtracting it from all observations based on a row condition

I have a data frame that looks as follows:

1266	2005	247.694000000000	3.30900000000000	12.6660000000000	55.5250000000000
1266	2006	262.753000000000	3.31500000000000	7.94400000000000	77.4340000000000
1266	2007	280.887000000000	1.35000000000000	24.9980000000000	134.837000000000
1266	2008	273.932000000000	5.47000000000000	10.8960000000000	116.382000000000
1266	2009	200.235000000000	5.12200000000000	0.286000000000000	89.5280000000000
1266	2010	188.817000000000	1.28100000000000	10.6860000000000	79.7920000000000
1266	2011	180.035000000000	3.27900000000000	22.5640000000000	98.5920000000000
1266	2012	185.083000000000	3.26700000000000	32.1710000000000	127.187000000000
1266	2013	198.840000000000	2	23.4260000000000	101.661000000000
1266	2014	203.567000000000	2.25800000000000	18.0360000000000	88.6800000000000
1266	2015	460.580000000000	4.78800000000000	47.3390000000000	153.119000000000
1266	2016	458.662000000000	4.78100000000000	38.6260000000000	144.196000000000
1266	2017	419.182000000000	4.55000000000000	9.32800000000000	129.829000000000
1266	2018	423.422000000000	5.27500000000000	15.1480000000000	81.2810000000000
1266	2019	417.388000000000	5.33800000000000	43.7520000000000	122.251000000000
19965	2005	106.358000000000	1.60200000000000	28.7310000000000	57.0060000000000
19965	2006	180.282000000000	1.34200000000000	32.9220000000000	66.2500000000000
19965	2007	179.325000000000	1.34200000000000	20.2840000000000	51.8950000000000
19965	2008	165.411000000000	1.41700000000000	5.37800000000000	28.1780000000000
19965	2009	187.056000000000	0.831000000000000	4.09000000000000	20.4780000000000
19965	2010	235.837000000000	0.0300000000000000	8.72400000000000	31.1920000000000
19965	2011	230.408000000000	0.0320000000000000	23.4560000000000	57.2740000000000
19965	2012	267.499000000000	0.125000000000000	6.26700000000000	54.0430000000000
19965	2013	310.908000000000	0.109000000000000	24.8400000000000	70.6920000000000
19965	2014	345.077000000000	5.10900000000000	24.7320000000000	87.4700000000000
19965	2015	370.056000000000	0.114000000000000	22.4160000000000	78.0280000000000
19965	2016	399.050000000000	5.11900000000000	18.0020000000000	80.4280000000000
19965	2017	380.673000000000	0.123000000000000	37.2260000000000	99.8230000000000
19965	2018	508.249000000000	0.128000000000000	40.2870000000000	103.554000000000
19965	2019	493.549000000000	25.2210000000000	33.0640000000000	109.903000000000
27301	2005	9.20400000000000	0.0280000000000000	-1.17600000000000	6.62100000000000
27301	2006	9.68100000000000	1.14000000000000	-0.760000000000000	4.58200000000000
27301	2007	9.07000000000000	0.0500000000000000	-0.637000000000000	3.47600000000000
27301	2008	9.44400000000000	0.590000000000000	-0.640000000000000	3.89300000000000
27301	2009	8.64100000000000	0.840000000000000	-0.977000000000000	3.81400000000000
27301	2010	8.49800000000000	0.0260000000000000	0.119000000000000	5.05400000000000
27301	2011	9.66000000000000	2.17200000000000	0.351000000000000	5.49700000000000
27301	2012	12.6740000000000	2.95200000000000	-0.628000000000000	3.58300000000000
27301	2013	14.5330000000000	2.87100000000000	0.472000000000000	4.12500000000000
27301	2014	19.0520000000000	0.199000000000000	0.563000000000000	9.11700000000000
27301	2015	24.4640000000000	0.234000000000000	0.500000000000000	7.44200000000000
27301	2016	26.6150000000000	0.211000000000000	0.312000000000000	7.51200000000000
27301	2017	31.7990000000000	0.240000000000000	0.334000000000000	11.9900000000000
27301	2018	39.0620000000000	0.843000000000000	-1.51200000000000	4.50600000000000
27301	2019	32.0610000000000	0.770000000000000	-0.993000000000000	7.81300000000000
30443	2005	2124.80000000000	11.7000000000000	208.500000000000	3259.70000000000
30443	2006	2082.20000000000	7.80000000000000	110.600000000000	3214.30000000000
30443	2007	2185.70000000000	6.90000000000000	264.600000000000	3365.50000000000
30443	2008	2651	358	267.900000000000	3531
30443	2009	2596	4.90000000000000	243.700000000000	3496.40000000000
30443	2010	2517.70000000000	5.30000000000000	193	3552.90000000000
30443	2011	2504	2.20000000000000	201.200000000000	3589.70000000000
30443	2012	2533.40000000000	2.70000000000000	235.300000000000	3421.20000000000
30443	2013	2589.20000000000	2.80000000000000	184.500000000000	3683.70000000000
30443	2014	2675.30000000000	2.20000000000000	262.800000000000	3927.50000000000
30443	2015	2596.10000000000	1.50000000000000	230.800000000000	4056.50000000000
30443	2016	2653.30000000000	0.600000000000000	351.600000000000	4011.50000000000
30443	2017	2766.90000000000	0.600000000000000	237.500000000000	4085.90000000000
30443	2018	3255.20000000000	0.500000000000000	184.300000000000	4493.90000000000
30443	2019	3349.90000000000	32.8000000000000	213.400000000000	4489
157875	2005	107.360000000000	49.8980000000000	9.54900000000000	62.5660000000000
157875	2006	133.522000000000	3.37800000000000	11.1020000000000	95.9370000000000
157875	2007	99.7900000000000	6.64300000000000	12.1820000000000	112.401000000000
157875	2008	121.383000000000	4.22800000000000	21.6750000000000	162.913000000000
157875	2009	103.651000000000	3.70600000000000	8.72300000000000	136.541000000000
157875	2010	101.624000000000	3.52700000000000	13.6730000000000	144.898000000000
157875	2011	133.219000000000	4.38400000000000	15.3880000000000	167.211000000000
157875	2012	129.587000000000	3.42200000000000	0.884000000000000	133.379000000000
157875	2013	148.817000000000	4.46100000000000	13.4130000000000	146.402000000000
157875	2014	161.125000000000	5.15500000000000	9.65600000000000	158.487000000000
157875	2015	179.921000000000	6.11100000000000	13.8320000000000	196.422000000000
157875	2016	181.204000000000	4.46100000000000	12.3070000000000	208.777000000000
157875	2017	178.168000000000	3.37000000000000	8.02400000000000	198.277000000000
157875	2018	218.073000000000	7.49400000000000	0.477000000000000	204.680000000000
157875	2019	237.888000000000	8.24800000000000	-27.8290000000000	187.511000000000

I want to calculate the mean for each year for coulmn 6 and then subtract it from all observations of that year for column 6, indexed by column 1. For example for 2019, I want to calculate the mean of column 6 across all column 1 ids for 2019 and then subtract it from all 2019 observations of column 6.

How do I do this?

[Walter Roberson]

findgroups and splitapply

[dpb]

" I want to calculate the mean of column 6 across all column 1 ids for 2019"

Does that mean grouping by year AND id or ignore the ids and average all of each year?

[Prerna Mishra]

Grouping by year to find the mean and then for each id subtract the mean of the correspoing year.

[dpb]

The ID has nothing to do with it then -- the mean is the same for each year for all ids.

Answer 1

[Matt J]

G=findgroups(data(:,2));
Means=splitapply(@mean, data(:,6),G);

data(:,6)=data(:,6)-Means(G);