Why does concat Series to DataFrame with index matching columns not work?

Question

I want to append a Series to a DataFrame where Series's index matches DataFrame's columns using pd.concat, but it gives me surprises:

df = pd.DataFrame(columns=['a', 'b'])
sr = pd.Series(data=[1,2], index=['a', 'b'], name=1)
pd.concat([df, sr], axis=0)
Out[11]: 
     a    b    0
a  NaN  NaN  1.0
b  NaN  NaN  2.0

What I expected is of course:

df.append(sr)
Out[14]: 
   a  b
1  1  2

It really surprises me that pd.concat is not index-columns aware. So is it true that if I want to concat a Series as a new row to a DF, then I can only use df.append instead?

@juanpa.arrivillaga didn't work either. As it turns out it has the same output as when axis=0. — Vim
– Vim, Commented Nov 3, 2017 at 7:15

jezrael · Accepted Answer · 2017-11-03 07:14:05Z

11

Need DataFrame from Series by to_frame and transpose:

a = pd.concat([df, sr.to_frame(1).T])
print (a)
   a  b
1  1  2

Detail:

print (sr.to_frame(1).T)
   a  b
1  1  2

Or use setting with enlargement:

df.loc[1] = sr
print (df)
   a  b
1  1  2

answered Nov 3, 2017 at 7:14

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mouwsy Over a year ago

I am surprised that the first code by jezrael is exactly the same to the code shown in the official pandas user guide on how to do this. To me it looks somewhat hacky, that's why I would strongly endorse to change pd.concat so that it is index-columns aware (as OP called it).

abcdehc · Accepted Answer · 2022-04-08 12:46:56Z

1

"df.loc[1] = sr" will drop the column if it isn't in df

df = pd.DataFrame(columns = ['a','b'])
sr = pd.Series({'a':1,'b':2,'c':3})
df.loc[1] = sr

df will be like:

   a  b
1  1  2

answered Apr 8, 2022 at 12:46

abcdehc

113 bronze badges

Collectives™ on Stack Overflow

Why does concat Series to DataFrame with index matching columns not work?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related