Python: merge duplicates by unique column in CSV

user_15779442 0
Viewed 100

I've got a problem with my python code. I'm trying to merge rows in my data frame. I've got something like that:

CODE_ID SIZE 1 2 3 4 ... 18 CODE
12345 Big X . . . ... . abc
12345 Big . X . . ... . def
67890 Medium . . . X ... . ghi
67890 Medium . . X . ... . jkl
13579 Small . . . X ... . xyz

I would like to merge the duplicates by CODE_ID, but strings in CODE column I'd like to join. Just like that:

CODE_ID SIZE 1 2 3 4 ... 18 CODE
12345 Big X X . . ... . abc; def
67890 Medium . . X X ... . ghi; jkl
13579 Small . . . X ... . xyz

I did it with agg, but it's just part of work.

g_df = df.groupby("CODE_ID")
group = g_df["CODE"].agg(lambda column: "; ".join(column))
group = group.reset_index(name="CODE")

Because the result is like that:

CODE_ID CODE
12345 abc; def
67890 ghi; jkl
13579 xyz

How can I add the rest of columns? Or maybe it's the wrong way to do that?

pandas dataframe aggregate unique python
1 Answers

You can convert Xs and .s to 1/0, then groupby and max, and map it back to Xs and .s:

(df
     .set_index(['CODE_ID', 'SIZE'])
     .filter(regex='\d+')              # numeric column names
     .replace({'X': 1, '.': 0})        # map to 1/0s
     .groupby(level=[0, 1])            # groupby code and size
     .max()                            # max
     .replace({1: 'X', 0: '.'})        # map back to X/.
).merge(                               # add ;-separated codes
    df.groupby(['CODE_ID', 'SIZE'])["CODE"].apply('; '.join),
    left_index=True,
    right_index=True,
)

Output:

   CODE_ID    SIZE  1  2  3  4 18      CODE
0    12345     Big  X  X  .  .  .  abc; def
1    13579   Small  .  .  .  X  .       xyz
2    67890  Medium  .  .  X  X  .  ghi; jkl
user_67927430
Related