I need to determine the initial individual weight of each animal as accurately as possible, taking into account the following challenges:
- The animals may be weighed on different days.
- There may be missing data for some animals.
To address these issues, I currently use the formula:
$$ \text{Initial Individual Weight} = \text{Initial Total Weight} \cdot\dfrac{\text{Final Individual Weight}}{\text{Final Total Weight}} $$
However, this formula doesn't account for the weighing date variations and missing data. How can I adapt the calculation to overcome these challenges? Complexity is not a concern as the data is stored in a database, and I have the ability to create a script to perform this calculation.
Example:
- On January $1, 2023$, at $15:00:00$, I recorded the entry of $10$ sheep with a total weight of $100$ kilograms.
- Subsequently, I obtained the individual weights for each sheep on different dates. However, one of them was not weighed.
| Sheep | Date | Weight |
|---|---|---|
| sheep1 | 2023-01-15 09:12:00 | 15kg |
| sheep2 | 2023-01-16 14:25:00 | 10kg |
| sheep3 | 2023-01-17 11:34:00 | 12kg |
| sheep4 | 2023-01-18 18:52:00 | 14kg |
| sheep5 | 2023-01-19 07:41:00 | 11kg |
| sheep6 | 2023-01-20 16:59:00 | 13kg |
| sheep7 | 2023-01-21 22:08:00 | 16kg |
| sheep8 | 2023-01-22 12:37:00 | 9kg |
| sheep9 | 2023-01-23 05:19:00 | 17kg |