Merge columns and fill the empty space with the merged data using Pandas Python-CodePudding

I need to merge 38 to 36, 45 to 43, and 79 to 78 filling in the empty space on the merging column.

Dataset

	0	5	36	38	43	45	78	79
1	A	01JUN2022		1.2		B		1.2
2	C	01JUN2022		1.4		D		1.4
3	E	01JUN2022	1.5		F		1.6
4	G	01JUN2022	1.7		H		1.7
5	I	01JUN2022	1.4		J		1.8
6	K	01JUN2022	1.7		L		1.3

Required output

	0	5	36	43	79
1	A	01JUN2022	1.2	B	1.2
2	C	01JUN2022	1.4	D	1.4
3	E	01JUN2022	1.5	F	1.6
4	G	01JUN2022	1.7	H	1.7
5	I	01JUN2022	1.4	J	1.8
6	K	01JUN2022	1.7	L	1.3

CodePudding user response：

You can rename the columns and groupby.first:

# assuming the values are integer
# for string use {'38': '36'...}
merge = {38: 36, 45: 43, 78: 79}

out = df.rename(columns=merge).groupby(level=0, axis=1).first()

output:

  0          5    36 43   79
1  A  01JUN2022  1.2  B  1.2
2  C  01JUN2022  1.4  D  1.4
3  E  01JUN2022  1.5  F  1.6
4  G  01JUN2022  1.7  H  1.7
5  I  01JUN2022  1.4  J  1.8
6  K  01JUN2022  1.7  L  1.3

CodePudding user response：

Try this;

df[36] = df[[36,38]].sum(axis=1)
df[43] = df.apply(lambda x: x[43] if pd.isna(x[45]) else x[45],axis=1)
df[79] = df[[78,79]].sum(axis=1)

df.drop([38,45,78],axis=1,inplace=True)