python - Pandas columnwise merge rows which satisfy condition

Question

I have a pandas dataframe like so:

0     1     2     ...   20
data  NaN   2010  ...   2020
id    Name  val   ...   val
1     Tom   55    ...   67
2     Jerry 45    ...   78
.
.

Now I am looking for row which contains id in any column and creating a mask like so:

mask = (df.select_dtypes(object).apply(lambda x: x.str.contains('id', case=False)).any(axis=1))

Now I want to column wise merge all the rows (separated by "_"), which come before id is found, like so:

0        1     2         ...   20
data_id  Name  2010_val  ...   2020_val
1        Tom   55        ...   67
2        Jerry 45        ...   78
.
.

question from:https://stackoverflow.com/questions/65920580/pandas-columnwise-merge-rows-which-satisfy-condition

Answer 1

If possible order of values from back all values before id first processing values before id and then apped values after id:

df = df.iloc[::-1]

mask = (df.select_dtypes(object).apply(lambda x: x.str.contains('id', case=False)).any(axis=1))


m = mask.cumsum().gt(0)
df = pd.concat([df[m].agg(lambda x: '_'.join(x.dropna())).to_frame().T,
                df[~m]], ignore_index=True)