python 十进制年份与datetime转换

1 covert datetime dates to decimal years

import datetime
def year_fraction(date):
    start = datetime.date(date.year, 1, 1).toordinal()
    year_length = datetime.date(date.year+1, 1, 1).toordinal() - start
    return date.year + float(date.toordinal() - start) / year_length

2 covert decimal years to datetime dates

from datetime import datetime, timedelta

start = 2010.043836
year = int(start)
rem = start - year

base = datetime(year, 1, 1)
result = base + timedelta(seconds=(base.replace(year=base.year + 1) - base).total_seconds() * rem)
print(result)
#2010-01-17 00:00:12.096002

如果想批量处理一些数据，则可以利用dataframe 列遍历：df.apply(function)。默认以列为单位，可以设置axis=1，对行进行处理。

#实现将特定列十进制数据转换成python中datetime 格式
from datetime import datetime, timedelta
import pandas as pd

df = pd.read_excel('.\test.xlsx')
print(df)

def deci2datetime (start):
    year = int(start)
    rem = start - year
    
    base = datetime(year,1,1)
    result = base + timedelta(seconds=(base.replace(year=base.year + 1) - base).total_seconds() * rem)
    
    return result

df1 = df['decimal'].apply(deci2datetime)
print(df1)
df1.to_excel('.\datetime.xlsx')

参考资料：https://stackoverflow.com/questions/36213383/pandas-dataframe-how-to-apply-function-to-a-specific-column

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.apply.html

**更新：**202207192020/1/13 23:59:56转换成2020-01-13

df['date'] = pd.to_datetime(df['date'],format='%Y/%m/%d')
df['date_new'] = df['date'].dt.strftime('%Y-%m-%d')

补充：
利用Excel十进制年份转年月日格式：
EXCEL:
=DATE(INT(A1),1,1) + (A1-INT(A1)) * (DATE(INT(A1)+1,1,1) - DATE(INT(A1),1,1))

python代码：十进制年转年月日格式（考虑闰年、平年的情况）

#decyear2date
df = pd.read_csv('1.csv')
# 判断是否为闰年的函数
def is_leap_year(year):
    return year % 4 == 0 and (year % 100 != 0 or year % 400 == 0)
# 根据'decyear'的整数部分判断是否为闰年，并计算对应的日期
def calculate_date(row):
    year = int(row['time'])
    if is_leap_year(year):
        return datetime(year, 1, 1) + pd.DateOffset(days=int((row['time'] % 1) * 366))
    else:
        return datetime(year, 1, 1) + pd.DateOffset(days=int((row['time'] % 1) * 365))
df['date'] = df.apply(calculate_date, axis=1)
df.to_csv('1.csv',index=False)
print('done!')

经测试，两种方法计算的结果一致。