Python to convert different date formats in a column

Question

I am trying to convert a column which has different date formats.

For example:

month
2018-01-01             float64
2018-02-01             float64
2018-03-01             float64
2018-03-01 00:00:00    float64
2018-04-01 01:00:00    float64
2018-05-01 01:00:00    float64
2018-06-01 01:00:00    float64
2018-07-01 01:00:00    float64

I want to convert everything in the column to just month and year. For example I would like Jan-18, Feb-18, Mar-18, etc.

I have tried using this code to first convert my column to datetime:

df['month'] =  pd.to_datetime(df['month'], format='%Y-%m-%d')

But it returns a float64:

Out

month
2018-01-01 00:00:00    float64
2018-02-01 00:00:00    float64
2018-03-01 00:00:00    float64
2018-04-01 01:00:00    float64
2018-05-01 01:00:00    float64
2018-06-01 01:00:00    float64
2018-07-01 01:00:00    float64

In my output to CSV the month format has been changed to 01/05/2016 00:00:00. Can you please help me covert to just month and year e.g. Aug-18.

Thank you

jpp · Accepted Answer · 2018-08-28 15:07:15Z

1

I assume you have a Pandas dataframe. In this case, you can use pd.Series.dt.to_period:

s = pd.Series(['2018-01-01', '2018-02-01', '2018-03-01',
               '2018-03-01 00:00:00', '2018-04-01 01:00:00'])

res = pd.to_datetime(s).dt.to_period('M')

print(res)

0   2018-01
1   2018-02
2   2018-03
3   2018-03
4   2018-04
dtype: object

As you can see, this results in a series of dtype object, which is generally inefficient. A better idea is to set the day to the last of the month and maintain a datetime series internally represented by integers.

answered Aug 28, 2018 at 15:07

jpp

166k37 gold badges301 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

thor Over a year ago

Yes I have a Pandas dataframe. Unfortunately, your suggestion does not work for me. I get ValueError: to assemble mappings requires at least that [year, month, day] be specified: [day,year] is missing

jpp Over a year ago

A bit strange. I copied input data from your question. Can't help any further, I'm afraid, without accurate input data.

mad_ Over a year ago

I believe input data is not of float type here as mentioned by OP in the question.

Collectives™ on Stack Overflow

Python to convert different date formats in a column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related