How to effectively read excel in pandas dataframe-CodePudding

I have unfortunately no experience with excel dataset in pandas and its first time that I have to work with it. The dataset look like this:

 Columa_1   Column_2   Column3      Column4   Column5
  Germany   Business    Automobile   32400     text1
                        Pesticide    348700    text2
                        Chemisty     97245     text3
            Homework    Cleaning     56248     text3
                        Cooking      67354     text4
            Schooling   attending    78464     text5

The dataset in excel look like this, from excel point of view its clear that Column_1 applies to all rows, similarly values: Pesticide and chemisty also has Column_2 as Business etc. etc.

However, when I change this sheet into Pandas dataframe, the whole blank are replaced with NA's :( How to deal with this so intuitive idea of Excel from Pandas point of view? Is there any way-around?

Thanks and Best Regards,

CodePudding user response：

You can use ffill (forward fill) to carry non-NA values forward:

df = pd.read_excel(...).ffill()