筛选出季度最后一个可用日期的行 pandas

huangapple go评论86阅读模式
英文:

Filter out rows of the last available date of the quarter pandas

问题

我已经做了一些工作,但它一直报错:

  1. ValueError: cannot insert Date, already exists

我该如何解决这个问题?

英文:

I have a dataset that looks like this

  1. data = {'Date': ['2022-01-01', '2022-02-15', '2022-03-10', '2022-04-20', '2022-05-05', '2022-06-30', '2022-07-15', '2022-08-10', '2022-09-25', '2022-09-25'],
  2. 'Value': [10, 15, 20, 25, 30, 35, 40, 45, 50, 50]}

What I want to do is get the last available date of each quarter so that the result would look like

  1. Date Value
  2. 0 2022-03-10 20
  3. 1 2022-06-30 35
  4. 2 2022-09-25 50
  5. 3 2022-09-25 50

What I have done is something like this

  1. import pandas as pd
  2. # Create sample DataFrame
  3. data = {'Date': ['2022-01-01', '2022-02-15', '2022-03-10', '2022-04-20', '2022-05-05', '2022-06-30', '2022-07-15', '2022-08-10', '2022-09-25', '2022-09-25'],
  4. 'Value': [10, 15, 20, 25, 30, 35, 40, 45, 50, 50]}
  5. df = pd.DataFrame(data)
  6. # Convert 'Date' column to datetime format
  7. df['Date'] = pd.to_datetime(df['Date'])
  8. # Create a new DataFrame with last dates of quarters
  9. last_dates = df.resample('Q', on='Date').last().reset_index()
  10. # Merge the original DataFrame with the last_dates DataFrame
  11. df = pd.merge(df, last_dates, on='Date')
  12. print(df)

But it keeps throwing me the error

  1. ValueError: cannot insert Date, already exists

How can I resolve this issue?

答案1

得分: 0

可能的解决方案:

  1. df['Date'] = pd.to_datetime(df['Date'])
  2. (df.groupby(df['Date'].dt.to_period('Q'))
  3. .agg({'Date': 'last', 'Value': 'last'})
  4. .reset_index(drop=True).merge(df))

输出:

  1. Date Value
  2. 0 2022-03-10 20
  3. 1 2022-06-30 35
  4. 2 2022-09-25 50
  5. 3 2022-09-25 50
英文:

A possible solution:

  1. df['Date'] = pd.to_datetime(df['Date'])
  2. (df.groupby(df['Date'].dt.to_period('Q'))
  3. .agg({'Date': 'last', 'Value': 'last'})
  4. .reset_index(drop=True).merge(df))

Output:

  1. Date Value
  2. 0 2022-03-10 20
  3. 1 2022-06-30 35
  4. 2 2022-09-25 50
  5. 3 2022-09-25 50

huangapple
  • 本文由 发表于 2023年7月7日 00:50:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/76630992.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定