Let's sort out how python can read data files.
1. python built-in methods (read, readline, readlines)
- read(): read the entire file at one time. The read(size) method is recommended. The larger the size, the longer the running time
- readline(): read one line at a time. Used when there is not enough memory. Generally, it is not used very much
- readlines(): read the contents of the whole file at one time and return to the list by line, which is convenient for us to traverse
2. Built in module (csv)
python has built-in csv module for reading and writing csv files. csv is a comma delimited file, which is one of the most common data storage formats in data science.
csv module can easily read and write various volume data. Of course, large amount of data needs code level optimization.
- csv module reads files
# Read csv file import csv with open('test.csv','r') as myFile: lines=csv.reader(myFile) for line in lines: print (line)
- csv module write file
''' No one answers any questions? Xiaobian created a Python exchange of learning QQ Group: 531509025 Look for like-minded partners to help each other,There are also good videos and tutorials in the group PDF e-book! ''' import csv with open('test.csv','w+') as myFile: myWriter=csv.writer(myFile) # Writerow write line by line myWriter.writerow([7,8,9]) myWriter.writerow([8,'h','f']) # writerow multiline write myList=[[1,2,3],[4,5,6]] myWriter.writerows(myList)
3. Use numpy Library (loadtext, load, fromfile)
- Loadtext method
Loadtext is used to read text files (including txt, csv, etc.) and gz or bz2 format compressed file, on the premise that each line of file data must have the same number of values.
import numpy as np # The dtype parameter in loadtext() is set to float by default # This is set to str string for easy display np.loadtxt('test.csv',dtype=str) # out: array(['1,2,3', '4,5,6', '7,8,9'], dtype='<U5')
- load method
load is used to read numpy npy, .npz or pickled persistent files.
import numpy as np # Mr. Cheng npy file np.save('test.npy', np.array([[1, 2, 3], [4, 5, 6]])) # Load npy files using load np.load('test.npy') ''' out:array([[1, 2, 3], [4, 5, 6]]) '''
- fromfile method
The fromfile method can read simple text data or binary data. The data comes from the binary data saved by the tofile method. When reading data, you need to specify the element type and modify the shape of the array appropriately.
''' No one answers any questions? Xiaobian created a Python exchange of learning QQ Group: 531509025 Look for like-minded partners to help each other,There are also good videos and tutorials in the group PDF e-book! ''' import numpy as np x = np.arange(9).reshape(3,3) x.tofile('test.bin') np.fromfile('test.bin',dtype=np.int) # out:array([0, 1, 2, 3, 4, 5, 6, 7, 8])
4. Use pandas Library (read_csv, read_excel, etc.)
pandas is one of the most commonly used analysis libraries for data processing. It can read data files in various formats and generally output dataframe format.
Such as: txt, csv, excel, json, clipboard, database, html, hdf, parquet, pickled file, sas, stata, etc
- read_csv method
read_csv method is used to read csv format files and output dataframe format.
import pandas as pd pd.read_csv('test.csv')
- read_excel method
Read excel files, including xlsx, xls and xlsm formats
import pandas as pd pd.read_excel('test.xlsx')
Control the reading of any parameter (sep) of the file through the separator
- read_json method
Read json format file
df = pd.DataFrame([['a', 'b'], ['c', 'd']],index=['row 1', 'row 2'],columns=['col 1', 'col 2']) j = df.to_json(orient='split') pd.read_json(j,orient='split')
- read_html method
Read html table
- read_clipboard method
Read clipboard contents
- read_pickle method
Read plckled persistent file
- read_sql method
Read the database data, connect the database, and then pass in the sql statement
- read_dhf method
Read hdf5 files, suitable for reading large files
- read_parquet method
Read parquet file
- read_sas method
Read sas file
- read_stata method
Read stata file
- read_gbq method
Reading google bigquery data
5. Read and write excel files (xlrd, xlwt, openpyxl, etc.)
python has many libraries for reading and writing excel files. In addition to pandas mentioned earlier, there are xlrd, xlwt, openpyxl, xlwings and so on.
- xlrd Library: read data from excel and support xls and xlsx
- xlwt Library: modify excel. xlsx format cannot be modified
- xlutils Library: modify an existing file in xlw and xlrd
- openpyxl: it mainly reads and edits excel in xlsx format
- xlwings: read, write and modify xlsx, xls and xlsm format files
- xlsxwriter: used to generate excel tables, insert data, insert icons and other table operations. Reading is not supported
- Microsoft Excel API: pywin32 needs to be installed to communicate directly with Excel process. You can do anything you can do in Excel, but it is slow
6. Operate the database (pymysql, cx#u Oracle, etc.)
python supports the interaction of almost all databases. After connecting to the database, you can use sql statements to add, delete, modify and query.
- pymysql: used to interact with mysql database
- sqlalchemy: used to interact with mysql database
- cx_Oracle: used to interact with oracle database
- sqlite3: built-in library, which is used to interact with sqlite database
- pymssql: used to interact with sql server database
- pymongo: used to interact with mongodb non relational database
- redis and pyredis: used to interact with non relational databases