CODEWITHSUNDEEP
pythonstringpandasdatetimedataframe
Joe
Joe

Reputation: 13091

Python Panda dataframe - whole row from String to Date

I have a csv file:

Date,2018-07-31,2018-08-31,2018-09-30
Value,12,34,56

and then load it into DF with:

df = pd.read_csv('test.csv')

Whole first row of dates is taken as a String. How to change values in a first row ('date') to TimeStamp object so that can use function like: df.columns[0].month.

How to change the whole first row with for example to_datetime() option?

Upvotes: 1

Views: 60

Answers (2)

jpp
jpp

Reputation: 164623

Series are always columnwise

So you can't set a row to a Pandas datetime series and use Pandas datetime methods on it seamlessly. You can concoct workarounds to convert the row to a datetime series each time you use it. But that's inefficient and doesn't leverage vectorisation.

You can have either your row index or column index as datetime.

datetime index: transpose your dataframe

The cleanest solution is to transpose your dataframe and use a datetime index:

from io import StringIO

x = StringIO("""Date,2018-07-31,2018-08-31,2018-09-30
Value,12,23,45""")

df = pd.read_csv(x)
df = df.set_index('Date').T
df.index = pd.to_datetime(df.index)

print(df)

Date        Value
2018-07-31     12
2018-08-31     23
2018-09-30     45

print(df.index.month)

Int64Index([7, 8, 9], dtype='int64')

datetime columns

If you need columns as datetime, you can use set_index and then pd.to_datetime:

df = df.set_index('Date').rename_axis('')
df.columns = pd.to_datetime(df.columns)

print(df)

       2018-07-31  2018-08-31  2018-09-30

Value          12          23          45

print(df.columns.month)

Int64Index([7, 8, 9], dtype='int64')

Upvotes: 1

nimrodz
nimrodz

Reputation: 1594

you could try apply T on your DF and then use pd.to_datetime

df = pd.read_csv('test.csv').T
df.reset_index(inplace=True)
df = df.rename(columns={'index':'Date',0:'Value'}).tail(df.shape[0]-1)
df.Date = pd.to_datetime(df.Date)

df
         Date Value
1  31/07/2018    12
2  31/08/2018    23
3  30/09/2018    45

OR

df.T
                1           2           3
Date   31/07/2018  31/08/2018  30/09/2018
Value          12          23          45

Upvotes: 0

Related Questions