Python: Data displayed as ‘None’ in dataframe

Question

I am trying to pull out data from my table into a data frame, but upon doing so, I was only shown 'None' instead of the number original value/data that i have input in my table. Codes as attached below:

conditions = [
(ontime['DepDelay'] <= 0),
(ontime['DepDelay'] >= 0)
]
values = ['2', '1']
ontime['DelayStatus'] = np.select(conditions, values)
pd.DataFrame(ontime)

However, upon pulling the data from the table 'ontime', it is showing me as 'None' instead of the value '1' or '2'

q4 = c.execute('''
SELECT ontime.Origin AS Origin,
   ontime.Dest AS Dest,
   ontime.DayOfMonth AS DayOfMonth,
   ontime.Month AS Month,
   ontime.Year AS Year,
   ontime.ArrDelay AS ArrivalDelay,
   ontime.DepDelay AS DepDelay,
   ontime.DelayStatus AS DelayStatus
FROM ontime
WHERE ontime.Cancelled='0' AND
  ontime.ArrDelay > '0' AND
  ontime.ArrDelay != 'Na' AND
  ontime.DepDelay != 'Na'
GROUP BY Origin, Dest, DayOfMonth,Month,Year
ORDER BY Year ASC, Month ASC, DayOfMonth ASC
''').fetchall()
q4 = pd.DataFrame (q4, columns['OriginCountry','Destination','DayOfMonthArrive','MonthArrive','YearArrive','ArrivalDelay','DepDelay','DelayStatus'])
pd.DataFrame(q4)

Although not entirely sure I have even change the class from a string to an integer and still I am getting the value ‘None’

user_1440565 · Answer

The first code snippet calculates the value for a column in a dataframe named "DelayStatus". It never saves that value back to the database. So when you do a SQL query directly, you still get None because the underlying database column has NULL.

You have at least 2 options from what I see:

You can just use the dataframe with the computed "DelayStatus" to do further processing.
You can write SQL code that updates the DelayStatus column in each row.

Python: Data displayed as ‘None’ in dataframe

1 Answers