13

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?

Improve this question
7
  • 2
    you can rename columns in dataframe before you save it. Commented May 16, 2019 at 10:44
  • why don't check documentation for dataframe.to_csv(). There is option header - maybe it renames headers in file. Commented May 16, 2019 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. Commented May 16, 2019 at 10:49
  • check header again - you can use different names than in columns. Commented May 16, 2019 at 10:52
  • @furas header can change value, but the number of columns must not change. Commented May 16, 2019 at 11:01

3 Answers 3

Reset to default
13

EDIT (13-08-24)

open the file with 'w' flag (as highlighted by @eric-duminil and @alessandro in the comment)

Correct code

with open('file.csv', 'w') as file:
    file.write('numrows numcols note\n')
    df.to_csv(file, header=False, index=False)

OLD

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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.
@divyanshu-srivastava Worked well for me Div. Too bad we can't bold the title or other things at the same time.
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 is right, with the "a" argument if you run multiple times your script it will append many times the same data. In my case it worked fine with the "w" option.
10

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")
Improve this answer

3 Comments

Its the same, just that in this case the file is opened twice.
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 :)
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.
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')
Improve this answer

7 Comments

There is no need to create a Dataframe for the first custom string.
Creating an entire dataframe just for a header is wasteful
I like this answer because it's all in pandas, thus it shows the strength and limits of pandas.
@THN The question never stated that an all-panda answer is expected.
@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.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.