Currently I have a data frame that looks like this below:

what I need to do is first edit the position column by adding a " " after the tens place so the first number should be 12345 67

I think I would have to first break up every number in the position, then measure the length, and finally add the " " sign by adding it the length of the value - 2?

Adding the " " sign will cause it to align left in excel so I need to make sure it is aligned right.

I tried using df = df.style.set_properties(subset=["position"], **{'text-align': 'right'}) but this doesn't work because it appears I need columns that have a similar name.

What would be another way to get both of these complete?

Thank you in advanced.

UPDATE

I was able to break up the position column to two columns and added a third column with the " " symbol. Then I combined all 3 new columns and replaced the position column. And lastly deleted the new columns using the following:

 df['col1']= df['position'].astype(str).str[:-2]
 df['col2'] = df['col'].str[:-2]
 df['col3'] = df['col'].str[-2:]
 df['col4'] = ' '
 df['position'] = df[['col1','col3','col2']].apply(lambda row: ''.join(row.values.astype(str)), axis=1)
 df = df.drop(["col", "col1", "col2", "col3", "col4"], axis=1)

The only thing left I need to do is be able to align the new value to the right because in excel it aligns left when I added the " " sign

CodePudding user response：

Excel by default aligns numerical values to the right and text values to the left. So you won't be able to make the change in pandas, but when you write to excel you can modify the alignment after saving using something like openpyxl see here for an example

another option if you want the cells to retain their numerical value would be to leave them as a number but format them to display with the ' '. You could do this by setting the number format to "# ##"