0

I'm following Boolean indexing from here https://stackoverflow.com/a/28236391/5087619

I am struggling to sum the values for where a match is found from another list. Below is what I have.

        result = c.execute("SELECT user_ID, onDuration FROM onRecord").fetchall()
        records = []
        userIDs = []
        for r in result:
            records.append([r[0], r[1]])
            userIDs.append(r[0])
        c.close()
        userIDs = removeDups(userIDs)
        calcResult = []
        for r in records:
            for ids in userIDs:
                calcResult.append (df.loc[df[r[0] == ids[0], r[1]]].sum())

The function so far is capturing all the records from the onRecord table. Which then puts the rows into 2d lists, and another of just user IDs to remove duplicates.

Currently throwing the error:

Command raised an exception: TypeError: 'int' object is not subscriptable

What I am trying to do is sum the column of onDuration where the user_IDs duplicate. Perhaps I am taking the wrong approach.

Data output example:  
user_ID | onDuration  
123456 | 00:25:00  
789101 | 00:15:00  
101112 | 00:15:00  
123456 | 00:50:00  
101112 | 00:20:00

The desired output is

[[123456, 01:15:00], [789101, 00:15:00], [101112, 00:35:00]]
JohnWick
  • 31
  • 8
  • After doing some reading and research I'm a lot closer to where I need to be using pandas. I have now put the rows from SQL into a dictionary successfully. Now the issue is this line does not "sum" the total duration, it joins them together like a join string calculateData = df.groupby(df.index).agg({'onDuration':sum}) – JohnWick Nov 12 '21 at 02:42
  • Solved with pd.to_timedelta – JohnWick Nov 12 '21 at 02:56

1 Answers1

0

Solved by putting the data from SQL into a dictionary and using pandas. See comments

JohnWick
  • 31
  • 8
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 12 '21 at 07:18