Pandas TimeSeries With duration of event

Question

I've been googling for this for a while and haven't found a proper solution. I have a time series with a couple of million rows that has a rather odd structure:

VisitorID Time              VisitDuration
1         01.01.2014 00:01  80 seconds
2         01.01.2014 00:03  37 seconds

I would want to know how many people are on the website during a certain moment. For this I would have to transform this data into something much bigger:

Time             VisitorsPresent
01.01.2014 00:01 1
01.01.2014 00:02 1
01.01.2014 00:03 2 
...

But doing something like this seems highly inefficient. My code would be:

dates = {}
for index, row in data.iterrows(): 
    for i in range(0,int(row["duration"])):
        dates[index+pd.DateOffset(seconds=i)] = dates.get(index+pd.DateOffset(seconds=i), 1) + 1

Then I could transfer this into a series and be able to resample it:

result = pd.Series(dates)
result.resample("5min",how="mean").plot()

Could you point me to a right direction?

EDIT---

Hi HYRY Here is a head()

    uid        join_time_UTC      duration 
    0  1  2014-03-07 16:58:01      2953     
    1  2  2014-03-07 17:13:14      1954    
    2  3  2014-03-07 17:47:38       223

Answer 1

Create some dummy data first:

import numpy as np
import pandas as pd
start = pd.Timestamp("2014-11-01")
end = pd.Timestamp("2014-11-02")
N = 100000
t = np.random.randint(start.value, end.value, N)
t -= t % 1000000000

start = pd.to_datetime(np.array(t, dtype="datetime64[ns]"))
duration = pd.to_timedelta(np.random.randint(100, 1000, N), unit="s")
df = pd.DataFrame({"start":start, "duration":duration})
df["end"] = df.start + df.duration

print df.head(5)

Here is what the data looks like:

   duration               start                 end
0  00:13:45 2014-11-01 08:10:45 2014-11-01 08:24:30
1  00:04:07 2014-11-01 23:15:49 2014-11-01 23:19:56
2  00:09:26 2014-11-01 14:04:10 2014-11-01 14:13:36
3  00:10:20 2014-11-01 19:40:45 2014-11-01 19:51:05
4  00:02:48 2014-11-01 02:25:47 2014-11-01 02:28:35

Then do the value count:

enter_count = df.start.value_counts()
exit_count = df.end.value_counts()
df2 = pd.concat([enter_count, exit_count], axis=1, keys=["enter", "exit"])
df2.fillna(0, inplace=True)
print df2.head(5)

here is the counts:

                     enter  exit
2014-11-01 00:00:00      1     0
2014-11-01 00:00:02      2     0
2014-11-01 00:00:04      4     0
2014-11-01 00:00:06      2     0
2014-11-01 00:00:07      2     0

finally resample and plot:

df2["diff"] = df2["enter"] - df2["exit"]
counts = df2["diff"].resample("5min", how="sum").fillna(0).cumsum()
counts.plot()

the output is: