Pandas reads excel, TXT, and CSV files

Pandas Reads the document officially provided by the file

The content required for reading a file in pandas must belong to the official document address

Pandas.pydata.org/pandas-docs…

The “Input/Output” command is written for pandas. The “IO” command is written for pandas. The “Input/Output” command is written for pandas

Pandas reads the TXT file

Read TXT files need to determine whether the TXT file conforms to the basic format, that is, whether there is \t,,, and other special delimiters

Usually TXT files look like this

TXT File Example

The following files are spaced with Spaces

Encoding 3 2019-03-22 00:06:32.6835965 Ashshsh 3 2019-03-22 00:06:32.6835965 Ashshsh 4 The 2017-03-22 00:06:32. 8041945 egggCopy the code

The read command can be read_CSV or read_table

import pandas as pd
df =  pd.read_table("./test.txt")
print(df)

import pandas as pd
df =  pd.read_csv("./test.txt")
print(df)
Copy the code

Note, however, that this is a DataFrame of three rows and one column, rather than the three rows and four columns required

import pandas as pd
df =  pd.read_csv("./test.txt")
print(type(df))
print(df.shape)

<class 'pandas.core.frame.DataFrame'>
(3, 1)
Copy the code

Read_csv function

df =  pd.read_csv("./test.txt",sep=' ')
Copy the code

Making, 1, 2, 3, DDD, u, 1 and 4, asd, the as, df, 12 behavior,Copy the code

Making, 1, 2, 3, DDD, u, 1 and 4, asd, the as, df, 12 behavior,Copy the code

df =  pd.read_csv("./demo.txt",header=None,names=['a','b','c','d','e'])
print(df)

df =  pd.read_csv("./demo.txt",header=None,index_col=False,names=['a','b','c','d','e'])
print(df)
Copy the code

import pandas as pd

def fun(x):
    return str(x)+"-haha"

df =  pd.read_csv("./test.txt",sep=' ',header=None,index_col=0,converters={3:fun})
print(type(df))
print(df.shape)
print(df)
Copy the code

Common problems with the read_CSV function

1. In some ides, an error will be reported if the file path or file name contains Chinese characters when importing data files using the READ_csv function.

The solution

Import pandas as pd #df=pd.read_csv('F:/ test/data.txt ') F =open('F:/ test/data.txt ')Copy the code

1. Exclude rows using the skiprows argument. Its function is to exclude a row. Note that the first 3 lines are skiprows=3 and the third line is skiprows=3
2. For irregular delimiters, use regular expressions to read files. Delimiters in files use Spaces, so we just set sep=” “to read files. When the delimiter is not a single space, it may be one space or multiple Spaces, and if you still use sep=” “to read the file, you may get a very strange data because it will also treat Spaces as data. data = pd.read_csv(“data.txt”,sep=”\s+”)
3. The encoding parameter must be set if Chinese encoding errors occur in the read file
4. Add indexes for rows and columns add column indexes with names and row indexes with index_col

Read_csv This command has a number of parameters. Most are unnecessary, since most of the files you download come in standard formats.

Read_table function

Read_fwf function

Reads a file with a fixed-width column, such as a file

Id8141 360.242940 149.910199 11950.7 ID1594 444.953632 166.985655 11788.4 ID1849 364.136849 183.628767 11806.2ID1230 413.836124 184.375703 11916.8 ID1948 502.953953 173.237159 12468.3Copy the code

The read_fwf command has two additional parameters that can be set

Colspecs:

You need to give a list of tuples with a half-open interval, [from,to], which by default extrapolates from the first 100 rows.

Example:

import pandas as pd
colspecs = [(0, 6), (8, 20), (21, 33), (34, 43)]
df = pd.read_fwf('demo.txt', colspecs=colspecs, header=None, index_col=0)
Copy the code

Widths:

Instead of the Colspecs argument, use a width list directly

widths = [6, 14, 13, 10]
df = pd.read_fwf('demo.txt', widths=widths, header=None)
Copy the code

