How to round values in a column of sets within a pandas dataframe?-CodePudding

A confidence interval calculation returns two numbers inside a set of brackets, which I put into a dataframe. From this point, I need to round down the values, and put the output into an excel file.

In the below sample code, how can I round down the values in Col1, to 2 decimals.

import pandas as pd
import xlwings

# example data frame
inp = [{'Id': 0, 'Col1': (1.245987, 3.12345), 'Col2': 9},
       {'Id': 1, 'Col1': (1.3386, 3.19826), 'Col2': 9},
       {'Id': 2, 'Col1': (1.4673, 3.23462), 'Col2': 10},
       {'Id': 3, 'Col1': (1.562624, 3.32546), 'Col2': 10}, 
       {'Id': 4, 'Col1': (1.2573, 3.436537), 'Col2': 11},
       {'Id': 5, 'Col1': (1.273883, 3.58924), 'Col2': 12}
       ]
df = pd.DataFrame(inp)


# round down col1 values
### df["Col1"] = df["Col1"].round(2)    # doesn't work

df["Col1"] = df["Col1"].astype(str)       # without this, values not exported by xlwings

book = xlwings.Book(r'C:\Users\Dyer01\Documents\Mappe1.xlsx')
sheet = book.sheets["Tabelle1"]
sheet.range("c3").options(header=False, index=False).value = df

What I need is:

   Id      Col1        Col2
0   0  (1.25, 3.12)     9
1   1  (1.34, 3.20)     9
2   2  (1.47, 3.23)    10
3   3  (1.56, 3.33)    10
4   4  (1.26, 3.44)    11
5   5  (1.27, 3.60)    12

CodePudding user response：

You can use panda.apply on column Col1 and round(num, 2) for rounding nums to 2 decimals.

df['Col1'] = df['Col1'].apply(lambda x : tuple([round(x[0], 2), round(x[1], 2)]))
print(df)

   Id          Col1  Col2
0   0  (1.25, 3.12)     9
1   1   (1.34, 3.2)     9
2   2  (1.47, 3.23)    10
3   3  (1.56, 3.33)    10
4   4  (1.26, 3.44)    11
5   5  (1.27, 3.59)    12

If you want to add zero after rounding you can use f-string.

>>> df['Col1'].apply(lambda x : f'({round(x[0], 2):.2f}, {round(x[1], 2):.2f})')

0    (1.25, 3.12)
1    (1.34, 3.20)
2    (1.47, 3.23)
3    (1.56, 3.33)
4    (1.26, 3.44)
5    (1.27, 3.59)
Name: Col1, dtype: object

CodePudding user response：

We can utilize numpy np.round function to do that, first we round it with certain decimal point which in this case we set it to 2 then we convert them back into a tuple.

import numpy as np

df['Col1'] = df['Col1'].apply(lambda r: np.round(r,decimals=2))
df['Col1'] = tuple(map(tuple,df['Col1']))

Output:

    Id     Col1       Col2
0   0   (1.25, 3.12)    9
1   1   (1.34, 3.2)     9
2   2   (1.47, 3.23)    10
3   3   (1.56, 3.33)    10
4   4   (1.26, 3.44)    11
5   5   (1.27, 3.59)    12