Odtnhj

I have a panda with a few columns like this

username A time place
AAA B 1 YYY
AAA C 2 YYY
AAA D 1 YYY
AAA B 3 ZZZ
AAA C 4 ZZZ
AAA B 3 ZZZ
BBB B 1 YYY
BBB C 2 YYY
BBB D 1 YYY
BBB B 7 ZZZ
BBB C 8 ZZZ
BBB B 9 ZZZ
CCC B 6 YYY
CCC C 5 YYY
CCC D 8 YYY
CCC B 7 ZZZ
CCC C 8 ZZZ
CCC B 9 ZZZ

in the above panda, all the columns except time are strings. TIme is a float column.

I am trying create a sequence such that for every username, I want the all the rows of a username collated to one row. The output dataframe wants to look like this.

username A time place
AAA B+C+D+B+C+B 1+2+1+3+4+3 YYY+YYY+YYY+ZZZ+ZZZ+ZZZ
BBB B+C+D+B+C+B 1+2+1+7+8+9 YYY+YYY+YYY+ZZZ+ZZZ+ZZZ
CCC B+C+D+B+C+B 6+5+8+7+8+9 YYY+YYY+YYY+ZZZ+ZZZ+ZZZ

I am using the '+' as a separator, but it can be any character generally used for separators(like ,/ ..etc)

I have been able to do that for all the columns using

df.groupby('username')['A].apply('+',join).reset_index()

and the same for all columns. I am finally merging all the individual df`s to get the form I want.

For the time column I am able to do but am looking to get a column of type floats. I am having difficulty doing that. Hoping somebody more knowledgeable can guide me here.

I have even tried changing the output column after the fact with
df['time'].astype(float)

but am getting all NaN`s.

I believe you need convert all columns to strings with agg:

df = df.astype(str).groupby('username', as_index=False).agg('+'.join)
print (df)
 username A time place
0 AAA B+C+D+B+C+B 1.0+2.0+1.0+3.0+4.0+3.0 YYY+YYY+YYY+ZZZ+ZZZ+ZZZ
1 BBB B+C+D+B+C+B 1.0+2.0+1.0+7.0+8.0+9.0 YYY+YYY+YYY+ZZZ+ZZZ+ZZZ
2 CCC B+C+D+B+C+B 6.0+5.0+8.0+7.0+8.0+9.0 YYY+YYY+YYY+ZZZ+ZZZ+ZZZ

If need sum numeric columns and join by + strings columns:

df = (df.groupby('username', as_index=False)
 .agg(lambda x: x.sum() if np.issubdtype(x.dtype, np.number) else '+'.join(x)))
print (df)
 username A time place
0 AAA B+C+D+B+C+B 14.0 YYY+YYY+YYY+ZZZ+ZZZ+ZZZ
1 BBB B+C+D+B+C+B 28.0 YYY+YYY+YYY+ZZZ+ZZZ+ZZZ
2 CCC B+C+D+B+C+B 43.0 YYY+YYY+YYY+ZZZ+ZZZ+ZZZ