英文:
Find the sum of values in rows of one column for where the other column has NAN in Pandas
问题
我有一个包含列A和B的数据框。列A中的数据是不连续的，其中一些行是NAN，而B中的数据是连续的。我想创建第三列，对于每组A中的NAN行，它将具有这些相同行中B的值的总和加上B中的下一个有效值。
对于A中的NAN和在有效数字后的行，C中的所有其他值应为NAN。
示例：
data = {'A': [1, 1, None, None, 2, 5, None, None, 3, 4, 3, None, 5],'B': [10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130]}
除了需要B的总和加上B中的下一个有效值的行之外，其他行都可以正常工作。
我使用以下代码。但是目前看起来有点混乱。
result = df.groupby(df['A'].isnull().cumsum())['B'].sum().reset_index()df_result = pd.DataFrame({'C': result['Pumped']})df_result.loc[1:, 'C'] -= result.loc[0, 'Pumped']df.loc[~mask, 'C'] = df.loc[~mask, 'Pumped']valid_rows_after_nan = df['dWL'].notnull() & mask.shift(1).fillna(False)df.loc[valid_rows_after_nan, 'C'] = df_resultprint(df)
我希望输出的结果如下所示：
data = {'A': [1, 1, None, None, 2, 5, None, None, 3, 4, 3, None, 5],'B': [10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130],'C': [10, 20, None, None, 120, 60, None, None, 240, 100, 110, None, 5]}
英文:
I have a dataframe with columns A and B. Column A has non continuous data where some of the rows are NAN and B has continuous data. I would like to create a third column where for each set of A rows with NAN it will have the sum of values in those same rows in B + the next valid value in B.
All other values in C should be NAN for NAN in A AND the value of B for rows following a valid number in A.
Example:
data = {'A': [1, 1, None, None, 2, 5, None, None,3 ,4, 3, None , 5],'B': [10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130]}
Everything works fine except for the rows where I need the sum of B + next valid value in B.
I use the following code. I have this code but is seems it's a mess by now.
`result = df.groupby(df['A'].isnull().cumsum())['B'].sum().reset_index()df_result = pd.DataFrame({'C': result['Pumped']})df_result.loc[1:, 'C'] -= result.loc[0, 'Pumped']df.loc[~mask, 'C'] = df.loc[~mask, 'Pumped']valid_rows_after_nan = df['dWL'].notnull() & mask.shift(1).fillna(False)df.loc[valid_rows_after_nan, 'C'] = df_resultprint(df)`
I would like the output to look like this:
`data = {'A': [1, 1, None, None, 2, 5, None, None,3 ,4, 3, None , 5],'B': [10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130],'C': [10, 20, None, None, 120, 60, None, None, 240, 100, 110, None, 5]}
答案1
得分: 4
使用groupby.transform的简单版本：
# 识别非NA值并反转m = df.loc[::-1, 'A'].notna()# 对前面的NA进行分组求和，并在NA处进行掩码df['C'] = df.groupby(m.cumsum())['B'].transform('sum').where(m)
输出结果：
A B C0 1.0 10 10.01 1.0 20 20.02 NaN 30 NaN3 NaN 40 NaN4 2.0 50 120.05 5.0 60 60.06 NaN 70 NaN7 NaN 80 NaN8 3.0 90 240.09 4.0 100 100.010 3.0 110 110.011 NaN 120 NaN12 5.0 130 250.0
英文:
A simple version using groupby.transform:
# identify the non-NA and reversem = df.loc[::-1, 'A'].notna()# group the preceding NA, sum, mask where NAdf['C'] = df.groupby(m.cumsum())['B'].transform('sum').where(m)
Output:
A B C0 1.0 10 10.01 1.0 20 20.02 NaN 30 NaN3 NaN 40 NaN4 2.0 50 120.05 5.0 60 60.06 NaN 70 NaN7 NaN 80 NaN8 3.0 90 240.09 4.0 100 100.010 3.0 110 110.011 NaN 120 NaN12 5.0 130 250.0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论