1

I have a function that writes a set of information in rows to a CSV file in Python. The function is supposed to append the file with the new row, however I am finding that sometimes it misbehaves and places the new row in a separate space of the CSV (please see picture as an example).

Whenever I reformat the data manually I delete all of the empty cells again, so you know.

Hoping someone can help, thanks!

def Logger():

    fileName = myDict[Sub]

    with open(fileName, 'a+', newline="") as file:
        writer = csv.writer(file)

        if file.tell() == 0:
            writer.writerow(["Date", "Asset", "Fear", "Anger", "Anticipation", "Trust", "Surprise", "Sadness", "Disgust", "Joy",
                        "Positivity", "Negativity"])
        writer.writerow([date, Sub, fear, anger, anticip, trust, surprise, sadness, disgust, joy, positivity, negativity])

enter image description here

Musa
  • 96,336
  • 17
  • 118
  • 137
Luke Ashton
  • 39
  • 1
  • 6
  • What program is this that is displaying/importing your CSV? Are you certain it isn't a problem with that program, and not your python or resulting CSV? – JNevill Nov 04 '21 at 17:43
  • Numbers on mac, I am sure as when I view the csv directly in my IDE the problem remains – Luke Ashton Nov 04 '21 at 17:48
  • Gotcha ok. I know that's like a "Did you turn it off and back on again" thing, but anytime I see CSV imported into a spreadsheet I hear alarms go off. Excel, as an example, is well known for destroying CSV data. – JNevill Nov 04 '21 at 17:51
  • csv files are easy to examine in a simple text editor like notepad++, which should make the problem obvious. – RufusVS Nov 04 '21 at 18:15
  • Yeah, can you paste the CSV as text, and focus on the lines where it's misbehaving? I'd expect to see a good row, followed by as many commas as you have columns, then a good row... all on the same line. – Zach Young Nov 05 '21 at 01:00

2 Answers2

1

At first I thought it was a simple matter of there not being a trailing newline, and the new row being appended on the same line, right after the last row, but I can see what looks like a row's worth of empty columns between them.

This whole appending thing looks tricky. If you don't have to use Python, and can use a command-line tool instead, I recommend GoCSV.

Here's a sample file based on your screenshot I mocked up:

base.csv

Date,Asset,Fear,Anger,Anticipation,Trust,Surprise,Sadness,Disgust,Joy,Positivity,Negativity
Nov 1,5088,0.84,0.58,0.73,1.0,0.26,0.89,0.22,0.5,0.69,0.59
Nov 2,4580,0.0,0.88,0.7,0.71,0.57,0.78,0.2,0.22,0.21,0.17
Nov 3,2469,0.72,0.4,0.66,0.53,0.65,0.64,0.67,0.78,0.54,0.32,,,,,,,

I'm calling it base because it's the file that will be growing, and you can see it's got a problem on the last line: all those extras commas (I don't know how they got there ‍♂️).

The first step will be to clean it, and trim those pesky extra commas:

% gocsv clean base.csv > tmp
% mv tmp > base.csv

and now base.csv looks like:

Date,Asset,Fear,Anger,Anticipation,Trust,Surprise,Sadness,Disgust,Joy,Positivity,Negativity
Nov 1,5088,0.84,0.58,0.73,1.0,0.26,0.89,0.22,0.5,0.69,0.59
Nov 2,4580,0.0,0.88,0.7,0.71,0.57,0.78,0.2,0.22,0.21,0.17
Nov 3,2469,0.72,0.4,0.66,0.53,0.65,0.64,0.67,0.78,0.54,0.32

Here's another set of data to append, sample2.csv:

Date,Asset,Fear,Anger,Anticipation,Trust,Surprise,Sadness,Disgust,Joy,Positivity,Negativity
Nov 4,6040,0.69,0.89,0.72,0.44,0.21,0.15,0.03,0.63,0.78,0.42
Nov 5,7726,0.72,0.12,0.95,0.6,0.88,0.1,0.43,1.0,1.0,0.68
Nov 6,9028,0.87,0.34,0.46,0.57,0.15,0.3,0.8,0.32,0.17,0.42
Nov 7,3544,0.16,0.9,0.37,0.8,0.67,0.0,0.11,0.72,0.93,0.35

GoCSV's stack command will do this job:

% gocsv stack base.csv sample2.csv > tmp
% mv tmp base.csv

and now base.csv looks like:

Date,Asset,Fear,Anger,Anticipation,Trust,Surprise,Sadness,Disgust,Joy,Positivity,Negativity
Nov 1,5088,0.84,0.58,0.73,1.0,0.26,0.89,0.22,0.5,0.69,0.59
Nov 2,4580,0.0,0.88,0.7,0.71,0.57,0.78,0.2,0.22,0.21,0.17
Nov 3,2469,0.72,0.4,0.66,0.53,0.65,0.64,0.67,0.78,0.54,0.32
Nov 4,6040,0.69,0.89,0.72,0.44,0.21,0.15,0.03,0.63,0.78,0.42
Nov 5,7726,0.72,0.12,0.95,0.6,0.88,0.1,0.43,1.0,1.0,0.68
Nov 6,9028,0.87,0.34,0.46,0.57,0.15,0.3,0.8,0.32,0.17,0.42
Nov 7,3544,0.16,0.9,0.37,0.8,0.67,0.0,0.11,0.72,0.93,0.35

This can be scripted and simplified like this:

% gocsv clean base.csv > base
% gocsv clean sample.csv > sample
% gocsv stack base sample > base.csv
% rm base sample
Zach Young
  • 10,137
  • 4
  • 32
  • 53
0

Try this instead...

    def Logger(col_one, col_two):
        fileName = 'data.csv'

        with open(fileName, 'a+') as file:
            writer = csv.writer(file)
            file.seek(0)
            if file.read().strip() == '':
                writer.writerow(["Date", "Asset"])
            writer.writerow([col_one, col_two])
Ali Shazin
  • 36
  • 1
  • 2