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

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: