Divide Columns By Different Values In A Data Frame

I need to normalize my data by dividing x and y by width and height. X/width and y/height Input DataFrame example: ID X1 Y1 X2 Y2 X3 Y3 X4 Y4 X5 Y5 Width Height 1 1 2 1 2

Solution 1:

With filtering the columns based on their names and running update:

df.update(df.filter(like='X').div(df['Width'],0))
df.update(df.filter(like='Y').div(df['Height'],0))
df = df.drop(columns=['Width','Height']).add_suffix('n').rename(columns={'IDn':'ID'})
df

Output:

    ID  X1n  Y1n  X2n  Y2n  X3n  Y3n  X4n  Y4n  X5n  Y5n
0    1  0.5  0.2  0.5  0.2  0.5  0.2  0.5  0.2  0.5  0.2
1    2  0.5  0.2  0.5  0.2  0.5  0.2  0.5  0.2  0.5  0.2

Solution 2:

Update will be problematic if you are missing width or height measurements since it will not overwrite the original with NaN.

wcols = df.columns[df.columns.str.contains('X')]
hcols = df.columns[df.columns.str.contains('Y')]

df.loc[:, wcols] = df.loc[:, wcols].divide(df.Width, axis=0)
df.loc[:, hcols] = df.loc[:, hcols].divide(df.Height, axis=0)

df = df.drop(columns=['Width', 'Height'])
# Doesn't mess up IDs name
df.columns = [f'{col}n'if col != 'ID'else col for col in df.columns]

Out:

   ID  X1n  Y1n  X2n  Y2n  X3n  Y3n  X4n  Y4n  X5n  Y5n
0   1  0.5  0.2  0.5  0.2  0.5  0.2  0.5  0.2  0.5  0.2
1   2  0.5  0.2  0.5  0.2  0.5  0.2  0.5  0.2  0.5  0.2

Solution 3:

When you have columns like X1, X2, X123 and so on,

pd.concat([df.filter(regex='^X\d+').div(df.loc[0,'Width']),
           df.filter(regex='^Y\d+').div(df.loc[0,'Height']) ],axis=1)\
.rename(columns=lambda x: x+'n')\
.assign(Height = df.Height.values[0])\
.assign(Width= df.Width.values[0])\
.assign(ID = df.ID)

Gives:

   X1n  X2n  X3n  X4n  X5n  Y1n  Y2n  Y3n  Y4n  Y5n  HeightWidth  ID
00.50.50.50.50.50.20.20.20.20.2102110.50.50.50.50.50.20.20.20.20.21022

Solution 4:

Another possible method could be by zipping the columns after applying filter() and pandas.concat():

dfX= df.filter(regex= 'X').div(df['Width'],axis =0)
dfY =df.filter(regex= 'Y').div(df['Height'],axis =0)
df= pd.concat([dfX,dfY], axis = 1)
df_final = df[[col for elem in list(zip(dfX.columns, dfY.columns)) for col in elem]].add_suffix('n')