在执行books['Date'].at[i] = start + timedelta(days=i)时会报错
unsupported type for timedelta days component: numpy.int64
最终修改为books['Date'].at[i] = start + timedelta(days=int(i))可以正常运行
任务5： 填充日期序列
首先对于日期的递增有三种方式，
a. 年递增，b. 月递增， c. 日递增
1. 日递增
books['Date'].at[i] = start + timedelta(days=i)
打印结果：
ID Name InStore Date
0 1 Book_001 Yes 2018-01-01
1 2 Book_002 No 2018-01-02
2 3 Book_003 Yes 2018-01-03
3 4 Book_004 No 2018-01-04
4 5 Book_005 Yes 2018-01-05
5 6 Book_006 No 2018-01-06
6 7 Book_007 Yes 2018-01-07
7 8 Book_008 No 2018-01-08
8 9 Book_009 Yes 2018-01-09
9 10 Book_010 No 2018-01-10
10 11 Book_011 Yes 2018-01-11
11 12 Book_012 No 2018-01-12
12 13 Book_013 Yes 2018-01-13
13 14 Book_014 No 2018-01-14
14 15 Book_015 Yes 2018-01-15
15 16 Book_016 No 2018-01-16
16 17 Book_017 Yes 2018-01-17
17 18 Book_018 No 2018-01-18
18 19 Book_019 Yes 2018-01-19
19 20 Book_020 No 2018-01-20
2. 递增年
books['Date'].at[i] = date(start.year+i,start.month,start.day)
打印结果：
ID Name InStore Date
0 1 Book_001 Yes 2018-01-01
1 2 Book_002 No 2019-01-01
2 3 Book_003 Yes 2020-01-01
3 4 Book_004 No 2021-01-01
4 5 Book_005 Yes 2022-01-01
5 6 Book_006 No 2023-01-01
6 7 Book_007 Yes 2024-01-01
7 8 Book_008 No 2025-01-01
8 9 Book_009 Yes 2026-01-01
9 10 Book_010 No 2027-01-01
10 11 Book_011 Yes 2028-01-01
11 12 Book_012 No 2029-01-01
12 13 Book_013 Yes 2030-01-01
13 14 Book_014 No 2031-01-01
14 15 Book_015 Yes 2032-01-01
15 16 Book_016 No 2033-01-01
16 17 Book_017 Yes 2034-01-01
17 18 Book_018 No 2035-01-01
18 19 Book_019 Yes 2036-01-01
19 20 Book_020 No 2037-01-01
3. 递增月
注：月份没有最多只有12个月，超过12个月则年增加1.
学一个小算法：
f 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)
注： d：日期， md： 增加的月份， yd： 年份增加， m：月份。
递增月代码
books['Date'].at[i] = add_month(start, i)
打印结果
ID Name InStore Date
0 1 Book_001 Yes 2018-01-01
1 2 Book_002 No 2018-02-01
2 3 Book_003 Yes 2018-03-01
3 4 Book_004 No 2018-04-01
4 5 Book_005 Yes 2018-05-01
5 6 Book_006 No 2018-06-01
6 7 Book_007 Yes 2018-07-01
7 8 Book_008 No 2018-08-01
8 9 Book_009 Yes 2018-09-01
9 10 Book_010 No 2018-10-01
10 11 Book_011 Yes 2018-11-01
11 12 Book_012 No 2018-12-01
12 13 Book_013 Yes 2019-01-01
13 14 Book_014 No 2019-02-01
14 15 Book_015 Yes 2019-03-01
15 16 Book_016 No 2019-04-01
16 17 Book_017 Yes 2019-05-01
17 18 Book_018 No 2019-06-01
18 19 Book_019 Yes 2019-07-01
19 20 Book_020 No 2019-08-01
4. 文件输出保存
将编辑好的DataFrame输出保存，注意设定‘ID’为index
import pandas as pd
from datetime import date, timedelta
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)
books = pd.read_excel('C:/Temp/Books.xlsx', skiprows=3, usecols='C:F', index_col=None,
dtype={'ID': str, 'InStore': str, 'Date': str})
start = date(2018, 1, 1)
for i in books.index:
books['ID'].at[i] = i + 1
books['InStore'].at[i] = 'Yes' if i % 2 == 0 else 'No'
books['Date'].at[i] = add_month(start, i)
books.set_index('ID', inplace=True)
books.to_excel('C:/Temp/output.xlsx')
print('done!')
5. 另外一种改值的方法
上面介绍了使用Series的at[]方法找到对应的值进行修改。
我们也可以直接用DataFrame的at[]方法进行修改。
代码修改如下：
import pandas as pd
from datetime import date, timedelta
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)
books = pd.read_excel('C:/Temp/Books.xlsx', skiprows=3, usecols='C:F', index_col=None,
dtype={'ID': str, 'InStore': str, 'Date': str})
start = date(2018, 1, 1)
for i in books.index:
books.at[i, 'ID'] = i + 1
books.at[i, 'InStore'] = 'Yes' if i % 2 == 0 else 'No'
books.at[i, 'Date'] = add_month(start, i)
books.set_index('ID', inplace=True)
books.to_excel('C:/Temp/output.xlsx')
print('done!')
授课教师
Tim老师
课程特色
视频(30)
下载资料(25)
