1
$\begingroup$

My data looks something like this:

(* {{{year, value1, value2, county code, county}}} *)

sampledata = {{{2003, 13.5, 54.2, 1, Adams}, 
               {2004, 13.2, 56.2, 1, Adams}, 
                2005, 12.2, 54.2, 1, Adams}}, 
              {{2003, 12.1, 54.2, 2, Berks}, 
               {2004, 13.3, 52.2, 2, Berks}, 
               {2005, 13.1, 58.88, 2, Berks}}}

I have more data for more years and counties, and it is grouped by county. How do I get the rolling five-year averages for value1 and value2 for each county?

Then, how do I format it:

(* {{{year range, value1avg, value2avg, county code, county}}} *)

example = {{{2003-2007, 13.3, 55.5, 1 Adams}, 
            {2004-2008, 13.2, 54.5, 1, Adams}},   
           {{2003-2007, 14.4, 55.2, 2, Berks}, 
            {2004-2008, 14.1, 56.5, 2, Berks}}}
Improve this question
$\endgroup$
0

3 Answers 3

Reset to default
4
$\begingroup$ 
$Version

(* "14.3.0 for Mac OS X ARM (64-bit) (July 8, 2025)" *)

SeedRandom[1234];

data = Table[{yr, Round[RandomReal[{13, 14}], 0.1], 
   Round[RandomReal[{50, 60}], 0.1], 
   code, {"Adams", "Berks", "Celts"}[[code]]},
  {code, {1, 2, 3}}, {yr, 2003, 2010}]

(* {{{2003, 13.9, 55.2, 1, "Adams"}, {2004, 13.1, 53.8, 1, 
   "Adams"}, {2005, 13., 59.3, 1, "Adams"}, {2006, 13.5, 54.8, 1, 
   "Adams"}, {2007, 13.2, 57.6, 1, "Adams"}, {2008, 14., 52.2, 1, 
   "Adams"}, {2009, 13.5, 58.8, 1, "Adams"}, {2010, 13.6, 52.6, 1, 
   "Adams"}}, {{2003, 13.9, 54.2, 2, "Berks"}, {2004, 14., 55.9, 2, 
   "Berks"}, {2005, 13.1, 57.9, 2, "Berks"}, {2006, 13.7, 57.5, 2, 
   "Berks"}, {2007, 13.4, 56.3, 2, "Berks"}, {2008, 13.8, 51.1, 2, 
   "Berks"}, {2009, 13.5, 53.1, 2, "Berks"}, {2010, 13.8, 52.2, 2, 
   "Berks"}}, {{2003, 13.2, 58.8, 3, "Celts"}, {2004, 13.4, 56.8, 3, 
   "Celts"}, {2005, 13.9, 50.1, 3, "Celts"}, {2006, 13.7, 53.2, 3, 
   "Celts"}, {2007, 13.7, 57.7, 3, "Celts"}, {2008, 13.6, 50.3, 3, 
   "Celts"}, {2009, 13.6, 56.2, 3, "Celts"}, {2010, 13.8, 53.9, 3, 
   "Celts"}}} *)

Map[{ToString[Min[#[[All, 1]]]] <> "-" <> ToString[Max[#[[All, 1]]]],
   Mean[#[[All, 2]]], Mean[#[[All, 3]]], #[[1, 4]], #[[1, 5]]} &,
 Partition[#, 5, 1] & /@ data, {2}]

(* {{{"2003-2007", 13.34, 56.14, 1, "Adams"}, {"2004-2008", 13.36, 55.54,
    1, "Adams"}, {"2005-2009", 13.44, 56.54, 1, "Adams"}, {"2006-2010",
    13.56, 55.2, 1, "Adams"}}, {{"2003-2007", 13.62, 56.36, 2, 
   "Berks"}, {"2004-2008", 13.6, 55.74, 2, "Berks"}, {"2005-2009", 
   13.5, 55.18, 2, "Berks"}, {"2006-2010", 13.64, 54.04, 2, 
   "Berks"}}, {{"2003-2007", 13.58, 55.32, 3, "Celts"}, {"2004-2008", 
   13.66, 53.62, 3, "Celts"}, {"2005-2009", 13.7, 53.5, 3, 
   "Celts"}, {"2006-2010", 13.68, 54.26, 3, "Celts"}}} *)
Improve this answer
$\endgroup$
1
$\begingroup$

Using a similar approach as @Bob Hanlon, but different syntax, which I personally find easier to read:

SeedRandom[1234];

data = Table[{yr, Round[RandomReal[{13, 14}], 0.1], 
   Round[RandomReal[{50, 60}], 0.1], 
   code, {"Adams", "Berks", "Celts"}[[code]]},
  {code, {1, 2, 3}}, {yr, 2003, 2010}]


({StringRiffle[{First[#1], Last[#1]}, "-"], Mean[#2], Mean[#3], 
      Sequence @@ (First /@ {##4})} & @@@ (Transpose /@ 
      Partition[#, 5, 1])) & /@ data
Improve this answer
$\endgroup$
0
$\begingroup$

You could make this easier if you structured your data differently (or used a Dataset), but that's probably best left as a separate question.

Here's an alternate (using the same test data as the other answers):

SeedRandom[1234];
data = 
  Table[
    {yr, Round[RandomReal[{13, 14}], 0.1], Round[RandomReal[{50, 60}], 0.1], code, {"Adams", "Berks", "Celts"}[[code]]}, 
    {code, {1, 2, 3}}, 
    {yr, 2003, 2010}];

BlockMap[
  ComapApply[{Query[MinMax, 1]/*(StringRiffle[#, "-"] &), Query[Mean, 2], Query[Mean, 3], Query[First, 4], Query[First, 5]}], 
  data, 
  {1, 5}, 
  1]
Improve this answer
$\endgroup$

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.