export dataframe to file.txt

[dramauh]

Dear Community,

I would like to export a dataframe with empty cells, floats and strings to a file.txt. I have tried several functions such as to_csv, np.save and f.write. The best so far was f.write. However, I cannot get format that I want.
I can export without problems the first two lines. The problem is the float part of the dataframe. This is the code that I am using:

import pandas as pd
import numpy as np
X=3
Y=3
# Create matrix with dimensions X,Y
lon=[[-180, -175, -170],[-180, -175, -170],[-180, -175, -170]]

#Create data and lon dataframe
data=pd.DataFrame([['gridtype=','curvilinear'],['gridsize=',X*Y]])
dslon=pd.DataFrame(lon)

#Create a columns of empty values with Y rows 
emptycol=pd.DataFrame([['']]*Y)
#allocate the string 'xvals=' in the first cell
emptycol.iloc[0,0]='xvals='

#Merge dataframe empty col with dslon
horizontal_concat = pd.concat([emptycol, dslon],axis=1)
#Reset columns index
horizontal_concat.columns = range(horizontal_concat.columns.size)
#Merge the rows of horizontal_concat on the bottom of data and reset index
c_concat =pd.concat([data, horizontal_concat], axis=0)
c_concat.index = range(c_concat.index.size)
#Replace Nan values by empty cells
cc=c_concat.replace(np.nan,'')
#Write file into a data.txt 
for j in range(0,5):
 [line]=cc.iloc[[j]].values   
 with open('data.txt', 'a') as f:     
  if j <3:    
   for row in line:
     f.write(str(row))
   f.write('\n')
  else:
   line_h=np.empty((len(line[:])),dtype=object)  
   line_h[:]=line.reshape(1,-1)  
   for row in line_h:
    f.writelines(str(row)+',') 
   f.write('\n')
 f.close() 

I would like to export to this txt format

gridtype=curvilinear             
gridsize= 9      
xvals=  -180,  -175,  -170,    
        -180,  -175,  -170,    
        -180,  -175,  -170,     

Thanks so much in advance for your help.

Kind regards,

PD: I am aware that the code looks dirty. There is problably another way to write the same script, but clear and clean. I am open to new ideas. I would like to improve my python skills step by step.

[snippsat]

The dataframe looks like this

Post a working Dataframe and not a example you have put together.

PD: I am aware that the code looks dirty.

Yes if gone make this this mess the why use Pandas DataFrame in the first place?

Bye working DataFrame i mean like this,so this can you also just copy and run.

import pandas as pd

data = {
    "Name": ["Jai", "Princi", "Gaurav", "Anuj"],
    "Height": [5.1, 6.2, 5.1, 5.2],
    "Qualification": ["Msc", "MA", "Msc", "Msc"],
}

df = pd.DataFrame(data)
address = ["Delhi", "Bangalore", "Chennai", "Patna"]
df["Address"] = address 

Show DataFrame and some export examples.

>>> df
     Name  Height Qualification    Address
0     Jai     5.1           Msc      Delhi
1  Princi     6.2            MA  Bangalore
2  Gaurav     5.1           Msc    Chennai
3    Anuj     5.2           Msc      Patna

>>> df.to_dict()
{'Address': {0: 'Delhi', 1: 'Bangalore', 2: 'Chennai', 3: 'Patna'},
 'Height': {0: 5.1, 1: 6.2, 2: 5.1, 3: 5.2},
 'Name': {0: 'Jai', 1: 'Princi', 2: 'Gaurav', 3: 'Anuj'},
 'Qualification': {0: 'Msc', 1: 'MA', 2: 'Msc', 3: 'Msc'}}

>>> df.to_csv(index=False)
('Name,Height,Qualification,Address\r\n'
 'Jai,5.1,Msc,Delhi\r\n'
 'Princi,6.2,MA,Bangalore\r\n'
 'Gaurav,5.1,Msc,Chennai\r\n'
 'Anuj,5.2,Msc,Patna\r\n')

>>> df.to_string()
('     Name  Height Qualification    Address\n'
 '0     Jai     5.1           Msc      Delhi\n'
 '1  Princi     6.2            MA  Bangalore\n'
 '2  Gaurav     5.1           Msc    Chennai\n'
 '3    Anuj     5.2           Msc      Patna')

>>> print(df.to_xml())
<?xml version='1.0' encoding='utf-8'?>
<data>
  <row>
    <index>0</index>
    <Name>Jai</Name>
    <Height>5.1</Height>
    <Qualification>Msc</Qualification>
    <Address>Delhi</Address>
  </row>
  <row>
    <index>1</index>
    <Name>Princi</Name>
    <Height>6.2</Height>
    <Qualification>MA</Qualification>
    <Address>Bangalore</Address>
  </row>
  <row>
    <index>2</index>
    <Name>Gaurav</Name>
    <Height>5.1</Height>
    <Qualification>Msc</Qualification>
    <Address>Chennai</Address>
  </row>
  <row>
    <index>3</index>
    <Name>Anuj</Name>
    <Height>5.2</Height>
    <Qualification>Msc</Qualification>
    <Address>Patna</Address>
  </row>
</data>

[dramauh]

Hi snippsat,
you are right. I should have created a simple working example. I have already updated my post. The code should work without any issue. The real file will have thousands of lines. In fact, xvals is a matrix with 3060*510 values.

[snippsat]

It's not a normal dataFrame that easy to export.
To get DataFrame format to a file,can do a tricks bye using .to_clipboard() and catch the output.

# Your DataFrame
>>> cc
           0            1      2      3
0  gridtype=  curvilinear              
1  gridsize=            9              
2     xvals=         -180 -175.0 -170.0
3                    -180 -175.0 -170.0
4                    -180 -175.0 -170.0

>>> cc.to_clipboard(index=False)
>>> ccc = pd.io.clipboard.clipboard_get()
# This is how test.txt will look
>>> print(ccc)
0	1	2	3

gridtype=	curvilinear		

gridsize=	9		

xvals=	-180	-175.0	-170.0

	-180	-175.0	-170.0

	-180	-175.0	-170.0
>>> with open("test.txt", "w") as f:
...     print(ccc, file=f)   

[dramauh]

I think I found out the problem. It was not about the code. It seems notepad is not a good choice when you have a large data file. Notepad++ displayed the file correctly with all the columns aligned. Thank so much for your help snippsat.

Kind regards,

[sarahroxon7]

Thank you I figured out the problem here, I got the same error with Notepad++!
slope game