基于使用.loc的多索引计算更新多索引 Dataframe 中的值

6l7fqoea  于 2021-09-29  发布在  Java
关注(0)|答案(2)|浏览(251)

我有一个 df :

df = pd.DataFrame.from_dict({('group', ''): {0: 'A',
  1: 'A',
  2: 'A',
  3: 'A',
  4: 'A',
  5: 'A',
  6: 'A',
  7: 'A',
  8: 'A',
  9: 'B',
  10: 'B',
  11: 'B',
  12: 'B',
  13: 'B',
  14: 'B',
  15: 'B',
  16: 'B',
  17: 'B',
  18: 'all',
  19: 'all'},
 ('category', ''): {0: 'Amazon',
  1: 'Apple',
  2: 'Facebook',
  3: 'Google',
  4: 'Netflix',
  5: 'Tesla',
  6: 'Total',
  7: 'Uber',
  8: 'total',
  9: 'Amazon',
  10: 'Apple',
  11: 'Facebook',
  12: 'Google',
  13: 'Netflix',
  14: 'Tesla',
  15: 'Total',
  16: 'Uber',
  17: 'total',
  18: 'Total',
  19: 'total'},
 (pd.Timestamp('2020-06-29 00:00:00'), 'last_sales'): {0: 195.0,
  1: 61.0,
  2: 106.0,
  3: 61.0,
  4: 37.0,
  5: 13.0,
  6: 954.0,
  7: 4.0,
  8: 477.0,
  9: 50.0,
  10: 50.0,
  11: 75.0,
  12: 43.0,
  13: 17.0,
  14: 14.0,
  15: 504.0,
  16: 3.0,
  17: 252.0,
  18: 2916.0,
  19: 2916.0},
 (pd.Timestamp('2020-06-29 00:00:00'), 'sales'): {0: 1268.85,
  1: 18274.385000000002,
  2: 19722.65,
  3: 55547.255,
  4: 15323.800000000001,
  5: 1688.6749999999997,
  6: 227463.23,
  7: 1906.0,
  8: 113731.615,
  9: 3219.6499999999996,
  10: 15852.060000000001,
  11: 17743.7,
  12: 37795.15,
  13: 5918.5,
  14: 1708.75,
  15: 166349.64,
  16: 937.01,
  17: 83174.82,
  18: 787625.7400000001,
  19: 787625.7400000001},
 (pd.Timestamp('2020-06-29 00:00:00'), 'difference'): {0: 0.0,
  1: 0.0,
  2: 0.0,
  3: 0.0,
  4: 0.0,
  5: 0.0,
  6: 0.0,
  7: 0.0,
  8: 0.0,
  9: 0.0,
  10: 0.0,
  11: 0.0,
  12: 0.0,
  13: 0.0,
  14: 0.0,
  15: 0.0,
  16: 0.0,
  17: 0.0,
  18: 0.0,
  19: 0.0},
 (pd.Timestamp('2020-07-06 00:00:00'), 'last_sales'): {0: 26.0,
  1: 39.0,
  2: 79.0,
  3: 49.0,
  4: 10.0,
  5: 10.0,
  6: 436.0,
  7: 5.0,
  8: 218.0,
  9: 89.0,
  10: 34.0,
  11: 133.0,
  12: 66.0,
  13: 21.0,
  14: 20.0,
  15: 732.0,
  16: 3.0,
  17: 366.0,
  18: 2336.0,
  19: 2336.0},
 (pd.Timestamp('2020-07-06 00:00:00'), 'sales'): {0: 3978.15,
  1: 12138.96,
  2: 19084.175,
  3: 40033.46000000001,
  4: 4280.15,
  5: 1495.1,
  6: 165548.29,
  7: 1764.15,
  8: 82774.145,
  9: 8314.92,
  10: 12776.649999999996,
  11: 28048.075,
  12: 55104.21000000002,
  13: 6962.844999999999,
  14: 3053.2000000000003,
  15: 231049.11000000002,
  16: 1264.655,
  17: 115524.55500000001,
  18: 793194.8000000002,
  19: 793194.8000000002},
 (pd.Timestamp('2020-07-06 00:00:00'), 'difference'): {0: 0.0,
  1: 0.0,
  2: 0.0,
  3: 0.0,
  4: 0.0,
  5: 0.0,
  6: 0.0,
  7: 0.0,
  8: 0.0,
  9: 0.0,
  10: 0.0,
  11: 0.0,
  12: 0.0,
  13: 0.0,
  14: 0.0,
  15: 0.0,
  16: 0.0,
  17: 0.0,
  18: 0.0,
  19: 0.0},
 (pd.Timestamp('2021-06-28 00:00:00'), 'last_sales'): {0: 96.0,
  1: 56.0,
  2: 106.0,
  3: 44.0,
  4: 34.0,
  5: 13.0,
  6: 716.0,
  7: 9.0,
  8: 358.0,
  9: 101.0,
  10: 22.0,
  11: 120.0,
  12: 40.0,
  13: 13.0,
  14: 8.0,
  15: 610.0,
  16: 1.0,
  17: 305.0,
  18: 2652.0,
  19: 2652.0},
 (pd.Timestamp('2021-06-28 00:00:00'), 'sales'): {0: 5194.95,
  1: 19102.219999999994,
  2: 22796.420000000002,
  3: 30853.115,
  4: 11461.25,
  5: 992.6,
  6: 188143.41,
  7: 3671.15,
  8: 94071.705,
  9: 6022.299999999998,
  10: 7373.6,
  11: 33514.0,
  12: 35943.45,
  13: 4749.000000000001,
  14: 902.01,
  15: 177707.32,
  16: 349.3,
  17: 88853.66,
  18: 731701.46,
  19: 731701.46},
 (pd.Timestamp('2021-06-28 00:00:00'), 'difference'): {0: 0.0,
  1: 0.0,
  2: 0.0,
  3: 0.0,
  4: 0.0,
  5: 0.0,
  6: 0.0,
  7: 0.0,
  8: 0.0,
  9: 0.0,
  10: 0.0,
  11: 0.0,
  12: 0.0,
  13: 0.0,
  14: 0.0,
  15: 0.0,
  16: 0.0,
  17: 0.0,
  18: 0.0,
  19: 0.0},
 (pd.Timestamp('2021-07-07 00:00:00'), 'last_sales'): {0: 45.0,
  1: 47.0,
  2: 87.0,
  3: 45.0,
  4: 13.0,
  5: 8.0,
  6: 494.0,
  7: 2.0,
  8: 247.0,
  9: 81.0,
  10: 36.0,
  11: 143.0,
  12: 56.0,
  13: 9.0,
  14: 9.0,
  15: 670.0,
  16: 1.0,
  17: 335.0,
  18: 2328.0,
  19: 2328.0},
 (pd.Timestamp('2021-07-07 00:00:00'), 'sales'): {0: 7556.414999999998,
  1: 14985.05,
  2: 16790.899999999998,
  3: 36202.729999999996,
  4: 4024.97,
  5: 1034.45,
  6: 163960.32999999996,
  7: 1385.65,
  8: 81980.16499999998,
  9: 5600.544999999999,
  10: 11209.92,
  11: 32832.61,
  12: 42137.44500000001,
  13: 3885.1499999999996,
  14: 1191.5,
  15: 194912.34000000003,
  16: 599.0,
  17: 97456.17000000001,
  18: 717745.3400000001,
  19: 717745.3400000001},
 (pd.Timestamp('2021-07-07 00:00:00'), 'difference'): {0: 0.0,
  1: 0.0,
  2: 0.0,
  3: 0.0,
  4: 0.0,
  5: 0.0,
  6: 0.0,
  7: 0.0,
  8: 0.0,
  9: 0.0,
  10: 0.0,
  11: 0.0,
  12: 0.0,
  13: 0.0,
  14: 0.0,
  15: 0.0,
  16: 0.0,
  17: 0.0,
  18: 0.0,
  19: 0.0}}).set_index(['group','category'])

