我在 Pandas Dataframe 中有一列,名为“Excel_Date”。此列数据如下所示:
Excel_Date
Before Q1 2018
Before Q1 2014
Before Q4 2018
42457
42457
42520
nan
nan
列的 dtype('O')。
我不知道如何以正确的方式获得它。
期望的输出
Excel_Date
Before Q1 2018 #Or even better: the first month and day of Q1 (1/1/2018)
Before Q1 2014 #Or even better: the first month and day of Q1 (1/1/2014)
Before Q4 2018 #Or even better: the first month and day of Q4 (10/1/2018)
3/28/2016
3/28/2016
5/30/2017
nan
nan
示例中的“#Or even better:...”会很棒!但我能理解这可能有点困难。
我尝试了什么?
我试着把问题分成更小的子问题:
1. Create a column, with only the numeric values
> df['Excel_Date2'] = df['Excel_Date'].str.extract("(\d*\.?\d+)", expand=True
2. After that, I tried to deal with the numbers. But I failed.
>import datetime as dt
>import pandas as pd
>pd.TimedeltaIndex(df['Excel_Date2'], unit='d') + dt.datetime(1899, 12, 30)
非常非常感谢!
请您参考如下方法:
首先仅获取数值,然后使用您的解决方案:
s = pd.to_numeric(df['Excel_Date'], errors='coerce')
df['new'] = pd.to_timedelta(s,unit='d').add(pd.datetime(1899,12,30)).fillna(df['Excel_Date'])
print (df)
Excel_Date new
0 Before Q1 2018 Before Q1 2018
1 Before Q1 2014 Before Q1 2014
2 Before Q4 2018 Before Q4 2018
3 42457 2016-03-28 00:00:00
4 42457 2016-03-28 00:00:00
5 42520 2016-05-30 00:00:00
6 NaN NaN
7 NaN NaN
更好的是导出四分位数,转换为日期时间,最后用四分位数的日期时间替换缺失值:
df1 = df['Excel_Date'].str.extract("(Q[1-4])\s+([1-2]\d{3})", expand=True)
s1 = pd.to_datetime(df1[1] + df1[0])
s2 = pd.to_numeric(df['Excel_Date'], errors='coerce')
df['new'] = pd.to_timedelta(s2, unit='d').add(pd.datetime(1899, 12, 30)).fillna(s1)
print (df)
Excel_Date new
0 Before Q1 2018 2018-01-01
1 Before Q1 2014 2014-01-01
2 Before Q4 2018 2018-10-01
3 42457 2016-03-28
4 42457 2016-03-28
5 42520 2016-05-30
6 NaN NaT
7 NaN NaT
