Calculate mean Intensity for equivalent rows having same h,k,and l-CodePudding

enter image description here

I would like to calculate the mean intensity for those rows which have equivalent h,k,l. By equivalent I mean h= /- h and k==/-k and l= /- l . These three conditions need to be satisfied, then these rows are equivalent and I need to get the mean of their corresponding intensities in new column. For example the first 2 shaded rows are equivalent, so calculate average= (31.26 42.19)/2 and type it in a new column next to each.

CodePudding user response：

Create groups by /-h, /-k and /-l and compute mean intensity:

grps = [df['h'].abs(), df['k'].abs(), df['l'].abs()]
df['Mean Intensity'] = df.groupby(grps)['Intensity'].transform('mean')
print(df)

# Output:
   h  k  l  Intensity  Mean Intensity
0  1 -1 -1        138          152.00
1  0  1 -1        186          168.25
2  1  1  1        124          152.00
3  0  1  1        159          168.25
4  0  1  0        176          176.00
5  1 -1 -1        194          152.00
6  0  1 -1        199          168.25
7  0  0 -1        172          172.00
8  0 -1 -1        129          168.25
9  1  0 -1        134          134.00

Setup:

data = {'h': [1, 0, 1, 0, 0, 1, 0, 0, 0, 1],
        'k': [-1, 1, 1, 1, 1, -1, 1, 0, -1, 0],
        'l': [-1, -1, 1, 1, 0, -1, -1, -1, -1, -1],
        'Intensity': [138, 186, 124, 159, 176, 194, 199, 172, 129, 134]}
df = pd.DataFrame(data)
print(df)

# Output:
   h  k  l  Intensity
0  1 -1 -1        138
1  0  1 -1        186
2  1  1  1        124
3  0  1  1        159
4  0  1  0        176
5  1 -1 -1        194
6  0  1 -1        199
7  0  0 -1        172
8  0 -1 -1        129
9  1  0 -1        134

CodePudding user response：

Try:

out = df.groupby([df['h'].abs(),df['k'].abs(),df['l'].abs()])['Intensity'].transform('mean')

Group by ['h','k','l'] and find the mean Intensity of each group and apply group mean to each row.