I need to drop all rows in a certain column where there is no value or is "null": Using Python and Pandas

user_15465344 0
Viewed 261

I need to drop all rows in a certain column where there is no value ie where it is "null". But the problem is that I do not know the name of the column. But know that it is the 5th column across so I have tired using some iloc methods like "notna" and "notnull"(see below). I have included a sample image of the type of data I am working with. The reason I am trying to do this is because there is a varying number of junk rows at the top of my csv file/dataframe that I am trying to get rid of. But the number of rows is different each time so I cannot use something that will just drop a certain known number of header rows. That is why I am trying to get rid of all null rows in a certain column because I know that it will also get rid of all the junk rows at the top of my dataset.

These are some methods I have tried using but they didn't work.

df = df[df[df.iloc[:, 4]].notna()]

df = df[pd.notnull(df[df.iloc[:, 4])]

df = df.dropna(subset=[df.iloc[:, 5]])

So for example here in this image I am trying to drop all rows where column 5 (the Date column) is null but that columns name is not "Date" yet because of the junk rows at the top. I am trying to get rid of all the null rows in column 5 so that only the populated columns remain and the junk rows at the top will be eliminated:

See the table here

enter image description here

pandas dataframe python
2 Answers

Your first two versions have an extra df[]. You can use either:

df = df[df.iloc[:, 4].notna()]

Or:

df = df[pd.notnull(df.iloc[:, 4])]

To break it down more explicitly, these are using boolean indexing. For example the first one uses df.iloc[:, 4].notna() to get a boolean index of notna and then filters df with it:

notna_boolean_index = df.iloc[:, 4].notna()
df = df.loc[notna_boolean_index] # can also leave out `.loc` for boolean indexes
user_131383640

You can simply parse your data by passing na_values and then do drop_na. To handle the junk rows at the top you can use skiprows while reading the csv. Below is sample code that might help you achieve the above,

Read csv,

df = pd.read_csv('/tmp/test.csv', na_values=['null'], keep_default_na=True, skiprows=3)

Although i believe null is taken by default as na value but you can use the above to be safe.

Then you can simple drop the na rows based on a column,

df.drop_na(subset=column_name)
user_36067230
Related