Is there a way to custom clean a row in Pandas?

user_15488129 0
Viewed 65

I'm new with Pandas and I'm trying to use it to clean a database that is composed of index, artwork title, and artwork dimension.

What I have is:

db1 = {'title' : ['121 art1 magic world 100x82 2000.jpg', '383 art2 fantastic comic 61x61 2017.jpg']}

What I need is

db2 = {'index': [121,383],
       'title' : ['art1 magic world', 'art2 fantastic comic'],
       'dimension': ['100x82','61x61']
       'year': [2000, 2017]

What I've unsuccessfully tried:

  • str.split(expand=True) method, on df = pd.DataFrame(dict) but I get stuck on the fact that the title is composed of numerous words.

  • .replace() method to clean df['title'] but I'm sure that that there is a best practice.

Could you please help? Thanks in advance.

pandas python regex
1 Answers

You can create a fun regex pattern along with .str.extract to extract the information you want like this:

df = pd.DataFrame(db1)
new_df = df["title"].str.extract(r"(?P<index>\d+)\s(?P<title>.*)\s(?P<dimension>\d+x\d+)\s(?P<year>\d+)\.\w+")

print(new_df)
  index                 title dimension  year
0   121      art1 magic world    100x82  2000
1   383  art2 fantastic comic     61x61  2017

The regex pattern works as follows:

  • (?P<index>\d+) Capture the digits at the beginning of the string. We'll store this in a capture group titled "index"
  • \s, after the digits find a whitespace character. We don't want to do anything with this character, so it won't be in a capture group.
  • (?P<title>.*) Next find ALL characters up until we bump into the next pattern:
  • \s(?P<dimension>\d+x\d+) the pattern here is any number of digits followed by an "x" followed by more digits. We store this string into a capture group called dimension. This string must also begin with a space that we need to be present, but ultimately ignore.
  • \s After we found the dimension, we expect another whitespace character that we do not do anything with.
  • (?P<year>\d+), our last capture group "year" just finds the last digits at near the end of our pattern.
  • \.\w+: the end of our pattern is a period "." followed by any combination of letters and/or numbers- this is to match ".jpg" but will also match any other file suffix.
user_142784480
Related