user1017373 user1017373 - 14 days ago 5
Python Question

pandas groupby on dataframe and assigning back to orignal df fails with transform

I have the following dataframe, with certain values in column :ID' repeating.
I want to group by the datafarme

df = pd.DataFrame(
{'Score': [0.65, 0.57, 0.56, 0.689, 0.56, 0.85, 0.45, 0.15, 0.14, 0.4678],
'ID': ['AAA', 'BBB', 'SAS', 'SAP', 'TCS', 'BBB', 'CTC', 'CTC', 'CTC', 'CTC'],
'Sample': ['AE01', 'AE01', 'AE03', 'AE03', 'AE03', 'AE05', 'AE05', 'AE05', 'AE05', 'AE05'],
'Freq': [1, 14, 14, 15, 16, 17, 18, 19, 20, 21],
'Type': ['Non', 'IND', 'IND', 'IND', 'IND', 'IND', 'IND', 'IND', 'IND', 'IND']},
columns=['Sample','ID','Type','Score','Freq'])


When I try to add the 'Re' column towards the group by option to get it assigned to the original dataframe df by following one-liner using
transform


df['Re']=df.drop_duplicates(['Sample','ID']).groupby(['ID']).transform(size)


I am getting following error,

ValueError: Wrong number of items passed 4, placement implies 1


I know I could do merge to get the original dataframe assigned to the group by results, but when I do merge I getting certain rows which are repeating multiple time in the original df, which I don't need. Any better solution would be great.
Thank you

Answer

I think you need first assign to new DataFrame output of drop_duplicates, because obviously drop_duplicates remove some duplicates rows (if exist), so cannot assign new column Re to original df.

Another problem is need add Series to [] for groupby, because transform work with Series only. If use size you can use some of columns which are not used for grouping, here it is ID.

df = df.drop_duplicates(['Sample','ID'])
df['Re']= df.groupby(['ID'])['Sample'].transform('size')
print (df)
  Sample   ID Type  Score  Freq  Re
0   AE01  AAA  Non  0.650     1   1
1   AE01  BBB  IND  0.570    14   2
2   AE03  SAS  IND  0.560    14   1
3   AE03  SAP  IND  0.689    15   1
4   AE03  TCS  IND  0.560    16   1
5   AE05  BBB  IND  0.850    17   2
6   AE05  CTC  IND  0.450    18   1

df = df.drop_duplicates(['Sample','ID'])
df['Re']= df.groupby(['ID'])['Type'].transform('size')
print (df)
  Sample   ID Type  Score  Freq  Re
0   AE01  AAA  Non  0.650     1   1
1   AE01  BBB  IND  0.570    14   2
2   AE03  SAS  IND  0.560    14   1
3   AE03  SAP  IND  0.689    15   1
4   AE03  TCS  IND  0.560    16   1
5   AE05  BBB  IND  0.850    17   2
6   AE05  CTC  IND  0.450    18   1

If dont assign, get NaN:

df['Re']= df.drop_duplicates(['Sample','ID']).groupby(['ID'])['Sample'].transform('size')
print (df)
  Sample   ID Type   Score  Freq   Re
0   AE01  AAA  Non  0.6500     1  1.0
1   AE01  BBB  IND  0.5700    14  2.0
2   AE03  SAS  IND  0.5600    14  1.0
3   AE03  SAP  IND  0.6890    15  1.0
4   AE03  TCS  IND  0.5600    16  1.0
5   AE05  BBB  IND  0.8500    17  2.0
6   AE05  CTC  IND  0.4500    18  1.0
7   AE05  CTC  IND  0.1500    19  NaN
8   AE05  CTC  IND  0.1400    20  NaN
9   AE05  CTC  IND  0.4678    21  NaN
Comments