how to clip pandas for a multiple column in a data frame

Here is the df:

{'Type 1': {1: 123.0,
  2: 123.0,
  3: 123.0,
  4: 123.0,
  5: 123.0,
  6: 45.0,
  7: 45.0,
  8: 45.0,
  9: 45.0,
  10: 9.5,
  11: 9.5,
  12: 9.5,
  13: 2.34,
  14: 2.34,
  15: 2.34},
 'Type 2': {1: 0,
  2: 0,
  3: -90,
  4: -90,
  5: -90,
  6: -90,
  7: -90,
  8: -270,
  9: -270,
  10: -270,
  11: -270,
  12: 180,
  13: 180,
  14: 181,
  15: 181},
 'Type 3': {1: 0,
  2: 0,
  3: 0,
  4: 0,
  5: 55,
  6: 55,
  7: 55,
  8: 55,
  9: 55,
  10: 9,
  11: 9,
  12: 3,
  13: 3,
  14: 3,
  15: 3},
 'Type 4': {1: 5.0,
  2: 5.0,
  3: 5.0,
  4: 5.0,
  5: 10.0,
  6: 123.0,
  7: 12.0,
  8: 23.0,
  9: 16.0,
  10: 3.14,
  11: 0.0,
  12: 0.0,
  13: 0.0,
  14: 0.0,
  15: 18.0},
 'Type 5': {1: 65536,
  2: 65536,
  3: 65536,
  4: 65536,
  5: 78888888,
  6: 665,
  7: 665,
  8: 665,
  9: 665,
  10: 665,
  11: 665,
  12: 665,
  13: 665,
  14: 665,
  15: 665},
 'Type 6': {1: 3.4124,
  2: 3.4124,
  3: 3.4124,
  4: 3.4124,
  5: 3.4124,
  6: 3.4124,
  7: 3.4124,
  8: 3.4124,
  9: 3.4124,
  10: 3.4124,
  11: 3.4124,
  12: 3.4124,
  13: 3.4124,
  14: 3.4124,
  15: 3.4124},
 'Type 7': {1: 0,
  2: 0,
  3: 2,
  4: 2,
  5: 2,
  6: 1,
  7: 1,
  8: 1,
  9: 1,
  10: 10,
  11: 10,
  12: 9,
  13: 9,
  14: -5,
  15: -5},
 'Type 8': {1: 'convert the string to 0 and non-zero value to 1',
  2: 'convert the string to 0 and non-zero value to 1',
  3: 'convert the string to 0 and non-zero value to 1',
  4: 'convert the string to 0 and non-zero value to 1',
  5: 'convert the string to 0 and non-zero value to 1',
  6: 'convert the string to 0 and non-zero value to 1',
  7: 'convert the string to 0 and non-zero value to 1',
  8: 'convert the string to 0 and non-zero value to 1',
  9: 'convert the string to 0 and non-zero value to 1',
  10: 'convert the string to 0 and non-zero value to 1',
  11: 'convert the string to 0 and non-zero value to 1',
  12: 'convert the string to 0 and non-zero value to 1',
  13: 'convert the string to 0 and non-zero value to 1',
  14: 'convert the string to 0 and non-zero value to 1',
  15: 'convert the string to 0 and non-zero value to 1'},
 'Type 9': {1: 0,
  2: 0,
  3: 0,
  4: 0,
  5: 0,
  6: 1,
  7: 1,
  8: 0,
  9: 0,
  10: 8,
  11: 8,
  12: 0,
  13: 0,
  14: 45,
  15: 45}}

each column in the dataframe has a lower and an upper limit as mentioned in the below list

eg:

lower_limit = [3,-90,0,0,0,1,0,0,0]          #Type 1 lower limit is 3...
upper_limit = [100,90,50,100,65535,3,1,1,1]  #Type 1 upper limit is 100...

lower_limit = pd.Series(lower_limit)
upper_limit = pd.Series(upper_limit)

df.clip(lower_limit, upper_limit, axis = 1)

But this returns every element as nan

whereas the expected result is to clip each column based on the upper limit and lower limit mentioned in the list…

Using for loop, I was able to make the necessary change, but it was extremely slower when the size of df is huge

I understand clipping is the faster way to make the changes to df but it doesnt work as expected, I am doing some mistake in it and advice if any other alternative ways of clipping the columns in a faster way?

Answer

From documentation, lower and upper must be float or array-like, not Series.

You could do

lower_limit = [3,-90,0,0,0,1,0,'',0]          #Type 1 lower limit is 3...
upper_limit = [100,90,50,100,65535,3,1,'',1]  #Type 1 upper limit is 100...

df.clip(lower_limit, upper_limit, axis = 1)

but column Type 8 is as string so you’d get an empty column with clip, you can fix with

lower_limit = [3,-90,0,0,0,1,0,df['Type 8'].min(),0]
upper_limit = [100,90,50,100,65535,3,1,df['Type 8'].max(),1]

Advertisement

Answer