Read_fwf is not used very often and can be found at pandas.pydata.org/pandas-docs… learning

Read_msgpack function

Read_clipboard function

Read data from the clipboard, which can be thought of as the clipboard version of read_table. Useful when converting web pages to tables

This place has the following BUG

module ‘pandas’ has no attribute ‘compat’

I updated the pandas and it can be used normally

Another pitfall is that when reading the clipboard, if you copy Chinese, it is very easy to read the data

The solution

1. Open the site-packages\pandas\ IO \clipboard.py file for self-retrieval
2. Text = text.decode(‘ utF-8 ‘)
3. Save it and use it

Read_excel function

Import pandas as pd s = '{" index ": [1, 2, 3]," columns ": (" a", "b"), "data" : [[1, 3], [2, 5], [6, 9]]}' df = pd.read_json(s,orient='split')Copy the code

import pandas as pd
s = '[{"a":1,"b":2},{"a":3,"b":4}]'
df = pd.read_json(s,orient='records')
Copy the code

{"a":1,"b":1},{"a":2,"b":2}
Copy the code

[{"a":1,"b":1},{"a":2,"b":2}]
Copy the code

{"a":1,"b":1}
{"a":2,"b":2}
Copy the code

Read_html function

Use method, right-click in the web page if found table is also table can be used

For example: data.stcn.com/2019/0304/1…

<table class="..." id="..." > <thead> <tr> <th>... </th> </tr> </thead> <tbody> <tr> <td>... </td> </tr> <tr>... </tr> </tbody> </table> < TABLE > : Define the table <thead> : Define the header of the table < TBody > : Define the body of the table <tr> : define the row of the table <th> : Define the header of the table < TD > : define the table cellsCopy the code

Common BUG

Error ImportError: html5lib not found, please install it

Just install HTML5lib, or use parameters

import pandas as pd
df = pd.read_html("http://data.stcn.com/2019/0304/14899644.shtml",flavor ='lxml')
Copy the code

For more information, see pandas.pydata.org/pandas-docs…

Pandas is a short table of read and write functions

import pandas as pd 2 3 csvframe = pd.read_csv('pandas_data_test\myCSV_01.csv') 4 print(csvframe, "\n-----*-----") 5 csvframe1 = pd.read_table('pandas_data_test\myCSV_01.csv',sep=',') 6 print(csvframe1, "\n-----*-----") 7 csvframe2 = pd.read_csv('pandas_data_test\ mycsv_02.csv ',header=None) Print (csvframe2, "\n-----*-----") 9 csvframe20 = Pd. read_csv('pandas_data_test\ mycsv_02.csv ',names=['white','red','blue','green','animal']) # "\n-----*-----") 11 12 csvframe30 = pd.read_csv('pandas_data_test\myCSV_03.csv') 13 print(csvframe30, "\ n * -- -- -- -- -- -- -- -- -- -") 14 csvframe31 = pd. Read_csv (' pandas_data_test \ myCSV_03 CSV, index_col = [' color ', 'status']) # level index of 15 print(csvframe31, "\ n * -- -- -- -- -- -- -- -- -- -") 17 txtframe4 = 16 pd. Read_table (' pandas_data_test \ ch05_04 TXT, sep = '\ s +) # 18 print according to the canonical parse (txtframe4, "\n-----*-----") 19 txtframe5 = pd.read_table('pandas_data_test\ch05_05.txt',sep=r'\D+',header=None,engine='python') 20 Print (txtframe5, "\n-----*-----") 21 # Use skiprows option to exclude unnecessary lines. Assign the row number of the row to be excluded to the array. Read_table ('pandas_data_test\ch05_06.txt',sep=',',skiprows=[0,1,3,6]) 23 print(txtframe6) 24 Out[1]: 25 white red blue green animal 26 0 1 5 2 3 cat 27 1 2 7 8 5 dog 28 2 3 3 6 7 horse 29 3 2 2 8 3 duck 30 4 4 4 2 1 mouse  31 5 4 4 2 1 mou 32 -----*----- 33 white red blue green animal 34 0 1 5 2 3 cat 35 1 2 7 8 5 dog 36 2 3 3 6 7 horse 37 3 2 2 8 3 duck 38 4 4 4 2 1 mouse 39 5 4 4 2 1 mou 40 -----*----- 41 0 1 2 3 4 42 0 1 5 2 3 cat 43 1 2 7 8 5 dog 44 2 3 3 6 7 horse 45 3 2 2 8 3 duck 46 4 4 4 2 1 mouse 47 -----*----- 48 white red blue green animal 49 0 1 5 2 3 cat 50 1 2 7  8 5 dog 51 2 3 3 6 7 horse 52 3 2 2 8 3 duck 53 4 4 4 2 1 mouse 54 -----*----- 55 color status iteml item2 item3 56 0 black up 3 4 6 57 1 black down 2 6 7 58 2 white up 5 5 5 59 3 white down 3 3 2 60 4 white left 1 2 1 61 5 red up 2 2 2 62 6 red down 1 1 4 63 -----*----- 64 iteml item2 item3 65 color status 66 black up 3 4 6 67 down 2 6 7 68 white up 5 5 5 69 down 3 3 2 70 left 1 2 1 71 red up 2 2 2 72 down 1 1 4 73 -----*----- 74 white red blue green 75 0 1 5 2 3 76 1 2 7  8 5 77 2 3 3 6 7 78 -----*----- 79 0 1 2 80 0 0 123 122 81 1 1 124 321 82 2 2 125 333 83 -----*----- 84 white red blue green animal 85 0 1 5 2 3 cat 86 1 2 7 8 5 dog 87 2 3 3 6 7 horse 88 3 2 2 8 3 duck 89 4 4 4 2 1 mouseCopy the code

