pandas读写excel文件

pandas读写excel文件

pandas是一个十分强大的数据处理工具，最近需要处理数据并输入到excel,简单列举它的几个用法：
1.按行写入excel:

import os
import pandas as pd

cur_dir = os.path.dirname(__file__)
# c创建一个Dataframe数据类型，并写入表头
res = pd.Dataframe(columns=("k1", "k2", "k3"))
#添加两行数据
res.loc[1] = ["11", "22", "33"]
res.loc[2] = ["111", "222", "333"]
# 写入excel文件
res.to_excel(os.path.join(cur_dir, "test.xlsx"))
#读取excel内容
df =pd.read_excel(os.path.join(cur_dir, "test.xlsx"))
print(df.head())

输出为：

   Unnamed: 0   k1   k2   k3
0           1   11   22   33
1           2  111  222  333

2.按列写入：

df2 =pd.Dataframe({"data2": ['aa', 'bb', 'cc', 'dd', 'ee'],"data3":['ss', 'ff', 'gg', 'hh', 'kk']})
df2.to_excel(os.path.join(cur_dir, "test1.xlsx"), sheet_name="sheet1",startcol=0, index=False)

to_excel每次运行会覆盖上一次生成的文件
输出为：

3.交换两列

import os
cur_dir = os.path.dirname(__file__)
import pandas as pd

df2 =pd.Dataframe({"data2": ['aa', 'bb', 'cc', 'dd', 'ee'],"data3":['ss', 'ff', 'gg', 'hh', 'kk']})
df2.to_excel(os.path.join(cur_dir, "test1.xlsx"), sheet_name="sheet1",startcol=0, index=False)

dfnew = df2[['data3', 'data2']]
print(dfnew)
#两种方法均可
dfnew2 =pd.Dataframe(df2, columns=['data3', 'data2'])
print(dfnew2)

交换后的内容为：

  data3 data2
0    ss    aa
1    ff    bb
2    gg    cc
3    hh    dd
4    kk    ee
  data3 data2
0    ss    aa
1    ff    bb
2    gg    cc
3    hh    dd
4    kk    ee

4.把某一行转换为list打印

df2 =pd.Dataframe({"data2": ['aa', 'bb', 'cc', 'dd', 'ee'],"data3":['ss', 'ff', 'gg', 'hh', 'kk']})
df2.to_excel(os.path.join(cur_dir, "test1.xlsx"), sheet_name="sheet1",startcol=0, index=False)

print(df2)
print("*"*10)
print(df2.loc[1])
print("*"*10)
print(df2.iloc[1].tolist())
print("*"*10)
tmp = df2.iloc[1].to_string()
print(tmp, type(tmp))

输出为：

  data2 data3
0    aa    ss
1    bb    ff
2    cc    gg
3    dd    hh
4    ee    kk
**********
data2    bb
data3    ff
Name: 1, dtype: object
**********
['bb', 'ff']
**********
data2    bb
data3    ff 

5.获取行索引列索引

df2 =pd.Dataframe({"data2": ['aa', 'bb', 'cc', 'dd', 'ee'], "data3": ['ss', 'ff', 'gg', 'hh', 'kk']})
df2.to_excel(os.path.join(cur_dir, "test1.xlsx"), sheet_name="sheet1", startcol=0, index=False)

print(df2.index)
print(df2.columns)
for x in df2.columns:
    print(x)

pandas更多功能还需要继续学习~~