CODEWITHSUNDEEP
pythonpandaspython-datetime
Graven
Graven

Reputation: 5

Pandas: Add column with date

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

Upvotes: 0

Views: 214

Answers (2)

rhug123
rhug123

Reputation: 8768

Try this:

from pandas.tseries.offsets import MonthEnd

df['Date'] = df.apply(lambda x: pd.to_datetime('1/1/' + str(x['Year'])) + MonthEnd(x['SAP Period']),axis=1)

Another option:

s = pd.to_datetime(df['SAP Period'] + '/1/' + df['Year'],errors='coerce') + pd.tseries.offsets.MonthEnd(0)
s.where(s.notna(),pd.to_datetime('1' + '/1/' + df['Year']))

or

s = pd.to_datetime('1/1/' + df['Year'].astype(str)).where(df['SAP Period'].eq(0),df['Date'] + pd.tseries.offsets.MonthEnd(0))

Upvotes: 0

Umar.H
Umar.H

Reputation: 23099

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

Upvotes: 1

Related Questions