CODEWITHSUNDEEP
pythondatetimepandas
David Hancock
David Hancock

Reputation: 1071

Using pandas to_datetime with timestamps

I'm trying to covert my these timestamps into a %Y-%m-%d %H:%M format. Here's a sample of the data: 

0    1450753200
1    1450756800
2    1450760400
3    1450764000
4    1450767600
Name: ohlcv_start_date, dtype: int64

Could someone explain what type of timestamp these are and what code I need to convert them properly, because when I use:

pd.to_datetime(df[TS], unit='ms').dt.strftime('%Y-%m-%d %H:%M')

It converts the time into:

0        1970-01-01 00:00
1        1970-01-01 00:00
2        1970-01-01 00:00
3        1970-01-01 00:00
4        1970-01-01 00:00

Which isn't correct

EDIT: Thanks Mr Chum.

What i'm actually trying to do is merge the values of different assets by timestamp. Each asset starts and finishes at slightly different times and Upon analysis it seems there is gaps in the data:

 market_trading_pair  next_future_timestep_return ohlcv_start_date  \
0        Poloniex_ETH_BTC                 3.013303e-03    2015-12-22 03      
1        Poloniex_ETH_BTC                 3.171481e-03    2015-12-22 05   
2        Poloniex_ETH_BTC                -1.381575e-03    2015-12-22 07   
3        Poloniex_ETH_BTC                -4.327704e-03    2015-12-22 08

The best I can think to solve this problem is to create a new data frame and fill in the rows with time stamps incrementing by one hours, from here i can simple merge in the asset data. Any idea how to generate ascending timstamps ?

Upvotes: 8

Views: 13742

Answers (1)

EdChum
EdChum

Reputation: 394041

Pass unit='s' to get the values as it's epoch time:

In [106]:
pd.to_datetime(df['timestamp'], unit='s')
Out[106]:
index
0   2015-12-22 03:00:00
1   2015-12-22 04:00:00
2   2015-12-22 05:00:00
3   2015-12-22 06:00:00
4   2015-12-22 07:00:00
Name: timestamp, dtype: datetime64[ns]

You can convert to string if you desire:

In [107]:

pd.to_datetime(df['timestamp'], unit='s').dt.strftime('%Y-%m-%d %H:%M')
Out[107]:
index
0    2015-12-22 03:00
1    2015-12-22 04:00
2    2015-12-22 05:00
3    2015-12-22 06:00
4    2015-12-22 07:00
Name: timestamp, dtype: object

Upvotes: 17

Related Questions