Fill a gap with that device own average
Your task
Given readings with columns device, ts and reading, replace each missing reading with the mean of that device other readings. A device with no readings at all keeps null. Return device, ts and reading, sorted by device ascending and then ts ascending.
Examples
fill_with_device_mean(readings: dataframe) -> dataframeExample 1
Input
fill_with_device_mean(readings)readings
| device | ts | reading |
|---|---|---|
| a1 | 2024-05-01 | 20.5 |
| a1 | 2024-05-02 | NULL |
| a1 | 2024-05-03 | 22.5 |
| b2 | 2024-05-01 | NULL |
| b2 | 2024-05-02 | 31 |
| b2 | 2024-05-03 | 33 |
Output
| device | ts | reading |
|---|---|---|
| a1 | 2024-05-01 | 20.5 |
| a1 | 2024-05-02 | 21.5 |
| a1 | 2024-05-03 | 22.5 |
| b2 | 2024-05-01 | 32 |
| b2 | 2024-05-02 | 31 |
| b2 | 2024-05-03 | 33 |