我正在尝试更新 difference 索引处的列 B, total 像这样：

df.loc[('B','total'),(slice(None),'difference')] = df.loc[('B','total'),(slice(None),'last_sales')] / 
                                                   df.loc[('A','total'),(slice(None),'sales')] * 100

但出于某种原因，我 nan 而不是价值观。我不知道为什么这两种情况都会发生

df.loc[('B','total'),(slice(None),'last_sales')]

# and

df.loc[('A','total'),(slice(None),'sales')]

返回值

2020-06-29 00:00:00  last_sales   252.000
2020-07-06 00:00:00  last_sales   366.000
2021-06-28 00:00:00  last_sales   305.000
2021-07-07 00:00:00  last_sales   335.000

2020-06-29 00:00:00  sales   113,731.615
2020-07-06 00:00:00  sales    82,774.145
2021-06-28 00:00:00  sales    94,071.705
2021-07-07 00:00:00  sales    81,980.165

使用 df.loc[('B','total'),(slice(None),'last_sales')].values/(df.loc[('A','total'),(slice(None),'sales')] * 100).values 我得到：

array([2.21574274e-05, 4.42167056e-05, 3.24220763e-05, 4.08635430e-05])

但当我使用：

df.loc[('B','total'),(slice(None),'difference')]=df.loc[('B','total'),(slice(None),'last_sales')].to_numpy()/(df.loc[('A','total'),(slice(None),'sales')] * 100).to_numpy()

