按照特定模式从列中提取字符串 [英] Extract string from column following a specific pattern
问题描述
请原谅我的熊猫新手问题，但我有一列美国城镇和州，例如下面所示的截短的版本(出于某些奇怪的原因，该列的名称称为阿拉巴马州[edit]"，该列与列中的前0-7个城镇值):
Please forgive my panda newbie question, but I have a column of U.S. towns and states, such as the truncated version shown below (For some strange reason, the name of the column is called 'Alabama[edit]' which is associated with the first 0-7 town values in the column):
0 Auburn (Auburn University)[1]
1 Florence (University of North Alabama)
2 Jacksonville (Jacksonville State University)[2]
3 Livingston (University of West Alabama)[2]
4 Montevallo (University of Montevallo)[2]
5 Troy (Troy University)[2]
6 Tuscaloosa (University of Alabama, Stillman Co...
7 Tuskegee (Tuskegee University)[5]
8 Alaska[edit]
9 Fairbanks (University of Alaska Fairbanks)[2]
10 Arizona[edit]
11 Flagstaff (Northern Arizona University)[6]
12 Tempe (Arizona State University)
13 Tucson (University of Arizona)
14 Arkansas[edit]
15 Arkadelphia (Henderson State University, Ouach...
16 Conway (Central Baptist College, Hendrix Colle...
17 Fayetteville (University of Arkansas)[7]
18 Jonesboro (Arkansas State University)[8]
19 Magnolia (Southern Arkansas University)[2]
20 Monticello (University of Arkansas at Monticel...
21 Russellville (Arkansas Tech University)[2]
22 Searcy (Harding University)[5]
23 California[edit]
每个州的城镇均在每个州名称下方，例如费尔班克斯(列值9)是阿拉斯加州的一个镇.
The towns that are in each state are below each state name, e.g. Fairbanks (column value 9) is a town in the state of Alaska.
我想要做的是根据州名拆分城镇名称，这样我就有两列州"和地区名称"，其中每个州名称与每个城镇名称相关联，如下所示:
What I want to do is to split up the town names based on the state names so that I have two columns 'State' and 'RegionName' where each state name is associated with each town name, like so:
RegionName State
0 Auburn (Auburn University)[1] Alabama
1 Florence (University of North Alabama) Alabama
2 Jacksonville (Jacksonville State University)[2] Alabama
3 Livingston (University of West Alabama)[2] Alabama
4 Montevallo (University of Montevallo)[2] Alabama
5 Troy (Troy University)[2] Alabama
6 Tuscaloosa (University of Alabama, Stillman Co... Alabama
7 Tuskegee (Tuskegee University)[5] Alabama
8 Fairbanks (University of Alaska Fairbanks)[2] Alaska
9 Flagstaff (Northern Arizona University)[6] Arizona
10 Tempe (Arizona State University) Arizona
11 Tucson (University of Arizona) Arizona
12 Arkadelphia (Henderson State University, Ouach... Arkansas
. . .等等.
我知道每个州名后面都有一个字符串"[edit]"，我认为我可以用它来分割和分配城镇名称.但是我不知道该怎么做.
I know that each state name is followed by a string '[edit]', which I assume I can use to do the split and assignment of the town names. But I don't know how to do this.
此外，我知道我还需要清除许多其他数据，例如删除括号内和方括号[[]]中的字符串.以后可以做...重要的部分是将州和镇分开，并将每个镇分配给其适当的美国.我们将不胜感激任何建议.
Also, I know that there's a lot of other data cleaning I need to do, such as removing the strings within parentheses and within the brackets '[]'. That can be done later...the important part is splitting up the states and towns and assigning each town to its proper U.S. Any advice would be most appreciated.
推荐答案
在没有太多上下文或对您的数据的访问的情况下，我建议按照以下方式进行操作.首先，修改读取数据的代码:
Without much context or access to your data, I'd suggest something along these lines. First, modify the code that reads your data:
df = pd.read_csv(..., header=None, names=['RegionName'])
# add header=False so as to read the first row as data
现在，使用str.extract提取状态名称，这仅应提取名称，只要该名称后接子字符串"[edit]"即可.然后，您可以使用ffill转发填充所有NaN值.
Now, extract the state name using str.extract, this should only extract names as long as they are succeeded by the substring "[edit]". You can then forward fill all NaN values using ffill.
df['State'] = df['RegionName'].str.extract(
r'(?P<State>.*)(?=\s*\[edit\])'
).ffill()
这篇关于按照特定模式从列中提取字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
