I generated the following pandas dataframe after pivotting my data:

df_pivot=df.pivot_table(
values=[1911, 1912, 1916, 1917], index=['h_code','h_name'], 
columns=['sort_code', 'reg','c_name','c_code']
)

I would now like to add a column to my df (let's call it c_new) based on the result of dividing values for 1917 by values for 1911, for each 'h_name', but for the same c_name. Therefore, Each c_name should then have a value resulting from this division (=1917/1911) for each row of h_name.

How can I do this?

1 Answers

1
jezrael On Best Solutions

Use DataFrame.xs for select by top level, create same names of level and divide with DataFrame.join:

a = df_pivot.xs(1917, axis=1,level=0, drop_level=False).rename(columns={1917:'out'})
b = df_pivot.xs(1911, axis=1,level=0, drop_level=False).rename(columns={1911:'out'})

Or:

a = df_pivot[[1917]].rename(columns={1917:'out'})
b = df_pivot[[1911]].rename(columns={1911:'out'})

df_pivot = df_pivot.join(a/b)