How to treat as a single column whatever comes in parentheses in python

Question

I have a CSV file, which I am reading it with Pandas. There is a situation where I am splitting a single column into multiple column by space.

Example:

CSV:

ID  , Reference
101 , WHERE ID = 'TEST'
102 , WHERE ID IN (A,B,C)
103 , WHERE ID IN ("Hello World", "I AM NEW")

Output:

    ID  REF_1 REF_2 REF_3      REF_4   REF_5 REF_6 REF_7 REF_8   REF_9
0  101  WHERE    ID     =       TEST
1  102  WHERE    ID    IN  (A, B, C)
2  103  WHERE    ID    IN   ('Hello   World'   'I    AM   NEW   USER')

Desired output:

    ID  REF_1 REF_2 REF_3                             REF_4
0  101  WHERE    ID     =                              TEST
1  102  WHERE    ID    IN                         (A, B, C)
2  103  WHERE    ID    IN  ('Hello World', 'I AM NEW USER')

Answer 1

One quick workaround:

import pandas as pd

df = pd.DataFrame(
    {
        "ID": [101, 102, 103],
        "REF_1": ["WHERE", "WHERE", "WHERE"],
        "REF_2": ["ID", "ID", "ID"],
        "REF_3": ["=", "IN", "IN"],
        "REF_4": ["TEST", "(A, B, C)", "('Hello "],
        "REF_5": ["", "", "World'"],
        "REF_6": ["", "", "'I "],
        "REF_7": ["", "", "AM "],
        "REF_8": ["", "", "NEW "],
        "REF_9": ["", "", "USER')"],
    }
)

columns = ["REF_5", "REF_6", "REF_7", "REF_8", "REF_9"]

for column in columns:
    df["REF_4"] = df["REF_4"] + df[column]
df["REF_4"] = df["REF_4"].str.replace("''", "', '")

df.drop(columns=columns, inplace=True)

print(df)
# Outputs
    ID  REF_1 REF_2 REF_3                             REF_4
0  101  WHERE    ID     =                              TEST
1  102  WHERE    ID    IN                         (A, B, C)
2  103  WHERE    ID    IN  ('Hello World', 'I AM NEW USER')