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