Machine Learning Tutorials, Courses and Certifications
Related Articles
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)]