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
Share:
13,984

Related videos on Youtube

alexbk66
Author by

alexbk66

Updated on May 15, 2022

Comments

  • alexbk66
    alexbk66 13 days

    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
    alexbk66 over 5 years
    You 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
    alexbk66 over 5 years
    Normally I use parse_dates = {'Date': ['Date','Time']} in pd.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
    jezrael over 5 years
    Some dates are missing? Then need NaT ? Or replace missing values in dates by some value like 2004-01-01? Or need remove rows with missing dates?
  • jezrael
    jezrael over 5 years
    You can first not set Date column as index and use df.Date = pd.to_datetime(df.Date, errors='coerce') for replace problematic values to NaT. Can you change sample your data with missing value in column Date and add desired output?
  • alexbk66
    alexbk66 over 5 years
    I remove the rows with missing Date: frame = frame.loc[pd.notnull(frame.index)]
  • alexbk66
    alexbk66 over 5 years
    Yeah, 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
    jezrael over 5 years
    You can use frame= frame[pd.notnull(frame.index)] instead frame = frame.loc[pd.notnull(frame.index)], I think loc is not necessary.