[FIXED] CSV File-Pandas dataFrame column separation

Issue

I have this data set as such,

As you can see that 3rd column (Age at , start of presidency ) have been merged as well as 4th column(Age at, end of presidency) How can I separate them ? Thanks in advance.

Solution

If possible split by uppercase:

data = [{'Age atend of presidency': '65 years, 10 daysMar 4, 1797'}, 
        {'Age atend of presidency': '65 years, 10 daysMar 4, 1797'}
         ,{'Age atend of presidency': '65 years, 10 daysMar 4, 1797'}]

df = pd.DataFrame(data)

df[['age1','end2']] = df['Age atend of presidency'].str.split("([A-Z][^A-Z]*)", expand=True).iloc[:, :-1]
print (df)
            Age atend of presidency               age1         end2
0  65 years, 10 daysMar 4, 1797  65 years, 10 days  Mar 4, 1797
1  65 years, 10 daysMar 4, 1797  65 years, 10 days  Mar 4, 1797
2  65 years, 10 daysMar 4, 1797  65 years, 10 days  Mar 4, 1797

Or you can split by days:

df[['age1','end2']] = df['Age atend of presidency'].str.split("days", expand=True)
df['age1'] += 'days'
print (df)
        Age atend of presidency               age1         end2
0  65 years, 10 daysMar 4, 1797  65 years, 10 days  Mar 4, 1797
1  65 years, 10 daysMar 4, 1797  65 years, 10 days  Mar 4, 1797
2  65 years, 10 daysMar 4, 1797  65 years, 10 days  Mar 4, 1797

Or:

df[['age1','a', 'end2']] = df['Age atend of presidency'].str.split("(days)", expand=True)
df['age1'] += df.pop('a')

Answered By - jezrael