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?

CSV is a flat text file, you can't put logic in it like you can with Excel. You'll need to modify your dataframe using your logic, then write it to CSV at the end. — MattDMo, Sep 29 '20 at 21:58

score 0 · Answer 1 · answered Sep 29 '20 at 22:31

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

Thank you, I will try this out. I was just working to see if I could get the database to make the change similar to how I can create the change in Excel: — AntwonA, Sep 30 '20 at 10:55
@rcrlii thanks so much for the help. Had to make some minor changes but its now working! — AntwonA, Sep 30 '20 at 19:32

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?

1 Answers1