melt columns and add 20 minutes to each row in date column

Question

I'm trying to take this dataframe(with 1 row in this example):

id    Date                    value_now    value+20min    value+60min    value+80min
0     2015-01-11 00:00:01        12             15            18             22

and to transform it to this:

id    Date                    Value
0     2015-01-11 00:00:01      12     
0     2015-01-11 00:20:01      15     
0     2015-01-11 00:40:01      18     
0     2015-01-11 01:00:01      22      

as you can see I need to change the value in respond to the columns and create rows, I understood I can do it using melt, but I'm having hard time doing it. Please help me with that..... Thank you!

Answer 1

you can melt the dataframe then use the variable column and split on + then use the right side of the split and convert to timedelta and add them back to date:

final = df.melt(['id','Date'])
final['Date'] += pd.to_timedelta(final['variable'].str.split('+').str[1].fillna('0min'))
print(final.drop('variable',1))

---

   id                Date  value
0   0 2015-01-11 00:00:01     12
1   0 2015-01-11 00:20:01     15
2   0 2015-01-11 00:40:01     18
3   0 2015-01-11 01:20:01     22

Another way proposed by @YOBEN_S where you can find the numeric in the variable column and convert to timedelta and add with the Date with df.assign:

final1 = (df.melt(['id','Date']).assign(Date=lambda x : 
        x['Date']+pd.to_timedelta(x['variable'].str.findall(r'\d+')
        .str[0].fillna(0).astype(float),unit='min')))

Answer 2

Here's one approach:

out = df.melt(id_vars=['id', 'Date'])

minutes = pd.to_numeric(out.variable.str.rsplit('+',1).str[-1]
                                    .str.rstrip('min'), 
                        errors='coerce')

out['Date'] = pd.to_datetime(out.Date)

out['Date'] = out.Date + pd.to_timedelta(minutes.fillna(0), unit='m')

---

print(out.drop('variable',1))

id                Date               value
0  2015-01-11 2020-02-14 00:00:01     12
1  2015-01-11 2020-02-14 00:20:01     15
2  2015-01-11 2020-02-14 00:40:01     18
3  2015-01-11 2020-02-14 01:20:01     22