e.g. In the table above, I want to take an average of C values from rows 0,5,6,7,8 because all of these rows have 18.3 in some of the columns. Then I want to store this average in another data frame in a row corresponding to '18.3'. Then, I want to take an average of C values from rows 1,5,9,10,11 because all these rows have 95.8 in some of the columns. Then I want to store this average in another data frame in a row corresponding to '95.8'. Similarly, I want to repeat this operation for each unique value that appears in columns A and B. I am unable to figure out a way to do this. Any hits will be helpful!

CodePudding user response：

I believe I understand what your asking, you want to store the mean of column C for each unique value in A and B as a row in a new df.

The below code reads in the dataframe, which I created as data.csv, then finds the Unique values between the A and B columns calculates the mean of C where the rows of either A or B match the unique value.

We then create a new data frame with the mean and the unique value.

    import pandas as pd


    df = pd.read_csv("data.csv")


    unique_a = df.A.unique().tolist()
    unique_b = df.B.unique().tolist()
    b_uniques = [ i for  i in unique_b if i not in unique_a]

    unique_a  = b_uniques

    output = []
    value = []
    for i in unique_a:
        output.append( df[(df['A']==i) | (df['B']==i)]['C'].mean())
        value.append(i)




    out_df = pd.DataFrame({"mean":output, "Group Value": value})


    output:
      mean  Group Value
0  2.336000         84.3
1  1.180000         18.3
2  3.882000         95.8
3  1.512500        192.7
4  4.708000        617.0
5  0.965000         54.0
6  2.836667        544.0
7  0.620000         52.7

CodePudding user response：

Try this:

import pandas as pd

s = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14]
a = [84.3, 84.3, 84.3, 84.3, 84.3, 18.3, 18.3, 18.3, 18.3, 95.8, 95.8, 95.8, 192.7, 192.7, 617.0]
b = [18.3, 95.8, 192.7, 617.0, 54.0, 95.8, 192.7, 617.0, 544.0, 52.7, 617.0, 54.0, 617.0, 544.0, 544.0]
c = [10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130, 140, 150]

df = pd.DataFrame(s, columns=['Sr. No.'])
df['A'] = a
df['B'] = b
df['C'] = c

completeSet = set(list(df['B'])   list(df['A']))
list_df_num = []
list_df_avg = []

for num in completeSet:
    list_df_num.append(num)
    tmp = df[(df['A'] == num) | (df['B'] == num)]
    if len(tmp) > 0:
        avg = sum(list(tmp['C'])) / len(list(tmp['C']))
        list_df_avg.append(avg)
    else:
        list_df_avg.append(0)

result = pd.DataFrame(list_df_num, columns=['Number'])
result['Average'] = list_df_avg

print(result)