Reading and Writing data in Pandas¶

In [6]:
#import libraries
import pandas as pd
import numpy as np

1. Reading from TEXT File¶

  • Let’s read a dataset named data.txt in the working directory.
In [8]:
df=pd.read_table("data.txt")
df
Out[8]:
Name,Score,Age
0 A,80,28
1 B,85,25
2 C,70,23
3 D,90,20
4 E,75,20
In [9]:
df=pd.read_table("data.txt",sep=',')
df
Out[9]:
Name Score Age
0 A 80 28
1 B 85 25
2 C 70 23
3 D 90 20
4 E 75 20

Reading from CSV¶

In [11]:
df = pd.read_csv("melbourne.csv")
df
Out[11]:
Suburb Address Rooms Type Price Method SellerG Date Distance Postcode ... Bathroom Car Landsize BuildingArea YearBuilt CouncilArea Lattitude Longtitude Regionname Propertycount
0 Abbotsford 68 Studley St 2 h NaN SS Jellis 03-09-2016 2.5 3067.0 ... 1.0 1.0 126.0 NaN NaN Yarra -37.80140 144.99580 Northern Metropolitan 4019.0
1 Abbotsford 85 Turner St 2 h 1480000.0 S Biggin 03-12-2016 2.5 3067.0 ... 1.0 1.0 202.0 NaN NaN Yarra -37.79960 144.99840 Northern Metropolitan 4019.0
2 Abbotsford 25 Bloomburg St 2 h 1035000.0 S Biggin 04-02-2016 2.5 3067.0 ... 1.0 0.0 156.0 79.0 1900.0 Yarra -37.80790 144.99340 Northern Metropolitan 4019.0
3 Abbotsford 18/659 Victoria St 3 u NaN VB Rounds 04-02-2016 2.5 3067.0 ... 2.0 1.0 0.0 NaN NaN Yarra -37.81140 145.01160 Northern Metropolitan 4019.0
4 Abbotsford 5 Charles St 3 h 1465000.0 SP Biggin 04-03-2017 2.5 3067.0 ... 2.0 0.0 134.0 150.0 1900.0 Yarra -37.80930 144.99440 Northern Metropolitan 4019.0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
23542 Wyndham Vale 25 Clitheroe Dr 3 u NaN PN Harcourts 26-08-2017 27.2 3024.0 ... 1.0 0.0 552.0 119.0 1990.0 NaN -37.90032 144.61839 Western Metropolitan 5262.0
23543 Wyndham Vale 19 Dalrymple Bvd 4 h NaN S hockingstuart 26-08-2017 27.2 3024.0 ... NaN NaN NaN NaN NaN NaN -37.87882 144.60184 Western Metropolitan 5262.0
23544 Yallambie 17 Amaroo Wy 4 h 1100000.0 S Buckingham 26-08-2017 12.7 3085.0 ... 3.0 2.0 NaN NaN NaN NaN -37.72006 145.10547 Northern Metropolitan 1369.0
23545 Yarraville 6 Agnes St 4 h 1285000.0 SP Village 26-08-2017 6.3 3013.0 ... 1.0 1.0 362.0 112.0 1920.0 NaN -37.81188 144.88449 Western Metropolitan 6543.0
23546 Yarraville 33 Freeman St 4 h 1050000.0 VB Village 26-08-2017 6.3 3013.0 ... 2.0 2.0 NaN 139.0 1950.0 NaN -37.81829 144.87404 Western Metropolitan 6543.0

23547 rows × 21 columns

3. Reading from JSON¶

In [ ]:
df =pd.read_json('itronix.json')  
df

4. Reading from SQL¶

In [ ]:
pip install pysqlite3
In [ ]:
import sqlite3  
con = sqlite3.connect("database.db")
In [ ]:
df = pd.read_sql_query("SELECT * FROM student", con)  
df

5. Reading from Excel¶

In [ ]:
df = pd.read_excel('itronix.xlsx')  
df

Writing Data in Pandas¶

To store the data in csv file You can use the to_csv method to write the dataset in csv format.

In [13]:
data = [['Robin',26,45.34],['Karan',25,78.5],['Priya',23,87.67],['Varun',22,56],['Keisha',23,97]]
df=pd.DataFrame(data,columns=['Name','Age','Marks'])
df
Out[13]:
Name Age Marks
0 Robin 26 45.34
1 Karan 25 78.50
2 Priya 23 87.67
3 Varun 22 56.00
4 Keisha 23 97.00

Save data to CSV File¶

In [14]:
df.to_csv("new_data.csv")

Save data to HTML Table¶

In [15]:
df.to_html("new_data.html")

Copy Data¶

In [18]:
df.to_clipboard(sep=',')

Save data to JSON¶

In [19]:
df.to_json("new_data.json")

Save data to Excel¶

In [22]:
df.to_excel("new_data.xlsx")

Save data to XML¶

  • pip install lxml
In [25]:
df.to_xml("new_data.xml")

Save data to SQL¶

  • pip install sqlalchemy
In [32]:
from sqlalchemy import create_engine
engine = create_engine('sqlite:///itronix.db', echo=False)
In [33]:
df.to_sql('users', con=engine)
Out[33]:
5
In [34]:
engine.execute("SELECT * FROM users").fetchall()
Out[34]:
[(0, 'Robin', 26, 45.34),
 (1, 'Karan', 25, 78.5),
 (2, 'Priya', 23, 87.67),
 (3, 'Varun', 22, 56.0),
 (4, 'Keisha', 23, 97.0)]