How to detect #N/A in a data frame (data taken from xlsx file) using pandas?

Anil Soren

The blank cells with no data can be checked with: if pd.isna(dataframe.loc[index_name, column_name] == True)

but if the cell has #N/A, the above command does not work nor dataframe.loc[index, column_name] == '#N/A'.

On reading that cell, it shows NaN, but the above codes does not work. My main target is to capture the release dates and store it in a list.

Dataframe Code

abokey

If you're reading your dataframe tft from a spreadsheet (and it seems to be the case here), you can use the parameter na_values of pandas.read_excel to consider some values (e.g #N/A) as NaN values like below :

tft= pd.read_excel("path_to_the_file.xlsx", na_values=["#N/A"])

Otherwise, if you want to preserve those #N/A values/strings, you can check/select them like this :

tft.loc[tft["Release Data"].eq("#N/A")] #will return a dataframe

In the first scenario, your code would be like this :

rel_date= []

for i in range(len(tft)):
    if pd.isna(tft["Release Date"])
        continue
    else:
        rel_date.append(int(str(tft.loc[i, "Release Date"]).split()[1]))

However, there is no need for the loop here, you can make a list of the release dates with this :

rel_date= (
            tft["Release Date"]
                .str.extract(("Release (\d{8})"), expand=False)
                .dropna()
                .astype(int)
                .drop_duplicates()
                .tolist()
          )
print(rel_date)
[20220603, 20220610]

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Reading 'raw' xlsx file from httr response into data frame

How do I remove NA from a data frame with the intention of using sapply on the data frame

How to detect pattern in cells of data frame and convert them to NA using R?

How to sort the data that has been taken from an xml file to a html table using php?

How to drop multiple columns from a data frame using pandas?

How to find duplicates from a full data frame using pandas?

Disaggregate pandas data frame using ratios from another data frame

How to store this JSON file in a Pandas data frame?

I want to search data in csv file where data is taken from file1.csv and searching in file2.csv in using pandas

reformatting a sequential data file into a data frame using pandas

How to detect and tabulate data from an excel file?

How to make a dictionary from the pandas data frame?

How to delete a column from a data frame with pandas?

How to delete a column from a data frame with pandas?

How to extract cell from pandas data frame

How to create Pandas data frame from a tuple

How to fill NA values in a pandas Data Frame with linear regression prediction using scikit learn in Python?

How to write this data into xlsx file

Retrive data from MongoDB using mongoose and generate CSV/XLSX file

Inserting data from pandas data frame to Sqlite using Sqlite query

Sorting Data from a Data Frame to a specific format using Pandas Python

How to order columns of the data frame according to the string variables taken from the column

Pandas: How to merge two data frames and fill NaN values using values from the second data frame

How to subset multiple pandas data frames from a single time series data frame using row level iteration?

how to convert nested dict data to data frame using pandas

Python: How do I syntax data scraping from xlsx file?

How to extract data from xlsx or csv file as JSON?

How to print data for a specific value (string) from a data frame (pandas)

How to filter data from Pandas Data frame Dynamically?