I have a DataFrame with epoch timestamp and I want to create a new column with a formatted date string.
index timestamp
0 1456407930
1 1456407945
2 1456407961
3 1456407977
4 1456407992
5 1456408008
6 1456408024
7 1456408040
8 1456408055
9 1456408071
10 1456408087
11 1456408102
First, I successfully convert the timestamp to datetime format using
data['date_num'] = mdate.epoch2num(data['timestamp'])
But I didn't find a function to get a new column with a string formatted date (such as “%Y-%m-%d”)
.
I would appreciate any ideas? Patricio
Because our Epoch time is specified in milliseconds, we may convert it to seconds. To convert milliseconds to seconds, first, divide the millisecond count by 1000. Later, we use DATEADD() to add the number of seconds since the epoch, which is January 1, 1970 and cast the result to retrieve the date since the epoch.
You can take an epoch time divided by 86400 (seconds in a day) floored and add 719163 (the days up to the year 1970) to pass to it. Awesome, this is as manual as it gets.
Specify unit='s'
with pd.to_datetime
. Then use pd.Series.dt.strftime
.
df['date'] = pd.to_datetime(df['timestamp'], unit='s')\
.dt.strftime('%Y-%m-%d')
print(df)
index timestamp date
0 0 1456407930 2016-02-25
1 1 1456407945 2016-02-25
2 2 1456407961 2016-02-25
3 3 1456407977 2016-02-25
4 4 1456407992 2016-02-25
5 5 1456408008 2016-02-25
6 6 1456408024 2016-02-25
7 7 1456408040 2016-02-25
8 8 1456408055 2016-02-25
9 9 1456408071 2016-02-25
10 10 1456408087 2016-02-25
11 11 1456408102 2016-02-25
You can use map
method by passing a lambda
expression as argument.
df['new_column'] = df['timestamp'].map(lambda val: datetime.datetime.fromtimestamp(val).strftime('%Y-%m-%d'))
Output
timestamp new_column
0 1456407930 2016-02-25
1 1456407945 2016-02-25
-------------------------
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With