By Ben


2019-06-12 07:20:40 8 Comments

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?

5 comments

@gehbiszumeis 2019-06-12 09:06:48

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

@Andrew Li 2019-06-12 07:55:54

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?

@Asnim P Ansari 2019-06-12 07:25:06

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

@Ben 2019-06-12 07:28:07

Thank you! The last bracket is too much. But I receive the error: "KeyError: 'TIMESTAMP'. What does that mean?

@vlemaistre 2019-06-12 07:26:48

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

@gmds 2019-06-12 07:26:37

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)]

Related Questions

Sponsored Content

11 Answered Questions

[SOLVED] How to remove a key from a Python dictionary?

34 Answered Questions

[SOLVED] How to get the current time in Python

  • 2009-01-06 04:54:23
  • user46646
  • 2946736 View
  • 2535 Score
  • 34 Answer
  • Tags:   python datetime time

19 Answered Questions

[SOLVED] How to remove an element from a list by index?

  • 2009-03-09 18:16:11
  • Joan Venge
  • 2302870 View
  • 1302 Score
  • 19 Answer
  • Tags:   python list

12 Answered Questions

[SOLVED] How can I make a time delay in Python?

27 Answered Questions

[SOLVED] Remove specific characters from a string in Python

5 Answered Questions

[SOLVED] Creating an empty Pandas DataFrame, then filling it?

14 Answered Questions

[SOLVED] "Large data" work flows using pandas

26 Answered Questions

[SOLVED] How to remove items from a list while iterating?

  • 2009-07-30 15:36:42
  • lfaraone
  • 431191 View
  • 879 Score
  • 26 Answer
  • Tags:   python iteration

26 Answered Questions

[SOLVED] How can I remove a trailing newline?

  • 2008-11-08 18:25:24
  • RidingThisToTheTop
  • 1589341 View
  • 1551 Score
  • 26 Answer
  • Tags:   python newline trailing

1 Answered Questions

Sponsored Content