current position:Home>Python data analysis - file reading

Python data analysis - file reading

2022-01-31 23:16:06 Xiao Wang is not serious

This is my participation 11 The fourth of the yuegengwen challenge 19 God , Check out the activity details :2021 One last more challenge

txt、Csv、Excel、JSON、SQL File read (Python)

txt File read and write

Create a txt file

image-20211117191500810

f=open(r'text.txt','r',encoding='utf-8')
s=f.read()
f.close()
print(s)
 Copy code 

image-20211117192720306

open( ) Is the way to open a file

'text.txt' file name In the same folder, so you can omit the path

If not in the same folder ‘xxx/xxx/text.txt’ File name preceded by path

encoding: Set character encoding

read( ) Is to read the contents of the file

close( ) It's closing the file

with

open( ) When the function method opens a file and reads the contents of the file , If you don't close the file , You will not be able to modify this file . When the file is opened and the contents of the file are written , If the file is not closed, the written contents cannot be saved .

stay Python In language , Provides with And open( ) Use with function methods

adopt with And open( ) Function collocation, no need to write close( ) Function method

with open(r'text.txt','r',encoding='utf-8') as f:
    s=f.read()
print(s)
 Copy code 

image-20211117193040278

write in

with open(r'text.txt','w') as f:
    f.write('qwertyuiop')
 Copy code 

image-20211117194905626

Write multiple rows

with open(r'text.txt','w') as f:
    text=['asdfghjk\n','xcvbnmrtyui\n','123456789\n']
    f.writelines(text)
 Copy code 

image-20211117195100045

open(r'text.txt','w') Function ,'w' Parameter means write , The original contents of the file will be overwritten

File open mode

  • r read-only Read only default mode
  • w Just write Write in the original file , Overwrite the original file
  • a Just write Do not overwrite the original file , Append at the end
  • wb write in Write... In binary form , Use... When saving pictures
  • r+ Reading and writing Do not overwrite the original file , Append at the end
  • w+ Reading and writing Write in the original file , Overwrite the original file
  • a+ Reading and writing Do not overwrite the original file , Append at the end

CSV File read and write

read_csv( )

Read... In the current directory text.csv

import pandas as pd
a=pd.read_csv(r'text.csv')
print(a)
 Copy code 

image-20211118154707796

Set fields

import pandas as pd
a=pd.read_csv(r'text.csv',names=['id','name'])
print(a)
 Copy code 

image-20211118154925920

Specify the corresponding index column

import pandas as pd
a=pd.read_csv(r'text.csv',names=['id','name'],index_col='id')
print(a)
 Copy code 

image-20211118155101296

import pandas as pd
a=pd.read_csv(r'text.csv',names=['id','name'],index_col=0)
print(a)
 Copy code 

image-20211118155936730

Get the specified column

import pandas as pd
a=pd.read_csv(r'text.csv',names=['id','name'],usecols=[0])
print(a)
b=pd.read_csv(r'text.csv',names=['id','name'],usecols=['id'])
print(b)
 Copy code 

image-20211118160111622

write in

to_csv( )

import pandas as pd
data={'id':['1','2','3'],'name':['gh','jk','ty']}
a=pd.DataFrame(data)
a.to_csv(r'text.csv')
 Copy code 

image-20211118160721939

Set write column

import pandas as pd
data={'id':['1','2','3'],'name':['gh','jk','ty']}
a=pd.DataFrame(data)
a.to_csv(r'text.csv',columns=['id'])
 Copy code 

image-20211118160925017

Set write mode

mode w For writing ( Cover ) a To add

import pandas as pd
data={'id':['1','2','3'],'name':['gh','jk','ty']}
a=pd.DataFrame(data)
a.to_csv(r'text.csv')
a.to_csv(r'text.csv',mode='a')
 Copy code 

image-20211118161148822

Whether to write the column name field

header

import pandas as pd
data={'id':['1','2','3'],'name':['gh','jk','ty']}
a=pd.DataFrame(data)
a.to_csv(r'text.csv')
a.to_csv(r'text.csv',mode='a',header=False)
 Copy code 

image-20211118161439353

The second write does not write the column name

Delete index

index=None