我得到：

2020-06-29 00:00:00  difference   0.000
2020-07-06 00:00:00  difference   0.000
2021-06-28 00:00:00  difference   0.000
2021-07-07 00:00:00  difference   0.000
pythonpandas

2条答案

按热度按时间
kgqe7b3p

kgqe7b3p1#

尝试通过 values 属性：

df.loc[('B','total'),(slice(None),'difference')]=(df.loc[('B','total'),(slice(None),'last_sales')].values/(df.loc[('A','total'),(slice(None),'sales')] ).values)*100

或分步骤：

s1=df.loc[('B','total'),(slice(None),'last_sales')].values
s2=df.loc[('A','total'),(slice(None),'sales')].values
df.loc[('B','total'),(slice(None),'difference')]=(s1/s2)*100

或
通过 to_numpy() 方法：

df.loc[('B','total'),(slice(None),'difference')]=(df.loc[('B','total'),(slice(None),'last_sales')].to_numpy()/(df.loc[('A','total'),(slice(None),'sales')]).to_numpy())*100

或分步骤：

s1=df.loc[('B','total'),(slice(None),'last_sales')].to_numpy()
s2=df.loc[('A','total'),(slice(None),'sales')].to_numpy()
df.loc[('B','total'),(slice(None),'difference')]=(s1/s2)*100

产量 df.loc[('B','total'),(slice(None),'difference')] : 2

020-06-29 00:00:00  difference    0.221574
2020-07-06 00:00:00  difference    0.442167
2021-06-28 00:00:00  difference    0.324221
2021-07-07 00:00:00  difference    0.408635
Name: (B, total), dtype: float64
赞(0）分享  回复(0）举报  2021-09-29
hfwmuf9z

hfwmuf9z2#

你需要同样的 MultiIndex 两者 Series 关于分部：

s1 = df.loc[('B','total'),(slice(None),'last_sales')].rename({'last_sales':'difference'})
s2 = df.loc[('A','total'),(slice(None),'sales')].rename({'sales':'difference'})
df.loc[('B','total'),(slice(None),'difference')] = s1 / s2 * 100
print (df.loc[('B','total'),(slice(None),'difference')])
2020-06-29 00:00:00  difference    0.221574
2020-07-06 00:00:00  difference    0.442167
2021-06-28 00:00:00  difference    0.324221
2021-07-07 00:00:00  difference    0.408635
Name: (B, total), dtype: float64
赞(0）分享  回复(0）举报  2021-09-29
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com