python & pandas- Calculation bewteen rows based on certain values in columns from DataFrame
<p>I have a large DataFrame (called df_NoMissing) with thousands of rows, and I need to do calculation and analysis with them.</p>

<pre><code>   NoDemande   NoUsager  Sens  IdVehiculeUtilise  Fait  HeureArriveeSurSite   HeureEffective      Periods
0 42196000013  000001    +         287VÃ©h          1  11/07/2015 08:02:07  11/07/2015 08:02:13    Matin
1 42196000013  000001    -         287VÃ©h          1  11/07/2015 08:17:09  11/07/2015 08:17:13    Matin
2 42196000002  000314    +         263VÃ©h          1  11/07/2015 09:37:43  11/07/2015 09:53:37    Matin
3 42196000016  002372    +         287VÃ©h          1  11/07/2015 09:46:42  11/07/2015 10:01:39    Matin
4 42196000015  000466    +         287VÃ©h          1  11/07/2015 09:46:42  11/07/2015 10:01:39    Matin
5 42196000002  000314    -         263VÃ©h          1  11/07/2015 10:25:17  11/07/2015 10:38:11    Matin
6 42196000015  000466    -         287VÃ©h          1  11/07/2015 10:48:51  11/07/2015 10:51:30    Matin
7 42196000016  002372    -         287VÃ©h          1  11/07/2015 11:40:56  11/07/2015 11:41:01    Matin
8 42196000004  002641    +         263VÃ©h          1  11/07/2015 13:39:29  11/07/2015 13:52:50    Soir
9 42196000004  002641    -         263VÃ©h          1  11/07/2015 13:59:56  11/07/2015 14:07:41    Soir  
</code></pre>

<p>What I want to do is to have two rows with the same value in the column <code>NoDemande</code>, <code>NoUsager</code>, <code>Periods</code> but different in column <code>Sens</code> do the subtraction between column <code>HeureArriveeSurSite</code> and <code>HeureEffective</code>. And because the result doesn't correspond to current DataFrame, so the result will be saved in a new DataFrame</p>

<hr>

<p>I tried to separate the DataFrame by identifying <code>Sens</code> so I could to the subtraction directly. But it doesn't work at all.</p>

<pre><code>df_new = pd.DataFrame(columns=['NoDemande', 'NoUsager', 'Periods', 'DureeTrajet']
df1 = df_NoMissing[(df_NoMissing['Sens'] == '+') &amp; (df_NoMissing['Periods'] == 'Matin')]
df2 = df_NoMissing[(df_NoMissing['Sens'] == '-') &amp; (df_NoMissing['Periods'] == 'Matin')] 

df_new['DureeTrajet'] = df2['HeureArriveeSurSite'].values-df1['HeureEffective'].values
</code></pre>

<p>This one returned: <code>ValueError: operands could not be broadcast together with shapes (1478,) (1479,)</code></p>

<p>I also tried the loaded way by telling exactly what I want each time:</p>

<pre><code>df1.loc[df1['NoDemande'] == '42196000015','HeureEffective'] - df2.loc[df2['NoDemande'] == '42196000015','HeureArriveeSurSite']
</code></pre>

<p>But this one came back with:</p>

<pre><code>4   NaT
6   NaT
dtype: timedelta64[ns]
</code></pre>

<p>So what should I do to get what I want?</p>

<hr>

<p><strong>EDIT</strong></p>

<p>The output will look like:</p>

<pre><code>  NoDemande   NoUsager   Periods   DureeTrajet
0 42196000013  000001     Matin     00:14:54
1 42196000002  000314     Matin     00:31:40
2 42196000016  002372     Matin     00:39:23
3 42196000015  000466     Matin     00:47:12
4 42196000004  002641     Soir      00:07:06
</code></pre>

<p>Any help will be really appreciated~</p>

<p>So my solution is:</p>

<ol>
<li><p>to join df1 and df2 (not append them, but join with outer join). For this you should rename all the columns in df2 except of NoDemande, NoUsager and Period. for example, in df1 it will be Sens, in df2 - Sens2. And after join try to subtract the dates as you want. </p></li>
<li><p>It can also be that you get some missings, if any of entries do not have a pair with another Sens value. hose who are not if paits you should just filter out, I think. Because you need just DureeTrajet for those users, who had several sessions, don't you? So, if a user had only one session, you don't need him in the df_new table?</p></li>
<li><p>At the end you shoud have only those entries, which have pairs. And for this you can subtract the dates.</p></li>
</ol>

<p><strong>EDIT:</strong></p>

<p>IF some entries have not just a pair, but two or more pairs, you should then define, which pair has more priority / makes more sense.</p>

python pandas dataframe