loc / iloc

import pandas as pd
import numpy as np

# 1) Sample frame
df = pd.DataFrame({
    'name':   ['mara', 'sam', 'alex', 'kim', 'lee'],
    'age':    [30, 22, 45, 18, 60],
    'city':   ['Sydney', 'Sydney', 'Melbourne', 'Brisbane', 'Sydney'],
    'salary': [95_000, 72_000, 110_000, 0, 0],
    'active': [True, True, False, True, False],
}, index=['e1', 'e2', 'e3', 'e4', 'e5'])

# 2) Column selection
df['name']                   # Series
df[['name', 'salary']]        # DataFrame (note the double brackets)
df.name                       # also works — but breaks for names like 'class'

# 3) Row selection by LABEL — .loc
df.loc['e3']                  # one row by index label  → Series
df.loc[['e1', 'e3']]          # multiple rows → DataFrame
df.loc['e2':'e4']              # INCLUSIVE on both ends with .loc

# 4) Row selection by POSITION — .iloc
df.iloc[0]                    # first row (position 0)
df.iloc[-1]                   # last row
df.iloc[1:3]                  # rows at positions 1, 2 (exclusive on stop, like Python)
df.iloc[[0, 2, 4]]

# 5) Rows + columns together
df.loc['e2':'e4', ['name', 'salary']]
df.iloc[1:4, [0, 3]]

# 6) Boolean masks — the workhorse
df[df['age'] >= 30]
df[(df['city'] == 'Sydney') & (df['salary'] > 70_000)]    # use & | ~  (NOT and / or / not)
df[df['city'].isin(['Sydney', 'Brisbane'])]
df[~df['active']]                                          # inactive rows
df[df['name'].str.startswith('m')]
df[df['salary'].between(50_000, 100_000)]

# Round parentheses around each comparison are MANDATORY because & has higher precedence than ==.

# 7) .loc with a mask — explicit and the recommended idiom for assignment
df.loc[df['salary'] == 0, 'active'] = False
df.loc[df['city'] == 'Sydney', ['salary']] *= 1.10        # 10% raise

# 8) .query — readable filter expressions
df.query('age >= 30 and city == \"Sydney\"')
df.query('salary > @cutoff', local_dict={'cutoff': 70_000})
df.query('name.str.startswith("m")', engine='python')

# 9) Missing values
df2 = pd.DataFrame({'a': [1, np.nan, 3], 'b': ['x', 'y', None]})
df2.dropna()                          # drop rows with any NaN
df2.dropna(subset=['a'])              # only check column 'a'
df2.fillna({'a': 0, 'b': ''})
df2['a'].isna() | df2['b'].isna()     # mask of any-na rows

# 10) at / iat — fast scalar access
df.at['e1', 'salary']                  # single cell by label
df.iat[0, 3]                            # single cell by position
df.at['e1', 'salary'] = 99_000          # fast assignment

# 11) Setting WITHOUT chained indexing
# ❌ Bad: chained — Pandas can't tell if you wanted a copy or the original
subset = df[df['city'] == 'Sydney']
subset['salary'] = 0                   # SettingWithCopyWarning; original may or may not change

# ✓ Good: single .loc on the original
df.loc[df['city'] == 'Sydney', 'salary'] = 0

# When you actually want a separate frame
subset = df[df['city'] == 'Sydney'].copy()
subset['salary'] = 0                   # safe — explicit copy

# 12) Index manipulation
df.reset_index(drop=False)             # 'e1' etc. becomes a column
df.set_index('name')                    # name becomes the index
df.sort_index()                         # sort by index labels

# 13) MultiIndex selection
mi = df.set_index(['city', 'name']).sort_index()
mi.loc['Sydney']                         # all Sydney rows
mi.loc[('Sydney', 'mara')]               # one row
mi.loc[('Sydney', slice(None)), :]        # cross-section
mi.xs('Sydney', level='city')             # explicit cross-section

# 14) Where vs mask
df['salary'].where(df['active'])         # NaN where condition is False
df['salary'].mask(df['active'])          # NaN where condition is True

# 15) Sample, head, tail
df.head(3)
df.tail(2)
df.sample(n=2, random_state=42)         # reproducible sample

# 16) Performance tips
#   • Vectorize: df['a'] + df['b'] beats a Python for-loop by orders of magnitude
#   • Avoid .iterrows() and .apply() unless you really need them
#   • Prefer .loc / .iloc over chained slices for assignment
#   • For huge frames, use categorical dtype on low-cardinality string columns
#   • Index for repeated lookups: df.set_index('col') + df.loc[key]

# 17) Common bugs
#   • df[df.age > 30] AND df.city == 'X' (Python and) → ValueError; use parens + &
#   • Using df.col when col contains spaces — switch to df['col']
#   • df.loc on integer index that LOOKS positional — .loc is label-based always
#   • Chained indexing with SettingWithCopyWarning — silent partial updates; use single .loc
#   • .iloc with a label key — KeyError or wrong row; mismatched .loc vs .iloc semantics
#   • Forgetting .copy() when you'll mutate — accidental writes to the source frame

import pandas as pd
df = pd.read_csv('users.csv')
print(df.loc[0, 'name'])         # label-based
print(df.iloc[0, 1])             # position-based
print(df.loc[df['age'] > 30, ['name', 'age']])