CODEWITHSUNDEEP
pythonpandas
Mohammad Reza
Mohammad Reza

Reputation: 1203

pandas add column using map based on two variable

I have a dataframe like this : enter image description here

what I want to do is to add a column based on two variables loyalty and new

I create a column based on dow and it is mean of waiting on each day of the week I did it like so:

dow_waiting = qdata.groupby('dow').agg('mean')['waiting']
qdata['dow_waiting'] = qdata.dow.map(dow_waiting)

I want to do the same for loyalty and new columns. if loyalty is 0 and new is 0 get mean of waiting for this situation and paste it in a new column loyalty_waiting and... how can I do this using map function

Upvotes: 0

Views: 175

Answers (1)

jottbe
jottbe

Reputation: 4521

You can do that with merge.

data= {'loyalty': [1, 1, 0, 1, 1, 1, 0, 0, 1, 0, 1, 1, 0, 1, 1, 1, 1, 0, 0, 1, 0, 0, 1, 0, 1, 0, 0, 1, 1, 1, 1, 0, 1, 0, 1, 1, 0, 0, 1, 0, 0, 1, 1, 1, 0, 1, 1, 1, 1, 0], 
 'new': [0, 0, 0, 1, 0, 1, 0, 1, 0, 0, 1, 1, 0, 1, 0, 1, 0, 1, 0, 0, 1, 1, 1, 1, 1, 1, 1, 0, 1, 0, 1, 1, 1, 1, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1], 
 'waiting': [17, 30, 43, 37, 10, 11, 30, 24, 25, 39, 34, 0, 11, 29, 12, 0, 12, 42, 3, 1, 9, 37, 32, 25, 17, 43, 17, 22, 3, 0, 9, 45, 24, 16, 22, 7, 12, 7, 36, 49, 34, 45, 12, 9, 7, 26, 22, 4, 24, 27]
}

df= pd.DataFrame(data)
# create the sums on loyalty and new
sums=df.groupby(['loyalty', 'new']).mean()
# join them with your original data frame
df.merge(sums, left_on=['loyalty', 'new'], right_index=True)

This yileds:

    loyalty  new  waiting_x  waiting_y
0         1    0         17  16.666667
1         1    0         30  16.666667
4         1    0         10  16.666667
8         1    0         25  16.666667
14        1    0         12  16.666667
16        1    0         12  16.666667
19        1    0          1  16.666667
27        1    0         22  16.666667
29        1    0          0  16.666667
34        1    0         22  16.666667
35        1    0          7  16.666667
41        1    0         45  16.666667
42        1    0         12  16.666667
43        1    0          9  16.666667
45        1    0         26  16.666667
2         0    0         43  23.857143
6         0    0         30  23.857143
9         0    0         39  23.857143
12        0    0         11  23.857143
18        0    0          3  23.857143
40        0    0         34  23.857143
44        0    0          7  23.857143
3         1    1         37  18.800000
5         1    1         11  18.800000
10        1    1         34  18.800000
11        1    1          0  18.800000
13        1    1         29  18.800000
15        1    1          0  18.800000
22        1    1         32  18.800000
24        1    1         17  18.800000
28        1    1          3  18.800000
30        1    1          9  18.800000
32        1    1         24  18.800000
38        1    1         36  18.800000
46        1    1         22  18.800000
47        1    1          4  18.800000
48        1    1         24  18.800000
7         0    1         24  27.153846
17        0    1         42  27.153846
20        0    1          9  27.153846
21        0    1         37  27.153846
23        0    1         25  27.153846
25        0    1         43  27.153846
26        0    1         17  27.153846
31        0    1         45  27.153846
33        0    1         16  27.153846
36        0    1         12  27.153846
37        0    1          7  27.153846
39        0    1         49  27.153846
49        0    1         27  27.153846

Upvotes: 1

Related Questions