German Company Registry IDs

Introduction

The function clean_de_handelsregisternummer() cleans a column containing German company registry id (handelsregisternummer) strings, and standardizes them in a given format. The function validate_de_handelsregisternummer() validates either a single handelsregisternummer strings, a column of handelsregisternummer strings or a DataFrame of handelsregisternummer strings, returning True if the value is valid, and False otherwise.

handelsregisternummer strings can be converted to the following formats via the output_format parameter:

  • compact: only number strings without any seperators or whitespace, like “Aachen HRA 11223”

  • standard: handelsregisternummer strings with proper whitespace in the proper places. Note that in the case of handelsregisternummer, the compact format is the same as the standard one.

Invalid parsing is handled with the errors parameter:

  • coerce (default): invalid parsing will be set to NaN

  • ignore: invalid parsing will return the input

  • raise: invalid parsing will raise an exception

The following sections demonstrate the functionality of clean_de_handelsregisternummer() and validate_de_handelsregisternummer().

An example dataset containing handelsregisternummer strings

[1]:

import pandas as pd
import numpy as np
df = pd.DataFrame(
    {
        "handelsregisternummer": [
            'Aachen HRA 11223',
            'Aachen HRC 44123',
            'BE 428759497',
            'BE431150351',
            "002 724 334",
            "hello",
            np.nan,
            "NULL",
        ],
        "address": [
            "123 Pine Ave.",
            "main st",
            "1234 west main heights 57033",
            "apt 1 789 s maple rd manhattan",
            "robie house, 789 north main street",
            "1111 S Figueroa St, Los Angeles, CA 90015",
            "(staples center) 1111 S Figueroa St, Los Angeles",
            "hello",
        ]
    }
)
df

[1]:
handelsregisternummer address
0 Aachen HRA 11223 123 Pine Ave.
1 Aachen HRC 44123 main st
2 BE 428759497 1234 west main heights 57033
3 BE431150351 apt 1 789 s maple rd manhattan
4 002 724 334 robie house, 789 north main street
5 hello 1111 S Figueroa St, Los Angeles, CA 90015
6 NaN (staples center) 1111 S Figueroa St, Los Angeles
7 NULL hello

1. Default clean_de_handelsregisternummer

By default, clean_de_handelsregisternummer will clean handelsregisternummer strings and output them in the standard format with proper separators.

[2]:

from dataprep.clean import clean_de_handelsregisternummer
clean_de_handelsregisternummer(df, column = "handelsregisternummer")

[2]:
handelsregisternummer address handelsregisternummer_clean
0 Aachen HRA 11223 123 Pine Ave. Aachen HRA 11223
1 Aachen HRC 44123 main st NaN
2 BE 428759497 1234 west main heights 57033 NaN
3 BE431150351 apt 1 789 s maple rd manhattan NaN
4 002 724 334 robie house, 789 north main street NaN
5 hello 1111 S Figueroa St, Los Angeles, CA 90015 NaN
6 NaN (staples center) 1111 S Figueroa St, Los Angeles NaN
7 NULL hello NaN

2. Output formats

This section demonstrates the output parameter.

standard (default)

[3]:

clean_de_handelsregisternummer(df, column = "handelsregisternummer", output_format="standard")

[3]:
handelsregisternummer address handelsregisternummer_clean
0 Aachen HRA 11223 123 Pine Ave. Aachen HRA 11223
1 Aachen HRC 44123 main st NaN
2 BE 428759497 1234 west main heights 57033 NaN
3 BE431150351 apt 1 789 s maple rd manhattan NaN
4 002 724 334 robie house, 789 north main street NaN
5 hello 1111 S Figueroa St, Los Angeles, CA 90015 NaN
6 NaN (staples center) 1111 S Figueroa St, Los Angeles NaN
7 NULL hello NaN

compact

[4]:

clean_de_handelsregisternummer(df, column = "handelsregisternummer", output_format="compact")

[4]:
handelsregisternummer address handelsregisternummer_clean
0 Aachen HRA 11223 123 Pine Ave. Aachen HRA 11223
1 Aachen HRC 44123 main st NaN
2 BE 428759497 1234 west main heights 57033 NaN
3 BE431150351 apt 1 789 s maple rd manhattan NaN
4 002 724 334 robie house, 789 north main street NaN
5 hello 1111 S Figueroa St, Los Angeles, CA 90015 NaN
6 NaN (staples center) 1111 S Figueroa St, Los Angeles NaN
7 NULL hello NaN