import pandas as pd
data={'id':['1','2','3'],'name':['gh','jk','ty']}
a=pd.DataFrame(data)
a.to_csv(r'text.csv',index=None)
a.to_csv(r'text.csv',mode='a',header=False,index=None)
 Copy code 

image-20211118161615766

Excel File read and write

read_excel( )

Parameters :

sheet_name='name' Is the name of the read sub table , You can write the table name 、 Location subscript .

index_col Specify the corresponding index column for , Subscript the field name or field list .

usecols To get the specified column

names Set column fields for

header For which row to use as the field name

nrows Get the number of rows for the specified

skiprows Skip specific lines for ,skipfooter Skip the end n That's ok

import pandas as pd
a=pd.read_excel(r'text.xlsx')
print(a)
 Copy code 

image-20211118163152122

image-20211118163158707

Selection table

sheet_name

image-20211118163248615

Create a new table

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=1)
print(a)
 Copy code 

image-20211118163411089

Set index columns

index_col

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,index_col=[0])
print(a)
 Copy code 

image-20211118163509449

Get the specified column

usecols

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,usecols=[0])
print(a)
 Copy code 

image-20211118163733752

Set column fields

names

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,names=['ID','NAME','CLASS'])
print(a)
 Copy code 

image-20211118163832628

Specify a behavior field name

header

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,header=1)
print(a)
 Copy code 

image-20211118164019711

Set the number of rows to get

nrows

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,nrows=2)
print(a)
 Copy code 

image-20211118164126982

skip n That's ok

skiprows Skip the former n That's ok

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,skiprows=1)
print(a)
 Copy code 

image-20211118164512708

skipfooter Skip the end n That's ok

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,skipfooter=3)
print(a)
 Copy code 

image-20211118164541263

write in

import pandas as pd
data={'id':[1,2,3,4],'name':['A','B','C','D']}
a=pd.DataFrame(data)
a.to_excel(r'text.xlsx')
 Copy code 

image-20211118170357301

Write multiple tables

import pandas as pd
data={'id':[1,2,3,4],'name':['A','B','C','D']}
a=pd.DataFrame(data)
writer = pd.ExcelWriter(r'text.xlsx')
a.to_excel(writer,sheet_name='1')
a.to_excel(writer,sheet_name='2')
writer.save()
writer.close()
 Copy code 

image-20211118192201277

image-20211118192210232

Write new sub table

import pandas as pd
import openpyxl
book = openpyxl.load_workbook(r'text.xlsx')
writer=pd.ExcelWriter(r'text.xlsx')
writer.book=book
writer.sheets=dict((ws.title,ws) for ws in book.worksheets)
data={'id':[5,2,8,4],'name':['H','B','C','D']}
a=pd.DataFrame(data)
a.to_excel(writer,sheet_name="3")
writer.save()
writer.close()
 Copy code 

image-20211118192654125

JSON File read and write

read_json()

import pandas as pd
a=pd.read_json(r'text.json',encoding='utf8')
print(a)
 Copy code 

image-20211118194544366

serialize

import pandas as pd
a=pd.read_json(r'text.json',encoding='utf8')
b=pd.json_normalize(a.data)
print(a)
print(b)
 Copy code 

image-20211118194921915

write in

to_json( )

force_ascii Encode the format for the data , The default is True, Chinese to Unicode Form write , If False, Chinese to ANSI Form write .

import pandas as pd
data={'id':[1,2,3],'name':['a','b','c']}
a=pd.DataFrame(data)
a.to_json('text.json',force_ascii=False)
 Copy code 

image-20211118200027122

SQL File read

import pymysql
con = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user='root',
    password='123456',
    db='test03',
    charset='utf8'
)
#  Create cursors 
cursor=con.cursor()
#  perform sql sentence 
cursor.execute("select * from test")
#  Explain all returned results 
res=cursor.fetchall()
print(res)
con.close()
 Copy code 

image-20211119091834244

Pandas Read MySQL Database content

import pymysql
import pandas as pd
con = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user='root',
    password='123456',
    db='test03',
    charset='utf8'
)
sql="select * from test"
pd=pd.read_sql_query(sql,con)
print(pd)
 Copy code 

image-20211119092109695

copyright notice
author[Xiao Wang is not serious],Please bring the original link to reprint, thank you.
https://en.pythonmana.com/2022/01/202201312316047635.html

Random recommended