Python自动化办公--Pandas玩转Excel（全30集）

前两节：

import pandas as pd

file = r'D:\desktop\K-RPA\work\数字广东\Excel\巡检结果Excel\专区内链\used\主题集成20220623163524.xls'
print(file)

# 创建Excel文件并写入
# pf = pd.DataFrame({'id': [1,2,3], 'Name': ['dsf','sdf','ret']})
# pf = pf.set_index('id')
# pf.to_excel(file)
#
# print(pf)


'''
    读取Excel
    header:指定表头,参数=None：不设表头
    index_col:指定列索引
'''

data = pd.read_excel(file,header=0, index_col=0)
# print(f"行列数：{data.shape}")
# print(f"列：{data.columns}")
# print(f"默认前五行：{data.head()}")
# print(f"默认后五行：{data.tail()}")

# 设置表头
# data.columns = ('id','name','erj','sd','fg','tyr','ret','ds')
# 指定行索引
# data.set_index('id', inplace=True)
# data.to_excel(r'D:\desktop\K-RPA\work\数字广东\Excel\巡检结果Excel\专区内链\used\test.xls')

# print(data)

# 索引相同时
# s = pd.Series([1, 2, 3], index=[1, 2, 3], name='A')
# s2 = pd.Series([10, 20, 30], index=[1, 2, 3], name='B')
# s3 = pd.Series([100, 200, 300], index=[1, 2, 3], name='C')
#
# df = pd.DataFrame({s.name: s, s2.name: s2, s3.name: s3})
# print(df)
# df = pd.DataFrame([s, s2, s3])
# print(df)

# 索引不同时
# s = pd.Series([1, 2, 3], index=[1, 2, 3], name='A')
# s2 = pd.Series([10, 20, 30], index=[1, 2, 3], name='B')
# s3 = pd.Series([100, 200, 300], index=[2, 3, 4], name='C')
#
# df = pd.DataFrame({s.name: s, s2.name: s2, s3.name: s3})
# print(df)
# df = pd.DataFrame([s, s2, s3])
# print(df)

'''
    Excel填充
    dtype：类型转换
'''
file = r'D:\desktop\K-RPA\work\数字广东\Excel\巡检结果Excel\专区内链\used\python s.xlsx'
print(file)
books = pd.read_excel(file, skiprows=6, usecols='C:G', index_col=None, dtype={"序号": str, 'title': str, 'time': str})
start = date(2022,6,28)
for i in books.index:
    # 序号填充
    books["序号"].at[i] = i + 1
    # title填充
    books["title"].at[i] = '偶数填充' if i % 2 == 0 else '奇数填充'
    # time填充
    books["time"].at[i] = start
print(books)

'''
    Excel填充
    dtype：类型转换
'''

# 月份加1
def add_month(d, md):
    yd = md // 12
    m = d.month + md % 12
    if m != 12:
        yd += m // 12
        m = m % 12
    return date(d.year + yd, m, d.day)


file = r'D:\desktop\K-RPA\work\数字广东\Excel\巡检结果Excel\专区内链\used\python s.xlsx'
print(file)
books = pd.read_excel(file, skiprows=6, usecols='C:G', index_col=None, dtype={"序号": str, 'title': str, 'time': str})
start = date(2022,6,28)

for i in books.index:
    # 序号填充
    # books["序号"].at[i] = i + 1
    books.at[i, "序号"] = i + 1
    # title填充
    # books["title"].at[i] = '偶数填充' if i % 2 == 0 else '奇数填充'
    books.at[i, "title"] = '偶数填充' if i % 2 == 0 else '奇数填充'
    # time填充:天数加1
    # books["time"].at[i] = start + timedelta(days=i)
    # 年日加1
    # books["time"].at[i] = date(start.year + i, start, start.day + 1)
    # 月份加1
    # books["time"].at[i] = add_month(start,i)
    books.at[i, "time"] = add_month(start, i)
print(books)

# 写入Excel
# books.set_index("序号", inplace=True)
# books.to_excel(file)