How to parse Cloud SQL CSV export nulls ("N) with Pandas read_csv

1
votes

Using Airflow, we're exporting data from Google Cloud SQL to a CSV, and eventually loading that CSV into a different SQL warehouse. However, Cloud SQL exports null values as the string "N (This is a known Google issue: https://issuetracker.google.com/issues/64579566). As an interim step, we need to open up the file and delete the "N. There are also actual strings in the csv, using " normally.

Ideally, we'd be able to do this with pandas - we're setup to use a DataFrame for the next step. However, I can't get read_csv to interpret the "N as nulls. Here's the basic command I've tried:

df = pd.read_csv(filepath, na_values='"N')

I've also tried na_values="\"N", but that gave me the same results.

It appears that read_csv checks for strings first, and for nulls second, so I'm getting output that looks like this:

Id                                                                           100
IncidentDate                                                 2018-08-29 07:00:00
StudentInvolved                                                      Psueudonym
IncidentLocation                                                       Classroom
IncidentCategory                                             Academic dishonesty
IncidentDescriptionDetails     [TESTING] How does this insert into table when...
FollowUp                                                                       0
ConsequenceGiven                                             Afterschool Academy
ConsequenceStartDate                                                         N,N
ConsequenceEndDate                                                           N,N
PrimaryViolation                                                 <email address>
Weapon                                                             <school_name>
CreatedBy                                                                    N,N
SiteName                                                                    1000
DisciplinaryActionAuthority                                                N,0,N
DocumentationUrl                                                             N,N
SIS_ID                                                                       N,N
SubmittedBy                                                                  N,N
Deleted                                                                      N,N
FollowUpNotes                                                                N,N
StudentLists_fk                                                              N,N

Any ideas on if read_csv is capable of parsing this?

2
pandasgoogle-cloud-sql

2 Answers

0
votes

You need to replace that value before loading into pandas dataframe. One way is to use sed command in linux.

sed 's/"N/NULL/g' <filename>.csv

then load into pandas

Automating the export statement:

folderName=`date +%m-%d-%Y`
fileName=`date +%H:%M:%S`
gs_path="gs://generic_test/$folderName/$fileName.csv"
gcloud sql export csv upc-asin-mapping $gs_path --query="select * from tableName;" --database=db_name
gsutil -m cp $gs_path .
sed -i "" 's/"N/NULL/g' $fileName.csv
gsutil -m cp $fileName.csv $gs_path

The above script will download the file, replace the "N with NULLand re-upload the same file with the same name. This is not a scalable approach.

0
votes

Ultimately had to brute force it by replacing the text.

    with open(filepath, 'r', encoding="utf-8") as inputFile:
        raw_text = inputFile.read()

    edited_text = raw_text.replace('"N,', ',')
    edited_text = edited_text.replace(',"N\n', ',\n')

    with open(edited_filepath, 'w', newline='\n', encoding="utf-8") as outputFile:
        outputFile.write(edited_text)

    df = pd.read_csv(edited_filepath, names=incident_columns)

The one downside is that this will mess up any string that legitimately begins with the N, or N\n, but hopefully our users aren't in the habit of inputting stuff like that.