Inserting values from a DataFrame into another dataframe

Question

Ok, my real problem is bigger than this, but I have a simple working example.

>>> import pandas as pd
>>> import numpy as np
>>> a = pd.DataFrame(np.array([[2, 1990], [4,1991], [5,1992]]), \
...                  index=[1,2,3], columns=['var', 'yr'])
>>> a
   var    yr
1    2  1990
2    4  1991
3    5  1992
>>> b = pd.DataFrame(index=a.index, columns=['new_var'])
>>> b
  new_var
1     NaN
2     NaN
3     NaN
>>> b[a.yr<1992].loc[:, 'new_var'] = a[a.yr<1992].loc[:, 'var']
>>> b
  new_var
1     NaN
2     NaN
3     NaN

I desire the following output:

>>> b
  new_var
1       2
2       4
3     NaN

suku · Accepted Answer · 2018-09-30 16:06:30Z

3

With that filtering stuff, you're creating a copy of a slice, and thus it won't assign.

Do this instead:

b.loc[a.yr<1992, 'new_var'] = a['var']

edited Sep 30, 2018 at 16:06

suku

11k16 gold badges80 silver badges126 bronze badges

answered Jan 26, 2017 at 18:24

cadolphs

9,7231 gold badge29 silver badges45 bronze badges

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

2 Comments

piRSquared Over a year ago

This is a good answer! However, the slice on a is unnecessary. This will suffice b.loc[a.yr<1992, 'new_var'] = a['var'] pandas will handle the alignment for you. +1 from me.

cadolphs Over a year ago

Cool. Yeah, Pandas seems to be pretty good at being reasonably concise.

piRSquared · Accepted Answer · 2017-01-26 19:25:02Z

1

you can also use assign + query to add intuitiveness

b.assign(new_var=a.query('yr < 1992')['var'])

   new_var
1      2.0
2      4.0
3      NaN

This returns the dataframe you'd want. You'll have to assign it back to b if you want it to persist.

answered Jan 26, 2017 at 19:25

piRSquared

295k68 gold badges509 silver badges654 bronze badges

2 Comments

MaxU - stand with Ukraine Over a year ago

this is rather unusual use case for assign + query ;-)

piRSquared Over a year ago

@MaxU I'm always trying to push on the edges.

MaxU - stand with Ukraine · Accepted Answer · 2017-01-26 21:31:08Z

0

yet another "creative" solution:

In [181]: b['new_var'] = np.where(a.yr < 1992, a['var'], b['new_var'])

In [182]: b
Out[182]:
  new_var
1       2
2       4
3     NaN

answered Jan 26, 2017 at 21:31

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Collectives™ on Stack Overflow

Inserting values from a DataFrame into another dataframe

3 Answers 3

2 Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Related