replace all the columns from import csv file using python

user_18164001 0
Viewed 36

How to replace all the series of column value separated by ; in the CSV file?

column A column B
       1       3;5
       4       3;7;11;14
       7       5;7;13;14

How to replace the column B by 3=HQ3, 5=HQT5,7=HTS7,11=HS11, 13=BST13 and 14=HFS14?

pandas python
1 Answers

You can split the column with .str.split + explode, replace the values with replace, and join it back together with groupby(level=0) + agg(list) + .str.join:

d = {
    '3': 'HQ3',
    '5': 'HQT5',
    '7': 'HTS7',
    '11': 'HS11',
    '13': 'BST13',
    '14': 'HFS14'
}

df['columnB'] = df['columnB'].str.split(';').explode().replace(d).groupby(level=0).agg(list).str.join(';')

Output:

>>> df
   columnA                columnB
0        1               HQ3;HQT5
1        4    HQ3;HTS7;HS11;HFS14
2        7  HQT5;HTS7;BST13;HFS14
Admin1
Related