Message Boards Message Boards

1
|
5836 Views
|
6 Replies
|
6 Total Likes
View groups...
Share
Share this post:

Extract data from a list based on the time interval

Hi,

  1. Which time interval all three lists have the data? Is it possible to specify that?

  2. Can we extract elements from list2 between 2016-2017?

    list1 = <|"Alberta" -> {2015, 2015, 2015, 2015, 2015, 2015, 2015, 
     2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016,
      2016, 2016, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 
     2017, 2017, 2017, 2017, 2017, 2018, 2018, 2018, 2018, 2018, 2018,
      2018, 2018, 2018, 2018, 2018, 2018}, 
   "Manitoba" -> {2011, 2011, 2011, 2012, 2012, 2012, 2012, 2012, 
     2012, 2012, 2012, 2012, 2012, 2013, 2013, 2013, 2013, 2013, 2013,
      2013, 2013, 2013, 2013, 2014, 2014, 2014, 2014, 2014, 2014, 
     2014, 2014, 2014, 2014, 2014, 2015, 2015, 2015, 2015, 2015, 2015,
      2015, 2015, 2015, 2015, 2015, 2015, 2015, 2016, 2016, 2016, 
     2016, 2016, 2016, 2016, 2016, 2016, 2016, 2017, 2017, 2017, 2017,
      2017, 2017, 2017, 2017, 2017, 2017, 2018, 2018, 2018, 2018, 
     2018, 2018, 2018, 2018, 2018, 2018}, 
   "Ottawa" -> {2010.`, 2010.`, 2011.`, 2011.`, 2011.`, 2011.`, 
     2011.`, 2011.`, 2011.`, 2011.`, 2011.`, 2011.`, 2011.`, 2011.`, 
     2012.`, 2012.`, 2012.`, 2012.`, 2012.`, 2012.`, 2012.`, 2012.`, 
     2012.`, 2012.`, 2012.`, 2012.`, 2012.`, 2013.`, 2013.`, 2013.`, 
     2013.`, 2013.`, 2013.`, 2013.`, 2013.`, 2013.`, 2013.`, 2013.`, 
     2013.`, 2013.`, 2014.`, 2014.`, 2014.`, 2014.`, 2014.`, 2014.`, 
     2014.`, 2014.`, 2014.`, 2014.`, 2014.`, 2014.`, 2014.`, 2015.`, 
     2015.`, 2015.`, 2015.`, 2015.`, 2015.`, 2015.`, 2015.`, 2015.`, 
     2015.`, 2015.`, 2015.`, 2015.`, 2016, 2016, 2016, 2016, 2016, 
     2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2017.`, 2017.`, 
     2017.`, 2017.`, 2017.`, 2017.`, 2017.`, 2017.`, 2017.`, 2017.`, 
     2017.`, 2017.`, 2017.`, 2018.`, 2018.`, 2018.`, 2018.`, 2018.`, 
     2018.`, 2018.`, 2018.`, 2018.`, 2018.`, 2018.`, 2018.`, 2018.`}|>;

minmax1 = MinMax /@ list1;

int = Interval /@ Map[DateObject@*ToString, minmax1, {2}];

tlp1 = TimelinePlot[int, ImageSize -> Full];

