If not date, get row number (using Python Pandas)

Python Leave a comment

This Python script returns the row number if the data in date column is not a date. It changes the data to NaT and using itertuple to check if its NaT, if yes, then return row number. Below is the dataframe screenshots before and after transform

Before using Pandas to transform data. Line 36 is not a date type.
After using Pandas, the dataframe indicates NaT (line 36) if it’s not a date type.

The Python program will change the non date type to NaT ( not sure what it stands for , maybe “Non applicable Type”…). Then using itertuple to iterate through Pandas dataframe, return row number if NaT and populate list with the row number. Below is the code:

import pandas as pd
import numpy as np

#location of file
excel_file = r"C:\Users\xxxxxx\Documents\xxxxxx\for_blog\gold_price_example.xlsx"

#try-catch if no file
try:
    df = pd.read_excel(excel_file)
except FileNotFoundError as ex:
    print("Unable to convert Excel to dataframe...please check")
    print (ex)
    quit()

print(df)

#after transform
df['sgd_date'] = pd.to_datetime(df['sgd_date'], format='%Y-%m-%d', errors='coerce')
print(df)

row_num = 0
list_not_date = []
for i in df.itertuples():
    if "NaT" in str(i):
        #print(row_num)
        list_not_date.append(row_num)
    row_num = row_num + 1

print(list_not_date)