python - 如何根据条件在pandas数据框中进行groupby级别[0,1]的累积划分？

Question

我有一个数据框，我想在其中追加行添加一些 groupby + 附加条件。寻找循环或其他任何可行的解决方案。

或者如果它更容易......

first meltdf 然后添加new ratio % colthen unmelt。

由于计算是定制的，我认为for loop可以找到有或没有 groupby 的解决方案。

---第6,7,8行是我的要求。---

0-14=child and unemployed

14-50=young and working

50+=old and unemployed

# ref line 6,7,8=showing which rows to (+) and (/)

目前我想在输出行 6、7、8 中放置 3 个条件：

d = { 'year': [2019,2019,2019,2020,2020,2020], 
      'age group': ['(0-14)','(14-50)','(50+)','(0-14)','(14-50)','(50+)'], 
      'con': ['UK','UK','UK','US','US','US'],
      'population': [10,20,300,400,1000,2000]}
df = pd.DataFrame(data=d)
df2 = df.copy()
df

    year    age group   con population
0   2019    (0-14)  UK  10
1   2019    (14-50) UK  20
2   2019    (50+)   UK  300
3   2020    (0-14)  US  400
4   2020    (14-50) US  1000
5   2020    (50+)   US  2000

需要输出：

    year    age group           con                   population
0   2019    (0-14)              UK                      10.0
1   2019    (14-50)             UK                      20.0
2   2019    (50+)               UK                      300.0
3   2020    (0-14)              US                      400.0
4   2020    (14-50)             US                      1000.0
5   2020    (50+)               US                      2000.0
6   2019    young vs child      UK-young vs child       2.0   # 20/10
7   2019    old vs young        UK-old vs young          15.0   #300/20
8   2019  unemployed vs working UK-unemployed vs working. 15.5  #300+10 20

现在试用：

df2 = df.copy()
criteria = [df2['con'].str.contains('0-14'),
            df2['con'].str.contains('14-50'),
            df2['con'].str.contains('50+')]
#conditions should be according to requirements

values = ['young vs child','old vs young', 'unemployed vs working']

df2['con'] = df2['con']+'_'+np.select(criteria, values, 0)
df2['age group'] = df2['age group']+'_'+np.select(criteria, values, 0)

df.groupby(['year','age group','con']).sum().groupby(level=[0,1]).cumdiv()

pd.concat([df,df2])

#----errors. cumdiv() not found and missing conditions criteria-------

也试过：

df['population'].div(df.groupby('con')['population'].shift(1))

#but looking for customisations into this 
#so it can first sum rows and then divide 
#according to unemployed condition-- row 8 reference. 

最近的小径

n_df_2 = df.copy()
con_list = [x for x in df.con]
year_list = [x for x in df.year]
age_list = [x for x in df['age group']]
new_list = ['young vs child','old vs young', 'unemployed vs working']

for country in con_list:
  

      bev_child =  n_df_2[(n_df_2['con'].str.contains(country)) & (n_df_2['age group'].str.contains(age_list[0]))]
      bev_work =  n_df_2[(n_df_2['con'].str.contains(country)) & (n_df_2['age group'].str.contains(age_list[1]))]
      bev_old =  n_df_2[(n_df_2['con'].str.contains(country)) & (n_df_2['age group'].str.contains(age_list[2]))]


      bev_child.loc[:,'population'] = bev_work.loc[:,'population'].max() / bev_child.loc[:,'population'].max() 
      bev_child.loc[:,'con'] = country +'-'+new_list[0]
      bev_child.loc[:,'age group'] = new_list[0]
      s = n_df_2.append(bev_child, ignore_index=True)


      bev_child.loc[:,'population'] = bev_child.loc[:,'population'].max() + bev_old.loc[:,'population'].max()/ bev_work.loc[:,'population'].max() 
      bev_child.loc[:,'con'] = country +'-'+ new_list[2]
      bev_child.loc[:,'age group'] = new_list[2]

      s = s.append(bev_child, ignore_index=True)

      bev_child.loc[:,'population'] = bev_old.loc[:,'population'].max() / bev_work.loc[:,'population'].max() 
      bev_child.loc[:,'con'] = country +'-'+ new_list[1]
      bev_child.loc[:,'age group'] = new_list[1]

      s = s.append(bev_child, ignore_index=True)
s

year    age group                  con                   population
0   2019    (0-14)                 UK                             10.0
1   2019    (14-50)                UK                             20.0
2   2019    (50+)                  UK                            300.0
3   2020    (0-14)                 US                            400.0
4   2020    (14-50)                US                           1000.0
5   2020    (50+)                  US                           2000.0
6   2020    young vs child         US-young vs child               2.5
7   2020    unemployed vs working  US-unemployed vs working        4.5
8   2020    old vs young           US-old vs young                 2.0

也请找到最简单的方法来解决它......请......

标签： pythonpandasdataframefunctionnumpy