CODEWITHSUNDEEP
pandasdataframepython-datetime
maninekkalapudi
maninekkalapudi

Reputation: 1118

Convert an epoch timestamp column to first day of the month in epoch

I have a epoch timestamp column as mentioned below. I need to convert this epoch timestamp column to first day of the month an the output should also be in epoch too.

time_stamp
1528974011
1530867602
1530867602
1530867604
1530867602
1528974012
1528974011

Example: first value in the above column 1530867602 corresponds to 14/06/2018 11:00:11 in datetime format. Now, the first day of the month for the same date is 01/06/2018 which I want in an epoch format.

This also can be achieved by following steps as below:

epoch->datetime->first_day_of_the_month->epoch_first_day_of_the_month

but is there any better way to do the same?

Thanks in advance!

Kindly do mention if there are any questions/resources on this

Upvotes: 2

Views: 473

Answers (1)

jezrael
jezrael

Reputation: 862751

Use:

#convert to datetimes
df['time_stamp'] = pd.to_datetime(df['time_stamp'], unit='s')

#first day of month
df = df.resample('MS', on='time_stamp').first()
print (df)
                    time_stamp
time_stamp                    
2018-06-01 2018-06-14 11:00:11
2018-07-01 2018-07-06 09:00:02

print (df['time_stamp'].index.floor('d'))
DatetimeIndex(['2018-06-01', '2018-07-01'], 
               dtype='datetime64[ns]', name='time_stamp', freq=None)

#remove times and convert to epoch
out = (df['time_stamp'].index.floor('d').astype(np.int64) // 10**9)
print (out)
Int64Index([1527811200, 1530403200], dtype='int64', name='time_stamp')

Another solution is convert column to month period, then to first day of month:

df['time_stamp'] = (pd.to_datetime(df['time_stamp'], unit='s')
                      .dt.to_period('M')
                      .dt.to_timestamp())

Then remove duplicates and convert to epoch:

df = df.drop_duplicates('time_stamp').astype(np.int64) // 10**9
print (df)

   time_stamp
0  1527811200
1  1530403200

Upvotes: 1

Related Questions