How to add difference row to each index value?

user_14328058 0
Viewed 66

I have a dataframe that looks like this:

Index_1 Index_2 Week_1 Week_2 Week_3
 0       A        1      2      3
         B        2      3      4
 1       A        4      5      6
         B        7      8      9

For each unique value in Index_1, I want to add a row to Index_2, called "Diff" that will calculate the difference between A and B in Week_1, Week_2, Week_3, and so on.

See below for what I'm hoping it'll look like:

Index_1 Index_2 Week_1 Week_2 Week_3
 0       A        1      2      3
         B        2      3      4
         Diff     1      1      1
 1       A        4      5      6
         B        7      8      9
         Diff     3      3      3
pandas dataframe python-3.x python
4 Answers

Another way is stack/unstack:

new_df = df.unstack(level=-1).stack(level=0)
new_df['Diff'] = new_df['B'] - new_df['A']
new_df = new_df.unstack(level=-1).stack(level=0)

Output:

                 Week_1  Week_2  Week_3
Index_1 Index_2                        
0       A             1       2       3
        B             2       3       4
        Diff          1       1       1
1       A             4       5       6
        B             7       8       9
        Diff          3       3       3
user_42384080

Sorry I won't code this out, but my recommendation is to recognize a few things.
You are displaying multiple values in two arrays that are 5 columns wide and 4 rows tall. (Or one array 7 rows tall), or a 3 dimensional array that stores all the information in a very concise and recognizable manner. It's all about choosing how to normalize data.
My recommendation is to write your code to store the Subtrahend and Minuend in the array. Then Write the code to calculate the Difference and store it in the array. Then write the code to display the array in the manner you intend. Tell the story, pseudocode the story, then code in the pseudocode.

user_40624540

Try this:

diff = df.groupby('Index_1').diff().dropna().rename(index={'B': 'Diff'})
df = df.append(diff).sort_index()

Result:

                 Week_1  Week_2  Week_3
Index_1 Index_2                        
0       A           1.0     2.0     3.0
        B           2.0     3.0     4.0
        Diff        1.0     1.0     1.0
1       A           4.0     5.0     6.0
        B           7.0     8.0     9.0
        Diff        3.0     3.0     3.0
user_25389390

What about with dict:

dict = {
    "dataframe":[
        {   
        "A":[1,2,3],
        "B":[2,3,4]
        },
        {   
        "A":[4,5,6],
        "B":[7,8,9]
        }
    ]
}

Using this code:

for i in dict["dataframe"]:
    i["diff"] = []
    for weak in range(len(i["A"])):
        i["diff"].append(i["B"][weak]-i["A"][weak])

result:

{
    "dataframe":[
        {   
        "A":[1,2,3],
        "B":[2,3,4],
        "diff":[1,1,1],
        },
        {   
        "A":[4,5,6],
        "B":[7,8,9],
        "diff":[3,3,3]
        }
    ]
}

I guess this is more readable for newbies like me.

user_76359950
Related