Turn each group into Pandas
Using Pandas, I called groupby on my dataframe and got this:
>>>grouped = df.groupby(['cid'])
for key, gr in grouped:
print(key)
print(gr)
Out: cid price
121 12
121 10
121 9
I want each group to rotate like this:
cid price1 price2 price3
121 12 10 9
What's the correct way to do this with Pandas?
source to share
Assuming you have a frame like
>>> df = pd.DataFrame({"cid": np.arange(64)//8, "price": np.arange(64)})
>>> df.head()
cid price
0 0 0
1 0 1
2 0 2
3 0 3
4 0 4
Then I think you can get what you want by concatenating groupby
and pivot
:
df = pd.DataFrame({"cid": np.arange(64)//8, "price": np.arange(64)})
df["num"] = df.groupby("cid")["price"].cumcount() + 1
pivoted = df.pivot(index="cid", columns="num", values="price")
pivoted.columns = "price" + pivoted.columns.astype(str)
pivoted = pivoted.reset_index()
which gives
>>> pivoted
cid price1 price2 price3 price4 price5 price6 price7 price8
0 0 0 1 2 3 4 5 6 7
1 1 8 9 10 11 12 13 14 15
2 2 16 17 18 19 20 21 22 23
3 3 24 25 26 27 28 29 30 31
4 4 32 33 34 35 36 37 38 39
5 5 40 41 42 43 44 45 46 47
6 6 48 49 50 51 52 53 54 55
7 7 56 57 58 59 60 61 62 63
Also: the number of characters after the end of lines, eg. "price5" is generally not a good idea. You cannot work with them, they are not sorted the way you would expect, etc.
First, we create a column showing which index is in the price:
>>> df["num"] = df.groupby("cid")["price"].cumcount() + 1
>>> df.head(10)
cid price num
0 0 0 1
1 0 1 2
2 0 2 3
[etc.]
7 0 7 8
8 1 8 1
9 1 9 2
Then we pivot
:
>>> pivoted = df.pivot(index="cid", columns="num", values="price")
>>> pivoted
num 1 2 3 4 5 6 7 8
cid
0 0 1 2 3 4 5 6 7
1 8 9 10 11 12 13 14 15
2 16 17 18 19 20 21 22 23
3 24 25 26 27 28 29 30 31
4 32 33 34 35 36 37 38 39
5 40 41 42 43 44 45 46 47
6 48 49 50 51 52 53 54 55
7 56 57 58 59 60 61 62 63
Then we fix the columns:
>>> pivoted.columns = "price" + pivoted.columns.astype(str)
>>> pivoted
price1 price2 price3 price4 price5 price6 price7 price8
cid
0 0 1 2 3 4 5 6 7
1 8 9 10 11 12 13 14 15
2 16 17 18 19 20 21 22 23
3 24 25 26 27 28 29 30 31
4 32 33 34 35 36 37 38 39
5 40 41 42 43 44 45 46 47
6 48 49 50 51 52 53 54 55
7 56 57 58 59 60 61 62 63
And finally, reset the index:
>>> pivoted = pivoted.reset_index()
>>> pivoted
cid price1 price2 price3 price4 price5 price6 price7 price8
0 0 0 1 2 3 4 5 6 7
1 1 8 9 10 11 12 13 14 15
2 2 16 17 18 19 20 21 22 23
3 3 24 25 26 27 28 29 30 31
4 4 32 33 34 35 36 37 38 39
5 5 40 41 42 43 44 45 46 47
6 6 48 49 50 51 52 53 54 55
7 7 56 57 58 59 60 61 62 63
source to share
Below is a brief variation on @DSM's approach using unstack()
. I'll be taking the data from @DSM sample so that it's easy to compare results with pivot()
vs unstack()
:
>>> df = pd.DataFrame({"cid": np.arange(64)//8, "price": np.arange(64)})
>>> df['num'] = df.groupby('cid').cumcount()
>>> df.set_index(['cid','num']).unstack()
price
num 0 1 2 3 4 5 6 7
cid
0 0 1 2 3 4 5 6 7
1 8 9 10 11 12 13 14 15
2 16 17 18 19 20 21 22 23
3 24 25 26 27 28 29 30 31
4 32 33 34 35 36 37 38 39
5 40 41 42 43 44 45 46 47
6 48 49 50 51 52 53 54 55
7 56 57 58 59 60 61 62 63
source to share