Interpolates one series, and outputs constant for the second (constant) series

user_12155246 0
Viewed 75

I'm trying to create a function that fills in missing numbers in multiple series, with different numerical scales, and at the same time generates a constant column for each of the series.

from tika import parser
import pandas as pd
import numpy as np
import io

rawtext = parser.from_file('D:\Selenium\Texto.txt')
text = rawtext['content']
f = io.StringIO(text)
f.readline()
data = f.read()
f.readline()
def fill(d):
    idx = range(d['col1'].min(), d['col1'].max() + 1)
    return d.set_index('col1').reindex(idx, method='ffill').reset_index()
g = df['col1'].lt(df['col1'].shift()).cumsum()
df = pd.concat([fill(g) for k, g in df.groupby(g)], ignore_index=True)
print(df)

Is it possible to create the following function with Pandas?

Sample dataframe:

1029 400
1035 400
1031 340
1039 340
1020 503
1025 503

Expected output:

1029 400
1030 400
1031 400
1032 400
1033 400
1034 400
1035 400
1031 340
1032 340
1033 340
1034 340
1035 340
1036 340
1037 340
1038 340
1039 340
1020 503
1021 503
1022 503
1023 503
1024 503
1025 503
missing-data pandas dataframe python
1 Answers
def fill(d):
    idx = range(d['col1'].min(), d['col1'].max() + 1)
    return d.set_index('col1').reindex(idx, method='ffill').reset_index()


g = df['col1'].lt(df['col1'].shift()).cumsum()
df = pd.concat([fill(g) for k, g in df.groupby(g)], ignore_index=True)

Details:

Identify all the monotonically increasing sections in the col1 where there are missing values. This can be done with the help of Series.lt + Series.shift and Series.cumsum to create a grouper g:

print(g)
0    0
1    0
2    1
3    1
4    2
5    2
Name: col1, dtype: int64

Then groupby the dataframe on this grouper and for each grouped frame fill the gaps using a custom define function which makes the use of reindex method of the dataframe to fill the missing values.

print(fill(g)) # sample filled values for first group
   col1  col2
0  1029   400
1  1030   400
2  1031   400
3  1032   400
4  1033   400
5  1034   400
6  1035   400

Finally using pd.concat, concatenate all these grouped frames after filling the missing values.

print(df)
    col1  col2
0   1029   400
1   1030   400
2   1031   400
3   1032   400
4   1033   400
5   1034   400
6   1035   400
7   1031   340
8   1032   340
9   1033   340
10  1034   340
11  1035   340
12  1036   340
13  1037   340
14  1038   340
15  1039   340
16  1020   503
17  1021   503
18  1022   503
19  1023   503
20  1024   503
21  1025   503
user_128331660
Related