Python Pandas replace all values if index is larger than a date

Question

I am looking for some help on a pandas data frame.

I have a data frame with the following structure

Date(indexed)      Total Clients   Sales Headcount    Total Products
2019-11-01         1005            5                  4
2019-12-01         1033            5                  5
2020-01-01         1045            10                 6
2020-02-01         1124            10                 10
2020-03-01         1199            10                 11

How can I fill in the column total products with 0's if the date is after 2020-01-01?

Expected outcome:

Date(indexed)      Total Clients   Sales Headcount    Total Products
2019-11-01         1005            5                  4
2019-12-01         1033            5                  5
2020-01-01         1045            10                 6
2020-02-01         1124            10                 0
2020-03-01         1199            10                 0

using .loc and assign it back

BENY
– BENY

2020-02-10 22:05:33 +00:00
Commented Feb 10, 2020 at 22:05 — BENY
– BENY, Commented Feb 10, 2020 at 22:05
Have you tried anything, done any research?

AMC
– AMC

2020-02-11 01:40:08 +00:00
Commented Feb 11, 2020 at 1:40 — AMC
– AMC, Commented Feb 11, 2020 at 1:40

Alexander · Accepted Answer · 2020-02-10 22:15:15Z

2

Make sure that your date column contains timestamps.

# Assuming `Date(indexed)` means that this column is the index of the dataframe.
df.index = pd.to_datetime(df.index)

Then use .loc to set all values from and including 2020 to zero.

df.loc['2020':, 'Total Products'] = 0

>>> df
            Total Clients  Sales Headcount  Total Products
Date                                                      
2019-11-01           1005                5               4
2019-12-01           1033                5               5
2020-01-01           1045               10               0
2020-02-01           1124               10               0
2020-03-01           1199               10               0

answered Feb 10, 2020 at 22:15

Alexander

111k32 gold badges212 silver badges208 bronze badges

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

2 Comments

Grant Culp Over a year ago

Thank you! I did mean that is an indexed column. If I wanted to use your logic for everything larger than "2020-01-01" set that column equal to 0. How do I do that?

Alexander Over a year ago

df.loc['2020':, :] = 0

Umar.H · Accepted Answer · 2020-02-10 22:09:38Z

1

using .loc to assign values based on a boolean.

# df['Date(indexed)'] = pd.to_datetime(df['Date(indexed)'])

df.loc[df['Date(indexed)'] > '2020-01-01','Total Products'] = 0

print(df)

  Date(indexed)  Total Clients  Sales Headcount  Total Products
0    2019-11-01           1005                5               4
1    2019-12-01           1033                5               5
2    2020-01-01           1045               10               6
3    2020-02-01           1124               10               0
4    2020-03-01           1199               10               0

answered Feb 10, 2020 at 22:09

Umar.H

23.1k7 gold badges50 silver badges94 bronze badges

Collectives™ on Stack Overflow

Python Pandas replace all values if index is larger than a date

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related