Split Pandas Column of type String using fixed width (similar to Excel text-to-columns functionality with fixed width)

Question

I have a dataframe of CCYPair and corresponding spot values similar to the below:

Current Dateframe:

d = {'CCYPair': ['EURUSD', 'USDJPY'], 'Spot': [1.2, 109]}
df = pd.DataFrame(data=d)

I am looking to split the CCYPair column into CCY1 and CCY2. This would be easily achieved in Excel using Text-to-columns or through Left and Right functions. However, even after searching for a while, I am finding it quite tricky to achieve the same result in a pandas dataframe.

I could only find pandas.read_fwf but that is for reading from a file. I already have a dataframe and am looking to split one of the columns based on fixed width.

I am sure I am missing something basic here - just can't figure out what.

I have tried df['CCY1'] = df['CCYPair'][0:3] But that applies the [0:3] on the column and not each entry within the column. So I end up getting the first three CCYPair values and then NaNs.

Expected outcome:

d = {'CCY1': ['EUR', 'USD'], 'CCY2': ['USD', 'JPY'], 'Spot': [1.2, 109]}
df = pd.DataFrame(data=d)

Answer 1

You can try extract:

df[['CCY1','CCY2']] = df.CCYPair.str.extract('(.{3})(.*)')

Output:

  CCYPair   Spot CCY1 CCY2
0  EURUSD    1.2  EUR  USD
1  USDJPY  109.0  USD  JPY

Answer 2

You can also use str.slice method:

df['CCY1'] = df['CCYPair'].str.slice(stop=3)
df['CCY2'] = df['CCYPair'].str.slice(start=3)

Output:

    CCYPair   Spot  CCY1  CCY2
0    EURUSD    1.2   EUR   USD
1    USDJPY  109.0   USD   JPY