Python Pandas DataFrame to create JSON file & using MySQL sample table to JSON string by to_json()

We can create JSON string or file by using data from Pandas DataFrame. We will first create one Pandas DataFrame by using some sample data.
https://www.plus2net.com/python/pandas-to_json.php

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)

After creating the DataFrame we used to_json() to create a JSON file
df.to_json('my_file.xlsx')

This will create the my_file.json file in the same directory. A different path can be given to store the file in different location in the system.
We can add different options while generating JSON string. One important option is orient. The value of orient option can be split, records,index, columns, values, table. The value index is the default value.
df.to_json(orient='table')


From MySQL database using read_sql()
We can connect to MySQL database by using SQLAlchemy engine and after connection we will create our DataFrame by using data from sample table by using read_sql.
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
sql="SELECT * FROM student LIMIT 0,10"
df=pd.read_sql(sql,my_conn)
df.to_json('D:\\my_data\\student.json')
Here 10 rows of data from the student table is taken and the JSON file is created.
Excel or CSV file to JSON
From excel file we can create JSON string by first creating the dataframe by reading the excel file by using the method read_excel()
df=pd.read_excel("D:\\my_data\\student.xlsx")
df.to_json()

Similarly we can read csv file and generate JSON string
df=pd.read_csv("D:\\my_data\\student.csv")
df.to_json()

#pandas_to_json #dataframetojson #pandasjson