Column pivoter

column_pivoter.py

# You: 
# - I have two columns of a pandas table.
# One column is id. the second is billing_branch, a list stored as a "#;" separated string.
# Show me pandas code to transform this into a two column table with id and billing_branch, with the list pivoted into one value per row.


import pandas as pd

# Sample DataFrame
data = {
    'id': [1, 2, 3],
    'billing_branch': ['branch1#;branch2#;branch3', 'branch4#;branch5', 'branch6']
}
df = pd.DataFrame(data)

# Split and explode in one step
df_exploded = df.assign(billing_branch=df['billing_branch'].str.split('#;')).explode('billing_branch').reset_index(drop=True).drop_duplicates()


"""
SQL version:

WITH SplitCTE AS (
    SELECT
        id,
        CAST('<x>' + REPLACE(billing_branch, '#;', '</x><x>') + '</x>' AS XML) AS BranchesXML
    FROM
        BillingBranches
),
ExplodedCTE AS (
    SELECT
        id,
        Branch.value('.', 'NVARCHAR(MAX)') AS billing_branch
    FROM
        SplitCTE
    CROSS APPLY
        BranchesXML.nodes('/x') AS Split(Branch)
)
SELECT
    id,
    billing_branch
FROM
    ExplodedCTE
ORDER BY
    id;


"""