Create new columns by multiplying all columns by one specific column-CodePudding

I have the following df:

pd.DataFrame({'Jugador': {0: 'F. Thauvin', 1: 'C. Rodríguez', 2: 'M. Meza', 3: 'F. Gorriarán', 4: 'V. Guzmán'}, '90s': {0: 22.0, 1: 28.0, 2: 40.0, 3: 39.0, 4: 35.0}, 'Duelos/90': {0: 26.02, 1: 15.31, 2: 24.72, 3: 18.59, 4: 19.82}, 'Acciones defensivas realizadas/90': {0: 4.25, 1: 4.82, 2: 6.02, 3: 7.39, 4: 4.62}, 'Duelos defensivos/90': {0: 4.39, 1: 4.71, 2: 4.66, 3: 6.59, 4: 4.28}, 'Entradas/90': {0: 0.32, 1: 0.46, 2: 0.91, 3: 0.52, 4: 0.09}, 'Tiros interceptados/90': {0: 0.0, 1: 0.07, 2: 0.18, 3: 0.26, 4: 0.11}, 'Interceptaciones/90': {0: 1.48, 1: 2.27, 2: 2.17, 3: 3.12, 4: 2.04}, 'Faltas/90': {0: 1.25, 1: 1.24, 2: 0.91, 3: 1.37, 4: 1.08}, 'Tarjetas amarillas/90': {0: 0.14, 1: 0.25, 2: 0.28, 3: 0.21, 4: 0.11}, 'Tarjetas rojas/90': {0: 0.05, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0}, 'Acciones de ataque exitosas/90': {0: 8.83, 1: 2.34, 2: 6.17, 3: 2.51, 4: 3.32}, 'Goles/90': {0: 0.28, 1: 0.14, 2: 0.2, 3: 0.18, 4: 0.26}, 'Goles, excepto los penaltis/90': {0: 0.28, 1: 0.14, 2: 0.18, 3: 0.05, 4: 0.26}, 'xG/90': {0: 0.3, 1: 0.1, 2: 0.2, 3: 0.19, 4: 0.2}, 'Goles de cabeza/90': {0: 0.0, 1: 0.07, 2: 0.03, 3: 0.0, 4: 0.03}, 'Remates/90': {0: 2.73, 1: 0.78, 2: 2.12, 3: 1.34, 4: 2.44}, 'Asistencias/90': {0: 0.18, 1: 0.18, 2: 0.2, 3: 0.1, 4: 0.23}, 'Centros/90': {0: 5.69, 1: 0.96, 2: 3.6, 3: 1.27, 4: 2.67}, 'Centros desde la banda izquierda/90': {0: 1.62, 1: 0.25, 2: 0.48, 3: 0.36, 4: 1.16}, 'Centros desde la banda derecha/90': {0: 4.02, 1: 0.64, 2: 3.07, 3: 0.9, 4: 1.5}, 'Centros al área pequeña/90': {0: 0.74, 1: 0.14, 2: 0.58, 3: 0.1, 4: 0.34}, 'Regates/90': {0: 9.34, 1: 1.77, 2: 6.2, 3: 2.25, 4: 2.55}, 'Duelos atacantes/90': {0: 17.66, 1: 5.71, 2: 12.52, 3: 6.92, 4: 7.6}, 'Toques en el área de penalti/90': {0: 4.16, 1: 1.03, 2: 2.39, 3: 1.08, 4: 2.44}, 'Carreras en progresión/90': {0: 3.05, 1: 1.81, 2: 2.92, 3: 1.21, 4: 1.73}, 'Aceleraciones/90': {0: 1.2, 1: 1.6, 2: 0.98, 3: 0.8, 4: 0.77}, 'Pases recibidos /90': {0: 25.98, 1: 22.4, 2: 21.87, 3: 26.75, 4: 22.63}, 'Pases largos recibidos/90': {0: 1.29, 1: 0.64, 2: 1.84, 3: 0.67, 4: 1.64}, 'Faltas recibidas/90': {0: 1.85, 1: 1.38, 2: 1.86, 3: 2.22, 4: 1.22}, 'Pases/90': {0: 36.1, 1: 34.49, 2: 29.23, 3: 40.96, 4: 31.05}, 'Pases hacia adelante/90': {0: 8.46, 1: 11.59, 2: 8.37, 3: 14.36, 4: 8.68}, 'Pases hacia atrás/90': {0: 6.8, 1: 6.24, 2: 4.76, 3: 5.27, 4: 5.39}, 'Pases laterales/90': {0: 12.94, 1: 11.48, 2: 10.63, 3: 13.04, 4: 10.89}, 'Pases cortos / medios /90': {0: 27.55, 1: 30.2, 2: 23.89, 3: 36.21, 4: 26.4}, 'Pases largos/90': {0: 3.1, 1: 3.44, 2: 1.89, 3: 3.56, 4: 2.27}, 'xA/90': {0: 0.2, 1: 0.16, 2: 0.19, 3: 0.11, 4: 0.18}, 'Asistencias/90.1': {0: 1.76, 1: 1.63, 2: 1.69, 3: 1.03, 4: 1.64}, 'Second assists/90': {0: 0.09, 1: 0.07, 2: 0.05, 3: 0.03, 4: 0.03}, 'Third assists/90': {0: 0.05, 1: 0.0, 2: 0.05, 3: 0.03, 4: 0.03}, 'Desmarques/90': {0: 0.97, 1: 0.99, 2: 1.06, 3: 0.7, 4: 0.71}, 'Jugadas claves/90': {0: 0.97, 1: 0.43, 2: 0.71, 3: 0.31, 4: 0.57}, 'Pases en el último tercio/90': {0: 3.7, 1: 5.35, 2: 3.8, 3: 6.77, 4: 4.68}, 'Pases al área de penalti/90': {0: 6.01, 1: 2.62, 2: 3.98, 3: 2.12, 4: 2.72}, 'Pases en profundidad/90': {0: 1.53, 1: 1.31, 2: 0.93, 3: 0.75, 4: 0.79}, 'Ataque en profundidad/90': {0: 1.76, 1: 1.13, 2: 1.23, 3: 0.83, 4: 0.85}, 'Centros desde el último tercio/90': {0: 1.99, 1: 0.46, 2: 1.28, 3: 0.34, 4: 0.77}, 'Pases progresivos/90': {0: 6.43, 1: 5.78, 2: 5.59, 3: 6.4, 4: 4.99}, 'Goles recibidos/90': {0: nan, 1: nan, 2: nan, 3: nan, 4: nan}, 'Remates en contra/90': {0: nan, 1: nan, 2: nan, 3: nan, 4: nan}, 'xG en contra/90': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0}, 'Goles evitados/90': {0: nan, 1: nan, 2: nan, 3: nan, 4: nan}, 'Pases hacía atrás recibidos del arquero/90': {0: 0.09, 1: 0.07, 2: 0.13, 3: 0.26, 4: 0.2}, 'Salidas/90': {0: nan, 1: nan, 2: nan, 3: nan, 4: nan}, 'Tiros libres/90': {0: 0.92, 1: 1.24, 2: 0.86, 3: 0.54, 4: 0.57}, 'Tiros libres directos/90': {0: 0.09, 1: 0.0, 2: 0.2, 3: 0.1, 4: 0.23}, 'Córneres/90': {0: 2.68, 1: 1.77, 2: 1.61, 3: 1.6, 4: 1.84}})

These are all columns with values calculated per 90 minutes played in a football match. I want to create new columns with all the total values by multiplying each per 90 column by the 90s column which indicates the amount of 90s minutes played.

I want to create a function that automatizes the process and creates all the new columns with the same title as the original minus the /90 part.

I was able to get a list of all the columns that contain /90 by using this:

col_90s = []

for col in df.columns:
  if col.__contains__('/90'):
    col_90s.append(col)

CodePudding user response：

IIUC, you can use:

new_df = df.join(df.filter(like='/90').mul(90)
                   .rename(columns=lambda x: x.replace('/90', ''))
                 )

CodePudding user response：

I think @mozway almost nailed it. However to make a meaningful result you have to multiply by the values in the column 90s instead of multiplying by 90.

df.join(df.filter(like='/90').apply(lambda x: x.mul(df['90s']))
                   .rename(columns=lambda x: x.replace('/90', '')))

If instead you would like to keep only the new columns you could do with

pd.DataFrame().join(df.filter(like='/90').apply(lambda x: x.mul(df['90s']))
                   .rename(columns=lambda x: x.replace('/90', '')), how='right')