Issue
I'm very new to Python and Pandas, hoping to get some help using a for loop to add a column for Date. I have tried the code below, and a few variations of this code, but always receive errors. I added a column for 'Day' just so I could return a date; but then I delete that column afterwards.
For rows with SAP Period = 0, I'd like to return 1/1/YYYY. For all other rows, I'd like to return the last day of the month.
for y in df_BW['SAP Period']:
if y == 0:
df_BW['Period'] = pd.to_datetime(df_BW[['Day', 'SAP Period2', 'Year']].astype(str).apply(' '.join, 1), format='%d %m %Y')
else:
df_BW['Period'] = pd.to_datetime(df_BW[['Day', 'SAP Period', 'Year']].astype(str).apply(' '.join, 1), format='%d %m %Y') + MonthEnd()
Year | SAP Period | Date |
---|---|---|
2020 | 0 | 1/1/2020 |
2020 | 1 | 1/31/2020 |
2020 | 2 | 2/29/2020 |
2020 | 0 | 1/1/2020 |
2020 | 2 | 2/29/2020 |
2020 | 2 | 2/29/2020 |
2020 | 3 | 3/31/2020 |
2020 | 12 | 12/31/2020 |
2021 | 0 | 1/1/2021 |
2021 | 1 | 1/31/2021 |
2021 | 3 | 3/31/2021 |
2021 | 0 | 1/1/2021 |
2021 | 2 | 2/28/2021 |
2021 | 3 | 3/31/2021 |
Solution
using np.where
with MonthEnd
from pandas.teseries.offset.MonthEnd
from pandas.tseries.offsets import MonthEnd
df['date'] = pd.to_datetime(df['Year'].astype(str) +
df['SAP Period'].replace(0,1).astype(str).str.zfill(2) +
'01',format='%Y%m%d')
df['date'] = np.where(df['SAP Period'].ne(0), df['date'] + MonthEnd(1), df['date'])
Year SAP Period Date date
0 2020 0 1/1/2020 2020-01-01
1 2020 1 1/31/2020 2020-01-31
2 2020 2 2/29/2020 2020-02-29
3 2020 0 1/1/2020 2020-01-01
4 2020 2 2/29/2020 2020-02-29
5 2020 2 2/29/2020 2020-02-29
6 2020 3 3/31/2020 2020-03-31
7 2020 12 12/31/2020 2020-12-31
8 2021 0 1/1/2021 2021-01-01
9 2021 1 1/31/2021 2021-01-31
10 2021 3 3/31/2021 2021-03-31
11 2021 0 1/1/2021 2021-01-01
12 2021 2 2/28/2021 2021-02-28
13 2021 3 3/31/2021 2021-03-31
Answered By - Umar.H
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.