concating rows with same index, removing duplicates

user_13957738 0
Viewed 32

*This isn't the first time it is asked here but I haven't seen any Q related to multiple columns

Example data:

               1        2        3 ........
Orange        |a        |d       |e
Orange        |b        |b       |e
Black         |y        |z       |nan
Black         |x        |y       |nan
Black         |z        |nan     |nan
Black         |w        |x       |y
Blue          |g        |h       |i
Blue          |i        |nan     |nan
..

I am trying to join same indexed rows, and drop duplicates i.e orange: a b d e

Joining same index rows done by:

df = df.groupby(df.index).agg(lambda z: ','.join(z.astype(str)))

After that I got all rows concatenated with a comma just inlaid in some columns. I tried to move them to separate columns:

 df = df.columns.str.split(',',expand=True)

But it did not work.

After I move them to separated columns, I'll use drop_duplicates().

Need help with the expand part.

Edited excpected (order isn't necessary):

               1        2        3       4       5       6       7....
Orange        |a        |b       |d      |e
Black         |y        |z       |x      |w
Blue          |g        |h       |i
pandas python split
1 Answers

You can do it by getting unique values of each group of index once you stack to get all values in one column and then create a Series with unique element per group. Finally unstack to get the expected output.

res = (
    df.stack()
      .groupby(level=0)
      .apply(lambda x: pd.Series(x.unique()))
      .unstack()
)
print(res)
        0  1  2    3
Black   y  z  x    w
Blue    g  h  i  NaN
Orange  a  d  e    b

Note that if the nan are string and you don't want them, you can add .replace('nan', np.nan) before the groupby, they will be remove during the unique.

user_92747320
Related