Filling dataframe columns based on ranges described in other columns
<p>I have a very interesting problem here, I have a dataset like,</p>

<pre><code>    id,    start,  end   
    1234    200   400
    1235    300   500
    1236    100   900
    1236    200   1200
    1236    300   1400
</code></pre>

<p><strong>Main Objective</strong> : I want to count the number of concurrent sessions for each id.</p>

<pre><code>at 100, id:1236 has 1 session running
at 200, id:1236 has 2 sessions
at 300, id:1236 has 3 sessions
...
at 1000m id:1236 has 2 sessions
etc
</code></pre>

<p><strong>My solution</strong> :</p>

<ul>
<li>Add columns from 1 to 1400 (min and max of sessions) to all rows</li>
<li>fill the columns between session start value and session end value with 1</li>
<li>then add all the rows of the user so to get the above result.</li>
</ul>

<p><strong>In pandas</strong> :</p>

<pre><code>df = pd.read_csv(data+fileName,sep="\t",usecols=[0,1,2],names=['id','start','end'])

for i in range(0,1440):
    df[str(i)]=0

print df.columns
</code></pre>

<p>I could add the columns and was thinking <strong>how to fill 1 to these columns between session start and end in each row</strong>. Each row can have different session start and end.</p>

<p>Any hint would help a lot. I am just trying it in pandas, but later <em>I have to port it to Apache pyspark where there is no pandas in worker nodes</em>.</p>

<p>In Pandas you could also do this: df[(df.start &lt;= t)&amp;(df.end >= t)].groupby("id").count()['start'].reset_index() where t is your desired time. Just rename the final column accordingly. But I don't know if this can be ported over the pyspark.@Khris</p>

python pandas dataframe pyspark-sql