CODEWITHSUNDEEP
pythonpandas
Tom J Muthirenthi
Tom J Muthirenthi

Reputation: 3340

Calculate Time in pandas by extracting

I have a set of data

Time1  Time2
XY40M  XY35M
XY5H   XY45M
XY30M  XY20M
XY1H   XY2H
XY1H30M   XY2H

I have to calculate the Total time in minutes

Time1+Time2
75
345
50
180
210

How can i derive this?

Upvotes: 0

Views: 66

Answers (1)

jezrael
jezrael

Reputation: 862701

Use str.extract with numpy.where:

a = df['Time1'].str.extract('(\d+[MH])', expand=False)
a1 = a.str[:-1].astype(int)
b = df['Time2'].str.extract('(\d+[MH])', expand=False)
b1 = b.str[:-1].astype(int)

df['Time'] = np.where(a.str[-1] == 'H', a1 * 60, a1) + np.where(b.str[-1] == 'H', b1 * 60, b1)

Another solution:

a = df['Time1'].str.extract('(\d+)([MH])', expand=True)
a1 = a[0].astype(int)
b = df['Time2'].str.extract('(\d+)([MH])', expand=True)
b1 = b[0].astype(int)

df['Time'] = np.where(a[1] == 'H', a1 * 60, a1) + np.where(b[1] == 'H', b1 * 60, b1)

print (df)
   Time1  Time2  Time
0  XY40M  XY35M    75
1   XY5H  XY45M   345
2  XY30M  XY20M    50
3   XY1H   XY2H   180

EDIT:

a = df['Time1'].str.extract('(\d+)([MH])(\d*)([M]*)', expand=True)
a1 = a[[0,2]].replace('', 0).astype(int)
b = df['Time2'].str.extract('(\d+)([MH])(\d*)([M]*)', expand=True)
b1 = b[[0,2]].replace('', 0).astype(int)

df['Time'] = np.where(a[1] == 'H', a1[0] * 60, a1[0]) + a1[2] + \
             np.where(b[1] == 'H', b1[0] * 60, b1[0]) + b1[2]

print (df)
     Time1  Time2  Time
0    XY40M  XY35M    75
1     XY5H  XY45M   345
2    XY30M  XY20M    50
3     XY1H   XY2H   180
4  XY1H30M   XY2H   210

Upvotes: 2

Related Questions