CODEWITHSUNDEEP
pythonpandascsv
iamgm
iamgm

Reputation: 41

Pandas: read_csv with date and time columns as one datetime column

I have csv file with "Date" and "Time" columns.

        Date      Time    Asset  Qty     Price Operation           Order   Fee
0  09.08.2020  10:26:11  Si-6.20    1  68675.00       Buy  26010327752252  1.06
1  09.08.2020  10:28:34  BR-7.20    2     40.80      Sell  26010327909139  2.48
2  09.08.2020  10:31:10  BR-7.20    2     40.68      Sell  26010328155020  2.48
3  09.08.2020  13:01:42  Si-6.20    4  68945.00      Sell  26010337903445  4.24
4  09.08.2020  13:01:48  BR-7.20    1     40.04       Buy  26010337907162  1.24

What I am trying to do is to convert Date, Time columns in one DateTime column.

            DateTIme    Asset  Qty     Price Operation           Order   Fee
0 2020-09-08 10:26:11  Si-6.20    1  68675.00       Buy  26010327752252  1.06
1 2020-09-08 10:28:34  BR-7.20    2     40.80      Sell  26010327909139  2.48
2 2020-09-08 10:31:10  BR-7.20    2     40.68      Sell  26010328155020  2.48
3 2020-09-08 13:01:42  Si-6.20    4  68945.00      Sell  26010337903445  4.24
4 2020-09-08 13:01:48  BR-7.20    1     40.04       Buy  26010337907162  1.24

Here this the code that I used

    df = pd.read_csv('table.csv', sep=';', dtype=dtypes)
    dt = pd.to_datetime(df['Date'] + ' ' + df['Time'])
    df.drop(['Date','Time'], axis=1, inplace=True)
    df.insert(0, 'DateTime', dt)

Is there a more elegant way to do this? I mean convert date and time columns in one datetime column when read csv file.

Upvotes: 1

Views: 964

Answers (2)

Umar.H
Umar.H

Reputation: 23099

Since you're not sure what order your columns come in we can just use a simple assign after you read your csv, then drop and rename.

import pandas as pd
df = pd.read_csv(file)
df = df.assign(Date=pd.to_datetime(df["Date"] + " " + df["Time"])).drop("Time", 1).rename(
                                                              columns={"Date": "DateTime"})

print(df)
             DateTime    Asset  Qty     Price Operation           Order   Fee
0 2020-09-08 10:26:11  Si-6.20    1  68675.00       Buy  26010327752252  1.06
1 2020-09-08 10:28:34  BR-7.20    2     40.80      Sell  26010327909139  2.48
2 2020-09-08 10:31:10  BR-7.20    2     40.68      Sell  26010328155020  2.48
3 2020-09-08 13:01:42  Si-6.20    4  68945.00      Sell  26010337903445  4.24
4 2020-09-08 13:01:48  BR-7.20    1     40.04       Buy  26010337907162  1.24

Upvotes: 0

wfgeo
wfgeo

Reputation: 3098

You could use a apply+lambda combo which is very popular (and typically quite fast) in pandas

I also used an f-string which I find more compact and readable but are only available in Python 3.6+

df = pd.read_csv('table.csv', sep=';', dtype=dtypes)
df["DateTime"] = df.apply(lambda row: pd.to_datetime(f'{row["Date"]} {row["Time"]}'), axis="columns")
df.drop(['Date','Time'], axis=1, inplace=True)

And if you want to get extra fancy you could chain them:

df = pd.read_csv('table.csv', sep=';', dtype=dtypes)
df["DateTime"] = df.apply(lambda row: pd.to_datetime(f'{row["Date"]} {row["Time"]}'), axis="columns")\
                   .drop(['Date','Time'], axis=1)

Upvotes: 2

Related Questions