Getting mean out of dataframe in a dictionary

Question

I have the below question to get a mean out of dataframe in a dictionary. The dictionary part is messing me up and wondering if anyone can help guide/teach me on this?

Question - Get the mean SalesDif for each Manager in a dictionary ans7, which has Manager as the key and mean results as the value.

    Store   TimeStamp   Manager Projected_Sales Sales   DT TTL  SalesDif
0   4007    2017-02-04 07:10:00 Monica H    281.0   296.0   192 15.0
1   4007    2017-02-04 08:04:00 David H 670.0   347.0   111 323.0
2   4007    2017-02-03 18:41:00 David H 784.0   649.0   143 135.0
3   4007    2017-02-04 10:06:00 David H 852.0   750.0   158 102.0
4   4007    2017-02-05 10:57:00 Zoey D  54.0    108.0   104 54.0
... ... ... ... ... ... ... ...
13173   16250   2017-09-25 13:22:00 Carmella R  669.0   758.0   238 89.0

[pandas-get-column-average-mean-with-round-value](https://stackoverflow.com/questions/31037298/pandas-get-column-average-mean-with-round-value) — Patrick Artner, Jul 05 '20 at 19:21

score 0 · Answer 1 · answered Jul 05 '20 at 20:50

Your dataframe for others to easily reproduce your problem: Can be easily created by coping your dataframe (df.to_clipboard()) and then using: dict_df = pd.read_clipboard().to_dict()

df = pd.DataFrame.from_dict({'Store': {0: '2017-02-04',
  1: '2017-02-04',
  2: '2017-02-03',
  3: '2017-02-04',
  4: '2017-02-05'},
 'TimeStamp': {0: '07:10:00',
  1: '08:04:00',
  2: '18:41:00',
  3: '10:06:00',
  4: '10:57:00'},
 'Manager': {0: 'Monica', 1: 'David', 2: 'David', 3: 'David', 4: 'Zoey'},
 'Projected_Sales': {0: 'H', 1: 'H', 2: 'H', 3: 'H', 4: 'D'},
 'Sales': {0: 281.0, 1: 670.0, 2: 784.0, 3: 852.0, 4: 54.0},
 'DT': {0: 296.0, 1: 347.0, 2: 649.0, 3: 750.0, 4: 108.0},
 'TTL': {0: 192, 1: 111, 2: 143, 3: 158, 4: 104},
 'SalesDif': {0: 15.0, 1: 323.0, 2: 135.0, 3: 102.0, 4: 54.0}})

The dictionary with the average sales dif per Manager:

dict_manager_sales = df[['Manager', 'SalesDif']].groupby(['Manager'])['SalesDif'].mean().to_dict()

First, slice the dataframe with the columns you want:

df[['Manager', 'SalesDif']]

Second, group the values and tell the groupby which method to use and for which columns:

.groupby(['Manager'])['SalesDif'].mean()

Third, create a dictionary from the dataframe, the index of the df will be the keys, the column (only 1 allowed) will be the values:

.to_dict()

Getting mean out of dataframe in a dictionary

1 Answers1