Pad middle values based on previouse and next values

Question

Let's say I've df Like this

   col     col2
0    0  Repeat1
1    3  Repeat2
2    5  Repeat3
3    7  Repeat4
4    9  Repeat5

Reproducable

L= [0,3,5,7,9]
L2 = ['Repeat1','Repeat2','Repeat3','Repeat4','Repeat5']

import pandas as pd
df = pd.DataFrame({'col':L})
df['col2']= L2
print (df)

How can fill missing intermidaite values such that my df will looks like this

  col     col2
0    0  Repeat1
1    1  Repeat1
2    2  Repeat1
3    3  Repeat2
4    4  Repeat2
5    5  Repeat3
6    6  Repeat3
7    7  Repeat4
8    8  Repeat4
9    9  Repeat5

Similar threads I've tried

Filling missing middle values in pandas dataframe (Filling Nan values for intermediate values but I don't need Nan)

Fill pandas dataframe with values in between (Very Big approch. I'm looking any functional appraoch)

Both cases helped me some extent But i was wondering is any ways to do it? :D

score 3 · Answer 1 · answered Feb 14 '23 at 14:39

3

You can reindex and ffill with "col" as temporary index:

out = (df.set_index('col')
         .reindex(range(df['col'].max()+1))
         .ffill()
         .reset_index()
      )

Output:

   col     col2
0    0  Repeat1
1    1  Repeat1
2    2  Repeat1
3    3  Repeat2
4    4  Repeat2
5    5  Repeat3
6    6  Repeat3
7    7  Repeat4
8    8  Repeat4
9    9  Repeat5

answered Feb 14 '23 at 14:39

mozway

194,879
13
39
75

Thank you @mozway ..How to do if my col has duplicate values? I can't use this If I've duplicate values It's throwing `ValueError: cannot reindex on an axis with duplicate labels` – Bhargav - Retarded Skills Feb 14 '23 at 14:52
@Bhargav what would be the expected behavior then? Can you provide another example? (almost) Everything is possible but you have to clearly define the rules ;) – mozway Feb 14 '23 at 14:53
I'm sorry updating... – Bhargav - Retarded Skills Feb 14 '23 at 14:53
I'm figuring out myself @mozway ..I will reach out if I fail to figure it out myself.. :D – Bhargav - Retarded Skills Feb 14 '23 at 15:10
1

@Bhargav sure, no worries (it's always better to try to scratch one's own itches, best way to learn!) – mozway Feb 14 '23 at 15:32

score 1 · Accepted Answer · answered Feb 14 '23 at 15:05

You can also merge and ffill

(df.merge(pd.DataFrame({'col': range(df['col'].max()+1)}), how='right')
       .ffill()
    )

Output:

   col     col2
0    0  Repeat1
1    1  Repeat1
2    2  Repeat1
3    3  Repeat2
4    4  Repeat2
5    5  Repeat3
6    6  Repeat3
7    7  Repeat4
8    8  Repeat4
9    9  Repeat5

PaulS · Answer 3 · 2023-02-14T20:30:13.020

Another possible solution, which is based on pandas.concat:

pd.concat([pd.DataFrame({'col': range(df['col'].max()+1)}),
            df.set_index('col')], axis=1).ffill()

Or, alternatively:

(pd.concat([df, pd.DataFrame(
    {'col': list(set(range(1, df.col.max()+1)).difference(df.col))})])
 .sort_values('col').ffill().reset_index(drop=True))

Output:

   col     col2
0    0  Repeat1
1    1  Repeat1
2    2  Repeat1
3    3  Repeat2
4    4  Repeat2
5    5  Repeat3
6    6  Repeat3
7    7  Repeat4
8    8  Repeat4
9    9  Repeat5

Pad middle values based on previouse and next values

3 Answers3