How to write a pandas.DataFrame to csv file with custom header?

Question

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?

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
@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

Divyanshu Srivastava · Accepted Answer · 2019-05-16T16:38:01.410

8

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)

edited May 16 '19 at 16:38

answered May 16 '19 at 10:47

Divyanshu Srivastava

1,121
6
21

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

score 7 · Answer 2 · edited May 16 '19 at 11:27

7

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")

edited May 16 '19 at 11:27

Divyanshu Srivastava

1,121
6
21

answered May 16 '19 at 10:50

Zionsof

1,066
8
19

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

score 1 · Answer 3 · answered May 16 '19 at 10:47

1

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')

answered May 16 '19 at 10:47

jezrael

729,927
78
1,141
1,090

There is no need to create a Dataframe for the first custom string. – Divyanshu Srivastava May 16 '19 at 10:52
Creating an entire dataframe just for a header is wasteful – Zionsof May 16 '19 at 10:56
1

I like this answer because it's all in pandas, thus it shows the strength and limits of pandas. – THN May 16 '19 at 11:05
@THN The question never stated that an all-panda answer is expected. – Divyanshu Srivastava May 16 '19 at 11:06
@DivyanshuSrivastava - First, it is still for OP, what answer is accepted :) So downvoting is possible, but not sure if good way for persuade another people about your solution is best. – jezrael May 16 '19 at 11:09
@DivyanshuSrivastava That's right, any good solution is appreciated. – THN May 16 '19 at 11:09
This is a slower approach. – Divyanshu Srivastava May 16 '19 at 11:15

How to write a pandas.DataFrame to csv file with custom header?

3 Answers3

Linked