主页 / 问题 / 875020
Accepted
GralL
Asked:2020-08-29 23:58:16 +0000 UTC2020-08-29 23:58:16 +0000 UTC

如何正确解析日期？

  • 772

我有一个这样的excel文件：

import pandas as pd

data = pd.read_excel(open('C:/home/Date.xlsx','rb'), sheetname='sheet')
data.head(10)

结论：

Year FY   Quarter FY  Month FY  
FY1998    FQ4         FM10_Jan
FY1998    FQ4         FM11_Feb
FY1998    FQ4         FM12_Mar
FY1999    FQ1         FM01_Apr  
FY1999    FQ1         FM02_May

如何将日期更改为 01/01/1998？

python

3 个回答

  1. 源数据框：

    In [149]: df
Out[149]:
  Year FY Quarter FY  Month FY
0  FY1998        FQ4  FM10_Jan
1  FY1998        FQ4  FM11_Feb
2  FY1998        FQ4  FM12_Mar
3  FY1999        FQ1  FM01_Apr
4  FY1999        FQ1  FM02_May

    矢量化（不使用慢循环）解决方案：

    In [150]: df['date'] = (pd.to_datetime(
     ...:                   df['Month FY'].str.split('_').str[1]
     ...:                   + df['Year FY'].str.replace('FY',''),
     ...:                   format='%b%Y')
     ...:               )
     ...:

In [151]: df
Out[151]:
  Year FY Quarter FY  Month FY       date
0  FY1998        FQ4  FM10_Jan 1998-01-01
1  FY1998        FQ4  FM11_Feb 1998-02-01
2  FY1998        FQ4  FM12_Mar 1998-03-01
3  FY1999        FQ1  FM01_Apr 1999-04-01
4  FY1999        FQ1  FM02_May 1999-05-01

In [152]: df.dtypes
Out[152]:
Year FY               object
Quarter FY            object
Month FY              object
date          datetime64[ns]    # <--- NOTE!
dtype: object
    • 2

  2. 我找到了一个解决方案：

    def pars(date_frame): 
   date_frame['Year FY'] = date_frame['Year FY'].apply(lambda row:      row.replace("FY" , ""))
date_frame['Month FY'] = date_frame['Month FY'].apply(lambda row: row.replace("FM" , "").replace("_","").replace('10Jan','01').replace('11Feb','02').replace('12Mar','03').replace('01Apr','04').replace('02May','05').replace('03Jun','06').replace('04Jul','07').replace('05Aug','08').replace('06Sep','09').replace('07Oct','10').replace('08Nov','11').replace('09Dec','12'))

def fun (row):
    str_date = '{}.{}.{}'.format('01', row['Month FY'], row['Year FY'])
    date = pd.datetime.strptime(str_date, '%d.%m.%Y')
    row['DateTime'] = date
    return row

date_frame['DateTime'] = np.NaN 
df_with_date = date_frame.apply(fun, axis = 1)
return df_with_date

    输出结果是一个附加列，其中日期以所需格式写入

    • 1
  3. Best Answer

    .replace("_","").replace('10Jan','01').replace('11Feb', ...这样做更方便

    import re

def replace(text, s_r_list):
    replacements = {re.escape(s): r for (s, r) in s_r_list}
    return re.compile("|".join(replacements)).sub(lambda m: replacements[re.escape(m.group(0))], text)


sr = ("FM" , ""), ("_",""), ('10Jan','01'), ('11Feb','02'), ('12Mar','03'),  # ...
s = '_asdFMqwe10Jan+12Mar_'
s = replace(s, sr)  # asdqwe01+03
    • 1

相关问题