CODEWITHSUNDEEP
pythondataframedictionarypandas-groupby
Tom Klarin
Tom Klarin

Reputation: 33

Grouping Keys in a dictionary based on dates?

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: enter image description here

Upvotes: 0

Views: 90

Answers (1)

Timus
Timus

Reputation: 11321

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}

Upvotes: 1

Related Questions