I tested some stuff out and did some more research. It appears pandas does not currently offer the functionality you're looking for.

df['cat'].interpolate(method='linear') will only work if the first/last values are filled in already. You would have to manually assign df.loc[5, 'cat1'] = 6 in this example, then a linear interpolation would work.

Some Options:

1. If the data is small enough, you can always export to Excel and use the fill there, then bring back into pandas.

2. Analyze the patterns yourself and design your own fill methods. For example, to get the year, you can use df['year'] = df.index.to_series().apply(lambda x: 2019 if x % 2 == 0 else 2020).

There are other Stack Overflow questions very similar to this, and none that I saw have a generic answer.

Try using fillna(value) method where it replaces the Nan with the value passed into it.

Below is my answer for the year. I understand that the cat1 is handled and cat2 can be ignored. One assumption I've made base on looking at the question is that the repeat pattern is consistent. If not, the factorize may not work.

The idea is to use factorise to extract the repeat pattern. Then form a list of the repeat pattern. The excel drag function is a cycle of the repeat pattern. So it's natural to use itertools cycle. (PS: this is first done in @jabellcu answer, so I don't want to take credit for it. If you think factorize + cycle is good, please check his answer.)

An advantage is that the codes is generic. You don't have to hardcode the values. You can turn it into a function and call it for whatever values there are in the dataframe.

import pandas as pd
d = { 'year': [2019,2020,2019,2020,np.nan,np.nan], 'cat1': [1,2,3,4,np.nan,np.nan], 'cat2': ['c1','c1','c1','c2',np.nan,np.nan]}
df = pd.DataFrame(data=d)
df

Enhanced Answer:

l = df['year'].factorize()[1].to_list()
c = cycle(l)
df['year'] = [next(c) for i in range(len(df))]

df['cat1'] = df['cat1'].interpolate(method='krogh') 
df['cat2'] = df['cat2'].fillna(method='ffill')
df

PS: I've left a question on post regarding how to handle cat2. Currently, I just assume it's ffill for the time being.

From the reading of the question, I assume that you don't need detection logic. So I won't provide. I just provide the conversion logic.

I would do the following:

from pandas.api.types import is_numeric_dtype
from itertools import cycle

def excel_drag(column):
    
    S = column[column.bfill().dropna().index].copy()  # drop last empty values
    numeric = is_numeric_dtype(S)
    groups = S.groupby(S, sort=False).apply(lambda df: len(df.index))
    
    if (len(groups) == len(S)):
        if numeric:
            # Extrapolate
            return column.interpolate(method='krogh')
        else:
            # ffill
            return column.ffill()
        
    elif (groups == groups.iloc[0]).all():  # All equal
        # Repeat sequence
        seq_len = len(groups)
        seq = cycle(S.iloc[:seq_len].values)
        filling = column[column.bfill().isna()].apply(lambda x: next(seq))
        return column.fillna(filling)
    
    else:
        # ffill
        return column.ffill()

With that function, df.apply(excel_drag, axis=0) results in:

     year  cat1 cat2
0  2019.0   1.0   c1
1  2020.0   2.0   c1
2  2019.0   3.0   c1
3  2020.0   4.0   c2
4  2019.0   5.0   c2
5  2020.0   6.0   c2