Grouping Keys in a dictionary based on dates?

Question

I have a dictionary (mydict) which its keys are dates (timestamp), and its items are data frames (they all in the same shape) with two columns index and values as follows:

  mydict: {
    "Timestamp('2019-01-04 06:05:32')" : {
      1, 4
      2,23
      3,2
      4,32
    },
    " Timestamp('2019-01-05 05:02:12')" : {
      1, 6
      2,3
      3,20
      4, 2

    }
    "Timestamp('2019-01-10 08:05:32')" : {
      1, 7
      2,33
      3,5
      4,21
    },
    " Timestamp('2019-01-11 04:02:12')" : {
      1, 4
      2,11
      3,7
      4,11

    }

I would like to group the keys based on the dates which are in the same week of the year. Then take the average of the data frames. For example, the first and second keys are in the first week of the year, so they should group together, and the same for third and fourth keys. Thus, my desired dictionary should be like this:

   mynew_dict:
    "week1" : {
      1, 5
      2,13
      3,11
      4,17
    },
    "week2" : {
      1, 5.5
      2,22
      3,6
      4,16

    }

Just to show the real values of the items for each key:

Tnx, In the first for loop I got this error: *AttributeError: 'Timestamp' object has no attribute 'strip'* — Tom Klarin, Jul 16 '21 at 11:48
No, they are in timestamp type, however, when I tried to use them as a string, it works in the first line (in the for loop), but I got this error error 'ValueError: Invalid format string' in the second line. — Tom Klarin, Jul 16 '21 at 12:18

Timus · Accepted Answer · 2021-07-16T21:38:30.953

You could try the following:

from itertools import groupby

mynew_dict = {}
for (year, week), keys in groupby(sorted(mydict),
                                  lambda key: (key.year, key.weekofyear)):
    dfs = [mydict[key] for key in keys]
    mynew_dict[f'week {week} of year {year}'] = sum(dfs[1:], dfs[0]) / len(dfs)

Result for your sample dictionary (if I understand it correctly)

mydict = {
    pd.Timestamp('2019-01-04 06:05:32'): pd.DataFrame([4, 23, 2, 32]),
    pd.Timestamp('2019-01-05 05:02:12'): pd.DataFrame([6, 3, 20, 2]),
    pd.Timestamp('2019-01-10 08:05:32'): pd.DataFrame([7, 33, 5, 21]),
    pd.Timestamp('2019-01-11 04:02:12'): pd.DataFrame([4, 11, 7, 11])
}

is

{'week 1 of year 2019':
      0
0   5.0
1  13.0
2  11.0
3  17.0,
 'week 2 of year 2019':
      0
0   5.5
1  22.0
2   6.0
3  16.0}

Grouping Keys in a dictionary based on dates?

1 Answers1