Example of python using pandas to read and write excel files

introductory

Now locally create an excel sheet, and two sheets with the following data:

sheet1：

sheet2:

Read excel file

pandas.read_excel(io, sheet_name=0, header=0, names=None, index_col=None, usecols=None)

io: excel file path.

sheet_name: Returns the specified sheet.

header: the header of the table, the default value is 0. You can also specify multiple lines. When header takes the value of None, data prints the most values, 0 compared to None will be less than one line, 1 compared to 0 will be less than one line. That is to say, set the header for how much, then that line before the data will be missing. header can also be set to a range of values such as header = [0, 1] that the first two rows of multiple indexes.

usecols: reads the specified column.

skiprows: Skip specific lines.

import pandas
 
a = pandas.read_excel("",sheet_name=0)#sheet_name can use subscripts, the name of the sheet
print(a) #Print all
print() # Printing information other than the first line
print([0]) # Print the value of the first line
print(data['Title column'].values) # Print the value of a specific column
 
# Read different sheets of the same file
data= pandas.read_excel("", ['Sheet1', 'Sheet2'])
print(data)# Print all elements of sheet1 and sheet2
print(('Sheet1')['result'][0]) # Print the first element of the result column of the sheet1 table.
 
 
#sheet_name = None returns data from all sheets
data = pandas.read_excel("", sheet_name=None)
print(data)
in the end:
"""
{'Sheet1':    case_id account      pswd  hope result
0      1.0     qwe  123456.0  Login Successful  cheng
1      NaN     NaN       NaN   NaN    bai, 'Sheet2':    1  2  3  4  5
0  a  b  c  d  e}
"""
 
#sheet_name can choose the name, subscript combination to extract multiple table data
data = pandas.read_excel("", sheet_name=['Sheet1',1])
print(data)
in the end:
"""
{'Sheet1':    case_id account      pswd  hope result
0      1.0     qwe  123456.0  Login Successful  cheng
1      NaN     NaN       NaN   NaN    bai, 1:    1  2  3  4  5
0  a  b  c  d  e}
"""
 
# Query the data in the specified column
data = pandas.read_excel('', sheet_name='Sheet1', usecols=['result',])
print(data)
in the end：
"""
  result
0  cheng
1    bai
"""
data = pandas.read_excel('', sheet_name='Sheet1', usecols=[0])
print(data)
in the end：
"""
   case_id
0      1.0
1      NaN
"""
data = pandas.read_excel('', sheet_name='Sheet1', usecols=[0, 1])
print(data)
in the end：
"""
   case_id account
0      1.0     qwe
1      NaN     NaN
"""

ExcelFile: To make it easier to read multiple tables in the same file

import pandas
 
 
# Read multiple sheets of a file at the same time, only need to read the memory once, better performance
data = ("")
sheets = pandas.read_excel(data)#sheet_name is not written, the default is to check the first sheet of data
sheets = pandas.read_excel(data, sheet_name="Sheet2")#View the data in the specified sheet
print(sheets)
 
# It could be written that way too
with ("") as xlsx:
    s1 = pandas.read_excel(xlsx, sheet_name="Sheet1")
    s2 = pandas.read_excel(xlsx, sheet_name="Sheet2")
print(s1)
print("-----------------------")
print(s2)
 
in the end:
"""
   case_id account pswd hope result
0 1.0 qwe 123456.0 login success cheng
1 NaN NaN NaN NaN bai
-----------------------
   1 2 3 4 5
0 a b c d e
"""

"""
index_col: the index of the corresponding column, you can set the range such as [0, 1] to set multiple indexes
na_values: specify the string to be displayed as NAN.
"""
with ('') as xls:
    data['Sheet1'] = pandas.read_excel(xls, 'Sheet1', index_col=None,
                                       na_values=['NA'])
    data['Sheet2'] = pandas.read_excel(xls, 'Sheet2', index_col=1)
 
    print(data)
    print("-------------------------------")
    print(data['Sheet1'])
    print("--------------------------------")
    print(data['Sheet2'])
 
in the end：
"""
{'Sheet1':    case_id account      pswd  hope result
0      1.0     qwe  123456.0  Login Successful  cheng
1      NaN     NaN       NaN   NaN    bai, 'Sheet2':    1  3  4  5
2            
b  a  c  d  e}
-------------------------------
   case_id account      pswd  hope result
0      1.0     qwe  123456.0  Login Successful  cheng
1      NaN     NaN       NaN   NaN    bai
--------------------------------
   1  3  4  5
2            
b  a  c  d  e
"""

Write to file:

Write data to excel

1. When the file does not exist, it will automatically create the file and write the data;

2. overwrite the data when the file exists.

3. sheet_name defaults to Sheet1 if not written.

4. file writing, remember to close excel.

data = {'Name': ['Zhang San','Li Si'],
        'Score': [100, 100]
       }
a= (data)
a.to_excel('', sheet_name='Sheet1',index=False)# index = FalseIndicates that the index is not written

excel writes to multiple sheets at once:

1. The following code is written in the sheet1, sheet2 two tables.

2. can be added in ExcelWriter mode parameter, the parameter defaults to w, modified to a then, you can have existed in the sheet of excel to add the sheet table.

df1 = ({'Name': ['Zhang San', 'Wang Si'], 'Score': [100, 100]})
df2 = ({'Age': ['18', '19'], 'Gender': ['Male', 'Female']})
 
with ('') as writer:
    df1.to_excel(writer, sheet_name='Sheet1', index=False)
    df2.to_excel(writer, sheet_name='Sheet2', index=False)
 
# Add a new sheet
df3 = ({'Add new table': ['1', '2']})
with ('', mode='a') as writer:
    df3.to_excel(writer, sheet_name='Sheet3', index=False)

summarize

to this article on the use of python pandas read and write excel files to this article, more related python pandas read and write excel content, please search for my previous articles or continue to browse the following related articles I hope you will support me in the future more!