Splitting columns but not all new rows are created

Question

I have a csv that looks like this:

    FIPS     display_name               Value
    2013    "Aleutians East, (AK)"      172.9
    2016    "Aleutians West, (AK)"      172.2

I want to split a column into 2 columns. I tried this:

df['county','state'] = df['display_name'].str.split(', ',expand=True)

The output was this:

cnty_fips   display_name    Value   (county, state)
2013    "Aleutians East    172.9    "Aleutians East
2016    "Aleutians West    172.2    "Aleutians West

Not sure why the second half of the data is erased and not made into a new column. How can I get two new columns when I split a column where one gives the county and another the state?

score 0 · Answer 1 · answered Sep 30 '20 at 17:17

The issue here is that str.split returns two columns, but you are trying to assign that value to a single column. The following should produce the desired output.

temp = df.display_name.str.split(", ", expand=True)
temp.columns = ["county", "state"]

pd.concat([df.drop("display_name", axis=1), temp], axis=1)

   FIPS  value          county state
0  2013  172.9  Aleutians East  (AK)
1  2016  172.2  Aleutians West  (AK)

score 0 · Answer 2 · answered Sep 30 '20 at 18:12

When you split a column into two separate columns, you need to store them in two separate columns. In your case, your are storing it in columns, but not wrapping it into the dataframe.

df = pd.DataFrame({'Fips': [2013,2016], 
                   'display_name': ["Aleutians East, (AK)","Aleutians West, (AK)"],
                   'value': [172.9,172.2]})
df[['county','state']] = df['display_name'].str.split(',',expand=True)

Notice that the two columns are wrapped together into the dataframe as df[['county','state']], while you simply wrote it as df['county','state'].

The output looks like:

   Fips          display_name  value          county  state
0  2013  Aleutians East, (AK)  172.9  Aleutians East   (AK)
1  2016  Aleutians West, (AK)  172.2  Aleutians West   (AK)

If you want to delete the original column before separation

df = df.drop('display_name',axis=1)
print(df)
   Fips  value          county  state
0  2013  172.9  Aleutians East   (AK)
1  2016  172.2  Aleutians West   (AK)

Splitting columns but not all new rows are created

2 Answers2