title: Best way to clean, adapt and validate data
body: <p>I'm wondering what the best approach is to handling incoming data that includes units along with the values. 
  The incoming data has a label and a value. The label indicates what type of data it is and the value contains both the value and the unit. So for instance I have a field with label 'elevation' and value '120 m' or value '363 ft'. Basically I need to do four things:</p>
  
  <ol>
  <li>check the unit to see if I need to convert to 'base unit' (if it includes ft, calculate it meters)</li>
  <li>clean the value to strip the unit (remove ' m' and possibly thousands separator and change decimal character (point vs comma) )  </li>
  <li>validate the data (right now I check to see if value is '-' as this should be 0, but more validation should be done)</li>
  <li>check whether to include the field or not. (if the field is present as column in the database)</li>
  </ol>
  
  <p>How I'm handling it now (not efficient/clear!) is Using the KeyError as the fields are not mandatory so could not exist. </p>
  
  <pre><code>import pandas as pd
  df = pd.DataFrame({'elevation': ['1.200 m']})
  try:
      if ' m' in df['elevation'].values[0]:
          df['elevation'] = df['elevation'].values[0].replace(' m', '').replace('.', '')
          if df['elevation'].values[0] == '-':
              df['elevation'] = int(df['elevation'].values[0].replace('-', '0'))
      elif ' ft' in df['elevation'].values[0]:
          df['elevation'] = df['elevation'].values[0].replace(' ft', '')
          if df['elevation'].values[0] == '-':
             df['elevation'] = int(df['elevation'].values[0].replace('-', '0'))
          df['elevation'] = round(float(df['elevation'].values[0]) * 0.3048, 2)
  
  except KeyError:
      pass
  
  print(df)
  </code></pre>
  
  <p>The check if the field needs to be included is done by retrieving the columns from the table and check if the dataframe columns are in that list:</p>
  
  <pre><code># retrieve column map
  query = """SELECT * from information_schema.columns WHERE table_name = 'table' """
  try:
      df_columns = pd.read_sql(query, con=engine)
      columns = df_columns['column_name']
  except Exception as e:
      print('query failed', e)
  dropped_columns=[]
  for i in df.columns:
      if columns.isin([i]).any():
           pass
      else:
         df.drop(i, 1, inplace=True)
         print("column dropped:", i)
         dropped_columns.append(i)
  df_dropped = pd.DataFrame({'dropped_column': dropped_columns})
  </code></pre>
  
  <p>Few things that are nasty here; checking on the unit for every field seems inefficient. However, if I generalize this check I could end up with false matches (' m/s' for some velocity field) and different fields will have different 'normal values' to validate on. </p>
  
  <p>I need to do this for many different fields so I'm looking at an efficient way of handling this. I know there are modules like Cerberus and valideer available that might help out here, but curious to see how others are handling this. 
  Would you make a data mapping of all possible fields with the expected values, units,  conversions and normal values and check and adapt based on this?</p>