Python: pandas.to_csv adds unwanted quotes

user_13005361 0
Viewed 322

I reading a CSV file with pd.read_csv, as shown:

lisp = pd.read_csv('ida_lisp.ida', header=None, skip_blank_lines=False)

I replace %1% with key values and save the result to different locations, as shown:

heating_setpoint_s = [19, 20, 21, 22, 23, 24] 
for i in range(len(heating_setpoint_s)):
    lisp1 = lisp.replace('%1%', str(heating_setpoint_s[i]), regex=True)
    path = "C://Users//nico//"+str(i+1)+"_ida"
    os.chdir(path)
    lisp1.to_csv('ida_lisp.ida', header=False, index=False, na_rep=" ")

All is working fine for the exception of one line MDESIGN "" in the old CSV file: 

      K2 0.0
      TAU 1.0
      SPECPUMPPO 349.0
      MDESIGN ""

  MODULE chil
    TYPE SIMCHIL
      NFPLR 0
      COP 3

is changed after saving, to:

      K1 0.0
      K2 0.0
      TAU 1.0
      SPECPUMPPO 349.0
"      MDESIGN """""

  MODULE chil
    TYPE SIMCHIL
      NFPLR 0
      COP 3

Does anyone know how to prevent this?

pandas export-to-csv python
2 Answers

As per pandas documentation, to_csv() has a quotechar= parameter which defaults to ", maybe you can try another symbol, say ~, for quote char (i.e. lisp1.to_csv('ida_lisp.ida', header=False, index=False, na_rep=" ", quotechar='~') ). This way you can test if the problem is purely originating due to quote chars and not from your data.

user_93040450

I've made a couple tweaks to your code to help address this issue (in bold) and make things a little more straight-forward for you.

  • Updated your for loop to iterate your list directly, rather than convoluting with a range(len()) call.
  • Added a replace function to replace any stray " in your strings.
  • Added a strip function to clean all values to remove any leading/trailing whitespace so the output CSV is clean.
  • Updated your file path concatenation to use os.path.join.
  • Added a os.makedirs call in the event your storage directory does not exist.

Sample Code:

import os
import pandas as pd

lisp = pd.read_csv('ida_lisp.ida', header=None, skip_blank_lines=False)

heating_setpoint_s = [19, 20, 21, 22, 23, 24] 
for i in heating_setpoint_s:
    lisp1 = lisp.replace('%1%', str(i), regex=True).replace('"+', '', regex=True)
    # Clean column 0, assuming this is the only column, based on sample data provided.
    lisp1 = lisp1.iloc[:, 0].str.strip()
    path = "C:/Users/nico/{}_ida".format(i)
    if not os.path.exists(path):
        os.makedirs(path)
    lisp1.to_csv(os.path.join(path, 'ida_lisp.ida'), header=False, index=False, na_rep="")

Input from CSV

      K2 0.0
      TAU 1.0
      SPECPUMPPO 349.0
      MDESIGN ""

  MODULE chil
    TYPE SIMCHIL
      NFPLR 0
      COP 3

Output:

K2 0.0
TAU 1.0
SPECPUMPPO 349.0
MDESIGN

MODULE chil
TYPE SIMCHIL
NFPLR 0
COP 3
user_63404960
Related