How do I insert data into a desired column of a csv file?

The code displayed below creates a .csv file with my desired column headers, reads dates from a folder of files and then runs a machine learning model on those files. The goal of this is to allow me to see how many detections occur per day. My issue is that I cannot get the second insertion of data to go into the second column under the header. No matter my efforts it seems to always paste in the first column under the other data.

I have tried the columns(['']) command but I get a KeyError.

    # Creates .csv file.
with open(fr"/home/lm/anaconda3/envs/yolov8_rotiferdetect/CSV_files/exp_{lifespan_number}.csv", 'w') as csvfile:
filewriter = csv.writer(csvfile, delimiter=',',
quotechar='|', quoting=csv.QUOTE_MINIMAL)
filewriter.writerow(['Date','Rotifer Count'])

    # Load Original Images
org_images_path = fr"/wormbot/{lifespan_number}"
# Establish Prefix for Files
file_prefix = "frame"
# Sort Images
img_list = sorted(os.listdir(org_images_path))
# Filter Sorted Images by Prefix
img_list = [img_name for img_name in img_list if img_name if  img_name.startswith(file_prefix)]
# Loop through every other file in the folder
for index, img_name in enumerate(img_list):
if index % 2 ==0:
# Open Image
image_path = os.path.join(org_images_path, img_name)
# Get Date and Time
get_image = os.path.getmtime(image_path)
# Filter to Just Date Number
date_number = datetime.datetime.fromtimestamp(get_image).day
# Output Date Number
number_day = (date_number)
# Create Data Frame
df = pd.DataFrame([number_day])
# Input Into .csv File
df.to_csv(fr"/home/lm/anaconda3/envs/yolov8_rotiferdetect/CSV_files/exp_{lifespan_number}.csv", index=False, header=False, mode='a')

# Loop through all files in the folder
for image in sorted(os.listdir(output_crop)):
if image.endswith('.jpg'):
image_path = os.path.join(output_crop, image)
img = Image.open(image_path)
# Run Model with however much confidence on image.
results = model(img, conf=.19,verbose=False, max_det=5)
counts = {}
for result in results:
boxes = result.boxes.cpu().numpy()
for box in boxes:
cls = int(box.cls[0])
if not cls in counts.keys():
counts[cls] = 1
else:
counts[cls] += 1
try:
alive = (str(counts[key]))
except KeyError:
alive = ('0')
# creates data frame of current frame and count.
df = pd.DataFrame([alive])
# Add data frame to next open row in csv file, change accordingly.
df.to_csv(fr"/home/lm/anaconda3/envs/yolov8_rotiferdetect/CSV_files/exp_{lifespan_number}.csv", index=False, header=False, mode='a')
# Adds 1 to current_frame to count.
current_frame +=1
# Close current image
img.close()

I believe you could simplify this by creating two series, then merging them into your dataframe and writing them out to CSV.

Something like the below should work, but might have missed the essence of what you're trying to accomplish:

# Load Original Images
# Dates list
dates = []
org_images_path = fr"/wormbot/{lifespan_number}"
# Establish Prefix for Files
file_prefix = "frame"
# Sort Images
img_list = sorted(os.listdir(org_images_path))
# Filter Sorted Images by Prefix
img_list = [img_name for img_name in img_list if img_name if  img_name.startswith(file_prefix)]
# Loop through every other file in the folder
for index, img_name in enumerate(img_list):
if index % 2 ==0:
# Open Image
image_path = os.path.join(org_images_path, img_name)
# Get Date and Time
get_image = os.path.getmtime(image_path)
# Filter to Just Date Number
date_number = datetime.datetime.fromtimestamp(get_image).day
# Output Date Number
number_day = (date_number)
# Append number_day to dates list
dates.append(number_day)

Then we would process your counts next:

# Loop through all files in the folder
rotifier_counts = []
for image in sorted(os.listdir(output_crop)):
if image.endswith('.jpg'):
image_path = os.path.join(output_crop, image)
img = Image.open(image_path)
# Run Model with however much confidence on image.
results = model(img, conf=.19,verbose=False, max_det=5)
counts = {}
for result in results:
boxes = result.boxes.cpu().numpy()
for box in boxes:
cls = int(box.cls[0])
if not cls in counts.keys():
counts[cls] = 1
else:
counts[cls] += 1
try:
alive = (str(counts[key]))
rotifier_counts.append(alive)
except KeyError:
alive = ('0')
rotifier_counts.append(alive)
# Adds 1 to current_frame to count.
current_frame +=1
# Close current image
img.close()

Finally, we could create a series from our dates and rotifier_counts list and stand up the dataframe and write to CSV.

# Create series of dates to create data frame
s1 = pd.Series(dates, name="Dates")
s2 = pd.Series(rotifier_counts, name="Rotifer Counts")
# Create dataframe with column names
df = pd.concat([s1, s2], axis=1)
# Write df out to CSV
df.to_csv(fr"/home/lm/anaconda3/envs/yolov8_rotiferdetect/CSV_files/exp_{lifespan_number}.csv", index=False, header=True)