03.填充

跳过空白行+填充日月

# 填充
import pandas as pd
# 跳过空白行
books = pd.read_excel('books1.xlsx', skiprows=3, usecols='C:F')
print(books['ID'])
print(type(['ID']))

books['ID'].at[0] = 100
print(books['ID'])

import pandas as pd
books = pd.read_excel('books1.xlsx', skiprows=3, usecols='C:F', index_col=None, dtype={'ID':str})
for i in books.index:
    books['ID'].at[i]= i + 1
print(books['ID'])

填充添加天 月 与 年

# 填充天
import pandas as pd
from datetime import date,timedelta

books = pd.read_excel('books1.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'
    # timedelta 最多加到天
    books['Date'].at[i] = start + timedelta(days = i)
print(books)

#填充年
import pandas as pd
from datetime import date,timedelta

books = pd.read_excel('books1.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] = date(start.year + i, start.month, start.day)
print(books)

# 累积加月,较为复杂
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('books1.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)
#print(books)
books.set_index('ID', inplace=True)
books.to_excel('books11.xlsx')
print('Done!')