Reputation: 25
New to python and pandas and trying to figure this out.
I'm dealing with a data set that's pretty messy. There are 500 rows and 9 columns. In a few instances, data that should be in coulmn 9 has been indexed into column 8, along with column 8 data.
... Col 8 Col 9
0 2 weeks No. 13
1 1 week No. 2
2 12 weeks, No 1
3 15 weeks No. 8
4 7 weeks, No. 1
How can I separate the data and move to the proper column?
I applied a split(), but don't know how to move it over. I'm thinking I need to use the apply(), but not sure on how.
Any suggestions?
Upvotes: 2
Views: 36
Reputation: 41487
You can split()
with expand=True
, then fillna()
to fill the missing values:
df[['Col 8', 'Col 9']] = df['Col 8'].str.split(',', expand=True).fillna({1: df['Col 9']})
# Col 8 Col 9
# 0 2 weeks No. 13
# 1 1 week No. 2
# 2 12 weeks No 1
# 3 15 weeks No. 8
# 4 7 weeks No. 1
Upvotes: 1