A, install,

To install the software for pandas, you need to install the software for pandas. To install the software for pandas, you need to install the software for pandas. To install the software for pandas, you need to install the software. Net.4, vC-Compiler, and Winsdk_web 3: After steps 1 and 2 are ready, install pandas

2. Pandas operates the Excel table

Data: the file name test_pandas. XLSX

1: importing modules

import  pandas  as pd
Copy the code

2: There are two ways to read Excel files:

Method one: The first form is read by default
Import pandas as pd df=pd.read_excel('test_pandas. XLSX ')# Pd.set_option ('display.width',None) print(" Get all values :\n{0}". Format (data)Copy the code

Output:

Get all values: Case_id title data 0 1 normal url to https://www.lixinchuxing.com/url1.html {" uname ":" uname1 ", "PWD" : "pwd1"} 1 2 query vehicles https://www.lixinchuxing.com/url2.html {" uname ":" uname2 ", "PWD" : "pwd2}" 2, 3 for more details https://www.lixinchuxing.com/url3.html {" uname ":" uname3 ", "PWD" : "pwd3"}, 3, 4, modify configuration https://www.lixinchuxing.com/url4.html {" uname ":" uname4 ", "PWD" : "pwd4}"Copy the code
Method 2: Read by specifying the form name
Df =pd.read_excel('lemon.xlsx',sheet_name='student')# Print (" Get all values :\n{0}". Format (data)Copy the code

The output

Get all values: [[1 'Zhang SAN' 88 88 88 88 88] [2 'Li Si' 77 77 77 77 77] [3 'Wang Wu' 66 66 66] [4 'Zhao Liu' 55 55 55 55]]Copy the code

3, The table is used for manipulating the Excel

1: reads the specified single row, and the data is stored in the list

Data =df.iloc[0]. Values# 0 print(" read_excel('test_pandas. \n{0}".format(data))Copy the code

The output

Reads the specified row: [1 'normal landing' 'https://www.lixinchuxing.com/url1.html' '{" uname ":" uname1 ", "PWD" : "pwd1"}']Copy the code

2: read the specified multiple rows, the data will be stored in the nested list:

Print (" read_excel('test_pandas. XLSX ') data=df.iloc[[1,2]]. \n{0}".format(data))Copy the code

The output

Read data from the specified row: [[2 'query vehicles'' https://www.lixinchuxing.com/url2.html ' '{" uname ":" uname2 ", "PWD" : "pwd2"}'] [3 'for more details 'https://www.lixinchuxing.com/url3.html' '{"uname":"uname3","pwd":"pwd3"}']]Copy the code

3: read the specified column of the specified row:

Print (" read_excel('test_pandas. XLSX ') data=df.iloc[0,1] print(" read_excel('test_pandas.Copy the code

The output

Read the specified row: normal loginCopy the code

4: read the specified multi-row, multi-column value:

4.1

Iloc is the column index by position, using [[row number], [column number]]

Df =pd.read_excel('test_pandas. XLSX ') data=df.iloc[[0,1],[2,3]] \n{0}".format(data))Copy the code
4.2

Loc is indexed by column name, using [[row number], [column name]]

Read_excel ('test_pandas. XLSX ') data=df.loc[[0,1],["title","url"]]. \n{0}".format(data))Copy the code

The output is also:

[['https://www.lixinchuxing.com/url1.html'
  '{"uname":"uname1","pwd":"pwd1"}']
 ['https://www.lixinchuxing.com/url2.html'
  '{"uname":"uname2","pwd":"pwd2"}']]

Copy the code

5: Gets the specified column of all rows

Format (data) : df=pd.read_excel('test_pandas. XLSX ') data=df.iloc[:,[1,2].Copy the code

6: Gets the line number and prints it

Df =pd.read_excel('test_pandas. XLSX ') print(' df.index. Values ')Copy the code

7: Get the column name and print it

Df =pd.read_excel('test_pandas. XLSX ') print(" column title ",df.columns. Values)Copy the code

Get the value of the specified column:

Print (" print value \n",df['data']. Values) The output value [' {" uname ":" uname1 ", "PWD" : "pwd1"} ' '{" uname ":" uname2 ", "PWD" : "pwd2"}' '{" uname ":" uname3 ", "PWD" : "pwd3"}' '{"uname":"uname4","pwd":"pwd4"}']Copy the code

9: Get the form in the file

Reader = pd.ExcelFile('test_pandas. XLSX ') sheet_names = reader.sheet_names print(sheet_names) # 'score']Copy the code

4. Pandas processes Excel data into a dictionary

In daily interface automation, the data format we need is not two-dimensional array, but table head as key, each row of data as a list of nested dictionaries of value, each row of data as a test case, expressed by a dictionary, and multiple rows as a list. The following methods can convert excel data into the data format we need.

Import pandas as pd df=pd.read_excel('test_pandas. XLSX ') test_data=[] # Df.index. values:# retrieve the index of the row number and iterate over it: To_dict = to_dict; to_dict = to_dict; to_dict = to_dict; [[row number], [column name]] row_data=df.loc[I,keys].to_dict() \n{0}".format(test_data))Copy the code

The output

Finally access to the data is: [{" case_id ": 1, the 'title' : 'normal landing', 'url' : 'https://www.lixinchuxing.com/url1.html' and 'data' : '{" uname ":" uname1 ", "PWD" : "pwd1"}'}, {" case_id ": 2, 'title' : 'query vehicles',' url ': 'https://www.lixinchuxing.com/url2.html', 'data': '{"uname":"uname2","pwd":"pwd2"}'}, {'case_id': 3, 'title': 'for more details',' url ':' https://www.lixinchuxing.com/url3.html 'and' data ':' {" uname ":" uname3 ", "PWD" : "pwd3"} '}, {" case_id ": 4, 'title' : 'modify configuration', 'url' : 'https://www.lixinchuxing.com/url4.html' and 'data' : '{Copy the code