0

This is my df

"33, BUffalo New York"
"44, Charleston North Carolina "
], columns=['row'])

My intention is to split them by a comma followed by a space or just a space like this

33 Buffalo New York
44 Charleston North Carolina

My command is as follows:

df["row"].str.split("[,\s|\s]", n = 2, expand = True)

0   STD     City State
1   33      Buffalo New York
2   44      Charleston North Carolina
TiTo
  • 544
  • 5
  • 21
Echchama Nayak
  • 1,162
  • 2
  • 21
  • 38

1 Answers1

2

As explained in the pandas docs, your split command does what it should if you just remove the square brackets. This command works:

new_df = df["row"].str.split(",\s|\s", n=2, expand=True)

Note: if your cities have spaces in them, then this will fail. It works if the state has a space in it, because the n=3 ensures that exactly 3 columns result.
The only part that you are missing is to set the first row as the header. As answered here, you can use pandas' iloc command:

new_df.columns = new_df.iloc[0]
new_df = newdf[1:]
print (new_df)
# 0 STD        City            State
# 1  33     BUffalo         New York
# 2  44  Charleston  North Carolina
tiberius
  • 414
  • 2
  • 12