So I'm trying to transform a pandas dataframe where I have multiple duplicates of a "user" value (my index being a user ID number), demographic info (which shouldn't change) and various purchase variables which the goal is to sum (in a timely fashion)

Right now I'm using a couple for loops which I now is bad practice when using pandas:

for n in users:

     #Creating an empty array to use to shuffle the new values into
     #n being the user ID number
     uservalue = [n]
     #Add independent variables
     prods = df[df["User_ID"]==n]["Product_ID"].tolist()
     for v in range (0,len(xvars)):
     #Adding total spending variables
     for v in range (0, len(yvars)):
    user = pd.Series(uservalue, userdfcols)
    userdf = userdf.append(user, ignore_index = True)

So, rather than iterating over the entire 500k entry dataframe, putting everything in a container list and then appending that list into a new row, is there a more efficient way to transform by user ID? My feeling is that I should be appending values to the row individually using pandas methods and skip the list but would that even help?


  • $\begingroup$ If you didn't solve this yet, could you add a small input/output example to illustrate exactly what you want to achieve? $\endgroup$ – Shaido Jun 2 '20 at 9:01

So the question is count the number of duplications by user ID and other variables. For this you can use the df.groupby().size() method. This will return the counts of unique entries in with a particular value in a column.

Here is the documentation for this function: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.groupby.html


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.