如何获取数据表列中每一行的data type

Hey there! Let's break down how to get the data type of each row in a column depending on the tool you're using—here are common solutions tailored to different platforms:

If you're working with a DataFrame in Pandas, checking each row's data type is straightforward with apply() methods. Here are two useful approaches:

• Basic native type check:
  This returns the Python data type name for every value in the column:

  import pandas as pd
  
  # Example DataFrame with mixed data types
  df = pd.DataFrame({
      'mixed_col': [42, 'apple', 3.14, True, None]
  })
  
  # Add a new column with each row's data type
  df['row_data_type'] = df['mixed_col'].apply(lambda x: type(x).__name__)
  print(df)
  

  The output will list types like int, str, float, bool, and NoneType.

• Granular type inference:
  For more specific type labels (great for messy/mixed data), use Pandas' built-in inference function:

  df['row_data_type'] = df['mixed_col'].apply(lambda x: pd.api.types.infer_dtype([x], skipna=False))
  print(df)
  

  This returns labels such as integer, string, float, boolean, or empty for null values.

SQL databases handle this differently depending on the platform. Here are examples for popular systems:

• PostgreSQL:
  Use the pg_typeof() function directly to get the data type of each value:

  SELECT 
      your_column_name,
      pg_typeof(your_column_name) AS row_data_type
  FROM your_table_name;
  

• MySQL:
  MySQL doesn't have a dedicated function for this, but you can use a CASE statement to approximate value types:

  SELECT 
      your_column_name,
      CASE
          WHEN your_column_name IS NULL THEN 'NULL'
          WHEN your_column_name REGEXP '^[0-9]+$' THEN 'INTEGER'
          WHEN your_column_name REGEXP '^[0-9]+\\.[0-9]+$' THEN 'FLOAT'
          WHEN your_column_name IN ('TRUE', 'FALSE', 'true', 'false') THEN 'BOOLEAN'
          ELSE 'STRING'
      END AS row_data_type
  FROM your_table_name;
  

  Note: This is a rough estimate and might not cover all edge cases (like numeric strings with leading zeros).

• SQL Server:
  If your column uses the sql_variant type, use SQL_VARIANT_PROPERTY() to get the base type:

  SELECT 
      your_column_name,
      SQL_VARIANT_PROPERTY(your_column_name, 'BaseType') AS row_data_type
  FROM your_table_name;
  

In Excel, you can use built-in functions to map each row's data type to a readable label:

• First, use the TYPE() function in a new column (e.g., cell B2) to get a numeric code:

  =TYPE(A2)
  

  The codes correspond to:

  • 1 = Number
  • 2 = Text
  • 4 = Logical (TRUE/FALSE)
  • 16 = Error
  • 64 = Array

• To convert the code to a friendly name, wrap it in SWITCH():

  =SWITCH(TYPE(A2), 1, "Number", 2, "Text", 4, "Logical", 16, "Error", 64, "Array", "Unknown")
  

内容的提问来源于stack exchange，提问作者swarupa kommalapati