I have a pandas dataframe (df) with the following fields:

I want to create a dataframe (df2) like the following:

So, first of all, i create df2 and add an additional column by the following commands:

df2 = df.groupby("id", as_index= False).count()
df2["categories"] = str()

(this also counts the occurrences of various categories, which is something useful for what I intend to do)

Then, I use this loop:

for i in df2.itertuples():
   for entries in df.itertuples():
      if i.id == entries.id:
         df2["categories"].iloc[i.Index]  = entries.category
      else:
         pass

Using this code, I get the dataframe that I wanted. However, this implementation has several problems:

1. Doesn't look optimal.
2. If there are repeated entries (such as another column with "Eddie" and "magician"), the entry for Eddie in df2 would have "magician, plumber, magician" in categories.

Therefore I would like to ask the community: is there a better way to do this?

Also keep in mind that this is my first question on this website!

Thanks in advance!

CodePudding user response：

You can groupby your id and name columns and apply a function to the category one like this:

import pandas as pd

data = {
  'id': ['01', '01', '02', '03', '03'],
  'name': ['Eddie', 'Eddie', 'Martha', 'Jeremy', 'Jeremy'],
  'category': ['magician', 'plumber', 'actress', 'dancer', 'actor']
}

df = pd.DataFrame(data)
df2 = df.groupby(['id', 'name'])['category'].apply(lambda x: ', '.join(x)).reset_index()
df2

Output:

   id    name          category
0  01   Eddie  magician, plumber
1  02  Martha           actress
2  03  Jeremy      dancer, actor