I have this dataframe:

    c1  c2  
0   B   1   
1   A   2   
2   B   5
3   A   3
4   A   7

My goal is to keep tracking the values in column2, based on the letters of column1 separated by(:), the output should look like this:

    c1  list
0   A   2:3:7   
1   B   1:5 

What's the most pythonic way to do this:

At the moment I'm able to group by the column 1 and I'm trying to use the apply() function, but I do not know how to map and make this list in the new column.

CodePudding user response：

Try this:

df = df.groupby("c1")["c2"].apply(lambda x: ":".join([str(i) for i in x])).reset_index()

CodePudding user response：

You can use groupby

>>> import pandas as pd
>>> df = pd.DataFrame({'c1': ['B', 'A', 'B', 'A', 'A'], 'c2': [1, 2, 5, 3, 7]})
>>>
>>> df.c2 = df.c2.astype(str)
>>> new_df = df.groupby("c1")['c2'].apply(":".join).reset_index()
>>> new_df
  c1     c2
0  A  2:3:7
1  B    1:5

CodePudding user response：

i think you can just do a string join

df = pandas.DataFrame({"c1":list("BABAA"),"c2":[1,2,5,3,7]})
df['c2'] = df['c2'].astype(str)
df.groupby('c1').agg({'c2':':'.join})

you might get more mileage from

df.groupby('c1').agg({'c2':list})