compare values with fieldnames before writing it in csv file using python

Question

I'm using this code to compare value with their affiliated fieldnames before i store it in the csv-file to check the right column, because everytime when i reboot my machine the order of sensors in the dictionary will be changed autamatically. this why i have to control the the position of value in dict before i store it in csv file, otherwise i will get a mess csv file like the example bellow which provide the value temperatur is 1000 [°C] the luminisity 10 [lux]

import csv
from datetime import datetime
import os
import time
from collections import OrderedDict
from time import sleep


Datum = time.strftime("%Y-%m-%d")
Time = datetime.now().replace(microsecond=0).isoformat()

data = {'Stecker Energy': '2.37', 'Stromzaehler Strom L1 [A]': '0.0', '1OG Ultraviolet': '0.0','Stecker EG  Energie [kWh]': '4.3'}

filename = Datum + 'SOF.csv'
if not os.path.isfile(filename):
    outfile = open(filename, "w")
    lineKey = ""
    for x in data.keys():
        lineKey+= x + ","
    lineKey = 'Time '+ "," + lineKey[:-1]+"\n" # Delete last character and add new line
    outfile = open(filename, "a")
    outfile.write(lineKey)
    outfile.close()

#check first line of  fieldnames 
with open(filename,'r') as myFile:
    reader = csv.DictReader(myFile)
    fieldnames = reader.fieldnames
    myFile.close()
#Compare the values with their  keys (fieldnames)
with open(filename,'a') as outfile:
    lineValue = ""
    for sensor, value  in data.items():
        if sensor in data.items()==sensor in fieldnames:
            v = data[sensor]
            print("v", v)
            lineValue+= str(v) + "," 
    lineValue = Time + "," + lineValue[:-1] + "\n"
    print(lineValue)
    outfile.write(lineValue)
    outfile.close()

The problem is when i check CSV-file i found that value are written in the wrong column which means wrong fieldnames for Examlpe:

Example of this problem in CSV file

CSV Text Example

Time ,Stromzaehler Strom L1 [A],Stecker Energy,1OG Ultraviolet,Stecker EG Energie [kWh] 2017-11-21T17:56:10,2.37,0.0,0.0,4.3 2017-11-21T17:56:26,4.3,0.0,0.0,2.37 2017-11-21T17:56:28,0,0.0,2.37,4.3 2017-11-21T17:56:30,0,2.37,0.0,4.3

data = {'Stecker Energy': '', 'Stromzaehler Strom L1 [A]': '', '1OG Ultraviolet': '','Stecker EG  Energie [kWh]': ''}

Please post a textual version of your CSV file rather than an image. — Martin Evans, Nov 21 '17 at 11:46
@MartinEvans i edited my post. you can find i a textual version example above — AhmyOhlin, Nov 21 '17 at 12:31
Perhaps instead of manually constructing the output data by adding commas and newlines and such, you could use the `csv.DictWriter` class. This vastly reduces the likelihood of misplacing a separator character, and also gives your code a nice symmetrical feel since it's already using `DictReader` in the first half. — Kevin, Nov 21 '17 at 14:32
When I run your code, `print(fieldnames)` shows `['Time Temp Humidity Current ']` which looks wrong. The list should be four items long, not one. If your input file doesn't contain any commas, then I think you need to configure the DictReader so that it is aware of that fact. — Kevin, Nov 21 '17 at 15:08
@Kevin i have comma in my text file. i edited and i post it here — AhmyOhlin, Nov 21 '17 at 15:24
why not pandas? https://stackoverflow.com/questions/37070759/preserving-column-order-in-the-pandas-to-csv-method is an example — jeremycg, Nov 21 '17 at 18:07

Kevin · Accepted Answer · 2017-11-21T17:41:19.470

for sensor, value  in data.items():
    if sensor in data.items()==sensor in fieldnames:
        v = data[sensor]
        print("v", v)
        lineValue+= str(v) + ","

This doesn't look right to me. If you just want to iterate through all the fieldnames (other than "Time") and add the values in data that correspond to those fields, while also putting "n/a" for values not present in data, then I suggest doing:

with open(filename,'a') as outfile:
    lineValue = ""
    for fieldname in fieldnames[1:]: #skip the first one, since that's Time, which we have a custom value for already.
        v = data.get(fieldname, "n/a")
        lineValue += str(v) + ","
    lineValue = Time + "," + lineValue[:-1] + "\n"
    print(lineValue)
    outfile.write(lineValue)
    outfile.close()

Bonus suggestion: Adding commas between values manually is an error-prone approach. If possible, use DictWriter, which takes care of most formatting problems for you.

with open(filename,'a') as outfile:
    writer = csv.DictWriter(outfile, fieldnames)
    row = {}    
    for fieldname in fieldnames: #no need to skip Time here, we'll just overwrite it later.
        row[fieldname] = data.get(fieldname, "n/a")
    row["Time "] = Time
    writer.writerow(row)

If you insist on not doing that, at least store the values in a list, which you join once at the end:

with open(filename,'a') as outfile:
    values = [Time]
    for fieldname in fieldnames[1:]:
        v = data.get(fieldname, "n/a")
        values.append(str(v))
    outfile.write(",".join(values) + "\n")

Bonus bonus suggestion: if these solutions add extra empty lines between rows that you don't want, you open the file with the newline="" argument (if using Python 3):

with open(filename,'a', newline="") as outfile:

... Or open the file in binary mode (if using Python 2):

with open(filename,'ab') as outfile:

compare values with fieldnames before writing it in csv file using python

1 Answers1

Linked