Read some data from TXT file

1 print (csvframe2, "\ n * -- -- -- -- -- -- -- -- -- -") 2 # nrows = 2 to get the specified number of rows, Csvfram20 = pd.read_csv('pandas_data_test\ mycsv_02.csv ',skiprows=[2],nrows=2,header=None) 4 print(csvfram20) 5 Out[2]: 6 0 1 2 3 4 7 0 1 5 2 3 cat 8 1 2 7 8 5 dog 9 2 3 3 6 7 horse 10 3 2 2 8 3 duck 11 4 4 4 2 1 mouse 12 -----*----- 13 0 1  2 3 4 14 0 1 5 2 3 cat 15 1 2 7 8 5 dogCopy the code

Another interesting and common operation is to slice the text you want to parse and then walk through the sections, performing a specific operation on each of them.

For example, taking a list of numbers and adding them up every second row, and finally inserting the and into the Series object “is a simple example to understand,

It has no practical application, but once you understand how it works, you can apply it to more complex situations.

1 csvframe1 = pd.read_table('pandas_data_test\myCSV_01.csv',sep=',') 2 print(csvframe1, "\n-----*-----") 3 out = pd.Series() 4 pieces = pd.read_csv('pandas_data_test\myCSV_01.csv',chunksize=4) # The chunksize parameter determines the number of lines to split each section. 7 print(piece['white']) 8 out.at[i] = piece['white'].sum() 9 i += 1 10 print(out, "\n-----*-----") 11 Out[3]: 12 white red blue green animal 13 0 1 5 2 3 cat 14 1 2 7 8 5 dog 15 2 3 3 6 7 horse 16 3 2 2 8 3 duck 17 4 4 4 2 1 mouse  18 5 4 4 2 1 mou 19 -----*----- 20 0 1 21 1 2 22 2 3 23 3 2 24 Name: white, dtype: int64 25 4 4 26 5 4 27 Name: white, dtype: int64 28 0 8 29 1 8 30 dtype: int64Copy the code

Write data to a CSV file

