I'd like to broadcast or expand a dataframe columns-wise from a smaller set index to a larger set index based on a mapping specification. I have the following example, please accept small mistakes as this is untested

import pandas as pd

# my broadcasting mapper spec
mapper = pd.Series(data=['a', 'b', 'c'], index=[1, 2, 2])

# my data
df = pd.DataFrame(data={1: [3, 4], 2: [5, 6]})
print(df)
#    1  2
# --------
# 0  3  5 
# 1  4  6

# and I would like to get 
df2 = ...
print(df2)
#    a  b  c 
# -----------
# 0  3  5  5 
# 1  4  6  6

Simply mapping the columns will not work as there are duplicates, I would like to instead expand to the new values as defined in mapper:

# this will of course not work => raises InvalidIndexError
df.columns = df.columns.as_series().map(mapper) 

A naive approach would just iterate the spec ...

df2 = pd.DataFrame(index=df.index)
for i, v in df.iteritems():
    df2[v] = df[i]

CodePudding user response：

Use reindex and set_axis:

out = df.reindex(columns=mapper.index).set_axis(mapper, axis=1)

Output:

   a  b  c
0  3  5  5
1  4  6  6

CodePudding user response：

You can use pd.concat df.get:

pd.concat({v:df.get(k) for k,v in mapper.items()},axis=1)

   a  b  c
0  3  5  5
1  4  6  6