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
:
JavaScript
x
3
1
f = lambda x: x.reindex(range(x.index.min(), x.index.max() + 1)).interpolate()
2
df = df.set_index('id').groupby('timestamp')['strength'].apply(f).reset_index()
3
JavaScript
1
42
42
1
print (df)
2
timestamp id strength
3
0 1383260400000 10 0.007802
4
1 1383260400000 11 0.005010
5
2 1383260400000 12 0.007624
6
3 1383260400000 13 0.010238
7
4 1383260400000 14 0.012852
8
5 1383260400000 15 0.015466
9
6 1383260400000 16 0.018080
10
7 1383260400000 17 0.020693
11
8 1383260400000 18 0.023307
12
9 1383260400000 19 0.025921
13
10 1383260400000 20 0.028535
14
11 1383260400000 21 0.031149
15
12 1383260400000 22 0.033763
16
13 1383260400000 23 0.036377
17
14 1383260400000 24 0.038991
18
15 1383260400000 25 0.041605
19
16 1383260400000 26 0.044218
20
17 1383260400000 27 0.046832
21
18 1383260400000 28 0.049446
22
19 1383260400000 29 0.052060
23
20 1383260400000 30 0.054674
24
21 1383260400000 31 0.057288
25
22 1383260400000 32 0.059902
26
23 1383260400000 33 0.062516
27
24 1383260400000 34 0.065129
28
25 1383260400000 35 0.067743
29
26 1383260400000 36 0.070357
30
27 1383260400000 37 0.072971
31
28 1383260400000 38 0.075585
32
29 1383260400000 39 0.078199
33
30 1383260400000 40 0.080813
34
31 1383260400000 41 0.083427
35
32 1383260400000 42 0.086041
36
33 1383260400000 43 0.088654
37
34 1383260400000 44 0.091268
38
35 1383260400000 45 0.093882
39
36 1383260400000 46 0.096496
40
37 1383260400000 47 0.099110
41
38 1383260400000 48 0.163029
42