Adding single value to speciifc dataframe column

user_4180900 0
Viewed 76

I have a dataframe like:

principalDf =

    |   x   ||   y   ||   z   ||   values   |
    |  x0   ||  y0   ||  z0   ||   areal    |
    |  x1   ||  y1   ||  z1   ||   aimag    |
    |  x2   ||  y2   ||  z2   ||   breal    |
    |  x3   ||  y3   ||  z3   ||   bimag    |

I want to produce:

frobprincipalDf =

|       x       ||       y       ||       z       ||   values   |
|sqrt(x0^2+x1^2)||sqrt(y0^2+y1^2)||sqrt(z0^2+z1^2)||     a      |
|sqrt(x2^2+x3^2)||sqrt(y2^2+y3^2)||sqrt(z2^2+z3^2)||     b      |

I'm generating the required x, y and z data by:

frobprincipalDf = pd.DataFrame(columns=principalDf.columns)

for col in principalDf.columns[:-1]:
    for index in range(0,len(principalDf),2):
        frobnorm = np.sqrt(principalDf[col].iloc[index]**2+principalDf[col].iloc[index+1]**2)
        print(frobnorm)

How can I add frobnorm to a specific col in the newly created frobprincipalDf?

Is there a better way to do this than nested for loops?

It should be generalisable for any value name, such that if another value begins with a, it will not 'group' them together.

It looks as though the below is a complete solution to the initial problem of even-index pairs of rows addition. This is slightly modified answer of quasi-human's answer. The secondary problem of adding a single value to a specific df column is still there though.

frobprincipalDf = np.sqrt(principalDf[['x', 'y', 'z']].shift() ** 2 + principalDf[['x', 'y', 'z']] ** 2)
frobprincipalDf = pd.concat([frobprincipalDf,principalDf['values'].str[:2]], axis=1)
frobprincipalDf = frobprincipalDf[frobprincipalDf.index % 2 == 1].reset_index(drop=True)
pandas dataframe
2 Answers

You could use groupby+agg:

import numpy as np

group = df['values'].str.extract('(.*)(?:real|imag)', expand=False)
# 0    a
# 1    a
# 2    b
# 3    b

(df.select_dtypes('number')
   .groupby(group)
   .agg(lambda x: np.sqrt((x**2).sum()))
   .reset_index()
)

example input:

    x   y   z values
0   1   2   3  areal
1   4   5   6  aimag
2   7   8   9  breal
3  10  11  12  bimag

output:

  values          x          y          z
0      a   4.123106   5.385165   6.708204
1      b  12.206556  13.601471  15.000000
user_163434640

You can use shift method to pick out a pair of rows.

Code:

import numpy as np
import pandas as pd

# Create a sample dataframe
import io
s='''x,y,z,values
1,5,9,areal
2,6,10,aimag
3,7,11,breal
4,8,12,bimag'''
df = pd.read_csv(io.StringIO(s))

# Calculate values
df_calc = np.sqrt(df[['x', 'y', 'z']].shift() ** 2 + df[['x', 'y', 'z']] ** 2)

# Deal with strings
df_str = df['values'].str[:1]

# Concatenate the two dataframes
df_result = pd.concat([df_calc, df_str], axis=1)

# Pick out odd indice
df_result = df_result[df_result.index % 2 == 1].reset_index(drop=True)

Input:

x y z values
1 5 9 areal
2 6 10 aimag
3 7 11 breal
4 8 12 bimag

Output:

x y z values
2.23607 7.81025 13.4536 a
5 10.6301 16.2788 b
user_180598790
Related