I'm looking at a dataset where there's "City" columns and values in it are formatted like, for example, Entity["City", {"Hefei", "Anhui", "China"}]. I want to clean up this column so that only the name of the city remains. In the case of the above example, I want everything removed except for Hefei. I'm trying to use rstrip and lstrip with regex. Even though my regex seems to be correct based on online regex debugger websites, but the code doesn't work. Here's the code I'm using:
df['City'] = df['City'].map(lambda x: x.lstrip(r'(Entity["City", {")').rstrip(r'(",\s"\w+"}])'))
I want the result to be just Hefei. But the result that I'm getting is :
Hefei", "Anhui", "China
lstrip seems to be working, but rstrip only removes "}] and not the rest of the characters that I need removed.
I was hoping someone could tell where I'm making a mistake, or show me a better way to get this done.