CODEWITHSUNDEEP
pythonpandas
Ashish Akshantal
Ashish Akshantal

Reputation: 31

How to add last day of the month for each month in python

I have my data in the following format:

final.head(5)

Head of the data, displaying sales for each month from May 2015

(Head of the data, displaying sales for each month from May 2015)

I want to add the last day of the month for each record and want an output like this 

transactionDate sale_price_after_promo
05/30/2015  30393.8
06/31/2015  24345.68
07/30/2015  26688.91
08/31/2015  46626.1
09/30/2015  27933.84
10/31/2015  76087.55

I tried this 

pd.Series(pd.DatetimeIndex(start=final.start_time, end=final.end_time, freq='M')).to_frame('transactionDate')

But getting an error 

'DataFrame' object has no attribute 'start_time'

Upvotes: 2

Views: 69

Answers (2)

user16084293
user16084293

Reputation: 9

I am attempting to convert dynamically all date columns to YYYY-MM-DD format using dataframe that come from read_csv. columns are below.

input empno,ename,hiredate,report_date,end_date 1,sreenu,17-Jun-2021,18/06/2021,May-22

output empno,ename,hiredate,report_date,end_date 1,sreenu,2021-06-17,2021-06-18,2022-05-31

rules are

  1. if date is MMM-YY or MM-YYYY(May-22 or 05-2022) (then last day of the month(YYYY-MM-DD format - 2022-05-31)
  2. other than point 1 then it should be YYYY-MM-DD

Now i want create a method/function to identify all date datatype columns in dataframe then convert to YYYY-MM-DD format/user expected format.

Upvotes: 0

jezrael
jezrael

Reputation: 862511

Create PeriodIndex and then convert it to_timestamp:

df = pd.DataFrame({'transactionDate':['2015-05','2015-06','2015-07']})

df['date']  = pd.PeriodIndex(df['transactionDate'], freq='M').to_timestamp(how='end')
print (df)
  transactionDate       date
0         2015-05 2015-05-31
1         2015-06 2015-06-30
2         2015-07 2015-07-31

Upvotes: 2

Related Questions