3. inplace parameter

This deletes the given column from the returned DataFrame. A new column containing cleaned handelsregisternummer strings is added with a title in the format "{original title}_clean".

[5]:

clean_de_handelsregisternummer(df, column="handelsregisternummer", inplace=True)

[5]:
handelsregisternummer_clean address
0 Aachen HRA 11223 123 Pine Ave.
1 NaN main st
2 NaN 1234 west main heights 57033
3 NaN apt 1 789 s maple rd manhattan
4 NaN robie house, 789 north main street
5 NaN 1111 S Figueroa St, Los Angeles, CA 90015
6 NaN (staples center) 1111 S Figueroa St, Los Angeles
7 NaN hello

4. errors parameter

coerce (default)

[6]:

clean_de_handelsregisternummer(df, "handelsregisternummer", errors="coerce")

[6]:
handelsregisternummer address handelsregisternummer_clean
0 Aachen HRA 11223 123 Pine Ave. Aachen HRA 11223
1 Aachen HRC 44123 main st NaN
2 BE 428759497 1234 west main heights 57033 NaN
3 BE431150351 apt 1 789 s maple rd manhattan NaN
4 002 724 334 robie house, 789 north main street NaN
5 hello 1111 S Figueroa St, Los Angeles, CA 90015 NaN
6 NaN (staples center) 1111 S Figueroa St, Los Angeles NaN
7 NULL hello NaN

ignore

[7]:

clean_de_handelsregisternummer(df, "handelsregisternummer", errors="ignore")

[7]:
handelsregisternummer address handelsregisternummer_clean
0 Aachen HRA 11223 123 Pine Ave. Aachen HRA 11223
1 Aachen HRC 44123 main st Aachen HRC 44123
2 BE 428759497 1234 west main heights 57033 BE 428759497
3 BE431150351 apt 1 789 s maple rd manhattan BE431150351
4 002 724 334 robie house, 789 north main street 002 724 334
5 hello 1111 S Figueroa St, Los Angeles, CA 90015 hello
6 NaN (staples center) 1111 S Figueroa St, Los Angeles NaN
7 NULL hello NaN

4. validate_de_handelsregisternummer()

validate_de_handelsregisternummer() returns True when the input is a valid handelsregisternummer. Otherwise it returns False.

The input of validate_de_handelsregisternummer() can be a string, a Pandas DataSeries, a Dask DataSeries, a Pandas DataFrame and a dask DataFrame.

When the input is a string, a Pandas DataSeries or a Dask DataSeries, user doesn’t need to specify a column name to be validated.

When the input is a Pandas DataFrame or a dask DataFrame, user can both specify or not specify a column name to be validated. If user specify the column name, validate_de_handelsregisternummer() only returns the validation result for the specified column. If user doesn’t specify the column name, validate_de_handelsregisternummer() returns the validation result for the whole DataFrame.

[8]:

from dataprep.clean import validate_de_handelsregisternummer
print(validate_de_handelsregisternummer('Aachen HRA 11223'))
print(validate_de_handelsregisternummer('Aachen HRC 44123'))
print(validate_de_handelsregisternummer('BE 428759497'))
print(validate_de_handelsregisternummer('BE431150351'))
print(validate_de_handelsregisternummer("004085616"))
print(validate_de_handelsregisternummer("hello"))
print(validate_de_handelsregisternummer(np.nan))
print(validate_de_handelsregisternummer("NULL"))

True
False
False
False
False
False
False
False

Series

[9]:

validate_de_handelsregisternummer(df["handelsregisternummer"])

[9]:

0     True
1    False
2    False
3    False
4    False
5    False
6    False
7    False
Name: handelsregisternummer, dtype: bool

DataFrame + Specify Column

[10]:

validate_de_handelsregisternummer(df, column="handelsregisternummer")

[10]:

0     True
1    False
2    False
3    False
4    False
5    False
6    False
7    False
Name: handelsregisternummer, dtype: bool

Only DataFrame

[11]:

validate_de_handelsregisternummer(df)

[11]:
handelsregisternummer address
0 True False
1 False False
2 False False
3 False False
4 False False
5 False False
6 False False
7 False False 
[ ]: