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.