How to merge values in a column if two or more other columns have duplicated values?

user_19238905 0
Viewed 23

Here is the datarame:

col1 | Col2| Col3
190L|  L1  |m1:abc
190L|  L1  |m1:xyz
190L|  L1  |m1:abd
180L|  L2  |m2:ab2
180L|  L2  |m2:bcx
180L|  L2  |m2:nby

Output should be:

col1 | Col2| Col3
190L|  L1  |m1:abc,xyz,abd
180L|  L2  |m2.ab2,bcx,nby
pandas python-2.7
1 Answers

With the following dataframe:

import pandas as pd

df = pd.DataFrame(
    {
        "col1": ["190L", "190L", "190L", "180L", "180L", "180L"],
        "col2": ["L1", "L1", "L1", "L2", "L2", "L2"],
        "col3": ["m1:abc", "m1:xyz", "m1:abd", "m2:ab2", "m2:bcx", "m2:nby"],
    }
)

Here is one way to do it:

# Slice col3 in col3 ("abc") and new col4 ("m1"), group and join col3 ("abc,xyz,abd")
df = (
    df.assign(
        col4=df["col3"].map(lambda x: x[:2]), col3=df["col3"].map(lambda x: x[3:])
    )
    .groupby(["col1", "col2"])
    .agg(",".join)
)

# Update col3 ("m1"+":"+"abc,xyz,abd") and cleanup
df = (
    df.assign(col3=df["col4"].map(lambda x: x[:2]) + ":" + df["col3"])
    .drop(columns="col4")
    .sort_values("col2")
    .reset_index()
)

print(df)
# Output
   col1 col2            col3
0  190L   L1  m1:abc,xyz,abd
1  180L   L2  m2:ab2,bcx,nby
user_112460560
Related