Modify Pandas Multi-Index DataFrame Values using Pandas Series

Question

We have a multi-index DataFrame df

            0  1  2
Name Stock         
Tom  AAPL   0  0  0
     GOOG   0  0  0
     NFLX   0  0  0
John AAPL   0  0  0
     GOOG   0  0  0
     NFLX   0  0  0

and a Series s

AAPL    99
NFLX    11
dtype: int64

Question: How can we set the values in column 2 of the dataframe df using values from the series s?

In other words, only the values for index ('Tom', 'AAPL') and ('Tom', 'NFLX) in dataframe df should be set to 99 and 11, respectively. ('Tom', 'GOOG') should remain unchanged.

Failed Attempt

idx = pd.IndexSlice
df.loc[idx['Tom', :], 2] = s
print(df)

            0  1    2
Name Stock           
Tom  AAPL   0  0  NaN
     GOOG   0  0  NaN
     NFLX   0  0  NaN
John AAPL   0  0  0.0
     GOOG   0  0  0.0
     NFLX   0  0  0.0

Code to Reproduce Problem

stocks = ['AAPL', 'GOOG', 'NFLX']
names = ['Tom', 'John']
midx = pd.MultiIndex.from_product([names, stocks], names=['Name','Stock'])
df = pd.DataFrame(index=midx)
for i in range(3):
    df[i] = [0,0,0,0,0,0]
print(df)

s = pd.Series([99, 11], index=['AAPL','NFLX'])
print('\n', s, '\n')

idx = pd.IndexSlice
df.loc[idx['Tom', :], 2] = s
print(df)

What about John, AAPL and John, NFLX, shouldn't these be changed as well? — Erfan
– Erfan, Commented Oct 14, 2019 at 9:06
Changes are only for Tom, which I attempted to specify using df.loc[idx['Tom', :], 2] = s — Nyxynyx
– Nyxynyx, Commented Oct 14, 2019 at 9:13

jezrael · Accepted Answer · 2019-10-14 09:24:29Z

2

Create MultiIndex in Series by MultiIndex.from_product with Series.reindex for same index like original df and then set values with Series.fillna:

s = pd.Series([99, 11], index=['AAPL','NFLX'])
s.index = pd.MultiIndex.from_product([['Tom'], s.index])
s = s.reindex(df.index)
print(s)
Name  Stock
Tom   AAPL     99.0
      GOOG      NaN
      NFLX     11.0
John  AAPL      NaN
      GOOG      NaN
      NFLX      NaN
dtype: float64

df[2] = s.fillna(df[2])
print(df)
            0  1     2
Name Stock            
Tom  AAPL   0  0  99.0
     GOOG   0  0   0.0
     NFLX   0  0  11.0
John AAPL   0  0   0.0
     GOOG   0  0   0.0
     NFLX   0  0   0.0

edited Oct 14, 2019 at 9:24

answered Oct 14, 2019 at 9:10

jezrael

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

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

2 Comments

Nyxynyx Over a year ago

Will this work if the original df has values other than 0?

jezrael Over a year ago

@Nyxynyx - Sure, because df[2] = s.fillna(df[2]) replace non matched values by original

Erfan · Accepted Answer · 2019-10-14 09:25:40Z

1

Using index.get_level_values with loc:

s = pd.Series(data=[99,11], index=['AAPL', 'NFLX'])
s.index = pd.MultiIndex.from_product([['Tom'], s.index]) # thanks Jezrael

idx1 = df.index.get_level_values('Name') == 'Tom'
idx2 = df.index.get_level_values('Stock').isin(s.index)

df.loc[idx1&idx2, 2] = s

Output

              0    1     2
Name Stock                
Tom  AAPL   0.0  0.0  99.0
     GOOG   0.0  0.0   0.0
     NFLX   0.0  0.0  11.0
John AAPL   0.0  0.0   0.0
     GOOG   0.0  0.0   0.0
     NFLX   0.0  0.0   0.0

edited Oct 14, 2019 at 9:25

answered Oct 14, 2019 at 9:20

Erfan

43.4k10 gold badges76 silver badges86 bronze badges

3 Comments

jezrael Over a year ago

hmmm, what about s = pd.Series([99, 11], index=['NFLX','AAPL']) ?

Erfan Over a year ago

Yes you're right, need to have matching indices. Edited @jezrael

jezrael Over a year ago

daily cap, not seen ;)

Dev Khadka · Accepted Answer · 2019-10-14 09:18:55Z

you can do it by setting "Name" column and using merge like below

df1 = pd.DataFrame([['Tom', 'AAPL', '0', '0', '0'], ['Tom', 'GOOG', '0', '0', '0'], ['Tom', 'NFLX', '0', '0', '0'], ['John', 'AAPL', '0', '0', '0'], ['John', 'GOOG', '0', '0', '0'], ['John', 'NFLX', '0', '0', '0']], columns=('Name', 'Stock', '0', '1', '2'))
df1.set_index(["Name", "Stock"], inplace=True)
df2 = pd.DataFrame([['AAPL', '99'], ['NFLX', '11']], columns=('Stock', 'Value'))

df2["Name"]="Tom"
df1.merge(df2, on=["Name", "Stock"], how="left").set_index(["Name", "Stock"])

Result

            0  1  2 Value
Name Stock               
Tom  AAPL   0  0  0    99
     GOOG   0  0  0   NaN
     NFLX   0  0  0    11
John AAPL   0  0  0   NaN
     GOOG   0  0  0   NaN
     NFLX   0  0  0   NaN

Collectives™ on Stack Overflow

Modify Pandas Multi-Index DataFrame Values using Pandas Series

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related