I have a DataFrame with multiIndexed columns:
df = pd.DataFrame(np.arange(12).reshape(3,-1))
df.columns = [['A', 'A', 'B', 'B'], [0, 1, 0, 1]]
print(df)
That looks like this:
A B
0 1 0 1
0 0 1 2 3
1 4 5 6 7
2 8 9 10 11
I want to do something like df['C'] = df['B']
to get:
A B C
0 1 0 1 0 1
0 0 1 2 3 2 3
1 6 7 8 9 8 9
2 12 13 14 15 14 15
I get the ValueError: Wrong number of items passed 2, placement implies 1
.
I have also tried df.loc[:, slice('C', None)] = df['B']
with no luck.
Actually my case is a little more complicated: I have one more level in the multiindex (L0 I
) and I want to copy df['L0 I', 'B']
to df['L0 II', 'B']
(L0 II
doesn't exist).
You can extract column and create MultiIndex
in column:
df1 = df['B']
df1.columns = [['C'] * len(df1.columns), df1.columns]
print (df1)
C
0 1
0 2 3
1 6 7
2 10 11
Or select by slice and rename
first level:
df1 = df.loc[:, slice('B', None)].rename(columns={'B':'C'}, level=0)
print (df1)
C
0 1
0 2 3
1 6 7
2 10 11
And then join
or concat
together:
df = df.join(df1)
#alternative
#df = pd.concat([df,df1], axis=1)
print (df)
A B C
0 1 0 1 0 1
0 0 1 2 3 2 3
1 4 5 6 7 6 7
2 8 9 10 11 10 11
Another solution working here is use DataFrame.stack
, duplicate column with DataFrame.assign
and reshape back by DataFrame.unstack
:
df1 = df.stack().assign(C = lambda x: x['B']).unstack()
print (df1)
A B C
0 1 0 1 0 1
0 0 1 2 3 2 3
1 4 5 6 7 6 7
2 8 9 10 11 10 11