splitting data into equal size and its corresponding columns

2017-10-31 11:08:55 SM4 Movement
2017-10-31 11:09:11 SM4 No movement
2017-10-31 11:09:31 C14 Pressure
2017-10-31 11:09:31 SM4 Movement
2017-10-31 11:09:32 C14 No Pressure
2017-10-31 11:09:41 C14 Pressure
2017-10-31 11:09:43 SM3 Movement
2017-10-31 11:09:53 SM3 No movement
2017-10-31 11:09:56 M01 Open
2017-10-31 11:10:06 SM4 No movement
2017-10-31 11:11:21 SM4 Movement
2017-10-31 11:11:21 M01 Close

I want to split the first column into seconds from 11:08:55..11:08:56..11:08:57..11:08:58 and also the other two columns values should be repeated e.g. from second 55 to 11 SM4 should be repeated along with its value Movement.. after that 11:09:11 to 11:09:31 SM4 and “No movement” similar for other sensors

Answer

What you want is to resample your data to seconds interval and fillna with ffill:

df = df.resample('s').first().ffill()

Output

                    col1         col2
2017-10-31 11:08:55  SM4     Movement
2017-10-31 11:08:56  SM4     Movement
2017-10-31 11:08:57  SM4     Movement
2017-10-31 11:08:58  SM4     Movement
2017-10-31 11:08:59  SM4     Movement
...                  ...          ...
2017-10-31 11:11:17  SM4  No movement
2017-10-31 11:11:18  SM4  No movement
2017-10-31 11:11:19  SM4  No movement
2017-10-31 11:11:20  SM4  No movement
2017-10-31 11:11:21  SM4     Movement

[147 rows x 2 columns]

Note I assumed your index is in datetime format, otherwise use this before you run the code:

df.index = pd.to_datetime(df.index)

Note2: since you didn’t provide the column names, I called them col1 & col2

Leave a Reply

Your email address will not be published. Required fields are marked *