【Python】利用pandas进行.xlsx表格数据的读取和写入

读取：

import pandas as pd

#显示所有列
pd.set_option('display.max_columns', None)
#显示所有行
pd.set_option('display.max_rows', None)

df2 = pd.read_excel(r"C:\Users\xxx\Desktop\data1.xlsx")
df = pd.read_excel(r"C:\Users\xxx\Desktop\bylw\data\数据.xls",sheet_name='Sheet2')#Sheet2表示第2张表

print(df)
print(df2)
print(df['year']]#指定列名为year的列
print(df[0])#第一列
print(df[‘year’][3])#year列第3行

print(data.values[0])#按行读取，第一行

print("总行数：", df.shape[0])  # 总行数
print("总列数：", df.shape[1])  # 总列数

print(df.columns.to_list()) #提取表头，即标题行

h_num = df.shape[0]   # 总行数
h_list = []
for i in range(h_num):

    if pd.isnull(list(df.values[i])[0]):#判断每行第一个数据是否为空值
        pass
    else:
        h_list.append(list(df.values[i]))#将所有不空的数据装入列表

for i in hlist:
    if i[0].istitle()==True:#判断首字母是否大写
        print(i)

写入：

columns = ['x1','x2','x3','x4','x5','x6','x7','x8','x9','x10',
            'x11','x12','x13','x14','x15','x16','x17','x18','x19','x20']

list = [(99.8, 5.202, 21.407, 68.514, 62.655, 40.178, 74.275, 22970, 931, 304, 733, 22.9, 24.7, 6.97, 89.739, 1.13, 48.42, 18.27, 76.983, 38.512)]

#按行写入，行中数据为list中的元组
df = pd.DataFrame(list, columns=columns)
df.to_excel(r"C:\Users\xxx\Desktop\data1.xlsx", 'a',index=True)

 写入例子：

import pandas as pd
list1 =["厉飞雨","韩立","张铁"]
list2 = [14,15,16]
list3 = ["长老","医徒","弟子"]

k=[]
for i,j,z in zip(list1,list2,list3):
    l=[]
    l.append(i)
    l.append(j)
    l.append(z)
    k.append(tuple(l))
        
dw = pd.DataFrame(k,columns = ["姓名","年龄","职业"])
dw.to_excel(r"/home/user/桌面/01.xlsx","a",index=False)

 

结果：