How to move values from one dataframe to another in pandas?

Question

I have a df1 that looks like this:

           Symbol Order  Shares
Date                           
2009-01-14   AAPL   BUY     150
2009-01-21   AAPL  SELL     150
2009-01-21    IBM   BUY     400

And df2 looks like this:

           GOOG AAPL  XOM  IBM Cash
Date                               
2009-01-14  NaN  NaN  NaN  NaN  NaN
2009-01-21  NaN  NaN  NaN  NaN  NaN

I want to move the values in the first DF to the second so that the amount of shares populates under the appropriate stock symbol. So the above would look like:

           GOOG AAPL  XOM  IBM Cash
Date                               
2009-01-14  NaN  150  NaN  NaN  NaN
2009-01-21  NaN  -150 NaN  400  NaN

How would I move all values that I have in my first dataframe to the second dataframe?

Are you just looking to pivot or actually fill data in another dataframe?: df.pivot(None, 'Symbol', 'Shares') — It_is_Chris
– It_is_Chris, Commented Mar 7, 2019 at 20:28
@coldspeed that's a good point, there shoudl actually be only a single line for that in the second df. I am going to edit now . — Nerblo
– Nerblo, Commented Mar 7, 2019 at 20:30

cs95 · Accepted Answer · 2019-03-07 20:39:05Z

1

You really don't need df2 here. You can compute the result directly from df using some simple reshaping functions set_index, unstack and reindex. You just need the symbols list.

(df.assign(Shares=np.where(df.Order == 'BUY', df.Shares, -df.Shares))
   .drop('Order', 1)
   .set_index('Symbol', append=True)['Shares']
   .unstack(1)
   .reindex(df2.columns, axis=1))  # you can replace df2.columns with a list 

            GOOG   AAPL  XOM    IBM  Cash
Date                                     
2009-01-14   NaN  150.0  NaN    NaN   NaN
2009-01-21   NaN -150.0  NaN  400.0   NaN

answered Mar 7, 2019 at 20:39

cs95

406k106 gold badges744 silver badges798 bronze badges

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

Comments

It_is_Chris · Accepted Answer · 2019-03-07 20:39:07Z

1

Use np.select to convert numbers to negative if Order == 'SELL' then update

df['Shares'] = np.select([df['Order'] == 'SELL'], [-df['Shares']], df['Shares'])
df2.update(df.pivot(None, 'Symbol', 'Shares'))


            GOOG   AAPL  XOM    IBM  Cash
Date                                     
2009-01-14   NaN  150.0  NaN    NaN   NaN
2009-01-21   NaN -150.0  NaN  400.0   NaN

answered Mar 7, 2019 at 20:39

It_is_Chris

14.2k3 gold badges27 silver badges45 bronze badges

Comments

G.G · Accepted Answer · 2024-04-07 15:22:09Z

duckdb

df1.sql.select("*,case when \"order\"='BUY' then 1 else -1 end col1,col1*shares col2").set_alias("tb1").join(df2.sql.set_alias("tb2"),how="right",condition="tb1.date=tb2.date").select("tb1.date,case when Symbol='AAPL' then col2 end AAPL,case when Symbol='GOOG' then col2 end GOOG,case when Symbol='XOM' then col2 end XOM,case when Symbol='IBM' then col2 end IBM,case when Symbol='Cash' then col2 end Cash").aggregate("date,sum(AAPL) AAPL,sum(GOOG) GOOG,sum(XOM) XOM,sum(IBM) IBM,sum(Cash) Cash")

┌────────────┬────────┬────────┬────────┬────────┬────────┐
│    Date    │  AAPL  │  GOOG  │  XOM   │  IBM   │  Cash  │
│  varchar   │ int128 │ int128 │ int128 │ int128 │ int128 │
├────────────┼────────┼────────┼────────┼────────┼────────┤
│ 2009-01-21 │   -150 │   NULL │   NULL │    400 │   NULL │
│ 2009-01-14 │    150 │   NULL │   NULL │   NULL │   NULL │
└────────────┴────────┴────────┴────────┴────────┴────────┘

Collectives™ on Stack Overflow

How to move values from one dataframe to another in pandas?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related