Replace dataframe header with lines of string

Question

I have a pandas dataframe that looks like this:

                   X             Y          Z     Value
0       6.196133e+06  2.321880e+06   1356.439      0.01
1       6.196155e+06  2.321867e+06   1347.363      0.01
2       6.196176e+06  2.321854e+06   1338.229      0.01
3       6.196197e+06  2.321841e+06   1332.272      0.01
4       6.196218e+06  2.321828e+06   1326.691      0.01

How can I write the data to a .csv (or .txt) file without row indices, so that the columns are tab (or space) delimited, and the header is displayed as follows:

title = "dataset test"
variables = "X", "Y", "Z", "Value"
zone t = "Data Field",i = 134, j = 293, k = 5, f=point
6.196133e+06  2.321880e+06   1356.439      0.01
6.196155e+06  2.321867e+06   1347.363      0.01
6.196176e+06  2.321854e+06   1338.229      0.01
6.196197e+06  2.321841e+06   1332.272      0.01
6.196218e+06  2.321828e+06   1326.691      0.01

FYI: The header is Tecplot software header.

The header is user defined. i, j, k, and strings like "Data Field" or "dataset test" are all user defined. — Bob
– Bob, Commented Mar 2, 2018 at 20:08
I can glue the header lines parameters (i,j,k, and strings in " ") in advance based on other data. In fact I want just paste these three lines on top of my output files followed by tab (or space) separated data from dataframe. — Bob
– Bob, Commented Mar 2, 2018 at 20:13

rahlf23 · Accepted Answer · 2018-03-02 22:09:33Z

1

Prepare your inputs:

import pandas as pd
import os

os.chdir('Directory to save to')

df = pd.DataFrame([[6.196133e+06,2.321880e+06,1356.439,0.01],
                    [6.196155e+06,2.321867e+06,1347.363,0.01],
                    [6.196176e+06,2.321854e+06,1338.229,0.01],
                    [6.196197e+06,2.321841e+06,1332.272,0.01],
                    [6.196218e+06,2.321828e+06,1326.691,0.01]],columns = ['X','Y','Z','Value'])

info = pd.DataFrame([['title = "dataset test"'],
                    ['variables = ' + str(df.columns.tolist())],
                    ['zone t = "Data Field", i = 134, j = 293, k = 5, f=point']])

To save to Excel file:

writer = pd.ExcelWriter('out.xlsx', engine='openpyxl')

info.to_excel(writer, sheet_name='Output', header=None, index=False, startcol=0,startrow=0)
df.to_excel(writer, sheet_name='Output', header=None, index=False, startcol=0,startrow=3)

writer.save()

To save to CSV file:

with open('out.csv', 'w') as myfile:
    info.to_csv(myfile, header=None, index=False, sep=' ')
    df.to_csv(myfile, header=None, index=False, sep=' ')

edited Mar 2, 2018 at 22:09

answered Mar 2, 2018 at 20:34

rahlf23

9,0494 gold badges30 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bob Over a year ago

This works for the most part except the output is excel. There are scripts to read excel and write them to csv/txt but my files are big and many. Moreover I am afraid going back and forth mess up the file format and encoding which I want to avoid as well. thanks for the solution though.

rahlf23 Over a year ago

I have added an option to save to CSV, sorry for missing that in your original question.

Bob Over a year ago

Thanks this works the way that I want it. But it write the header with extra " and []:

"title = ""dataset test""" "variables = ['X', 'Y', 'Z', 'Value']" "zone t = ""Data Field"", i = 134, j = 293, k = 5, f=point"

rahlf23 Over a year ago

Not a problem @Bob!

rahlf23 Over a year ago

You can remove those when you specify the info DataFrame, just pass them in as strings using str()

|

tenfishsticks · Accepted Answer · 2018-03-02 20:37:21Z

1

(I haven't tested this personally, so there may need some tweaks ;-)) According to the Panda docs, something like this should work: https://pandas.pydata.org/pandas-docs/stable/io.html#io-store-in-csv

filename = 'output.csv'
with open(filename, 'w') as fid:
    # write the Tecplot header
    fid.write('title = "dataset test"')
    fid.write('variables = [{0}]'.format(df.columns))
    fid.write('zone t = "Data Field",i = 134, j = 293, k = 5, f=point')
# write the data
df.to_csv(filename, '\t', columns=df.columns, header=False, index=False, mode='w+')

edited Mar 2, 2018 at 20:37

answered Mar 2, 2018 at 20:16

tenfishsticks

4625 silver badges13 bronze badges

2 Comments

Bob Over a year ago

solution gives this error: 'file' object has no attribute 'writeline'.

Bob Over a year ago

It doesn't write the header and messes up the file format as well. Thanks for the try though.

Collectives™ on Stack Overflow

Replace dataframe header with lines of string

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related