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’

What is np in your first code snippet and c in the second? None in the returned values indicate that the database has NULL values in that column. I would start by using a database client such as pgadmin or dbeaver to inspect the database tables to see if they have the data you think they do. — Code-Apprentice
– Code-Apprentice, Commented Jan 19, 2022 at 22:05
Right. Your first snippet shows you creating DelayStatus as a computed field in a DataFrame. That won't affect the database table. Did you intend to change the database itself? — Tim Roberts
– Tim Roberts, Commented Jan 19, 2022 at 22:08
@Code-Apprentice they're near-necessarily numpy and some database cursor (perhaps from SQLAlchemy), though they should still indicate which are in use! — ti7
– ti7, Commented Jan 19, 2022 at 22:10
@Code-Apprentice Yes, as what ti7 have mention, and thanks! I’ll look into a database client — Wen Qing
– Wen Qing, Commented Jan 19, 2022 at 22:16
@Tim Robert, yes that was what I had intended to do in the first place, which is to add in a new column into the database, however I realised the above code might only work in a df but not in a db, thanks for pointing it out — Wen Qing
– Wen Qing, Commented Jan 19, 2022 at 22:17

Code-Apprentice · Accepted Answer · 2022-01-20 16:34:18Z

1

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.

answered Jan 20, 2022 at 16:34

Code-Apprentice

84k26 gold badges162 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python: Data displayed as ‘None’ in dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related