In Python, how do you insert an additional field in an created csv file that has elseif conditions on the field contents based on results of query?

Question

Here is the python code that takes the data from my query and packages it to go into a csv file.

...
    col_headers = [ i[0] for i in cursor.description ]
    rows = [ list(i) for i in cursor.fetchall()] 
    df = pd.DataFrame(rows, columns=col_headers)
    
    df.to_csv("PremiseCPE.csv", index=False)
       
    for row in cursor.fetchall():
        print (row)
...
  

The incoming data is in columns. I need to add an additional column (#6) called "Placemarks". I then need to add values in the new column row for each output from the database based on the values in in column #3 which is called cpeStatus. Below is the type of query structure I tried while creating a kml file:

...
    iif (row[4]) = 'Off', (row[6]) = "http://maps.google.com/mapfiles/kml/shapes/forbidden.png"
    ElseIf (row[4]) = 'Active', (row[6]) = "http://maps.google.com/mapfiles/kml/shapes/ranger_station.png"
    ElseIf (row[4]) = 'Ready, (row[6]) = "http://maps.google.com/mapfiles/kml/shapes/mechanic.png"
    ElseIf (row[4]) = 'Alarm', (row[6]) = "http://maps.google.com/mapfiles/kml/shapes/caution.png"
    ElseIf (row[4]) = 'Null', (row[6]) = "http://maps.google.com/mapfiles/kml/shapes/white_bubble.png"
    End If
...

The goal is to try to run this at the csv file level.

Can anyone help?

Answer 1

As @MattDMo says, you need to do this in the dataframe before writing the CSV. Also, I prefer a dictionary lookup to a long if...elif...else in python. Lastly, I suggest using pd.read_sql to query the database and create the df.

import pandas as pd

col_headers = ['col1', 'cols2', 'yada', 'cpeStatus', 'murgatroyd', 'noimagination']

rows = [[1, 2, 3, 'Off', 'is', 42],
        [2, 4, 42, 'Active', 'the', 42],
        [3, 9, 12, 'Ready', 'best', 42],
        [4, 16, 20, 'Off', 'name', 42],
        [5, 25, 30, 'Alarm', 'no', 42],
        [6, 36, 42, 'Null', 'its', 42],
        [7, 49, 56, 'Danger', 'not', 42],]

df = pd.DataFrame(rows, columns=col_headers)

plmks = {'Off': "forbidden.png",
         'Active': "ranger_station.png",
         'Ready': "mechanic.png",
         'Alarm': "caution.png",
         'Null': "white_bubble.png"}

df['Placemarks'] = [plmks.get(st, "headslap.png") for st in df['cpeStatus']]
print(df)
df.to_csv("PremiseCPE.csv", index=False)

yields the following df:

0     1      2     3       Off         is             42       forbidden.png
1     2      4    42    Active        the             42  ranger_station.png
2     3      9    12     Ready       best             42        mechanic.png
3     4     16    20       Off       name             42       forbidden.png
4     5     25    30     Alarm         no             42         caution.png
5     6     36    42      Null        its             42    white_bubble.png
6     7     49    56    Danger        not             42        headslap.png

and the following CSV:

col1,cols2,yada,cpeStatus,murgatroyd,noimagination,Placemarks
1,2,3,Off,is,42,forbidden.png
2,4,42,Active,the,42,ranger_station.png
3,9,12,Ready,best,42,mechanic.png
4,16,20,Off,name,42,forbidden.png
5,25,30,Alarm,no,42,caution.png
6,36,42,Null,its,42,white_bubble.png
7,49,56,Danger,not,42,headslap.png