Pandas adding Time column to Date index
13,984
You can first convert column Time
to_timedelta
, then add to index
, drop
column Time
and if necessary set index name
:
df.Time = pd.to_timedelta(df.Time + ':00', unit='h')
df.index = df.index + df.Time
df = df.drop('Time', axis=1)
df.index.name = 'Date'
print (df)
Value
Date
2004-05-01 00:15:00 3.58507
2004-05-02 00:30:00 3.84625
If column Time
is datetime.time
for me works cast to string
first (if necessary add :00
):
df.Time = pd.to_timedelta(df.Time.astype(str), unit='h')
df.index = df.index + df.Time
df = df.drop('Time', axis=1)
df.index.name = 'Date'
print (df)
Value
Date
2004-05-01 00:15:00 3.58507
2004-05-02 00:30:00 3.84625
Related videos on Youtube
Author by
alexbk66
Updated on January 08, 2020Comments
-
alexbk66 over 4 years
I have a dataframe, Date index type is Timestamp, Time column is datetime.Time:
Time Value Date 2004-05-01 0:15 3.58507 2004-05-02 0:30 3.84625 ...
How do I convert it to:
Value Date 2004-05-01 0:15 3.74618 2004-05-01 0:30 3.58507 2004-05-01 0:45 3.30998
I wrote a code which does work, but it's not very pythonic:
ind = frame.index.get_level_values(0).tolist() tms = frame['Time'] new_ind = [] for i in range(0, len(ind)): tm = tms[i] val = ind[i] + timedelta(hours=tm.hour, minutes=tm.minute, seconds=tm.second) new_ind.append(val) frame.index = new_ind del frame['Time']
-
alexbk66 over 7 yearsYou are quick @jezrael! Problem is that Pandas converts Time strings to
datetime.Time
, so how do I convert it to timedelta? Or disable auto converting? BTW, it's an excel file, so I use pd.read_excel() -
alexbk66 over 7 yearsNormally I use
parse_dates = {'Date': ['Date','Time']}
inpd.read_excel()
to combine two columns automatically, but in my datafile some dates are missing, which confuses Pandas index. So I have to do it manually, unfortunately. I might ask a separate question -
jezrael over 7 yearsSome dates are missing? Then need
NaT
? Or replace missing values in dates by some value like2004-01-01
? Or need remove rows with missing dates? -
jezrael over 7 yearsYou can first not set
Date
column as index and usedf.Date = pd.to_datetime(df.Date, errors='coerce')
for replace problematic values toNaT
. Can you change sample your data with missing value in columnDate
and add desired output? -
alexbk66 over 7 yearsI remove the rows with missing Date:
frame = frame.loc[pd.notnull(frame.index)]
-
alexbk66 over 7 yearsYeah, unfortunatelly that's what I have to do:
# Remove records with empty index (date)
frame = frame.loc[pd.notnull(frame.index)]
frame.is_copy = False # Disable SettingWithCopyWarning
# Now add values from 'Time' column to Index (Date)
frame.Time = pd.to_timedelta(frame.Time.astype(str), unit='h')
frame.index = frame.index + frame.Time
frame = frame.drop('Time', axis=1)
-
jezrael over 7 yearsYou can use
frame= frame[pd.notnull(frame.index)]
insteadframe = frame.loc[pd.notnull(frame.index)]
, I think loc is not necessary.