I want to interpolate (linear interpolation) data. but not indicated NA.
Here is my data.
| timestamp | id | strength |
|---|---|---|
| 1383260400000 | 10 | 0.007802251505435157 |
| 1383260400000 | 11 | 0.0050101566841440105 |
| 1383260400000 | 47 | 0.09910993935546883 |
| 1383260400000 | 48 | 0.16302926693340128 |
and expected data is :
| timestamp | id | strength |
|---|---|---|
| 1383260400000 | 10 | 0.007802251505435157 |
| 1383260400000 | 11 | 0.0050101566841440105 |
| 1383260400000 | 12 | interpolated strength |
| 1383260400000 | 13 | interpolated strength |
| 1383260400000 | 14 | interpolated strength |
| 1383260400000 | 15 | interpolated strength |
| 1383260400000 | 16 | interpolated strength |
| 1383260400000 | 17 | interpolated strength |
| 1383260400000 | … | interpolated strength |
| 1383260400000 | 47 | 0.09910993935546883 |
| 1383260400000 | 48 | 0.16302926693340128 |
and there are data with another timestamp.
| timestamp | id | strength |
|---|---|---|
| 1383261000000 | 73 | 0.00034018953748529387 |
| 1383261000000 | 80 | 0.015745603609017354 |
| 1383261000000 | 81 | 0.01332206498346922 |
| 1383261000000 | 101 | 0.003292329017912283 |
| 1383261000000 | 102 | 0.0028735259562922954 |
| 1383261000000 | 103 | 0.0033962211735905955 |
and the last id is 2025 and timestamp is 13833462000000
Advertisement
Answer
First convert values of id to index, so possible use lambda function in GroupBy.apply with Series.reindex and Series.interpolate:
f = lambda x: x.reindex(range(x.index.min(), x.index.max() + 1)).interpolate()
df = df.set_index('id').groupby('timestamp')['strength'].apply(f).reset_index()
print (df)
timestamp id strength
0 1383260400000 10 0.007802
1 1383260400000 11 0.005010
2 1383260400000 12 0.007624
3 1383260400000 13 0.010238
4 1383260400000 14 0.012852
5 1383260400000 15 0.015466
6 1383260400000 16 0.018080
7 1383260400000 17 0.020693
8 1383260400000 18 0.023307
9 1383260400000 19 0.025921
10 1383260400000 20 0.028535
11 1383260400000 21 0.031149
12 1383260400000 22 0.033763
13 1383260400000 23 0.036377
14 1383260400000 24 0.038991
15 1383260400000 25 0.041605
16 1383260400000 26 0.044218
17 1383260400000 27 0.046832
18 1383260400000 28 0.049446
19 1383260400000 29 0.052060
20 1383260400000 30 0.054674
21 1383260400000 31 0.057288
22 1383260400000 32 0.059902
23 1383260400000 33 0.062516
24 1383260400000 34 0.065129
25 1383260400000 35 0.067743
26 1383260400000 36 0.070357
27 1383260400000 37 0.072971
28 1383260400000 38 0.075585
29 1383260400000 39 0.078199
30 1383260400000 40 0.080813
31 1383260400000 41 0.083427
32 1383260400000 42 0.086041
33 1383260400000 43 0.088654
34 1383260400000 44 0.091268
35 1383260400000 45 0.093882
36 1383260400000 46 0.096496
37 1383260400000 47 0.099110
38 1383260400000 48 0.163029