Tanakorn Taweepoka
Tanakorn Taweepoka

Reputation: 197

Concatenation of date & time columns and assignment to index

I have this data

               Date   Time     Open     High      Low    Close  Volume
0      2013.07.09   7:00  101.056  101.151  101.016  101.130    1822
1      2013.07.09   8:00  101.130  101.257  101.128  101.226    2286
2      2013.07.09   9:00  101.226  101.299  101.175  101.180    2685
3      2013.07.09  10:00  101.178  101.188  101.019  101.154    2980

And I combine Date and Time to be this

                 Datetime  
0     2013-07-09 07:00:00  
1     2013-07-09 08:00:00 

So i want to replace DateTime into Date and Time in my data and also it's index. The result that I expect looks like this

Datetime     Time     Open     High      Low    Close  Volume

Here is my code

import pandas as pd

df = pd.read_csv(r"C:\Users\Administrator\Desktop\Csv for PYthon\USDJPY60.csv")
df['Datetime'] = pd.to_datetime(df['Date'].apply(str)+' '+df['Time'])
print(df)

Upvotes: 0

Views: 52

Answers (1)

jezrael
jezrael

Reputation: 862681

You can use parameters index_col and parse_dates in read_csv only:

df = pd.read_csv(r"C:\Users\Administrator\Desktop\Csv for PYthon\USDJPY60.csv"), 
                   index_col=['Date_Time'],
                   parse_dates=[['Date','Time']])

print (df)
                        Open     High      Low    Close  Volume
Date_Time                                                      
2013-07-09 07:00:00  101.056  101.151  101.016  101.130    1822
2013-07-09 08:00:00  101.130  101.257  101.128  101.226    2286
2013-07-09 09:00:00  101.226  101.299  101.175  101.180    2685
2013-07-09 10:00:00  101.178  101.188  101.019  101.154    2980

But if need Time column:

df = pd.read_csv(r"C:\Users\Administrator\Desktop\Csv for PYthon\USDJPY60.csv"), 
                   index_col=['Date'],
                   parse_dates=['Date'])

df.index = df.index + pd.to_timedelta(df['Time'] + ':00')
print (df)
                      Time     Open     High      Low    Close  Volume
2013-07-09 07:00:00   7:00  101.056  101.151  101.016  101.130    1822
2013-07-09 08:00:00   8:00  101.130  101.257  101.128  101.226    2286
2013-07-09 09:00:00   9:00  101.226  101.299  101.175  101.180    2685
2013-07-09 10:00:00  10:00  101.178  101.188  101.019  101.154    2980

And for index only (both columns are not changed):

df.index = pd.to_datetime(df['Date']+' '+df['Time'])
print (df)
                           Date   Time     Open     High      Low    Close  \
2013-07-09 07:00:00  2013.07.09   7:00  101.056  101.151  101.016  101.130   
2013-07-09 08:00:00  2013.07.09   8:00  101.130  101.257  101.128  101.226   
2013-07-09 09:00:00  2013.07.09   9:00  101.226  101.299  101.175  101.180   
2013-07-09 10:00:00  2013.07.09  10:00  101.178  101.188  101.019  101.154   

                     Volume  
2013-07-09 07:00:00    1822  
2013-07-09 08:00:00    2286  
2013-07-09 09:00:00    2685  
2013-07-09 10:00:00    2980  

Upvotes: 1

Related Questions