Search code examples
pythonpandaspython-re

Replace a pattern in column of a csv file using Python

I am looking for some help with editing CSV in python. I have a file in below format

Account InstanceId InstanceName InstanceType Platform Status AvailabilityZone PrivateIpAddress publicIpAdress Owner
12345678901 i-0abcdef1234567890 Test123 t3a.micro windows stopped ap-southeast-2a 10.0.0.100 arn:aws:sts::12345678901:user/test-user
98765432109 i-0987654321abcdefg test345 t2.nano Linux running ap-southeast-2b 10.0.0.200 99.99.99.100 arn:aws:sts::98765432109:assumed-role/testrole/user@example.com
98765432109 i-abcdefghij01234899 test987 t2.nano windows running ap-southeast-2b 10.0.0.201

I would like to edit this file in place and and have the resulting file in below format

Account InstanceId InstanceName InstanceType Platform Status AvailabilityZone PrivateIpAddress publicIpAdress Owner
12345678901 i-0abcdef1234567890 Test123 t3a.micro windows stopped ap-southeast-2a 10.0.0.100 test-user
98765432109 i-0987654321abcdefg test345 t2.nano Linux running ap-southeast-2b 10.0.0.200 99.99.99.100 user@example.com
98765432109 i-abcdefghij01234899 test987 t2.nano windows running ap-southeast-2b 10.0.0.201

I tried a few things with no luck and would really appreciate if you can help me with this. Thanks in advance.

Solution

  • Use:

    df['Owner'] = df['Owner'].str.split('/').str[-1]

    Demonstration:

    df = pd.DataFrame({'Owner': ['arn:aws:sts::12345678901:user/test-user']})
df['Owner'].str.split('/').str[-1]

    Output:

    enter image description here