Python, Pandas : write content of DataFrame into text File

Question

I have pandas DataFrame like this

        X    Y  Z    Value 
0      18   55  1      70   
1      18   55  2      67 
2      18   57  2      75     
3      18   58  1      35  
4      19   54  2      70

I want to write this data to a text file that looks like this:

I have tried something like

f = open(writePath, 'a')
f.writelines(['\n', str(data['X']), ' ', str(data['Y']), ' ', str(data['Z']), ' ', str(data['Value'])])
f.close()

It's not correct. How to do this?

EdChum · Accepted Answer · 2015-07-06 13:56:34Z

228

You can just use np.savetxt and access the np attribute .values:

np.savetxt(r'c:\data\np.txt', df.values, fmt='%d')

yields:

or to_csv:

df.to_csv(r'c:\data\pandas.txt', header=None, index=None, sep=' ', mode='a')

Note for np.savetxt you'd have to pass a filehandle that has been created with append mode.

edited Jul 6, 2015 at 13:56

answered Jul 6, 2015 at 13:33

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

4 Comments

Fred Zimmerman Over a year ago

Why is this answer getting so many more upvotes than @johndanger's? His only uses df, so seems preferable to using np.

DanielBell99 Over a year ago

For those dealing with str values; pass fmt=%s.

Shaun Han Over a year ago

What if I need to keep column names?

TourEiffel Over a year ago

@ShaunHan Did you have any update to do it ? I've posted an open question there

mcp · Accepted Answer · 2024-05-29 18:05:44Z

96

The native way to do this is to use df.to_string() :

with open(writePath, 'a') as f:
    f.write(df.to_string(header=False, index=False))

Which will output the following:

This method also lets you easily choose which columns to print with the columns attribute, lets you keep the column, index labels if you wish, and has other attributes for spacing ect.

edited May 29, 2024 at 18:05

mcp

2,1064 gold badges26 silver badges34 bronze badges

answered Sep 23, 2019 at 21:09

johnDanger

2,37920 silver badges25 bronze badges

3 Comments

Fred Zimmerman Over a year ago

Seems like this should be top answer, doesn't use any additional libraries than user requested (pandas).

questionto42 Over a year ago

@FredZimmerman I guess. df.to_csv() is usually better than this. And many people prefer working with np for speed reasons, they can make good use of np.savetxt(), which is still part of the question since it uses pandas for the Dataframe as a start.

yzerman Over a year ago

df.to_csv() would not generalize well to a dataframe that contains strings, as the method will insert quotes everywhere. But np.savetxt() with fmt='%s' will handle strings very well.

Anzel · Accepted Answer · 2015-07-06 13:36:16Z

39

You can use pandas.DataFrame.to_csv(), and setting both index and header to False:

In [97]: print df.to_csv(sep=' ', index=False, header=False)
18 55 1 70
18 55 2 67
18 57 2 75
18 58 1 35
19 54 2 70

pandas.DataFrame.to_csv can write to a file directly, for more info you can refer to the docs linked above.

edited Jul 6, 2015 at 13:36

answered Jul 6, 2015 at 13:34

Anzel

20.6k5 gold badges54 silver badges53 bronze badges

4 Comments

matanox Over a year ago

this will run into a lot of trouble when escaping needs to happen, it's not the solution to the general Pandas case!

DanielBell99 Over a year ago

I'm surprised by the amount of upvotes on this. Not something I'd recommend

Anzel Over a year ago

@StressedBoi_69420 care to elaborate? My answer here is of course not ideal as OP picked the better answer. It would be great if you provide more context, better still you provide an alternative solution -- so everyone can learn from your insight.

DanielBell99 Over a year ago

To print(...) isn't to store to a text file canonically; also your solution seems to use Python 2.

Nicolas Gervais · Accepted Answer · 2020-02-13 19:19:37Z

18

Late to the party: Try this>

base_filename = 'Values.txt'
with open(os.path.join(WorkingFolder, base_filename),'w') as outfile:
    df.to_string(outfile)
#Neatly allocate all columns and rows to a .txt file

edited Feb 13, 2020 at 19:19

