# Combine several rows of time series in a single row with the Pandas

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.

Current data state:

``````     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
```
```

Required data state:

``````     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
```
```

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

The idea here is to `reset_index` within each group of `'id'` to get a count which row of that particular `'id'` we are at. Then follow that up with `unstack` and `sort_index` to get columns where they are supposed to be.

Finally, flatten the multiindex.

``````df1 = df.set_index('id').groupby(level=0) \
.apply(lambda df: df.reset_index(drop=True)) \
.unstack().sort_index(axis=1, level=1)  # Thx @jezrael for sort reminder

df1.columns = ['{}{}'.format(x, int(x) + 1) for x in df1.columns]

df1
```
```
{"ft_lang_label":"__label__en","ft_lang_prob":0.78805035,"math_prob":0.7420514,"size":1263,"snap":"2023-14-2023-23","text_gpt3_token_len":381,"char_repetition_ratio":0.104845114,"word_repetition_ratio":0.017316017,"special_character_ratio":0.31591448,"punctuation_ratio":0.1295681,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9746247,"pos_list":[0,1,2],"im_url_duplicate_count":[null,4,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2023-05-30T09:05:24Z\",\"WARC-Record-ID\":\"<urn:uuid:89a5eb69-5aff-422a-b2c6-96970c4fe798>\",\"Content-Length\":\"20343\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:da765338-df1f-4f83-9253-c5ca11440f5b>\",\"WARC-Concurrent-To\":\"<urn:uuid:39cd52f1-bc2a-4836-b65b-33fa2d835df0>\",\"WARC-IP-Address\":\"104.21.21.124\",\"WARC-Target-URI\":\"https://www.codesd.com/item/combine-several-rows-of-time-series-in-a-single-row-with-the-pandas.html\",\"WARC-Payload-Digest\":\"sha1:R5RAFT3BQZ6WKXNTZI6WYUYZ4MKPA7UH\",\"WARC-Block-Digest\":\"sha1:O6MTMQE3PJ5A4AEEO53Z7NKZB2RTJICF\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2023/CC-MAIN-2023-23/CC-MAIN-2023-23_segments_1685224645417.33_warc_CC-MAIN-20230530063958-20230530093958-00349.warc.gz\"}"}