I have a dataframe like below. What i am trying to do is calculate a column E1 and F1 with a sort and group by then return the entire data frame. The B1 column is incremental, but not necessarily by 1, but the sort on B1 will be
A1 B1 C1 D1 A 8 0.4 1.3 A 1 0.25 1.25 B 5 0.5 1.02 C 2 0.32 1.85 B 1 0.15 1.22 B 4 0.66 1.97 B 3 0.29 1.87 C 1 0.99 1.22 C 3 0.78 1.39 C 4 0.65 1.99 A 2 0.32 1.02
if i only had one A1 value like
A1 B1 C1 D1 A 1 0.25 1.25 A 8 0.4 1.3 A 2 0.32 1.02
my workflow is
df.sort_values(by=['B1'])
df = df.reset_index(drop=True)
l = df.index.values
for i in range(len(l)):
tempZ = df.loc[l[i], 'B1'] - df.loc[l[i-1], 'B1']
tempJ = (df.loc[l [i], 'C1'] - df.loc[l[i-1], 'C1'])
tempI = (df.loc[l [i], 'D1'] - df.loc[l[i-1], 'D1'])
*** DO a whole series of mathematical calculations ***
X = *** Final mathematical calculations ***
Y = *** Final mathematical calculations ***
df.loc[l[i], 'E1'] = tempE
df.loc[l[i], 'F1'] = tempF
Which would then output my dataframe as expected
A1 B1 C1 D1 E1 F1 A 1 0.25 1.25 7.33 0.33 A 2 0.32 1.02 7.89 0.12 A 8 0.4 1.3 8.65 0.22
But i want to do it for the entire dataframe
A1 B1 C1 D1 E1 F1 A 1 0.25 1.25 7.33 0.33 A 2 0.32 1.02 7.89 0.12 A 8 0.4 1.3 8.65 0.22 B 1 0.15 1.22 12.32 0.77 B 3 0.29 1.87 1.11 0.12 B 4 0.66 1.97 7.12 0.99 B 5 0.5 1.02 9.25 0.55 C 1 0.99 1.22 2.35 0.123 C 2 0.32 1.85 6.32 1.03 C 3 0.78 1.39 6.33 1.00 C 4 0.65 1.99 12.02 0.33
So my dataframe needs be grouped by A1 and sorted on B1. the calculations are done on C1 and D1 columns, but also the previous columns.
So i guess my question is, what would be the most efficient way to accomplish this with a large dataframe where i would need to do some type of groupby then output the complete dataframe with the new columns?
Data
data = [['A',8,0.4,1.3],['A',1,0.25,1.25],['B',5,0.5,1.02],['C',2,0.32,1.85], ['B',1,0.15,1.22],['B',4,0.66,1.97],['B',3,0.29,1.87],['C',1,0.99,1.22], ['C',3,0.78,1.39],['C',4,0.65,1.99],['A',2,0.32,1.02]] df = pd.DataFrame(data, columns = ['A1', 'B1', 'C1', 'D1'])
EDIT: The calculations are quite a bit more complex than i show, i do various operations with the sin,cos, acos, etc and apply calcs on these. do not believe that diff would really work for me
Advertisement
Answer
I am using the following CSV (saved as test.csv):
A1,B1,C1,D1 A,8,0.4,1.3 A,1,0.25,1.25 B,5,0.5,1.02 C,2,0.32,1.85 B,1,0.15,1.22 B,4,0.66,1.97 B,3,0.29,1.87 C,1,0.99,1.22 C,3,0.78,1.39 C,4,0.65,1.99 A,2,0.32,1.02
The following code will group on A1 and sort on B1:
import pandas as pd
df = pd.read_csv('test.csv')
res = pd.DataFrame(columns = df.columns.tolist())
for _, df_chnk in df.groupby('A1', sort=True):
df_chnk = df_chnk.reset_index(drop=True).sort_values(by=['B1'], ascending=True)
res = res.append(df_chnk)
res = res.reset_index(drop=True)
print(res)
The above code will generate the following dataframe:
A1 B1 C1 D1 0 A 1 0.25 1.25 1 A 2 0.32 1.02 2 A 8 0.40 1.30 3 B 1 0.15 1.22 4 B 3 0.29 1.87 5 B 4 0.66 1.97 6 B 5 0.50 1.02 7 C 1 0.99 1.22 8 C 2 0.32 1.85 9 C 3 0.78 1.39 10 C 4 0.65 1.99
To perform the following operation:
tempZ = df.loc[l[i], 'B1'] - df.loc[l[i-1], 'B1'] tempJ = (df.loc[l [i], 'C1'] - df.loc[l[i-1], 'C1']) tempI = (df.loc[l [i], 'D1'] - df.loc[l[i-1], 'D1'])
Check out pandas.DataFrame.diff. You can use it on each of the df_chnk!