下决心一不改了
问题描述
这是我之前提出的问题的延伸.
This is an extension of an earlier question i had.
将 Pandas 列从字符串 Quarters 和 Years 数组转换为日期时间列
我有一个这样的数据框，其中日期混乱.
I have a dataframe like this where the dates are jumbled up.
我想将它们转换为日期时间对象.
I want to convert them to datetime objects.
所以 3Q '11 会变成 2011-09-30Q1 '20 将变为 2020-03-31
So 3Q '11 would become 2011-09-30 Q1 '20 would become 2020-03-31
Date Data 3Q '11 11.12 4Q '11 15.43 1Q '12 11.8 2Q '12 17 1Q '13 19.5 2Q '13 14.62 3Q '13 14.1 4Q '13 26 1Q '14 16.4 2Q '14 13.3 3Q '14 12.3 4Q '14 21.4 1Q '15 12.6 2Q '15 11 3Q '15 9.9 4Q '15 16.1 1Q '16 10.3 Q2 '16 10 Q3 '16 9.3 Q4 '16 13.1 Q1 '17 8.9 Q2 '17 11.4 Q3 '17 10.3 Q4 '17 13.2 Q1 '18 9.1 Q2 '18 11.6 Q3 '18 9.7 Q4 '18 12.9 Q1 '19 9.9 Q2 '19 12.3 Q3 '19 11.8 Q4 '19 15.9 Q1 '20 6.9 Q2 '20 12.4 Q3 '20 13.9
如果行全部匹配，我有以下公式来处理不同的数据帧，其中每行包含 Q 后跟数字或数字后跟 Q，
I have the following formula to handle the different dataframes if the rows all match where either every row contains Q followed by a number or a number followed by a Q,
if df['Date'][0].startswith('Q') == True:
df['Date'] = df['Date'].str.replace(" ","").str.split("'")
df['Date'] = (pd.to_datetime("20"+df['Date'].str[::-1].str.join('')) + pd.offsets.QuarterEnd(0))
else:
df['Date'] = df['Date'].str.replace("'","20").str.split(" ")
df['Date'] = pd.to_datetime(df['Date'].str.join('')) + pd.offsets.QuarterEnd(0)
但是，在这种情况下，数据框有两种数据，其中日期在同一帧中同时写为 Q3 或 3Q，我如何在应用其中之一之前对数据进行规范化?
However, in this case, the dataframe has both kinds of data where the dates are written written as both Q3 or 3Q within the same frame, how do i normalise the data before applying one of these?
推荐答案
你可以使用Series.replace 以获得正确的周期顺序，然后应用解决方案转换为日期时间:
You can use Series.replace for correct order of periods and then apply solution for convert to datetimes:
df = pd.DataFrame({'Date': ["3Q '11", "4Q '11", "1Q '12", "2Q '12", "1Q '13",
"Q2 '19", "Q3 '19", "Q4 '19", "Q1 '20"],
'Data': [11.12, 15.43, 11.8, 17.0, 19.5, 12.3, 11.8, 15.9, 6.9]})
print (df)
Date Data
0 3Q '11 11.12
1 4Q '11 15.43
2 1Q '12 11.80
3 2Q '12 17.00
4 1Q '13 19.50
5 Q2 '19 12.30
6 Q3 '19 11.80
7 Q4 '19 15.90
8 Q1 '20 6.90
df['Date'] = df['Date'].replace(r"^(d+)([Q])D*(d+)$", r'20321', regex=True)
df['Date'] = df['Date'].replace(r"^([Q]d+)D*(d+)$", r'2021', regex=True)
print (df)
Date Data
0 2011Q3 11.12
1 2011Q4 15.43
2 2012Q1 11.80
3 2012Q2 17.00
4 2013Q1 19.50
5 2019Q2 12.30
6 2019Q3 11.80
7 2019Q4 15.90
8 2020Q1 6.90
另一个想法是使用索引:
Another idea is use indexing:
m = df['Date'].str.startswith('Q')
df['Date'] = ('20' + df['Date'].str[-2:] + df['Date'].str[:2]
.where(m, df['Date'].str[1] + df['Date'].str[0]))
print (df)
Date Data
0 2011Q3 11.12
1 2011Q4 15.43
2 2012Q1 11.80
3 2012Q2 17.00
4 2013Q1 19.50
5 2019Q2 12.30
6 2019Q3 11.80
7 2019Q4 15.90
8 2020Q1 6.90
