Combine multiple time-series rows into one row with Pandas
<p>I am using a recurrent neural network to consume time-series events (click stream). My data needs to be formatted such that a each row contains all the events for an id. My data is one-hot encoded, and I have already grouped it by the id. Also I limit the total number of events per id (ex. 2), so final width will always be known (#one-hot cols x #events). I need to maintain the order of the events, because they are ordered by time.   </p>

<p>Current data state:   </p>

<pre><code>     id   page.A   page.B   page.C      
0   001        0        1        0
1   001        1        0        0
2   002        0        0        1
3   002        1        0        0
</code></pre>

<p>Required data state: </p>

<pre><code>     id   page.A1   page.B1   page.C1   page.A2   page.B2   page.C2      
0   001        0         1         0         1         0         0
1   002        0         0         1         1         0         1
</code></pre>

<p>This looks like a <code>pivot</code> problem to me, but my resulting dataframes are not in the format I need. Any suggestions on how I should approach this? </p>

<p>You can first create new column with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.cumcount.html" rel="nofollow"><code>cumcount</code></a> for new column name, then <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.set_index.html" rel="nofollow"><code>set_index</code></a> and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.unstack.html" rel="nofollow"><code>unstack</code></a>. Then you need sort columns in level <code>1</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sort_index.html" rel="nofollow"><code>sort_index</code></a>, remove <code>MultiIndex</code> from columns by <code>list comprehension</code> and last <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html" rel="nofollow"><code>reset_index</code></a>:</p>

<pre><code>df['g'] = (df.groupby('id').cumcount() + 1).astype(str)

df1 = df.set_index(['id','g']).unstack()
df1.sort_index(axis=1,level=1, inplace=True)
df1.columns = [''.join(col) for col in df1.columns]
df1.reset_index(inplace=True)
print (df1)
   id  page.A1  page.B1  page.C1  page.A2  page.B2  page.C2
0   1        0        1        0        1        0        0
1   2        0        0        1        1        0        0
</code></pre>

python pandas numpy