How to build new columns based on the unique initial letters from a dataframe pandas

user_10694247 0
Viewed 85

I have thousands of hostname, those i want to be assigned into different columns based on their first initial three letters. I see this can be done if its small list ans i know the initial letters but i have huge list.

I have google a lot but did not get any proper hint, tried df.assign but that's something not great fit.

Example hostname:

fox001
fox002
fox003
fox004
fox005
fox006
dbx001
dbx002
dbx003
dbx004
dbx005
dbx006
trd001
trd002
trd003
trd004
trd005
trd006
spl001
spl002
spl003
spl004
spl005
spl006

What is expected:

fox_host   db_host  trd_host spl_host (<-- column names)
fox001     dbx001   trd001   spl001
fox002     dbx002   trd002   spl002
fox003     dbx003   trd003   spl003
fox004     dbx004   trd004   spl004
fox005     dbx005   trd005   spl005
fox006     dbx006   trd006   spl006

my dataframe:

df = pd.read_csv('inventory_hostanme',header=None).rename( columns={ 0:"hostnames"})
print(df)

hostnames
fox001
fox002
fox003
fox004
fox005
fox006
dbx001
dbx002
dbx003
dbx004
dbx005
dbx006
trd001
trd002
trd003
trd004
trd005
trd006
spl001
spl002
spl003
spl004
spl005
spl006
pandas dataframe python-3.x linux
2 Answers

Use Series.groupby to group the column hostnames on the first three letters of the host value, then use pd.concat along axis=1 to concat each of the grouped dataframe creating a new dataframe with separate columns for each hosts:

hosts = pd.concat([
    g.rename(f'{k}_host').reset_index(drop=True)
    for k, g in df['hostnames'].groupby(df['hostnames'].str[:3])], axis=1)

Result:

# print(hosts)

  dbx_host fox_host spl_host trd_host
0   dbx001   fox001   spl001   trd001
1   dbx002   fox002   spl002   trd002
2   dbx003   fox003   spl003   trd003
3   dbx004   fox004   spl004   trd004
4   dbx005   fox005   spl005   trd005
5   dbx006   fox006   spl006   trd006
user_128331660

cumcount with .groupby of the first 3 characters in your column returns 0,1,2,3,4 for each group of letters. From there, pivot the dataframe and change the column headers:

df['a'] = df['hostnames'].str[0:3]
df['index'] = df.groupby(['a'])['a'].transform('cumcount')
df = df.pivot(values='hostnames', columns='index').apply(lambda x: pd.Series(x.dropna().values))
df.columns = df.iloc[0].str[0:3] + '_host'

output:

    dbx_host fox_host  spl_host     trd_host
0   dbx001   fox001    spl001       trd001
1   dbx002   fox002    spl002       trd002
2   dbx003   fox003    spl003       trd003
3   dbx004   fox004    spl004       trd004
4   dbx005   fox005    spl005       trd005
5   dbx006   fox006    spl006       trd006
user_63667700
Related