2018-02-01 12:45:06 8 Comments
Having such a Pandas DataFrame df
with a sorted, numeric index (representing f.e. time or distance) with possible duplicate values:
a b
0 4.0 1.0
1.5 5.5 2.5
1.5 5.5 2.5
2 6.0 3.0
4.5 8.5 5.5
I would like to create a column c
, with values from column a
with index shifts that are matching the original index. All index shifts that are not matching the original index should be still taken into account when filling in the original index values that did not get the value assigned, using f.e. linear interpolation.
Example:
Taking 0.5 as an example index shift, column c
would be constructed from column a
with index values of 0, 0.5, 1.5, 2, 2.5, 4.5 and 5, giving the following intermediate result with the missing values marked below as (i)
:
c
0 Nan(i)
0.5 4.0
1.5 4.75(i)
2 5.5
2.5 6.0
4.5 7.25(i)
5 8.5
The final result should be indexed using the original indexes used in df
:
a b c
0 4.0 1.0 Nan(i)
1.5 5.5 2.5 4.75(i)
1.5 5.5 2.5 4.75(i)
2 6.0 3.0 5.5
4.5 8.5 5.5 7.25(i)
There is a question how to take the value for the duplicate index, in this example one value was chosen, but a mean might be a better appraoch.
Related Questions
Sponsored Content
14 Answered Questions
3 Answered Questions
[SOLVED] How to query MultiIndex index columns values in pandas
- 2013-07-29 09:56:50
- Vyacheslav Shkolyar
- 67646 View
- 43 Score
- 3 Answer
- Tags: python pandas indexing slice multi-index
18 Answered Questions
6 Answered Questions
22 Answered Questions
[SOLVED] Adding new column to existing DataFrame in Python pandas
- 2012-09-23 19:00:01
- tomasz74
- 1436967 View
- 706 Score
- 22 Answer
- Tags: python pandas dataframe chained-assignment
12 Answered Questions
7 Answered Questions
1 Answered Questions
[SOLVED] For a DataFrame with sorted, float index and columns, calculate a value using linear interpolation, based on a DataFrame values
- 2018-02-21 13:51:49
- Krzysztof Słowiński
- 74 View
- 4 Score
- 1 Answer
- Tags: python pandas numpy dataframe interpolation
1 Answered Questions
lookup, interpolate and match between pandas dataframes
- 2017-11-24 14:58:59
- python_newbie
- 261 View
- 0 Score
- 1 Answer
- Tags: python pandas dataframe interpolation
2 comments
@Krzysztof Słowiński 2018-02-01 15:10:49
This is my current approach that takes one of the duplicate index values into account when constructing a new column.
@Mr. T 2018-02-01 14:33:50
I think, this is, what you try to achieve:
You can of course fill your new column with other values than numpy
NaN
.@Krzysztof Słowiński 2018-02-13 09:00:40
It is a good solution, but not all values are taken for filling in the missing values, only the ones that after the shift are contained in the original index. The original index is not evenly spaced and there are any shift values possible, so in the worst case no values will be present.
@Krzysztof Słowiński 2018-02-13 09:04:33
Please check the description with the intermediate step for calculating the result.
@Krzysztof Słowiński 2018-02-13 09:12:33
I've updated the description to better capture the idea of the problem.