擅长:python、mysql、java
<p>pandas<code>melt</code>与pyjanitor的<code>complete</code>的组合可以帮助转换数据:</p>
<pre><code> # pip install pyjanitor
import pandas as pd
import janitor
(df.melt(['email', 'room'], value_name = 'Start_Date')
.reindex([3,1,2,0])
# complete is a wrapper around pandas functions
# to expose missing values ... in this case it exposes the
# missing dates for each group in by
.complete([{'Start_Date':lambda df: pd.date_range(df.min(), df.max(),freq='H')}],
by=['email', 'room'])
.assign(End_Date = lambda df: df.Start_Date.add(pd.Timedelta('1 hour')))
.query('variable != "End Date"').drop(columns='variable'))
email room Start_Date End_Date
0 abc@corp.com L11M2 2021-02-01 08:00:00 2021-02-01 09:00:00
1 abc@corp.com L11M2 2021-02-01 09:00:00 2021-02-01 10:00:00
2 abc@corp.com L11M2 2021-02-01 10:00:00 2021-02-01 11:00:00
4 xyz@corp.com L12M4 2021-02-01 08:00:00 2021-02-01 09:00:00
5 xyz@corp.com L12M4 2021-02-01 09:00:00 2021-02-01 10:00:00
</code></pre>