How to split one column data into multiple columns using sql query/Python?-CodePudding

My data is in the form given below

Number
ABCD0001
ABCD0002
ABCD0003
GHIJ768O
GHIJ7681
GHIJ7682
SEDFTH1
SEDFTH2
SEDFTH3

I want to split this data into multiple colunms using postgreSQl/python script?

The output data should be like

Number1   Number2   Number3
ABCD0001  GHIJ7680  SEDFTH1
ABCD0002  GHIJ7681  SEDFTH2

Can I do this using an postgreSQl query or via a python script?

CodePudding user response：

This is just a quick solution to your problem, i'm still learning python myself. So this code snippet could probaly be optimized alot. But it solves your problem.

import pandas as pd

number = ['ABCD0001','ABCD0002','ABCD0003','GHIJ768O','GHIJ7681','GHIJ7682','SEDFTH1','SEDFTH2','SEDFTH3']

def find_letters(list_of_str):
    abc_arr = []
    ghi_arr = []
    sed_arr = []
    for i in range(len(list_of_str)):
        text = number[i]
        if text.__contains__('ABC'):
            abc_arr.append(text)
        if text.__contains__('GHI'):
            ghi_arr.append(text)
        if text.__contains__('SED'):
            sed_arr.append(text)
    df = pd.DataFrame({'ABC':abc_arr, 'GHI':ghi_arr, 'SED':sed_arr})
    return df

This code give this output. Screenshot Of Output

Edit:

Just realized the first output you showed is prob a dataframe aswell, below code is how you would handle it if your data is from a df and not a list.

import pandas as pd

data = {'Numbers': ['ABCD0001','ABCD0002','ABCD0003','GHIJ768O','GHIJ7681','GHIJ7682','SEDFTH1','SEDFTH2','SEDFTH3']}
df = pd.DataFrame(data)
print(df)

def find_letters(list_of_str):
    abc_arr = []
    ghi_arr = []
    sed_arr = []
    list_of_str = list_of_str.values.tolist()
    for i in range(len(list_of_str)):
        text = list_of_str[i][0]
        if text.__contains__('ABC'):
            abc_arr.append(text)
        if text.__contains__('GHI'):
            ghi_arr.append(text)
        if text.__contains__('SED'):
            sed_arr.append(text)
    df = pd.DataFrame({'ABC':abc_arr, 'GHI':ghi_arr, 'SED':sed_arr})
    return df
                        
find_letters(df)

Which gives this output output