list2 = <|"Alberta" -> {8.5`, 21.3`, 12.6`, 49.3`, 15.1`, 22.5`, 
     485.5`, 136.`, 34.`, 76.5`, 28.1`, 116.`, 33.3`, 1.4`, 3.2`, 
     13.1`, 3.6`, 20.4`, 47.9`, 513.5`, 40.6`, 5.4`, 52.7`, 54.2`, 
     70.8`, 82.1`, 3.9`, 0.4`, 4.8`, 34.4`, 50.1`, 76.2`, 475.6`, 
     56.3`, 16.9`, 77.3`, 2.4`, 74.4`, 12.1`, 22.1`, 0.1`, 14.`, 
     66.6`, 28.2`, 110.9`}, 
   "Manitoba" -> {28.`, 241.6`, 938.`, 545.2`, 242.8`, 152.1`, 61.3`, 
     5.`, 21.2`, 87.1`, 150.4`, 490.`, 1755.1`, 165.9`, 58.5`, 32.5`, 
     76.5`, 42.2`, 6.5`, 44.`, 20.7`, 50.8`, 497.6`, 138.9`, 47.3`, 
     22.9`, 33.3`, 53.6`, 7.`, 12.8`, 97.4`, 105.6`, 183.8`, 702.6`, 
     188.`, 201.6`, 126.2`, 13.5`, 19.3`, 4.1`, 17.4`, 5.4`, 32.4`, 
     79.2`, 52.1`, 19.5`, 758.7`, 250.6`, 116.5`, 126.2`, 3.8`, 83.5`,
      13.8`, 0.5`, 42.8`, 253.`, 890.7`, 154.6`, 21.8`, 120.1`, 41.9`,
      42.8`, 1.3`, 55.8`, 202.5`, 96.7`, 737.5`, 343.5`, 215.9`, 
     63.6`, 6.3`, 1.7`, 0.8`, 11.6`, 45.`, 146.2`, 347.1`}, 
   "Ottawa" -> {23.7`, 487.2`, 41.`, 24.`, 37.7`, 77.1`, 28.7`, 27.1`,
      36.9`, 14.5`, 0.4`, 22.3`, 4.2`, 313.9`, 63.1`, 74.4`, 17.4`, 
     48.8`, 48.9`, 5.`, 6.4`, 1.9`, 0.`, 56.4`, 49.2`, 33.`, 404.5`, 
     75.8`, 48.5`, 15.5`, 15.`, 38.7`, 63.2`, 6.9`, 0.2`, 14.`, 8.3`, 
     23.9`, 74.`, 384.`, 84.7`, 18.8`, 48.5`, 37.7`, 52.4`, 37.5`, 
     17.9`, 3.`, 2.4`, 19.8`, 26.4`, 69.3`, 418.4`, 14.8`, 38.6`, 
     65.2`, 56.`, 47.`, 96.1`, 2.2`, 0.`, 10.`, 74.6`, 35.8`, 16.7`, 
     457.`, 6.5`, 13.6`, 18.6`, 6.7`, 74.8`, 19.8`, 11.4`, 11.1`, 
     3.5`, 62.7`, 86.1`, 36.9`, 351.7`, 21.8`, 14.4`, 28.4`, 110.8`, 
     50.7`, 15.`, 17.1`, 23.`, 49.1`, 39.7`, 15.6`, 44.2`, 429.8`, 
     25.3`, 44.2`, 14.2`, 67.3`, 70.8`, 36.8`, 3.3`, 4.8`, 8.8`, 
     48.1`, 13.2`, 66.7`, 403.5`}|>;
POSTED BY: M.A. Ghorbani
6 Replies
Sort By:

Hello Mohammad,

maybe the easiest way is to create a nested association from your data:

as = Merge[#, Identity] & /@ Apply[Association@*Rule, Merge[{Round@list1, list2}, Transpose], {2}];

(Note that your year numbers should all be integers (for being used as keys), therefore I write Round@list1.) With this you can ask questions like:

What happened in Manitoba 2014?

as["Manitoba", 2014]

Which years have all cities in common? 

intersec = Intersection @@ Keys[Values@as]

What are the data for the years 2015 until 2017?

as[[All, Key /@ Range[2015, 2017]]]

What are the data for the common years?

as[[All, Key /@ intersec]]

Does that help? Regards -- Henrik
POSTED BY: Henrik Schachner

Henrik,

That's great work. Thank you so much.

Could you please have a look at the enclosed file.

Attachments:
Henrik's Solution.nb
POSTED BY: M.A. Ghorbani

Dear Mohammad,

The problem is that you do not have clean data: Most of the year numbers in list1 are strings, only some are numbers, and similar with the data values in list2. So I simply changed all to numbers, and now it seems to work.

Regards -- Henrik

Attachments:
Henrik_Solution_1.nb
POSTED BY: Henrik Schachner
Rohit Namjoshi
Rohit Namjoshi
Posted 4 years ago

Hi Mohammad,

Here is another way to construct the association from @Henrik Schachner 's nice solution.

as = {Round@list11, list22} // Merge[Transpose] // Map[GroupBy[First -> Last]];

Not sure if you are aware, but this dataset seems to have the same pattern as the rainfall dataset you shared a while back. The last value for a year is equal to the sum of the previous values for that year. e.g.

as["BURSA"] // Map[(Total@Most@# - Last@#) & /* Chop]

(*
<|1930 -> 0, 1931 -> 0, 1932 -> 0, 1933 -> 0, 1934 -> 0, 1935 -> 0, 
 1936 -> 0, 1937 -> 0, 1938 -> 0, 1939 -> 0, 1940 -> 0, 1941 -> 0, 
 1942 -> 0, 1943 -> 0, 1944 -> 0, 1945 -> 0, 1946 -> 0, 1947 -> 0, 
 1948 -> 0, 1949 -> 0, 1950 -> 0, 1951 -> 0, 1952 -> 0, 1953 -> 0, 
 1954 -> 0, 1955 -> 0, 1956 -> 0, 1957 -> 0, 1958 -> 0, 1959 -> 0, 
 1960 -> 0, 1961 -> 0, 1962 -> 0, 1963 -> 0, 1964 -> 0, 1965 -> 0, 
 1966 -> 0, 1967 -> 0, 1968 -> 0, 1969 -> 0, 1970 -> 0, 1971 -> 0, 
 1972 -> 0, 1973 -> 0, 1974 -> 0, 1975 -> 0, 1976 -> 0, 1977 -> 0, 
 1978 -> 0, 1979 -> 0, 1980 -> 0, 1981 -> 0, 1982 -> 0, 1983 -> 0, 
 1984 -> 0, 1985 -> 0, 1986 -> 0, 1987 -> 0, 1988 -> 0, 1989 -> 0, 
 1990 -> 0, 1991 -> 0, 1992 -> 0, 1993 -> 0, 1994 -> 0, 1995 -> 0, 
 1996 -> 0, 1997 -> 0, 1998 -> 0, 1999 -> 0, 2000 -> 0, 2001 -> 0, 
 2002 -> 0, 2003 -> 0, 2004 -> 0, 2005 -> 0, 2006 -> 0, 2007 -> 0, 
 2008 -> 0, 2009 -> 0, 2010 -> 0, 2011 -> 0, 2012 -> 0, 2013 -> 0, 
 2014 -> 0, 2015 -> 0, 2016 -> 0, 2017 -> 0, 2018 -> 509.2|>
*)

Might want to remove those values depending on how you intend to use this data.
POSTED BY: Rohit Namjoshi

Dear Rohit and Henrik,

I really appreciate your kindness and help to solve the problem.

I did somethings according to your solution way and got the final list at the last. For example; the first station "ACIPAYAM" has the incomplete data in 1977 and the missing data in 1978 as below:

1977 -> {73.6, 40.2}, 1978 -> Missing["KeyAbsent", 1978].

The second or "ADANA BÖLGE" station has the full record. Now, how do I extract the list of stations with full records? Is it possible to plot the time series for one of the stations with the full record?

Attachments:
Final List.nb
POSTED BY: M.A. Ghorbani
Rohit Namjoshi
Rohit Namjoshi
Posted 4 years ago

Hi Mohammad,

What is the definition of "full record"? Some years have 12 items, some have 13, in many cases the last item for a year is equal to the sum of the previous items in the year (as I pointed out earlier), is that last item part of the "full record"?

J["ACIPAYAM"] // Map[Length]

J["ACIPAYAM"] // DeleteMissing // Map[(Total@Most@# - Last@#) & /* Chop]

The data can be plotted in many ways, it is not clear to me what you mean by "plot the time series"? What is the time dimension?

J["ACIPAYAM"] // DeleteMissing //
 BarChart[#,
   ChartLabels -> Automatic,
   ImageSize -> 1000,
   AspectRatio -> 1/3,
   ColorFunction -> "Rainbow"] &
POSTED BY: Rohit Namjoshi
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Tag limit exceeded
Note: Only the first five people you tag will receive an email notification; the other tagged names will appear as links to their profiles.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract