potpie potpie - 6 months ago 29
Python Question

Python/Pandas -- convert day and hour columns into index of hour

I have a Data Frame that looks like this:

df
Date Hr CO2_resp
0 5/1/02 600 0.000889
1 5/2/02 600 0.000984
2 5/4/02 900 0.000912


How would I go about creating a column
Ind
that represents a number index of hours elapsed since midnight 5/1/02? Such that the column would read

df
Date Hr Ind CO2_resp
0 5/1/02 600 6 0.000889
1 5/2/02 600 30 0.000984
2 5/4/02 800 80 0.000912


Thanks.

Answer

You can use to_datetime with to_timedelta. Then convert timedelta to hours by np.timedelta64(1, 'h') and last if type of output is always int, cast by astype:

#convert column Date to datetime
df['Date'] = pd.to_datetime(df.Date)

df['Ind'] = ((df.Date 
              - pd.to_datetime('2002-05-01') 
              + pd.to_timedelta(df.Hr / 100, unit='h')) / np.timedelta64(1, 'h')).astype(int)
print (df)
        Date   Hr  CO2_resp  ind
0 2002-05-01  600  0.000889    6
1 2002-05-02  600  0.000984   30
2 2002-05-04  900  0.000912   81

If not dividing by 100 column Hr, output is different:

df['Ind'] = ((df.Date 
              - pd.to_datetime('2002-05-01') 
              + pd.to_timedelta(df.Hr,unit='h')) / np.timedelta64(1, 'h')).astype(int)
print (df)
        Date   Hr  CO2_resp  Ind
0 2002-05-01  600  0.000889  600
1 2002-05-02  600  0.000984  624
2 2002-05-04  900  0.000912  972