1 print(csvframe1) 2 print(csvframe1.to_csv('pandas_data_test\ CH05_07.csv ') Print (csvframe1.to_csv('pandas_data_test\ CH05_07b.csv ',index =False,header=False)) 5 Print (csvframe30.to_csv('pandas_data_test\ CH05_08.csv ')) 6 # Replace the empty field with the desired value using the na_rep option of to_csv(). Print (csvframe30.to_csv('pandas_data_test\ CH05_09.csv ',na_rep=" empty ") 8 Out[4]: 9 white red blue green animal 10 0 1 5 2 3 cat 11 1 2 7 8 5 dog 12 2 3 3 6 7 horse 13 3 2 2 8 3 duck 14 4 4 4 2 1 mouse 15 5 4 4 2 1 mou 16 None 17 None 18 None 19 NoneCopy the code

Enter the folder we can see the corresponding file:

Reading and writing HTML files

1 Frame = pd.dataframe (np.Arange (4).0 2 Print (frame.to_html()) 3 Frame 2 = pd.dataframe (0 Np. Random. The random ((4, 4)), the index = [' white ', 'black', 'red', 'blue1], the columns = [' up' and 'down', 'right' and 'left']) 4 s = [' < HTML > ']  5 s.append('<HEAD><TITLE>My DataFrame</TITLE></HEAD>') 6 s.append(' <B0DY>') 7 s.append(frame.to_html()) 8 s.append('</BODY></HTML>') 9 html = ''.join(s) 10 html_file = open('pandas_data_test\myFrame.html','w') 11 html_file.write(html) 12 html_file.close() 13 web_frames = pd.read_html('pandas_data_test\myFrame.html') 14 print(web_frames[0]) 15 ranking = pd.read_html('http://www.meccanismocomplesso.org/en/ Eccanismo-complesso-sito-2 / Classifia-Punteggio /') print(ranking[0][1:10]) 18 <table border="1" class="dataframe"> 19 <thead> 20 <tr style="text-align: right;" > 21 <th></th> 22 <th>0</th> 23 <th>1</th> 24 </tr> 25 </thead> 26 <tbody> 27 <tr> 28 <th>0</th> 29 <td>0</td> 30 <td>1</td> 31 </tr> 32 <tr> 33 <th>1</th> 34 <td>2</td> 35 <td>3</td> 36 </tr> 37 </tbody> 38 </table> 39 Unnamed: 0 0 1 40 0 0 0 1 41 1 1 2 3 42 # Nome Exp Livelli 43 1 2 admin 9029 NaN 44 2 3 BrunoOrsini 2124 NaN 45 3 4 Berserker 700  NaN 46 4 5 Dnocioni 543 NaN 47 5 6 albertosallusti 409 NaN 48 6 7 Jon 233 NaN 49 7 8 Mr.Y 180 NaN 50 8 9 michele sisinni 157 NaN 51 9 10 Selina 136 NaNCopy the code

Read data from XML

Pandas does not have any I/O API functions specifically designed to handle the XML format. It doesn’t, but this format is actually

This is important because a lot of structured data is stored in XML format. It doesn’t matter that pandas doesn’t have a specialized handler because Python

There are many libraries that read and write XML data (except pandas). One of these libraries, called LXML, is so good at processing large files that it has been converted from

Stand out from many similar libraries. This section describes how to use it to process XML files, and how to integrate it with PANDAS to maximize its functionality

Finally, the required data is retrieved from the XML file and converted into a DataFrame object.

The XML source file is shown below

Parse ('pandas_data_test\books.xml') 4 root = xml.getroot() # Print (root.book.author) 6 mes1 = root.book.getChildren () 7 print(" root.book.getchildren () ") \n", Mes1) 8 mes2 = root.book [1].getChildren () # print([child.tag for child in mes2]) # print([child.tag for child in mes2]) # Print ([child-.text for child in mes2]) # print([child-.text for child in mes2]) # print([child-.text for child in mes2]) # print([child-.text for child in mes2]) 12 272103_l_EnRoss, Mark 13 root.book.getChildren () 15 ['Author', 'Title', 'Genre', 15 ['Author', 'Title', 'Genre', 'Price', 'PublishDate'] 16 [' 272l03_l_EnBracket, Barbara', 'XML for Dummies', 'Computer', '35.95', '20L4-L2-L6 ']Copy the code

Read and write Microsoft Excel files

Read_excel () and to_excel() can read. XLS and. XLSX files.

Reading and writing JSON data read_json() and to_json() HDF5 Format So far, you have learned how to read and write data in text format. To analyze large amounts of data, it is best to use binary formats. Python has many kinds of binary data processing

Tool. The HDF5 library has had some success in this regard. HDF stands for Hierarchical Data Format. HDF5

Libraries focus on reading and writing HDF5 files, a data structure consisting of nodes capable of storing large data sets. The library is all in C language

Developed to provide python/ MATLAB and Java language interface. Its rapid expansion is the result of widespread use by developers, as well as its effectiveness

In particular, it is efficient to store large amounts of data in this format. HDF5 is simpler than other formats that handle binary data

Real-time compression is supported so that files can be compressed using repeated patterns in data structures. Currently, Python provides two ways to manipulate HDF5 data

Method: PyTables and H5PY. There are several differences between the two approaches, and the choice of one depends largely on your needs.

H5py provides interfaces to HDF5’s advanced apis. PyTables encapsulates many of the details of HDF5, providing more flexible data containers, index tables, and searches

Functions and other computing-related media. Pandas also has a class called HDFStore, similar to Diet, that stores pandas with PyTables

Object. Before using the HDF5 format, you must import the HDFStore class.

1 from pandas. IO. Pytables import HDFStore 2 If no, install 3 Frame = Pd. DataFrame (np) arange (16). Reshape (4, 4), the index = [' white ', 'black1', 'red' and 'blue'], the columns = [' up 'and' down ', 'right' and 'left']) 4 store = HDFStore('pandas_data_test\mydata.h5') 5 store['obj1'] = frame 6 frame1 = Pd. DataFrame (np) random. Rand (16). Reshape (4, 4), the index = [' white ', 'black1', 'red' and 'blue'], the columns = [' up 'and' down ', 'right' and 'left'])  7 store['obj2'] = frame1 8 print(store['obj1']) 9 print(store['obj2']) 10 Out[7]: 11 up down right left 12 white 0 1 2 3 13 black1 4 5 6 7 14 red 8 9 10 11 15 blue 12 13 14 15 16 up down right left 17 White 0.251269 0.422823 0.619294 0.273534 18 black1 0.593960 0.353969 0.966026 0.104581 19 red 0.964577 0.625644 0.342923 0.638627 20 blue 0.246541 0.997952 0.414599 0.908750 21 Closing Remaining open files:pandas_data_test\mydata.h5... doneCopy the code

Implement object serialization

The ****pickle module implements a powerful algorithm for pickling and deserializing data structures implemented in Python.

Serialization is the process of converting the hierarchy of objects into byte streams. Serialization facilitates the transfer, storage, and reconstruction of objects that can be reloaded using only the sink

Build an object that retains all of its original characteristics.

The pandas library is handy for object serialization (deserialization), all tools are readily available, and there is no need to import the cPickle module into a Python session

Block, all operations are implicit. Pandas’ serialization format is not entirely ASCII.

1 import pickle 2 data = { 'color': ['white','red'], 'value': [5, 7]} 3 pickled_data = pickle.dumps(data) 4 print(pickled_data) 5 nframe = pickle.loads(pickled_data) 6 print(nframe) 7 8 0 0 frame = pd.dataframe (np.arange(16). 0 0 Index = ['up','down','left','right']) 10 frame.to_pickle('pandas_data_test\frame.pkl') # Print (pd.read_pickle('pandas_data_test\frame.pkl')) 13 b'\x80\x03}q\x00(X\x05\x00\x00\x00colorq\x01]q\x02(X\x05\x00\x00\x00whiteq\x03X\x03\x00\x00\x00redq\x04eX\x05\x00\x00\x0 0valueq\x05]q\x06(K\x05K\x07eu.' 14 {'color': ['white', 'red'], 'value': [5, 7]} 15 0 1 2 3 16 up 0 1 2 3 17 down 4 5 6 7 18 left 8 9 10 11 19 right 12 13 14 15Copy the code

Interconnecting with databases **** In many applications, little data is used from text files, because text files are not the most efficient way to store data.

Data is often stored in SQL-like relational databases, and NoSQL databases have recently become popular as a supplement.

Loading data from an SQL database and converting it to a DataFrame object is simple. Several functions provided by PANDAS simplify the process.

The pandas. IO. SQL module provides a unified database independent interface called SQLAlchemy. This interface simplifies the connection mode for both

There is only one set of operation commands for any type of database. Connect to the database using the create_engine() function, which you can use to configure the driver

All required attributes such as username, password, port, and database instance. The typical form of a database URL is:

dialect+driver://username:password@host:port/database

The identifying name of a name, such as SQLite, mysql, PostgresQL, Oracle, or MSSQL. Drivername is used to connect using all lowercase letters

Name of the DBAPI to the database. If not specified, the “default” DBAPI will be imported (if available) – this default value is usually available to the back end

The widest range of drivers.

1 from sqlalchemy import create_engine 2 3 # PostgreSQL database 4 # default 5 engine = Create_engine (' postgresQL :// Scott :tiger@localhost/mydatabase') 6 # pg8000 drive 7 engine = Create_engine (' postgresQL +pg8000:// Scott :tiger@localhost/mydatabase') 8 # psycopg2 drive 9 engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase') 10 # MySql 11 # default 12 engine = Create_engine ('mysql:// Scott :tiger@localhost/foo') 13 14 # mysql-python create_engine('mysql+mysqldb://scott:tiger@localhost/foo') 16 17 # MySQL-connector-python 18 engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo') 19 # OurSQL 20 engine = create_engine('mysql+oursql://scott:tiger@localhost/foo') 21 # Oracle 22 engine = Create_engine ('oracle:// Scott :[email protected]:1521/sidname') 23 24 engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname') 25 # Microsoft SQL 26 # pyodbc 27 engine = create_engine('mssql+pyodbc://scott:tiger@mydsn') 28 29 # pymssql 30 engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')Copy the code

SQLite:

Because SQLite connects to local files, the URL format is slightly different. The File part of the URL is the file name of the database.

For relative file paths, this requires three slashes: engine = create_engine(‘sqlite:///foo.db’)

**** For absolute file paths, the absolute path is followed by three slashes:

Unix/Mac – 4 initial slashes in total

engine = create_engine(‘sqlite:absolute/path/to/foo.db’)

**Windows

engine = create_engine(‘sqlite:///C:\path\to\foo.db’)

Windows alternative using raw string

engine = create_engine(r’sqlite:///C:\path\to\foo.db’)

SQLite3 Data read and write **** Learn to use Python’s built-in SQLite database SQLite3. The SQLite3 tool implements a simple, lightweight DBMS, SQL,

It can therefore be built into any application implemented in The Python language. It is very useful, you can create an embedded database in a single file.

If you want to use all the features of a database without having to install a real database, this tool is the best choice. If you want to use real

SQLite3 does not practice database operations before using the database, or use the database to store data in a single program regardless of the interface

Wrong choice.

1 from sqlalchemy import create_engine 2 frame = pd.DataFrame( Np.arange (20).0 (4,5), Columns =[' White ','red','blue','black','green']) 0 # connect SQLite3 database 4 engine = 0 Create_engine ('sqlite:///pandas_data_test/foo.db') 5 # Convert DataFrame to database table. 6 # to_sql(self, name, con, schema=None, if_exists='fail', index=True, 7 # index_label=None, chunksize=None, Print (pd.read_sql('colors',engine))Copy the code

Running results: