11

I have a dataframe

import pandas as pd
df = pd.DataFrame([[1, 2], [3, 4]], columns=['a', 'b'])

I want to write df to a csv file but not using the columns ['a', 'b']. The first line is my custom string and the rest are the content of df.values. For example:

numrows numcols note
1 2
3 4

Can I do this with pandas or I have to manually loop through the content and write to file?

THN
  • 3,351
  • 3
  • 26
  • 40
  • 2
    you can rename columns in dataframe before you save it. – furas May 16 '19 at 10:44
  • why don't check documentation for [dataframe.to_csv()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html). There is option `header` - maybe it renames headers in file. – furas May 16 '19 at 10:46
  • 1
    @furas I want to write a custom string, it's not the same number of columns with the data content. See my example above. – THN May 16 '19 at 10:49
  • check `header` again - you can use different names than in columns. – furas May 16 '19 at 10:52
  • @furas header can change value, but the number of columns must not change. – THN May 16 '19 at 11:01
  • if you add empty column then you will have three columns with three headers - and you can normally save it . But in file you will see three headers with two columns - last empty column will be invisible for you. – furas May 16 '19 at 11:05
  • Please note that the accepted answer is wrong. – Eric Duminil Sep 06 '22 at 11:33

3 Answers3

11

You can first create a csv file with the custom text in the first line, and then append the dataframe to it. 

with open('file.csv', 'a') as file:
    file.write('Custom String\n')
    df.to_csv(file, header=False, index=False)

Also, see this post.

So, in your case, just use this

with open('file.csv', 'a') as file:
    file.write('numrows numcols note\n')
    df.to_csv(file, header=False, index=False)
Divyanshu Srivastava
  • 1,379
  • 11
  • 24
  • This is a principled solution, and works well when setting the write-mode correctly. I'm not sure which answer to accept. Will accept by the vote count later. – THN May 16 '19 at 11:07
  • @divyanshu-srivastava Worked well for me Div. Too bad we can't bold the title or other things at the same time. – Edison Jul 13 '22 at 03:37
  • 1
    Wait. This is wrong. You can run the code twice to see why : 'file.csv' will have two headers and two contents. "You can first create a csv file with the custom text in the first line, and then append the dataframe to it. " The problem is that if the file already exists, it appends the custom text to the existing content. – Eric Duminil Sep 06 '22 at 11:30
8

Improving over @Divyanshu Srivastava answer:

Not that it matters a lot, but no need for keeping open files:

with open(file_path, 'w') as f:
     f.write('Custom String\n')

df.to_csv(file_path, header=False, mode="a")
Divyanshu Srivastava
  • 1,379
  • 11
  • 24
Zionsof
  • 1,196
  • 11
  • 23
  • Its the same, just that in this case the file is opened twice. – Divyanshu Srivastava May 16 '19 at 11:11
  • Yes, only when you supply a file object instead of a path, you disable universal newlines, which in most cases won't budge anyone but still :) – Zionsof May 16 '19 at 11:13
  • The problem with the accepted answer is that it appends the header to whatever 'file.csv' was containing before. It doesn't just append the content to the header. – Eric Duminil Sep 06 '22 at 11:32
2

First write custom string and then all data without columns in append mode:

file = 'file.csv'
pd.DataFrame(columns=['numrows numcols note']).to_csv(file, index=False)
df.to_csv(file, header=None, index=False, mode='a')
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252