to_string to get string output from DataFrame using MySQL, Excel, Dictionary & CSV file as source

Returns console-friendly tabular string from the DataFrame.

import pandas as pd
my_dict={
	'NAME':['Ravi','Raju','Alex'],
	'ID':[1,2,3],'MATH':[30,40,50],
	'ENGLISH':[20,30,40]
	}
df=pd.DataFrame(data=my_dict)
df.to_string()

We can store the string in a file .

str1=df.to_string() # collect the string in a variable 
fob=open('D:\\my_data\\my_file.txt','w') # file object
fob.write(str1)    # write to file. 
fob.close()

This will create a file my_file.txt with the data from DataFrame inside my_data directory of D drive.

Python Pandas string output from DataFrame & using MySQL sample table as source by to_string()

Options

Some of the important options are explained here

columns

By default all columns are taken,however we can specify which columns to only include.

df.to_string(columns=['NAME','ID'])

Output

'   NAME  ID\n0  Ravi   1\n1  Raju   2\n2  Alex   3'

header

We can hide the header by setting it to False. We can also give aliases to use as column names.

df.to_string(header=['NAME1','ID2','MATH3','ENGLISH4'])

Output

'  NAME1 ID2 MATH3 ENGLISH4\n0  Ravi   1    30       20\n1  Raju   2    40       30\n2  Alex   3    50       40'

index=True/False

We can hide the index column by setting it to False ( default is True )

df.to_string(index=False)

col_space

Minimum width of each column.

df.to_string(col_space=10)

na_rep

String representation of NaN to use

justify

left, right , center ...

df.to_string(justify='center')

Using SQLAlchemy

We will collect records from our sample student table in MySQL database and display as string using to_string().
Collect SQL dump of sample student table below.
Read more on MySQL with SQLAlchemy connection. you can add path if you want the file to be created with the string ( sample is given above )

import pandas as pd 
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")
sql="SELECT * FROM student LIMIT 0,10 "
df = pd.read_sql(sql,my_conn)
df.to_string()

From Excel file to string output

In above code we have created one DataFrame by taking data from a MySQL database table. We can create DataFrame by using any excel data or by using any csv file or from any other sources. ( check here to create a DataFrame from 8 different sources )
Once a DataFrame is created, then using that we can create string output by using to_string(). Here is one example to read one Excel file to a DataFrame and generate the string, you can explore other sources to create a DataFrame and finally generate string / file.
We used read_excel() to read our sample student.xlsx file.

df=pd.read_excel("D:\\my_data\\student.xlsx") # Path of the file. 
df.to_string()

We can read one csv file by using read_csv()

df=pd.read_csv("D:\\my_data\\student.csv") # change the path
df.to_string()

Data input and output from Pandas DataFrame
Pandas read_csv() read_excel() read_json() to_csv() to_excel() tolist()

Numpy arrays Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

to_string(): DataFrame to string