-1

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

name value position length
table 5.0 1234567 .25
chair 8.0 789012 5
couch 6.0 345678 5
bed 5.3 1901234 .05

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

rcheeks23
  • 15
  • 5
  • What's `tens place`? What's the `dtype`? int or string? – m02ph3u5 Oct 15 '21 at 20:10
  • Tens place is the place value to the number under the position columns, so the first value 1234567, then 6 is in the tens place and the symbol would be between 5 and 6. So the next value I would need to insert the symbol between 0 and 1. the dtype is int as is but adding the "+" symbol would change it to a string i believe? – rcheeks23 Oct 15 '21 at 20:17

1 Answers1

0

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 "#+##"

see here for number format codes documentation

Dara Dadachanji
  • 16
  • 3