Python - Get string from csv and insert into another new column

Question

I have a dataframe like this "lot a kitt program [ID-55321]". i want to get [ID-555321] to another column and remove it from the existing column.

Example:

My code is like this.

import panda as df
df=pd.read_csv('C:\\Documents\\test.csv')
df["ID"] = df["short_description"].str.split("[")
print(df)

my output should be like this

lot a kitt program|[ID-555321]

Thanks.

what is the problem? didn't working: is useless information. Don't expect that we will run code to see problem. Do you get error message? Show it in question (not in comments) as text (not image). Do you get wrong result? Show wrong result and explain what is wrong. — furas
– furas, Commented Jun 27, 2022 at 3:26
maybe use split("[GFIT") and later add back "GFIT". Or maybe use regex to get it. — furas
– furas, Commented Jun 27, 2022 at 3:29

Utkonos · Accepted Answer · 2022-06-27 04:03:18Z

Rather than using pandas, I'm just using pure Python. I also had to hand enter all your data since it was in an image. Next time, please provide the CSV itself.

Here is your starting data:

"request_item.number","short_description"
"RITM1131122","System [GFIT-12345]"
"RITM1131395","Precision [3561]"
"RITM1090742","lot at kitting program [GFIT-54321]"

With that saved to a file called data.csv, the following code will extract the IDs that you're looking to extract and save the output as a list.

import csv
import json
import re

id_re = re.compile('\[(?P<id>GFIT-\d+?)\]')

output = list()

with open('data.csv') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        if match := re.search(id_re, row['short_description']):
            id_string = match.group(0)
            found_id = match.group('id')
            row['short_description'] = row['short_description'].replace(id_string, '').strip()
            row['ID'] = found_id
        else:
            row['ID'] = str()
        output.append(row)

print(json.dumps(output, sort_keys=True, indent=4))

Output:

[
    {
        "ID": "GFIT-12345",
        "request_item.number": "RITM1131122",
        "short_description": "System"
    },
    {
        "ID": "",
        "request_item.number": "RITM1131395",
        "short_description": "Precision [3561]"
    },
    {
        "ID": "GFIT-54321",
        "request_item.number": "RITM1090742",
        "short_description": "lot at kitting program"
    }
]

Now to write that back to a new CSV file named new.csv.

with open('new.csv', 'w') as csvfile:
    fieldnames = output[0].keys()
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

    writer.writeheader()
    for entry in output:
        writer.writerow(entry)

Here is the contents of the new CSV:

request_item.number,short_description,ID
RITM1131122,System,GFIT-12345
RITM1131395,Precision [3561],
RITM1090742,lot at kitting program,GFIT-54321

Collectives™ on Stack Overflow

Python - Get string from csv and insert into another new column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related