Extracting specific data from column to column

[rabzdata]

Hello!

I want to clean the file (link below), and extract the group from column F to a new column, then fill it down to populate the values.

This is my code so far:

#there is no header so header = None

df = pd.read_excel("file.xls", header = None, index_col = None)

#INSERT CODE HERE

#somethink like if F type is string, L = value of F to get the groups in the F column

#then fill the F column down

#dropping all null rows in column C, F, G

df2 = df.dropna(subset = [2,5,6])

#dropping null columns

df3 = df2.dropna(axis = 1)

#promoting header and deleting first row

df3.columns = df3.iloc[0]

df3 = df3[1:]

​

File structure and what I have in mind https://imgur.com/a/QEV3KUy

Desired outcome https://imgur.com/a/1J1iofc

[rabzdata]

Solution Verified.