The easiest way is to demonstrate my question with an example. Suppose I have the following long format data frame
<code>In [284]: import pandas as pd
In [285]: data = pd.DataFrame({"day": [0,0,0,0,0,0,1,1,1,1,1,1], "cat1": ["A", "A", "A", "B", "B", "B", "A", "A", "B", "B", "B", "B"], "cat2":["1", "1", "2", "1", "2", "2", "1", "2", "1", "1", "2", "2"], "value": [10, 230, 32,12, 12, 65, 12, 34, 97, 0, 12,1]})
In [286]: data
Out[286]:
day cat1 cat2 value
0 0 A 1 10
1 0 A 1 230
2 0 A 2 32
3 0 B 1 12
4 0 B 2 12
5 0 B 2 65
6 1 A 1 12
7 1 A 2 34
8 1 B 1 97
9 1 B 1 0
10 1 B 2 12
11 1 B 2 1
</code>
<code>In [284]: import pandas as pd
In [285]: data = pd.DataFrame({"day": [0,0,0,0,0,0,1,1,1,1,1,1], "cat1": ["A", "A", "A", "B", "B", "B", "A", "A", "B", "B", "B", "B"], "cat2":["1", "1", "2", "1", "2", "2", "1", "2", "1", "1", "2", "2"], "value": [10, 230, 32,12, 12, 65, 12, 34, 97, 0, 12,1]})
In [286]: data
Out[286]:
day cat1 cat2 value
0 0 A 1 10
1 0 A 1 230
2 0 A 2 32
3 0 B 1 12
4 0 B 2 12
5 0 B 2 65
6 1 A 1 12
7 1 A 2 34
8 1 B 1 97
9 1 B 1 0
10 1 B 2 12
11 1 B 2 1
</code>
In [284]: import pandas as pd
In [285]: data = pd.DataFrame({"day": [0,0,0,0,0,0,1,1,1,1,1,1], "cat1": ["A", "A", "A", "B", "B", "B", "A", "A", "B", "B", "B", "B"], "cat2":["1", "1", "2", "1", "2", "2", "1", "2", "1", "1", "2", "2"], "value": [10, 230, 32,12, 12, 65, 12, 34, 97, 0, 12,1]})
In [286]: data
Out[286]:
day cat1 cat2 value
0 0 A 1 10
1 0 A 1 230
2 0 A 2 32
3 0 B 1 12
4 0 B 2 12
5 0 B 2 65
6 1 A 1 12
7 1 A 2 34
8 1 B 1 97
9 1 B 1 0
10 1 B 2 12
11 1 B 2 1
Per day I have two categories. My goal is to aggregate the cat2 category in a specific way. For each tuple (date, cat1, cat2) I would like to perform the following:
<code>In [287]: data_day = data[data["day"]==0]
In [288]: data_day_cat1 = data_day[data_day["cat1"]=="A"]
In [289]: data_day_cat1_cat2 = data_day_cat1[data_day_cat1["cat2"]=="1"]
In [290]: data_day_cat1_cat2["value"].pow(2).mean()
Out[290]: np.float64(26500.0)
In [291]: data_day_cat1_cat2 = data_day_cat1[data_day_cat1["cat2"]=="2"]
In [292]: data_day_cat1_cat2["value"].pow(2).mean()
Out[292]: np.float64(1024.0)
</code>
<code>In [287]: data_day = data[data["day"]==0]
In [288]: data_day_cat1 = data_day[data_day["cat1"]=="A"]
In [289]: data_day_cat1_cat2 = data_day_cat1[data_day_cat1["cat2"]=="1"]
In [290]: data_day_cat1_cat2["value"].pow(2).mean()
Out[290]: np.float64(26500.0)
In [291]: data_day_cat1_cat2 = data_day_cat1[data_day_cat1["cat2"]=="2"]
In [292]: data_day_cat1_cat2["value"].pow(2).mean()
Out[292]: np.float64(1024.0)
</code>
In [287]: data_day = data[data["day"]==0]
In [288]: data_day_cat1 = data_day[data_day["cat1"]=="A"]
In [289]: data_day_cat1_cat2 = data_day_cat1[data_day_cat1["cat2"]=="1"]
In [290]: data_day_cat1_cat2["value"].pow(2).mean()
Out[290]: np.float64(26500.0)
In [291]: data_day_cat1_cat2 = data_day_cat1[data_day_cat1["cat2"]=="2"]
In [292]: data_day_cat1_cat2["value"].pow(2).mean()
Out[292]: np.float64(1024.0)
That is on the first day, for cat1 being A, I want a single line for all occurrence of cat2, where the latter is like a “root mean square error”. Currently I’m looping over all combination, but I was playing around with using groupby. However, something like:
<code>data.groupby(["day", "cat1", "cat2"])["value"].apply(lambda x: x**2).mean()
</code>
<code>data.groupby(["day", "cat1", "cat2"])["value"].apply(lambda x: x**2).mean()
</code>
data.groupby(["day", "cat1", "cat2"])["value"].apply(lambda x: x**2).mean()
Does work. What I would like to get is a DataFrame (only first two rows) like this:
<code> day cat1 cat2 value
0 0 A 1 26500
1 0 A 2 1024
</code>
<code> day cat1 cat2 value
0 0 A 1 26500
1 0 A 2 1024
</code>
day cat1 cat2 value
0 0 A 1 26500
1 0 A 2 1024
Is this possible without looping over all day, cat1 and cat2? Could groupby be used?
3
You can create a new column with the square value and then do the groupby:
<code>data = pd.DataFrame({"day": [0,0,0,0,0,0,1,1,1,1,1,1], "cat1": ["A", "A", "A", "B", "B", "B", "A", "A", "B", "B", "B", "B"], "cat2":["1", "1", "2", "1", "2", "2", "1", "2", "1", "1", "2", "2"], "value": [10, 230, 32,12, 12, 65, 12, 34, 97, 0, 12,1]})
data["value2"] = data["value"] * data["value"]
display(data)
gb = data.groupby(["day", "cat1", "cat2"])["value2"].mean()
display(gb)
</code>
<code>data = pd.DataFrame({"day": [0,0,0,0,0,0,1,1,1,1,1,1], "cat1": ["A", "A", "A", "B", "B", "B", "A", "A", "B", "B", "B", "B"], "cat2":["1", "1", "2", "1", "2", "2", "1", "2", "1", "1", "2", "2"], "value": [10, 230, 32,12, 12, 65, 12, 34, 97, 0, 12,1]})
data["value2"] = data["value"] * data["value"]
display(data)
gb = data.groupby(["day", "cat1", "cat2"])["value2"].mean()
display(gb)
</code>
data = pd.DataFrame({"day": [0,0,0,0,0,0,1,1,1,1,1,1], "cat1": ["A", "A", "A", "B", "B", "B", "A", "A", "B", "B", "B", "B"], "cat2":["1", "1", "2", "1", "2", "2", "1", "2", "1", "1", "2", "2"], "value": [10, 230, 32,12, 12, 65, 12, 34, 97, 0, 12,1]})
data["value2"] = data["value"] * data["value"]
display(data)
gb = data.groupby(["day", "cat1", "cat2"])["value2"].mean()
display(gb)
The exact expected logic is unclear, assuming you want to get the first group based on columns day/cat1/cat2 you could transform the DataFrameGroupBy object into an iterator and use next:
<code>g = iter(data.groupby(['day', 'cat1', 'cat2'], sort=False))
first_group = next(g)[1]
print(first_group)
# you can then get the other groups
# second_group = next(g)[1]
</code>
<code>g = iter(data.groupby(['day', 'cat1', 'cat2'], sort=False))
first_group = next(g)[1]
print(first_group)
# you can then get the other groups
# second_group = next(g)[1]
</code>
g = iter(data.groupby(['day', 'cat1', 'cat2'], sort=False))
first_group = next(g)[1]
print(first_group)
# you can then get the other groups
# second_group = next(g)[1]
Alternatively, for just the first group, you could build a mask with shift and cummin:
<code>cols = ['day', 'cat1', 'cat2']
m = data[cols].eq(data[cols].shift().bfill()).all(axis=1).cummin()
out = data[m]
</code>
<code>cols = ['day', 'cat1', 'cat2']
m = data[cols].eq(data[cols].shift().bfill()).all(axis=1).cummin()
out = data[m]
</code>
cols = ['day', 'cat1', 'cat2']
m = data[cols].eq(data[cols].shift().bfill()).all(axis=1).cummin()
out = data[m]
Output:
<code> day cat1 cat2 value
0 0 A 1 10
1 0 A 1 230
</code>
<code> day cat1 cat2 value
0 0 A 1 10
1 0 A 1 230
</code>
day cat1 cat2 value
0 0 A 1 10
1 0 A 1 230