1

I am trying to calculate the monthly averages for my data. My data looks as follows:

ID Value Month
001 542.1 9
021 4442.22 8
021 33411.3 8
214 5323.09 8
001 9644.11 7

Obviously this is just a very small example of the data I am working with. With the data above, I am trying to create a table with the ID's in the first column, the header being each month with the average values for that ID in that month. So the dataframe above after finding the averages would look like:

ID 7 8 9
001 9644.11 - 542.1
021 - 18926.76 -
214 - 85323.09 -

So as an example we had two values in month 8 for ID 021, so I took the average of both.

There are 12 months in my dataframe and over 150 ID's. Any help is appreciated. Please let me know if any clarifications are needed. I am working with pandas in python.

markovv.sim
  • 161
  • 1
  • 8
  • `df = df.groupby(['ID','month'])['Value'].mean()`? – brobertsp Oct 13 '21 at 18:19
  • [pivot_table](https://pandas.pydata.org/docs/reference/api/pandas.pivot_table.html) -> `df.pivot_table(index='ID', columns='Month', values='Value', aggfunc='mean', fill_value='-')` (note aggfunc mean is the default and could be omitted, remove the `fill_value='-'`if you want NaN instead of dashes) – Henry Ecker Oct 13 '21 at 18:57
  • This didn't make any changes to my df – markovv.sim Oct 13 '21 at 19:54

0 Answers0