Home >Backend Development >Python Tutorial >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))
header: Specify the header of the data table, the default value is 0, that is, the first row will be used as the headerusecols: Read the specified column, you can also use the name or index valuesheet_name
: Return the specified sheet
Ifsheet_name
is specified asNone
, 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 selectedsheet
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
Until one day Tiger reaches a level, you can change it like this. Of course, you can use>>> # 如: >>> pd.read_excel('1.xlsx', sheet_name=1, usecols=['等级', '属性1']) >>> pd.read_excel('1.xlsx', sheet_name=1, usecols=[1,2]) >>> # 返回的是相同的 DataFrame
.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 28Now we save it
data.to_excel('1.xlsx', sheet_name='Sheet1', index=False, header=True)index: The default is
TrueLeft is, whether to add row index, just go to the picture above!
Falseheader: Default is, right is
True
Trueand, whether Add a column mark and picture it!
The left one is False, the right one is
True
io, sheet_nameWhat if we capture a few more animals or add a few more attributes? Reference is given here: New column data:parameter usage is the same as function
pd. read_excel()
data['column name'] = [value 1, value 2, ...]New Row data, the num of the row here is the id value automatically added to the row in excel>>> data['特性'] = ['瞬杀', 'None', '炎火'] >>> data 名字 等级 属性1 属性2 天赋 特性 0 艾欧里娅 100 自然 冰 29 瞬杀 1 泰格尔 80 电 战斗 16 None 2 布鲁克克 100 水 None 28 炎火
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 NoneAfter 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!