How to merger same column values based on other column values?

user_12431156 0
Viewed 197

How to add a new column in pandas dataframe based on other column values , for example, Id columns has value and column Value has different values for Id, it needs to be merged as in the output.

df:

# dictionary of lists 
data = {'Id':["A", "A", "B", "B","B", "C", "D","E","E", "F", "G","G"], 
        'Value': ["10$", "2$", "30%", "43%", "12$", "43$", "27$", "40%" , 
                     "18$",np.nan,np.nan,"89%"]}

df = pd.DataFrame(data)
print(df)

    Id  Value
0   A   10$
1   A   2$
2   B   30%
3   B   43%
4   B   12$
5   C   43$
6   D   27$
7   E   40%
8   E   18$
9  F     nan
10 G    nan
11 G    89%

output :

 Id Value
0   A   10$, 2$
1   B   30%,43%,12$
3   C   43$
4   D   27$
5   E   40%,18$
6   F    nan
7   G    89%
pandas python-2.7 python-3.x python
3 Answers
df.groupby('Id')['Value'].apply(', '.join).reset_index()

print(df)

Output:


   Id   Value
0   A   S1, S2
1   B   S3, S3, S5
2   C   S6
3   D   S7
4   E   S8, S9
user_119892880

I am proposing to use the DataFrameGroupBy.aggregate function:

data = {'Id':["A", "A", "B", "B","B", "C", "D","E","E"], 
        'Value': ["S1", "S2", "S3", "S3", "S5", "S6", "S7", "S8" ,"S9"]}

df = pd.DataFrame(data)

df = df.groupby(by="Id", as_index=False).agg(
    {"Value": lambda s: ", ".join(s[~s.isnull()]) if not all(s.isnull()) else np.nan})    
print(df)

  Id       Value
0  A      S1, S2
1  B  S3, S3, S5
2  C          S6
3  D          S7
4  E      S8, S9
5  F         NaN
6  G          H9
user_53065510

Group by the ID column, use concatenation joininng as aggregate, you'll get the expected out put by removing duplicates in the new dataframe

df2 = df
df2['Value'] = df.groupby(['Id'])['Value'].transform(lambda x: ','.join(x))
df2 = df2.drop_duplicates()
df2

    Id  Value
0   A   S1,S2
2   B   S3,S3,S5
5   C   S6
6   D   S7
7   E   S8,S9
user_28792150
Related