1

Need some help on python insert to csv. Would like insert info and data frame to to csv. After insert info with writerow, when insert data frame into csv, there was missing some header from data frame.

Correct header without : writer.writerow(info) Correct Header from data frame

Wrong data frame header with : writer.writerow(info) Missing 'No' to 'Billno' from data frame. Missing header from data frame

df = pd.read_sql(query, cnxn)
info = ['Date From:','',fromdate,'','To',todate]
with open('C:/my_csv/'+reportname+'.csv', 'w', newline='') as file:
   writer = csv.writer(file)
   writer.writerow(info)
   folder_path = r'C:/my_csv'
   file_type = r'/*csv'
   files = glob.glob(folder_path + file_type)
   max_file = max(files, key=os.path.getctime)
   df.to_csv(max_file, index=True, index_label="No", header=True)
Test777
  • 23
  • 4
  • there are mismatched file paths `'C:/my_csv/'`, `r'C:\my_csv'` and you're writing to a different files – RomanPerekhrest Feb 17 '23 at 09:06
  • Just few header from the dataframe was missing. Other data from dataframe inserted. – Test777 Feb 17 '23 at 09:12
  • Your expected output does not match your `info` variable. What is `Header 1`, `Header 2`,.. ? Also, why you're using empty strings ? And what's the output of `print(df.shape)` ? Can you share a minimal reproducible example of your `df` (_just a few rows_) ? – Timeless Feb 17 '23 at 11:35
  • Hi @Timeless i have added 2 image. 1 was correct header without : writer.writerow(info) 2 was wrong data frame header with : writer.writerow(info) – Test777 Feb 20 '23 at 01:15
  • I don't understand what information is missing. Your `info` is present, but you are not writing your dataframe to the CSV file you opened. What's the `max_file` thing about? Why not just pass `file` to `df.to_csv`? – Tim Roberts Feb 20 '23 at 01:16
  • Hi @TimRoberts The data frame write in the csv file, but there was missing from 'No' to 'Billno' The max_file was write to the latest csv file inside the folder. – Test777 Feb 20 '23 at 01:23
  • That's probably because you're writing to the same file through two different handles. pandas writes the CSV, then your "writer.writerow" overwrites the first few bytes of the file. You need to share the file HANDLE, not the file name. – Tim Roberts Feb 20 '23 at 03:11
  • @TimRoberts May i know which file handle you mention that i need to share? csv.writer? Yes the csv.writer overwrite few bytes of the file. may i know how to avoid it to overwrite? – Test777 Feb 20 '23 at 03:38
  • 2
    You open the file. You call the handle `file`, and you pass it to the csv writer. You then give the filename string to pandas. It then has to open the SAME file again, getting a different file handle. The two are not synchronized. If you just pass the `file` variable to `df.to_csv` instead of a file name, it will write to the file you have already opened, and things will stay synchronized. – Tim Roberts Feb 20 '23 at 07:56

1 Answers1

1

Using this answer to a similar question as a template you could try something like:

import pandas as pd

data = {"Index" : [0,1], "A": [1, 1], "B": [2, 2], "C": [3,3]}

df = pd.DataFrame(data)
df.set_index("Index", inplace=True)

date_a = 19022023
date_b = 20022023

f = open('foo', 'a')
f.write(f"Info_1, {date_a}, Info_2, {date_b}\n")
df.to_csv(f, sep=",", header=True)
f.close()

>>> more foo

Info_1, 19022023, Info_2, 20022023
Index,A,B,C
0,1,2,3
1,1,2,3
plasma_r
  • 116
  • 5