[Python] Best way to assign a scalar to a new DataFrame column with a specific dtype

I am writing a routine to load a large dataset into a Pandas DataFrame from a bespoke text format.

As part of this process, I need to add new columns to a DataFrame. Sometimes I need to broadcast a scalar value into the whole column, and I use DataFrame.assign(new_column=SCALAR_VALUE) for this.

This works fine, but I have no control over the exact dtype chosen for the newly-added column:

import pandas
df = pd.DataFrame({'a': ['foo', 'bar'], 'b': [3, 4], 'c': [1.1, 2.2]}).convert_dtypes()
print(df.dtypes) # a: 'string[python]', b: 'Int64', c: 'Float64'
print(df.assign(d=42).d.dtype) # 'int64'
print(df.assign(d=3.14).d.dtype) # 'float'
print(df.assign(d='baz').d.dtype) # 'object'
print(df.assign(d=pd.NA).d.dtype) # 'object'


For reasons of time- and memory-efficiency, I would like to choose the correct dtype at the time when I .assign the new column, rather than converting it afterwards with .astype.

The least-awkward way I have found to do this is to create a Series of the correct dtype and shape (an awkward operation in and of itself) and then .assign that to the DataFrame:

s = pd.Series('baz', dtype='string').repeat(df.shape[1]).reset_index(drop=True)
print(df.assign(d=s).d.dtype) # 'string'


If the DataFrame already has a non-default index, it gets even more awkward:

# Create a DataFrame with a string index 'a', Int64 column 'b', Float64 column 'c'
df = pd.DataFrame({'a': ['foo', 'bar'], 'b': [3, 4], 'c': [1.1, 2.2]}).convert_dtypes().set_index('a')

# Create a Series 'c' of type 'string' containing a repeated scalar value
s = pd.Series('baz', dtype='string').repeat(df.shape[1]).reset_index(drop=True)

# In order to assign this to the DataFrame correctly, I
# have to reset and then un-reset the index
df = df.reset_index().assign(d=s).set_index('a')


Finally, I have the DataFrame structured as desired, with the correct dtype for the new column, and the scalar value broadcast across all rows:

assert dict(df.reset_index().dtypes) == {'a': 'string[python]', 'b': 'Int64', 'c': 'Float64', 'd': 'string[python]'}
assert (df.d == 'baz').all()


So, the question: is there a less awkward and error-prone way to do this, to assign a scalar value with an explicit dtype to a new column in one fell swoop?

(Note that the solutions in the related question Adding a new column with specific dtype in pandas do not help with the scalar case.)

Continue reading...