Pandas read and modify excel operation strategy in Python (code example)

The content of this article is about the Pandas read and modify excel operation guide (code example) in Python. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Environment: python 3.6.8

Let’s take a certain Miser number as an example:

>>> pd.read_excel('1.xlsx', sheet_name='Sheet2')
     名字   等级 属性1   属性2  天赋
0  四九幻曦  100  自然  None  21
1  圣甲狂战  100  战斗  None   0
2  时空界皇  100   光    次元  27

We use the pd.read_excel() function here to read excel. Let’s take a look at the API of read_excel(). Here we only intercept Some commonly used parameters:

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

io: Obviously, it is the path name string of the excel file

(if there is Chinese, it is python2 Laotie needs to use decode() to decode into unicode string)
For example:

>>> pd.read_excel('例子'.decode('utf-8))

sheet_name: Return the specified sheet
If sheet_name is specified as None, the entire sheet will be returned
If multiple sheets need to be returned, sheet_name## can be returned #Specify as a list, for example ['sheet1', 'sheet2']You can specify the selected

sheet based on the name string or index of sheet

>>> # 如:
>>> pd.read_excel('1.xlsx', sheet_name=0)
>>> pd.read_excel('1.xlsx', sheet_name='Sheet1')
>>> # 返回的是相同的 DataFrame

header: Specify the header of the data table, the default value is 0, that is, the first row will be used as the header

usecols: Read the specified column, you can also use the name or index value

>>> # 如:
>>> pd.read_excel('1.xlsx', sheet_name=1, usecols=['等级', '属性1'])
>>> pd.read_excel('1.xlsx', sheet_name=1, usecols=[1,2])
>>> # 返回的是相同的 DataFrame

Until one day Tiger reaches a level, you can change it like this. Of course, you can use

.iloc or .loc objects

>>> # 读取文件
>>> data = pd.read_excel("1.xlsx", sheet_name="Sheet1")

>>> # 找到 等级 这一列，再在这一列中进行比较
>>> data['等级'][data['名字'] == '泰格尔'] += 1
>>> print(data)

LOOK! He's upgraded! !

>>> data
     名字   等级 属性1   属性2  天赋
0  艾欧里娅  100  自然     冰  29
1   泰格尔   81   电    战斗  16
2  布鲁克克  100   水  None  28

Now we save it

data.to_excel('1.xlsx', sheet_name='Sheet1', index=False, header=True)

index: The default is

True, whether to add row index, just go to the picture above!

Left is

False, right is True

header: Default is

True, whether Add a column mark and picture it!
The left one is False, the right one is True

and

io, sheet_name parameter usage is the same as function pd. read_excel()

What if we capture a few more animals or add a few more attributes? Reference is given here:

New column data:

data['column name'] = [value 1, value 2, ...]

>>> data['特性'] = ['瞬杀', 'None', '炎火']
>>> data
     名字   等级 属性1   属性2  天赋    特性
0  艾欧里娅  100  自然     冰  29    瞬杀
1   泰格尔   80   电    战斗  16  None
2  布鲁克克  100   水  None  28    炎火

New Row data, the num of the row here is the id value automatically added to the row in excel

data.loc[num of the row] = [value 1, value 2, ...], (note the difference with
.iloc difference)

>>> data.loc[3] = ['小火猴', 1, '火', 'None', 31, 'None']
>>> data
     名字   等级 属性1   属性2  天赋    特性
0  艾欧里娅  100  自然     冰  29    瞬杀
1   泰格尔   80   电    战斗  16  None
2  布鲁克克  100   水  None  28    炎火
3   小火猴    1   火  None  31  None

After adding a row or a column, how to delete a row or a column? You can use

.drop()function

>>> # 删除列, 需要指定axis为1，当删除行时，axis为0
>>> data = data.drop('属性1', axis=1) # 删除`属性1`列
>>> data
     名字   等级   属性2  天赋    特性
0  艾欧里娅  100     冰  29    瞬杀
1   泰格尔   80    战斗  16  None
2  布鲁克克  100  None  28    炎火
3   小火猴    1  None  31  None

>>> # 删除第3,4行，这里下表以0开始，并且标题行不算在类, axis用法同上
>>> data = data.drop([2, 3], axis=0)
>>> data
     名字   等级 属性2  天赋    特性
0  艾欧里娅  100   冰  29    瞬杀
1   泰格尔   80  战斗  16  None

>>> # 保存
>>> data.to_excel('2.xlsx', sheet_name='Sheet1', index=False, header=True)

The above is the detailed content of Pandas read and modify excel operation strategy in Python (code example). For more information, please follow other related articles on the PHP Chinese website!