Concatenate the rows of each column being grouped by identify

Question

Welcome Help.

I would like to merge multiple rows of each ID of a dataframe into a single cell, Below is input format:

I have the following list of columns and Dataframe in pandas respectively

dict1 = {'ID': ['6610', '6610', '6610', '6620', '6620', '7540', '7540'],
 'NEW_ID': ['6615',
  '6615',
  '6615',
  '   ',
  '                                                                                    ',
  nan,
  nan],
 'OLD_PRICE': [17.22, 17.9, 17.22, 27.49, 20.42, 30.73, 29.55],
 'NEW_PRICE': [17.22, 17.22, 27.49, 18.99, 27.49, 29.55, 27.49],
 'LABEL': ['  NaN1', '  NaN2', '  NaN4', nan, '  Na', 'A', 'B']}

df = pd.DataFrame(dict1)

Expected output:

Answer 1

You can find duplicates in ID column with pd.Series.duplicated and fill the duplicates with blank value '':

mask = df["ID"].duplicated(keep="first")

df.loc[mask, "ID"] = ""
df["NEW_ID"] = df["NEW_ID"].fillna("")

print(df)

Prints:

     ID NEW_ID  OLD_PRICE  NEW_PRICE   LABEL
0  6610   6615      17.22      17.22    NaN1
1         6615      17.90      17.22    NaN2
2         6615      17.22      27.49    NaN4
3  6620             27.49      18.99     NaN
4                   20.42      27.49      Na
5  7540             30.73      29.55       A
6                   29.55      27.49       B

---

NOTE: If you need strip whitespace characters from your dataframe elements you can use this code:

df = df.applymap(lambda elem: elem.strip() if isinstance(elem, str) else elem)