remove specific characters from time stamp

user_6761328 0
Viewed 2290

I have a rather odd time format and the data frame df with the column TIMESTAMP:

2016-10-25T09:34:52.051713+01:00
2016-10-25T09:46:14.051620+01:00
2016-10-25T09:51:16.052435+01:00
....

but I need to use the data as time information. In the first place, I would like to get rid of the last 13 characters such that it looks like

2016-10-25T09:34:52
2016-10-25T09:46:14
2016-10-25T09:51:16
....

To do so, I tried

df['TIMESTAMP'] = df.apply(lambda x: x['TIMESTAMP'][:-13], axis = 1)

from Remove ends of string entries in pandas DataFrame column

but I receive the error:

TypeError: string indices must be integers

which I do not understand. I'm just fresh in python but I don't see much I've done wrong?

python-datetime jupyter-notebook datetime-format python
5 Answers

The strict answer to your question is to use the str accessor, which can take a slice, just like a normal string, and which will apply that slice to every value in the Series:

data = ['2016-10-25T09:34:52.051713+01:00',
        '2016-10-25T09:46:14.051620+01:00',
        '2016-10-25T09:51:16.052435+01:00']

s = pd.Series(data)

print(s.str[:-13])

Output:

0    2016-10-25T09:34:52
1    2016-10-25T09:46:14
2    2016-10-25T09:51:16

However, I think what you want is actually pd.to_datetime, which will (within reason) infer the format of your data and convert it into datetime objects:

print(pd.to_datetime(s))

Output:

0   2016-10-25 09:34:52.051713+01:00
1   2016-10-25 09:46:14.051620+01:00
2   2016-10-25 09:51:16.052435+01:00
dtype: datetime64[ns, pytz.FixedOffset(60)]
user_110154270

A simple way to do this is to use a list comprehension : 

df = pd.DataFrame({'TIMESTAMP' : ["2016-10-25T09:34:52.051713+01:00", "2016-10-25T09:46:14.051620+01:00"]})

 TIMESTAMP
0  2016-10-25T09:34:52.051713+01:00
1  2016-10-25T09:46:14.051620+01:00

df['TIMESTAMP'] = [x[:-13] for x in df['TIMESTAMP']]

Output : 

TIMESTAMP
0  2016-10-25T09:34:52
1  2016-10-25T09:46:14
user_114920240

I recommend to always use datetime package if you deal with any kind of dates. As of Python3.7, functions fromisoformat can digest your ISO format directly, while strftime can convert to any thinkable time format. (For Python3.6 or lower see this answer).

In your case with timestamps written in df['TIMESTAMP']:

from datetime import datetime as dt
df['TIMESTAMP'].apply(lambda x: dt.strftime(dt.fromisoformat(x), '%Y-%m-%dT%H:%M:%S'))

will do the trick and give your desired output:

             TIMESTAMP
0  2016-10-25T09:34:52
1  2016-10-25T09:46:14
2  2016-10-25T09:51:16
user_103093420

Please replace the transformation statement with below code

df['TIMESTAMP'] = df['TIMESTAMP'].apply(lambda x: x[-4:])

This will apply the transformation function to the entire column named time stamp and save it back with the same column name

user_56874160 
df = pd.DataFrame(
    data={
        'TIMESTAMP': [
            '2016-10-25T09:34:52.051713+01:00',
            '2016-10-25T09:46:14.051620+01:00',
            '2016-10-25T09:51:16.052435+01:00'
        ]
    }
)
df['TIMESTAMP'] = df['TIMESTAMP'].apply(lambda x: x[:19])

BTW, are you sure you don't need to keep the information of timezone?

user_84030960
Related