Replace column and extend index in DataFrame

user_10500689 0
Viewed 248

I have DataFrame x and I would like to replace one column with Series y

x = DataFrame([[1,2],[3,4]], columns=['C1','C2'], index=['a','b'])

   C1  C2
a   1   2
b   3   4

y = Series([5,6,7], index=['a','b','c'])

a    5
b    6
c    7

Simple replacement works fine but keeps original index of DataFrame

x['C1'] = y

   C1  C2
a   5   2
b   6   4

I need to have union of indeces of x and y. One solution would be to reindex before replacement

x = x.reindex(x.index.union(y.index), copy=False)
x['C1'] = y

   C1   C2
a   5  2.0
b   6  4.0
c   7  NaN

Is there simpler way?

pandas dataframe python
4 Answers

combine_first

Turn y into a DataFrame first with to_frame

y.to_frame('C1').combine_first(x)

   C1   C2
a   5  2.0
b   6  4.0
c   7  NaN

align and assign

Use align to... align the indices

x, y = x.align(y, axis=0)

x.assign(C1=y)

   C1   C2
a   5  2.0
b   6  4.0
c   7  NaN
user_23366540

Similar to your solution but more succinct, you use reindex, then assign:

res = x.reindex(x.index | y.index).assign(C1=y)

print(res)

   C1   C2
a   5  2.0
b   6  4.0
c   7  NaN
user_92095460

Your cat try use join:

pd.DataFrame(y,columns=['C1']).join(x[['C2']])

Output:

   C1  C2
a   5  2.0
b   6  4.0
c   7  NaN
user_83178300

You can use concat but you will have to fix the column names, i.e.

import pandas as pd
pd.concat([x.loc[:, 'C2'], y], axis = 1)

which gives,

    C2  0
a  2.0  5
b  4.0  6
c  NaN  7
user_56355800
Related