Python or Pandas Data Summarization(Converting Table into Dictionary of row_name:[col_name, value]....)

Question

Please find the data below where the Index name is repeated 3 to 5 times as the monthly report got consolidated for 3months improperly, before being handed over to me.

Name    Score   Rating
Peralta 0   40
Peralta 20  0
Peralta 0   0
Amy 0   40
Amy 20  40
Amy -20 40
Terry   0   0
Terry   -20 40
Terry   0   -40
Gina    20  0
Gina    0   0
Gina    -20 40

I have come so far with only pulling the column name as shown in the code in the end. But I am unable to bring the "Column Name" and "Cell Value" in place together.

I need to summarize the data in the below format: (the results can have tabs or commas, as per your comfort.)

### Final Result
# IndexName [col_name, cell_value]  [sum of positive numbers, result] [sum of negative numbers, result]


Peralta [Rating, 40]    [Score, 20]     [Sum_Total_of_positive_numbers, 60]
Amy     [Rating, 40]    [Score, 20]     [Rating, 40]    [Score, -20]    [Rating, 40]    [Sum_Total_of_positive_numbers, 140]    [Sum_Total_of_negative_numbers, -20]
Terry   [Score, -20]    [Rating, 40]    [Rating, -40]   [Sum_Total_of_positive_numbers, 40] [Sum_Total_of_negative_numbers, -60]
Gina    [Score, 20]     [Score, -20]    [Rating, 40]    [Sum_Total_of_positive_numbers, 60] [Sum_Total_of_negative_numbers, -20]

All the standard pandas functions for summarizing data have not helped, and/or the other libraries available to bring the summary in the above format doesn't exist.

The following code actually helped in bringing the column name down, but then the value of the Cell, pertaining to that column+row, isn't coming in..

for k, v in dff_dict.items():               # k: name of index, v: is a df
    check = v.columns[(v == 20).any()]
    if len(check) > 0:
        print((k, check.to_list()), file=open("output.txt", "a"))

Is there some way that I can achieve the "Final Result" as shown in the 2nd table, please? (the end result not necessarily in any particular format, like a dataframe or table) Thank you..

Note:

The original data may contain more than 20 columns hundreds of rows, but I have simplified the data, so pleassse do not limit to only a few rows or columns.'

Also the last 2 bracketed value shows the sum total of all the positive and negative numbers in the row, after bringing down the values in place. Positive numbers and negative numbers separately.

There is a helpful article here (Closest Possible answer), but then I need [column_name, value] unlike just the [row:value]

Answer 1

I think this is easier if you can split the overall data set into two, given the "positive and negative numbers sum separately" requirement.

Starting with your sample data:

import pandas as pd
import numpy as np
data = [
{"Name": "Peralta", "Score": 0, "Rating": 40},
{"Name": "Peralta", "Score": 20, "Rating": 0},
{"Name": "Peralta", "Score": 0, "Rating": 0},
{"Name": "Amy", "Score": 0, "Rating": 40},
{"Name": "Amy", "Score": 20, "Rating": 40},
{"Name": "Amy", "Score": -20, "Rating": 40},
{"Name": "Terry", "Score": 0, "Rating": 0},
{"Name": "Terry", "Score": -20, "Rating": 40},
{"Name": "Terry", "Score": 0, "Rating": -40},
{"Name": "Gina", "Score": 20, "Rating": 0},
{"Name": "Gina", "Score": 0, "Rating": 0},
{"Name": "Gina", "Score": -20, "Rating": 40},
]
df = pd.DataFrame(data).set_index("Name")

we can get positive- and negative- valued projections to work with:

df_pos = df.where(df>=0, other=0)
df_neg = df.where(df<0, other=0)

and then group and sum to get your desired results:

df_pos = df_pos.groupby(by="Name").sum()
df_pos["total_positive"] = df_pos.apply(np.sum, axis=1)

df_neg = df_neg.groupby(by="Name").sum()
df_neg["total_negative"] = df_neg.apply(np.sum, axis=1)

Note - at this stage the data is still in two dataframes, not flattened out to the [field, value] format you showed.