Merging two data frames but with different hz (Pandas)

Viewed 39

I have made an code with the purpose of merging two data frames (). However, one of the data frames contain data that for some reason has recorded 10 hz of data every other second and 11 every other. In order to avoid merge issues my solution to this problem was by creating an for loop looking like this:

for myrow in range (len(wholedf)):
        print(myrow)
        sepe = wholedf.at[myrow,'TIME_DIFFERENCE']
        if sepe != 0.0:
            resetcounter = 1
            row0 = myrow
        if resetcounter > 11:
            wholedf = wholedf.drop(wholedf.index[row0])
        myrow+=1
        resetcounter+=1

(I believe many of you understand the functionality of this. But for those who dont, the variable "sepe" returns the value of a time difference cell (either 0.0 or bigger than 0.0). Everytime it records that rows without difference (0.0) occurs after eachother in a span of 11 it removes the first row of these row0

The issues with using this code is both that its very very slow but also that it returns an unexpected error

ValueError: The truth value of an array with more than one element is ambiguous. Use a.any() or a.all()

for the line if sepe > 0.0:

My two data frames look like this:

1.

TIME                    SPEED   OTHER    OTHER2
...
2020-12-03 22:06:16     50      100      52
2020-12-03 22:06:17     60      130      22
2020-12-03 22:06:17     100     105      13
2020-12-03 22:06:17     24      99       104
2020-12-03 22:06:17     44      24       22
2020-12-03 22:06:17     66      20       257
2020-12-03 22:06:17     110     95       102
2020-12-03 22:06:17     90      22       24
2020-12-03 22:06:17     10      64       51
2020-12-03 22:06:17     11      66       61
2020-12-03 22:06:17     100     100      103
2020-12-03 22:06:17     120     175      111
2020-12-03 22:06:18     60      130      22
2020-12-03 22:06:18     100     105      13
2020-12-03 22:06:18     24      99       104
2020-12-03 22:06:18     44      24       22
2020-12-03 22:06:18     66      20       257
2020-12-03 22:06:18     110     95       102
2020-12-03 22:06:18     90      22       24
2020-12-03 22:06:18     10      64       51
2020-12-03 22:06:18     11      66       61
2020-12-03 22:06:18     100     100      103
...

(As you can see the rows with the 17th seconds are the lenght of 11 instead of 10)

2.

TIME                    FUEL    OTHER    OTHER2
...
2020-12-03 22:06:16.944 124     49       157
2020-12-03 22:06:17.044 113     42       132
2020-12-03 22:06:17.144 144     11       111
2020-12-03 22:06:17.244 224     29       557
2020-12-03 22:06:17.344 1224    422      151
2020-12-03 22:06:17.444 324     56       257
2020-12-03 22:06:17.544 18      29       157
2020-12-03 22:06:17.644 144     99       182
2020-12-03 22:06:17.744 672     14       977
2020-12-03 22:06:17.844 655     13       87
2020-12-03 22:06:17.944 194     15       14
...

(The data is recorded at 10hz)

Is there any other more clever solution to this problem? Or if this is the only solution, how do I fix the error?

Thank you in advance!

1 Answers

IIUC, try this:

df = df[df.groupby("TIME").cumcount() < 10]

output:
                TIME  SPEED  OTHER  OTHER2
                                       
2020-12-03  22:06:16     50    100      52
2020-12-03  22:06:17     60    130      22
2020-12-03  22:06:17    100    105      13
2020-12-03  22:06:17     24     99     104
2020-12-03  22:06:17     44     24      22
2020-12-03  22:06:17     66     20     257
2020-12-03  22:06:17    110     95     102
2020-12-03  22:06:17     90     22      24
2020-12-03  22:06:17     10     64      51
2020-12-03  22:06:17     11     66      61
2020-12-03  22:06:17    100    100     103
2020-12-03  22:06:18     60    130      22
2020-12-03  22:06:18    100    105      13
2020-12-03  22:06:18     24     99     104
2020-12-03  22:06:18     44     24      22
2020-12-03  22:06:18     66     20     257
2020-12-03  22:06:18    110     95     102
2020-12-03  22:06:18     90     22      24
2020-12-03  22:06:18     10     64      51
2020-12-03  22:06:18     11     66      61
2020-12-03  22:06:18    100    100     103
Related