CODEWITHSUNDEEP
pythonpandasdataframemulti-index
chad jensen
chad jensen

Reputation: 95

how to assign a value to the first row of every level zero group in pandas multiindex

I am trying to assign values to the first row of every level zero group in a multiindex dataframe. The first rows are dates and times and are not a common value from row to row. I have attached a sample of the code to reproduce a minimal dataframe.

import pandas as pd
import numpy as np

np.random.seed(123)

arrays = [np.array(["bar", "bar", "baz", "baz", "foo", "foo", "qux", "qux"]),np.array(["one", "two", "not_one", "two", "not_one_one", "two", "not_not_one", "two"]),]
df = pd.DataFrame(np.random.randn(8,4), index=arrays)

I have tried assigning values with and without the columns index.

df.loc[[df.groupby(level=0).nth(0)],'0'] = 100

xy = list(set(df.index.get_level_values(level=0)))
for ind1 in xy:
    df.loc[(ind1, df.iloc[0]),'0'] = 100

I have tried about 100 different variations of iloc with no luck, I have tried xs

df.groupby(level=0).xs(0, level=1) = 100

I have tried

df.loc[df.groupby(level=0).nth(0)] = 100

Also,

df.groupby(level=0).nth(0) = 100

I have spent several hours on this and am still not getting anywhere. Any help would be appreciated. I want to go from this:

                        0         1         2         3
bar one         -1.085631  0.997345  0.282978 -1.506295
    two         -0.578600  1.651437 -2.426679 -0.428913
baz not_one      1.265936 -0.866740 -0.678886 -0.094709
    two          1.491390 -0.638902 -0.443982 -0.434351
foo not_one_one  2.205930  2.186786  1.004054  0.386186
    two          0.737369  1.490732 -0.935834  1.175829
qux not_not_one -1.253881 -0.637752  0.907105 -1.428681
    two         -0.140069 -0.861755 -0.255619 -2.798589

to this:

                        0         1         2         3
bar one               100  0.997345  0.282978 -1.506295
    two         -0.578600  1.651437 -2.426679 -0.428913
baz not_one           100 -0.866740 -0.678886 -0.094709
    two          1.491390 -0.638902 -0.443982 -0.434351
foo not_one_one       100  2.186786  1.004054  0.386186
    two          0.737369  1.490732 -0.935834  1.175829
qux not_not_one       100 -0.637752  0.907105 -1.428681
    two         -0.140069 -0.861755 -0.255619 -2.798589

Upvotes: 2

Views: 365

Answers (1)

Shubham Sharma
Shubham Sharma

Reputation: 71687

You can use groupby + cumcount to create sequential counter per level=0 group then use boolean indexing with loc to update the values in column 0 where the counter is 0:

df.loc[df.groupby(level=0).cumcount().eq(0), 0] = 100

                          0         1         2         3
bar one          100.000000  0.997345  0.282978 -1.506295
    two           -0.578600  1.651437 -2.426679 -0.428913
baz not_one      100.000000 -0.866740 -0.678886 -0.094709
    two            1.491390 -0.638902 -0.443982 -0.434351
foo not_one_one  100.000000  2.186786  1.004054  0.386186
    two            0.737369  1.490732 -0.935834  1.175829
qux not_not_one  100.000000 -0.637752  0.907105 -1.428681
    two           -0.140069 -0.861755 -0.255619 -2.798589

Upvotes: 3

Related Questions