1

I have a panel data frame with dimensions in multi-index: country and year. For each country in level = 0 of the index, I want to divide (or subtract) some variable from the United States only.

In pseudo code

for country in countries_in_level0:
   Data[‘new_variable’][country] = Data[‘variable’][country] - Data[‘variable’][‘United States’]

What I tried to do is

Data[‘new_variable’] = Data[‘variable’] - Data[‘variable’].loc[‘United States’, :]

But I get NaN in for every country but the United States

Jorge Alonso
  • 103
  • 11
  • adding a `.loc` after `[]` will not get the assignment done... can edit your question to provide a [minimal example](https://stackoverflow.com/q/20109391/6692898) of your data and a table of how you want your output to look like? – RichieV Aug 15 '20 at 22:40
  • Could this be formulated as a question? – Al rl Aug 15 '20 at 22:45
  • Thank you for your comments, I tried to be a little bit more precise – Jorge Alonso Aug 15 '20 at 22:58

1 Answers1

0

If you need to substract "United States" to the entire DataFrame, you can use xs:

Data - Data.xs(("United States"))

Here an example:

arrays = [['United States', 'United States', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],
          [1, 2, 1, 2, 1, 2, 1, 2]]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['country','year'])
s = pd.DataFrame(10+(10*np.random.randn(8,2)), index=index)
s
                           0            1
country       year      
United States   1    8.012399    13.287124
                2   13.357553    -4.295128
baz             1   20.305391    12.381340
                2    0.070968     6.314961
foo             1   25.015921   -11.577952
                2    1.301654     6.000196
qux             1    4.198554    -6.915449
                2    5.071788    12.423901

s - s.xs(('United States'))
                             0          1
country       year      
United States   1     0.000000    0.000000
                2     0.000000    0.000000
baz             1    12.292992   -0.905785
                2   -13.286585   10.610089
foo             1    17.003522  -24.865077
                2   -12.055899   10.295324
qux             1   -3.813845   -20.202574
                2   -8.285765    16.719028

PS: If the question was reassign just United States, it is

s.loc[['United States']]=1000
Ricardo Rendich
  • 666
  • 5
  • 6