Nicolas Gervais

36.9k23 gold badges123 silver badges160 bronze badges

answered Jun 16, 2016 at 20:07

Rene Duchamp

2,6392 gold badges23 silver badges30 bronze badges

2 Comments

AHegde Over a year ago

This doesn't give a tab delimited text file, seems to output a space delimited file. I like the elegance of this code, is there a way to make the output tab delimited?

Simon Chemnitz-Thomsen Over a year ago

@AHegde If you use tab delim then it would not have the beautiful formatting? Im just guessing as using space allows for neat alignment of rows/columns with different length.

eyllanesc · Accepted Answer · 2019-12-05 01:03:01Z

14

@AHegde - To get the tab delimited output use separator sep='\t'.

For df.to_csv:

df.to_csv(r'c:\data\pandas.txt', header=None, index=None, sep='\t', mode='a')

For np.savetxt:

np.savetxt(r'c:\data\np.txt', df.values, fmt='%d', delimiter='\t')

edited Dec 5, 2019 at 1:03

eyllanesc

245k19 gold badges205 silver badges282 bronze badges

answered Dec 5, 2019 at 0:42

Manohar Rana

1391 silver badge5 bronze badges

Comments

Community · Accepted Answer · 2020-04-14 09:33:15Z

3

I used a slightly modified version:

with open(file_name, 'w', encoding = 'utf-8') as f:
    for rec_index, rec in df.iterrows():
        f.write(rec['<field>'] + '\n')

I had to write the contents of a dataframe field (that was delimited) as a text file.

edited Apr 14, 2020 at 9:33

CommunityBot

11 silver badge

answered Jan 23, 2020 at 4:35

Bhaskaran Mani

313 bronze badges

Comments

Stidgeon · Accepted Answer · 2020-04-11 19:35:14Z

Way to get Excel data to text file in tab delimited form. Need to use Pandas as well as xlrd.

import pandas as pd
import xlrd
import os

Path="C:\downloads"
wb = pd.ExcelFile(Path+"\\input.xlsx", engine=None)
sheet2 = pd.read_excel(wb, sheet_name="Sheet1")
Excel_Filter=sheet2[sheet2['Name']=='Test']
Excel_Filter.to_excel("C:\downloads\\output.xlsx", index=None)
wb2=xlrd.open_workbook(Path+"\\output.xlsx")
df=wb2.sheet_by_name("Sheet1")
x=df.nrows
y=df.ncols

for i in range(0,x):
    for j in range(0,y):
        A=str(df.cell_value(i,j))
        f=open(Path+"\\emails.txt", "a")
        f.write(A+"\t")
        f.close()
    f=open(Path+"\\emails.txt", "a")
    f.write("\n")
    f.close()
os.remove(Path+"\\output.xlsx")
print(Excel_Filter)

We need to first generate the xlsx file with filtered data and then convert the information into a text file.

Depending on requirements, we can use \n \t for loops and type of data we want in the text file.

Binita Bharati · Accepted Answer · 2022-04-06 08:24:52Z

If you have a Dataframe that is an output of pandas compare method, such a dataframe looks like below when it is printed:

    grossRevenue          netRevenue               defaultCost
             self  other         self         other             self  other
2098        150.0  160.0          NaN           NaN              NaN    NaN
2110       1400.0  400.0          NaN           NaN              NaN    NaN
2127          NaN    NaN          NaN           NaN              0.0  909.0
2137          NaN    NaN     0.000000  8.900000e+01              NaN    NaN
2150          NaN    NaN     0.000000  8.888889e+07              NaN    NaN
2162          NaN    NaN  1815.000039  1.815000e+03              NaN    NaN

I was looking to persist the whole dataframe into a text file as its visible above. Using pandas's to_csv or numpy's savetxt does not achieve this goal. I used plain old print to log the same into a text file:

 with open('file1.txt', mode='w') as file_object:
            print(data_frame, file=file_object)

Collectives™ on Stack Overflow

Python, Pandas : write content of DataFrame into text File

8 Answers 8

4 Comments

3 Comments

4 Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

4 Comments

3 Comments

4 Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related