2

Pandas 1.1.4

import pandas as pd
import numpy as np

mux = pd.MultiIndex.from_arrays([
    list('aaaabbbbbccddddd'),
    list('tuvwtuvwtuvwtuvw')
], names=['one', 'two'])

df = pd.DataFrame({'col': np.arange(len(mux))}, mux)
df["col2"] = 5    

print(df)

         col  col2
one two           
a   t      0     5
    u      1     5
    v      2     5
    w      3     5
b   t      4     5
    u      5     5
    v      6     5
    w      7     5
    t      8     5
c   u      9     5
    v     10     5
d   w     11     5
    t     12     5
    u     13     5
    v     14     5
    w     15     5

Now

df.loc[2:10, "col"] = 999

gives the expected result

one two           
a   t      0     5
    u      1     5
    v    999     5
    w    999     5
b   t    999     5
    u    999     5
    v    999     5
    w    999     5
    t    999     5
c   u    999     5
    v     10     5
d   w     11     5
    t     12     5
    u     13     5
    v     14     5
    w     15     5

but warns

FutureWarning: Slicing a positional slice with .loc is not supported, and will raise TypeError in a future version. Use .loc with labels or .iloc with positions instead. df.loc[2:10, "col"] = 999

Doing

df["col"].iloc[2:10] = 999

gives another (worse) warning:

SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy iloc._setitem_with_indexer(indexer, value)

How to do this correctly, while keeping the index?

I couldn't find this use case here

Gulzar
  • 23,452
  • 27
  • 113
  • 201
  • The warning indicates a possible solution, using `iloc`: ``df.iloc[2:10, 0] = 999`` – sammywemmy Dec 03 '20 at 19:32
  • @sammywemmy please see edit for why I can't do that. – Gulzar Dec 03 '20 at 20:13
  • 1
    `df.iloc[2:10, 0] = 999` does everything in one go. Why the need to select `col` before `iloc`? any particular reason? You could also try `df.loc[:, "col"].iloc[2:10]`; I just feel less is more. – sammywemmy Dec 03 '20 at 20:16
  • @sammywemmy `df.iloc[2:10, 0] = 999` relies on "col" being first, and the programmer knowing that. `df.loc[:, "col"].iloc[2:10]` gives `SettingWithCopyWarning` as stated in the edit. – Gulzar Dec 03 '20 at 20:37
  • can do the extremely ugly `df.iloc[2:10, np.where("col"==df.columns)[0]] = 999` but I was expecting a much cleaner standard appoach – Gulzar Dec 03 '20 at 20:39
  • How about getting the index location with `get_loc` : ``df.iloc[2:10, df.columns.get_loc("col")] = 999`` or ``df.loc(axis=1)["col"].iloc[2:10] = 999``. Not sure why you are getting the SettingWIthCopyWarning though – sammywemmy Dec 03 '20 at 21:13
  • @sammywemmy I ended up doing that. it makes little sense that I would have to do that. There must be a solution we don't know – Gulzar Dec 05 '20 at 13:35
  • Multiindex selection is primarily by labels; you are selecting by position. – sammywemmy Dec 05 '20 at 20:28

0 Answers0