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
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