0

I have this airport.csv file and I'm trying to insert these values to an Oracle table.
(Here is the small chunk of data for reference!)

4 code                               AAN
lat                              24.25
lon                              55.75
name                    Al Ain Airport
city                     Ayn al Faydah
state                        Abu Dhabi
country           United Arab Emirates
woeid                         12523371
tz                          Asia/Dubai
phone                                0
type                          Airports
email                            BLANK
url                              BLANK
runway_length                      NaN
elev                               NaN
icao                              OMAL
direct_flights                      12
carriers                             9
Name: 4, dtype: object
5 code                             AAQ
lat                             44.9
lon                          37.3167
name                Olkhovka Airport
city                    Novorossiysk
state             Krasnodarskiy Kray
country                       Russia
woeid                       12516605
tz                     Europe/Moscow
phone                              0
type                        Airports
email                          BLANK
url                            BLANK
runway_length                    NaN
elev                             NaN
icao                            URKA
direct_flights                    24
carriers                          15

But it seems these file has some "NaN" values in it which are causing some problem while inserting.

Mainly, runway_length and elev are the columns having "NaN" values.

I've been trying to fix this in different ways. But nothing's really working.

import pandas as pd
import numpy as np
import cx_Oracle
from cx_Oracle import DatabaseError
try:
    #create connection
    conn = cx_Oracle.connect('airport/pratik997@//localhost:1522/xe')
    print('connected!')

    #create cursor()
    c = conn.cursor()
    data = pd.read_csv('D:\\github repo\\python\\airportfiles/airport.csv', usecols=[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18], index_col=False)
    for i, row in data.iterrows():
#         insert = """insert into airport(code,lat,lon,name,city,state,country,woeid,tz,phone,type,email,url,runway_length,elev,icao,direct_flights,carriers) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)"""
#         c.execute(insert, tuple(row))
#     print('records inserted!')
    
#         print(row)
#         if(row['runway_length']=='NaN'):
#             row['runway_length']==0

#         elif(row['elev']=='NaN'):
#             row['elev']==0

        print(row['runway_length'].replace('NaN', '0'))
        print(i, row['elev'].replace('NaN', '0'))
    
except DatabaseError as e:
    err, = e.args
    print("error occured!", err.code)
    print("error msg", err.message)

finally:
    c.close()
    conn.close()

I don't want "NaN" values instead I want "0".
Please! Can somebody help with it?

1 Answer 1

1

Pandas has a function called fillna for this purpose

I suggest manipulating a copy of the "data" dataframe using this function before inserting it into your database.

data.fillna('0')
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.