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
  • 833
  • 2
  • 7
  • 28
Echchama Nayak
  • 971
  • 3
  • 23
  • 44

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
  • 430
  • 2
  • 14