How do I add certain elements from a column to another column?

Question

I started out with the dataframe below

    0
0   Alabama[edit]
1   Auburn (Auburn University)[1]
2   Florence (University of North Alabama)
3   Jacksonville (Jacksonville State University)[2]
4   Livingston (University of West Alabama)[2]

Then I cleaned it up to this

         State        RegionName
0        Alabama    
1        Auburn 
2        Florence   
3        Jacksonville   
4        Livingston

I am not sure how to move Auburn, Florence, Jacksonville and Livingston to RegionName as they are regions in Alabama. Also I need to apply the moving of regions(500+) to their respective states(50 states) for the rest of my data.

Below is how the data is mapped(I added in the type of each row on the side)

State-->Alaska  
Region->Fairbanks   
State-->Arizona 
Region->Flagstaff   
Region->Tempe   
Region->Tucson

Expected answer:

    State     RegionName
0  Alabama     Auburn
1  Alabama     Florence
2  Alabama     Jacksonville
3  Alabama     Livingston

how do you know which region is part of what state? Can you infer that from the data, somehow? — Roy2012, Jun 24 '20 at 14:56
do you have mapping of states and repective regions. if yes, can you give some sample data. — Narendra Prasath, Jun 24 '20 at 14:57
The data is added by state then all its respective regions going down the column. I could create a dictionary manually but there are about 567 rows in total. — yudhiesh, Jun 24 '20 at 14:58
Thanks. What data structure contains the mapping? Is it a dict? Another dataframe? Something else? — Roy2012, Jun 24 '20 at 15:11

score 0 · Answer 1 · answered Jun 24 '20 at 15:12

0

This is what I would do, starting with original data:

df['State'] = df[0].str.extract('(.*)\[edit\]').ffill()

df['RegionName'] = df[0].str.extract('(.*) \(')

df = df.dropna(subset=['RegionName'])

Output:

                                                 0    State    RegionName
1                    Auburn (Auburn University)[1]  Alabama        Auburn
2           Florence (University of North Alabama)  Alabama      Florence
3  Jacksonville (Jacksonville State University)[2]  Alabama  Jacksonville
4       Livingston (University of West Alabama)[2]  Alabama    Livingston

answered Jun 24 '20 at 15:12

Quang Hoang

146,074
10
56
74

Only problem is that there are other states below with their respective regions as well. – yudhiesh Jun 24 '20 at 15:14
This is a very common data set extracted from wikipedia. That said, the provided code should work. – Quang Hoang Jun 24 '20 at 15:15
Thank you for the help – yudhiesh Jun 24 '20 at 15:18

How do I add certain elements from a column to another column?

1 Answers1