Pandas version checks
-
[X] I have checked that this issue has not already been reported.
-
[X] I have confirmed this bug exists on the latest version of pandas.
-
[X] I have confirmed this bug exists on the main branch of pandas.
Reproducible Example
import pandas as pd
import numpy as np
ct_arr = np.array([[70, 150], [66, 81]])
counts = pd.DataFrame(
data=ct_arr,
columns=["Outstanding", "Not Outstanding"],
index=["Bank", "Credit Union"],
)
ctot = counts.copy()
# everything above this line is integers
ctot.loc["Total", :] = ctot.sum(axis=0)
ctot.loc[:, "Total"] = ctot.sum(axis=1)
print(ctot)
Issue Description
I generate a dataframe from an np.array() of integers. I want to calculate all the totals: by row, by column, and the grand total.
The code works, but the output is the float type.
Expected Behavior
Since all inputs are integers and I only add the numbers, I expected the output dataframe to also be only integers.
Installed Versions
Comment From: FlorinAndrei
This is the actual output:
Outstanding Not Outstanding Total
Bank 70.0 150.0 220.0
Credit Union 66.0 81.0 147.0
Total 136.0 231.0 367.0
Comment From: FlorinAndrei
And this is ctot before I do the sums:
Outstanding Not Outstanding
Bank 70 150
Credit Union 66 81
Comment From: simonjayhawkins
Thanks @FlorinAndrei for the report.
Note that the row expansion with a 2d indexer for .loc is causing the cast (the first total) and in the code sample this results in the floats in the row totals (second total and added column) and the DataFrame copy is also not relevant.
# single column DataFrame
df = pd.DataFrame([70, 150], dtype="i8")
df.loc["Total"] = df.sum(axis=0)
print(df.dtypes)
# 0 int64
# dtype: object
df = pd.DataFrame([70, 150], dtype="i8")
df.loc["Total", :] = df.sum(axis=0)
print(df.dtypes)
# 0 float64
# dtype: object
df = pd.DataFrame([70, 150], dtype="i8")
df.loc[:, "Total"] = df.sum(axis=1)
print(df.dtypes)
# 0 int64
# Total int64
# dtype: object
# # multi-column DataFrame
df = pd.DataFrame([[70, 150], [66, 81]], dtype="i8")
df.loc["Total"] = df.sum(axis=0)
print(df.dtypes)
# 0 int64
# 1 int64
# dtype: object
df = pd.DataFrame([[70, 150], [66, 81]], dtype="i8")
df.loc["Total", :] = df.sum(axis=0)
print(df.dtypes)
# 0 float64
# 1 float64
# dtype: object
df = pd.DataFrame([[70, 150], [66, 81]], dtype="i8")
df.loc[:, "Total"] = df.sum(axis=1)
print(df.dtypes)
# 0 int64
# 1 int64
# Total int64
# dtype: object
The result dtype from df.sum() is correct, so the title of this issue is misleading and the MRE could be reduced to
df = pd.DataFrame([70, 150], dtype="i8")
df.loc["Total", :] = pd.Series([220], dtype="i8")
df.dtypes
# 0 float64
# dtype: object
so it appears to be an indexing issue i.e. setting values with .loc with row expansion. (might be worth looking for similar issues)
contributions and PRs to fix welcome
Comment From: jreback
this is as expected and nothing to be done here
Comment From: mallikarjunamula
can you assign this issue to me?
Comment From: rhshadrach
@jreback - why should expanding an integer column with an integer value coerce to float?
Comment From: simonjayhawkins
@jreback - why should expanding an integer column with an integer value coerce to float?
In case the examples are not clear, does not coerce to float with 1d indexer
df = pd.DataFrame([70, 150], dtype="i8")
df.loc["Total"] = df.sum(axis=0)
print(df.dtypes)
# 0 int64
# dtype: object
does coerce to float with 2d indexer
df = pd.DataFrame([70, 150], dtype="i8")
df.loc["Total", :] = df.sum(axis=0)
print(df.dtypes)
# 0 float64
# dtype: object
and only happens with axis=0, i.e. adding rows.
looks like a bug to me. These should both produce the same output?
Comment From: phofl
Happens because of the reindex we are doing on the DataFrame before adding the values