Search code examples
pythonpandasdataframevariablesfill

Foward filling moving limit

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?

enter image description here

Solution

  • 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