Foward filling moving limit

Question

I have a dataframe with a lot of blanks. The first table on the image. I want to reach the right table. My idea is to use ffill() with a moving limit. The limit would adjust to what is on the right. So first we count the consecutive elements on the right and fill level2 (yellow) and then do the same for the level1 (green). Is it even possible?

score 0 · Accepted Answer · answered Mar 24 '21 at 21:32

Assuming the empty cells are empty strings (""), you can try:

df[df == ""] = np.nan
m = ~df["Level 1"].isna()
df.loc[m, "Level 2"] = ""
df.loc[m, "Level 3"] = ""
df.loc[:, ["Level 1", "Level 2"]] = df.loc[:, ["Level 1", "Level 2"]].ffill()
print(df.fillna(""))

Prints:

      Level 1   Level 2   Level 3
0   President                    
1   President    Office          
2   President    Office    Lucien
3   President    Office   Theresa
4         MEP                    
5         MEP    Bureau          
6         MEP    Bureau    Martin
7         MEP    Bureau  Juliette
8         MEP    Bureau     Romeo
9      Groups                    
10     Groups  Comittee          
11     Groups  Comittee      Paul
12     Groups  Comittee      Marc
13     Groups   Sub Com          
14     Groups   Sub Com    Julius
15     Groups   Sub Com    Marcus
16     Groups   Sub Com  Aurelius

Thank you very much. Can you explain what the tilde operator sign does ? — Guillaume Teissier, Mar 25 '21 at 07:51
@GuillaumeTeissier `~` operator is a bitwise NOT. https://stackoverflow.com/questions/46054318/tilde-sign-in-python-dataframe — Andrej Kesely, Mar 25 '21 at 10:07

Foward filling moving limit

1 Answers1