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:

18 55 1 70   
18 55 2 67 
18 57 2 75     
18 58 1 35  
19 54 2 70 

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

but it's not working. How to do this?

Solution 1

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

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

yields:

18 55 1 70
18 55 2 67
18 57 2 75
18 58 1 35
19 54 2 70

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.

Solution 2

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

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

Will output the following

18 55 1 70   
18 55 2 67 
18 57 2 75     
18 58 1 35  
19 54 2 70 

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.

Solution 3

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.

Solution 4

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

Solution 5

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

View more solutions

Comments

[AHegde]

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?

[matanster]

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

[Fred Zimmerman]

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

[Fred Zimmerman]

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

[questionto42standswithUkraine]

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

[DanielBell99]

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

[Anzel]

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

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

[DanielBell99]

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