0
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

Erfan
  • 40,971
  • 8
  • 66
  • 78
Tazar Hussain
  • 5
  • 3
  • so basically what you want to do is binning your data, 1s bins in this case? Did you check [posts like this one](https://stackoverflow.com/questions/6163334/binning-data-in-python-with-scipy-numpy)? or maybe also take a look at [pandas cut()](https://pandas.pydata.org/pandas-docs/version/0.23.4/generated/pandas.cut.html) – FObersteiner Jul 21 '19 at 15:42
  • The datetime we see, is that your index? And what is the column name from `SM4 Movement` etc. – Erfan Jul 21 '19 at 15:49

1 Answers1

2

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

Erfan
  • 40,971
  • 8
  • 66
  • 78
  • This worked for me, but there is one problem as I have three columns "Timestamp" , "sensor" and its "status" in my data some sensor activates at the same second for example 11:09:31 two sensors "A" and "B" are activated... the suggested method will take the sensor A and its status but but ignore another sensor at the same second.. how I can deal with this issue – Tazar Hussain Jul 21 '19 at 20:22
  • You have to set "Timestamp" as index: `df.set_index('Timestamp', inplace=True)`. And then use the method shown above. That should solve the problem @TazarHussain – Erfan Jul 21 '19 at 20:36