Pandas: create rows to fill numeric gaps

Question

From a DataFrame like this one:

ref             from    to
abcd            1       2
efgh            2       4
ijkl            1       3
mnop            3       4
qrst            4       4
uvwx            4       6

The idea would be to "fill gaps" between columns from and to so as to obtain:

ref             value
abcd            1
abcd            2
efgh            2
efgh            3
efgh            4
ijkl            1
ijkl            2
ijkl            3
mnop            3
mnop            4
qrst            4
uvwx            4
uvwx            5
uvwx            6

piRSquared · Accepted Answer · 2017-06-15 23:39:15Z

3

A numpy approach

r = df['ref'].values
f = df['from'].values
t = df['to'].values
pd.DataFrame(dict(
        ref=r.repeat(t - f + 1),
        value=np.concatenate([np.arange(f, t + 1) for f, t in zip(f, t)])
    ))

     ref  value
0   abcd      1
1   abcd      2
2   efgh      2
3   efgh      3
4   efgh      4
5   ijkl      1
6   ijkl      2
7   ijkl      3
8   mnop      3
9   mnop      4
10  qrst      4
11  uvwx      4
12  uvwx      5
13  uvwx      6

Timing

answered Jun 15, 2017 at 23:39

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Allen Qin · Accepted Answer · 2017-06-15 23:27:10Z

1

You can use groupby ref first, create a Series to fill the gaps and then transform it to a Dataframe and rename the column in the end.

df.groupby('ref').apply(lambda x: pd.Series(range(x['from'],x['to']+1)))\
                 .reset_index(level=1,drop=True)\
                 .reset_index()\
                 .rename(columns={0:'value'})
Out[22]: 
     ref  value
0   abcd      1
1   abcd      2
2   efgh      2
3   efgh      3
4   efgh      4
5   ijkl      1
6   ijkl      2
7   ijkl      3
8   mnop      3
9   mnop      4
10  qrst      4
11  uvwx      4
12  uvwx      5
13  uvwx      6

answered Jun 15, 2017 at 23:27

Allen Qin

20k9 gold badges55 silver badges68 bronze badges

Collectives™ on Stack Overflow

Pandas: create rows to fill numeric gaps

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related