CODEWITHSUNDEEP
pythonpandasdataframe
ProgSky
ProgSky

Reputation: 2620

Python Pandas Update Dataframe Column Types

I am reading an excel into pandas and all column headers are datetime except first one. I want to format the datetime column headers.

This is the test data and code. 

import pandas as pd
import datetime

d = {
         datetime.datetime(2018, 01, 31, 8, 00): [9.1, 8.5, 7.4, 6.5, 5.3],
         datetime.datetime(2018, 02, 28, 9, 00): [9.2, 8.5, 7.2, 6.1, 5.2],
         datetime.datetime(2018, 03, 31, 10, 00): [9.3, 8.5, 7.1, 6.1, 5.1],
         'ID': [1, 2, 3, 4, 5],
    }
df = pd.DataFrame(d, columns= ['ID', datetime.datetime(2018, 01, 31, 8, 00), datetime.datetime(2018, 02, 28, 9, 00), datetime.datetime(2018, 03, 31, 10, 00)])
df.loc[:, df.columns != 'ID'].columns = [x.date() for x in df.loc[:, df.columns != 'ID'].columns]

I want to format datetime header to just date. eg: Update 2018-01-31 08:00:00 to 2018-01-31.

I am use .date() function to achieve that. I think the way I am doing is wrong. Below line is not updating the datetime column header. 

df.loc[:, df.columns != 'ID'].columns = [x.date() for x in df.loc[:, df.columns != 'ID'].columns]

Any ideas or pointer? enter image description here

Thanks.

Upvotes: 3

Views: 160

Answers (3)

Giorgos Myrianthous
Giorgos Myrianthous

Reputation: 39930

Since version 0.15.0 of pandas, you can use the .dt accessor in order to get the date (and ignore the time) which will return datetime.date dtype

df['dates_without_time'] = df['datetime'].dt.date

Upvotes: 1

galaxyan
galaxyan

Reputation: 6141

import pandas as pd
import datetime

d = {
         datetime.datetime(2018, 01, 31, 8, 00): [9.1, 8.5, 7.4, 6.5, 5.3],
         datetime.datetime(2018, 02, 28, 9, 00): [9.2, 8.5, 7.2, 6.1, 5.2],
         datetime.datetime(2018, 03, 31, 10, 00): [9.3, 8.5, 7.1, 6.1, 5.1],
         'ID': [1, 2, 3, 4, 5],
    }

df = pd.DataFrame(zip(*d.values()), columns= map(lambda ele: ele if isinstance(ele, str) else ele.date(), d.keys()))
df



2018-01-31  2018-02-28  2018-03-31  ID
0   9.1   9.2   9.3  1
1   8.5   8.5   8.5  2
2   7.4   7.2   7.1  3
3   6.5   6.1   6.1  4
4   5.3   5.2   5.1  5

Upvotes: 1

Rakesh
Rakesh

Reputation: 82785

import pandas as pd
import datetime

d = {
         datetime.datetime(2018, 01, 31, 8, 00): [9.1, 8.5, 7.4, 6.5, 5.3],
         datetime.datetime(2018, 02, 28, 9, 00): [9.2, 8.5, 7.2, 6.1, 5.2],
         datetime.datetime(2018, 03, 31, 10, 00): [9.3, 8.5, 7.1, 6.1, 5.1],
         'ID': [1, 2, 3, 4, 5],
    }
df = pd.DataFrame(d, columns= ['ID', datetime.datetime(2018, 01, 31, 8, 00), datetime.datetime(2018, 02, 28, 9, 00), datetime.datetime(2018, 03, 31, 10, 00)])
df.columns = [x.date() if isinstance(x, datetime.datetime) else x for x in df.columns]
print(df.columns)

Output:

   ID  2018-01-31  2018-02-28  2018-03-31
0   1         9.1         9.2         9.3
1   2         8.5         8.5         8.5
2   3         7.4         7.2         7.1
3   4         6.5         6.1         6.1
4   5         5.3         5.2         5.1

Upvotes: 1

Related Questions