I have a Pandas dataframe which look like this.
CustId CustName Price_1 Priceqty1 Price_2 Priceqty2 Price_3 Priceqty3 Price_4 Price_5
5015 Axn 315.12 1 374 1 126.32 3 167.8765
5015 Axn 75.36 3 190.19 7 33.16 1 190.19 88
5015 Axn 123 5 4 18.07 2 0.073 12
7315 Bxy 12.0 4 22.345 3 77.89 1 345.0 3344
7315 Bxy 987.90 7 34.06 4 90.09 3 876.34 908.76
3283 Cxz 123.34 8 55.78 7 12 9 878.09 98.456
3283 Cxz 178.90 7 88 8 0.09 0 987.56
I have 5 different price columns with different values with different decimal places. For better understanding to end user I need to convert all the price columns values to a common decimal place.
To convert I need to follow certain Rule: 1.conversion should be done for every customer id separately. 2.Common decimal place will be determined by a price column value with largest decimal place.
Let's Take CustId 5015 example
CustId CustName Price_1 Priceqty1 Price_2 Priceqty2 Price_3 Priceqty3 Price_4 Price_5
5015 Axn 315.12 1 374 1 126.32 3 167.8765
5015 Axn 75.36 3 190.19 7 33.16 1 190.19 88
5015 Axn 123 5 4 18.07 2 0.073 12
If you see you can find 1st row of Price_4 column Has value of 167.8765 here the decimal place is of 4 . If you check all the price column values for Custid 5015 the decimal place of 4 is the largest one when compared with other price columns values. since 4 is largest one I need to convert every price column values of custid 5015 to 4 decimal places.
After conversion This how it should look like.
CustId CustName Price_1 Priceqty1 Price_2 Priceqty2 Price_3 Priceqty3 Price_4 Price_5
5015 Axn 315.1200 1 374.0000 1 126.3200 3 167.8765
5015 Axn 75.3600 3 190.1900 7 33.1600 1 190.1900 88
5015 Axn 123.0000 5 4 18.0700 2 0.0730 12
similar to custid 5015 the largest decimal value for custid 7315 would be 3
CustId CustName Price_1 Priceqty1 Price_2 Priceqty2 Price_3 Priceqty3 Price_4 Price_5
7315 Bxy 12.000 4 22.345 3 77.890 1 345.000 3344.000
7315 Bxy 987.900 7 34.060 4 90.090 3 876.340 908.760
Blank values should be Blank only.
I need to do this for every custid there are more than 800 different customer id. What's the most efficient way to do this?
Use custom function per groups:
def f(x):
#get string with maximal values after '.'
a = max([str(y).split('.')[1] for y in np.ravel(x) if pd.notna(y)], key=len)
#set format of floats
return x.applymap(lambda x: f'{x:.{len(a)}f}').replace('nan','')
df1 = df.filter(like='Price_')
df[df1.columns] = df1.groupby(df['CustId']).apply(f)
print (df)
CustId CustName Price_1 Priceqty1 Price_2 Priceqty2 Price_3 \
0 5015 Axn 315.1200 1 374.0000 1.00 126.3200
1 5015 Axn 75.3600 3 190.1900 7.00 33.1600
2 5015 Axn 123.0000 5 4.0000 18.07 2.0000
3 7315 Bxy 12.000 4 22.345 3.00 77.890
4 7315 Bxy 987.900 7 34.060 4.00 90.090
5 3283 Cxz 123.340 8 55.780 7.00 12.000
6 3283 Cxz 178.900 7 88.000 8.00 0.090
Priceqty3 Price_4 Price_5
0 3.000 167.8765
1 1.000 190.1900 88.0000
2 0.073 12.0000
3 1.000 345.000 3344.000
4 3.000 876.340 908.760
5 9.000 878.090 98.456
6 0.000 987.560