Updating values within python column based on date

Question

I have a dataset where I would like to replace and update values within a column when a data condition is met.

Data

ID  Date    State   File
AA  Q122    NY  closed
AA  Q222    NY  closed
AA  Q322    NY  closed
AA  Q422    NY  closed
AA  Q123    NY  closed
AA  Q223    NY  closed
AA  Q323    NY  closed
AA  Q423    NY  closed
BB  Q124    CA  closed
BB  Q224    CA  open
BB  Q324    CA  open
BB  Q424    CA  open
BB  Q125    CA  open
BB  Q225    CA  open
BB  Q325    CA  open
BB  Q425    CA  open

Desired

ID  Date    State   File
AA  Q122    NY  yes
AA  Q222    NY  yes
AA  Q322    NY  yes
AA  Q422    NY  yes
AA  Q123    NY  yes
AA  Q223    NY  closed
AA  Q323    NY  closed
AA  Q423    NY  closed
BB  Q124    CA  closed
BB  Q224    CA  open
BB  Q324    CA  open
BB  Q424    CA  open
BB  Q125    CA  open
BB  Q225    CA  open
BB  Q325    CA  open
BB  Q425    CA  open

Doing

df.loc[(df['ID'].eq('AA')) & (df['Date'] <= 'Q123'), 'File'] = 'yes'


**However this is not capturing the quarter order and assigns the string 'yes' to rows that do not meet the condition of less than or = to Q123.**

Still researching, any suggestion is appreciated- Perhaps I need to convert quarters to datetime longdate and base the condition off of this column.

What does Q123 stand for? Is that some sort of data abbreviation. Yes, you can convert to pd.Period or pd.Timestamps. — Scott Boston
– Scott Boston, Commented Jul 14, 2022 at 17:28
Yes I am creating a long date field, convert to datetime and reference this @ScottBoston — Lynn
– Lynn, Commented Jul 14, 2022 at 17:32

Naveed · Accepted Answer · 2022-07-14 17:39:50Z

2

here is one way to do it

to apply the condition, Date need to be in Year and Quarter, and then applying np.where condition. However, you need to put the condition in the same format i.e. YYQ (year, quarter)

df['File']= np.where( ( df['ID'] =='AA') &  
                      ((df['Date'].str[-2:] + df['Date'].str[1:2]).astype(int) <=231)
                     , 'yes'
                     , df['File'] )
df

    ID  Date    State   File
0   AA  Q122    NY      yes
1   AA  Q222    NY      yes
2   AA  Q322    NY      yes
3   AA  Q422    NY      yes
4   AA  Q123    NY      yes
5   AA  Q223    NY      closed
6   AA  Q323    NY      closed
7   AA  Q423    NY      closed
8   BB  Q124    CA      closed
9   BB  Q224    CA      open
10  BB  Q324    CA      open
11  BB  Q424    CA      open
12  BB  Q125    CA      open
13  BB  Q225    CA      open
14  BB  Q325    CA      open
15  BB  Q425    CA      open

edited Jul 14, 2022 at 17:39

answered Jul 14, 2022 at 17:34

Naveed

11.7k2 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Updating values within python column based on date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related