CODEWITHSUNDEEP
python-3.xpandaspandas-groupbytranspose
Jack
Jack

Reputation: 1

Groupby and transpose or unstack in Pandas

I have the following Python pandas dataframe: There are more EventName's than shown on this date. Each will have Race_Number = 'Race 1', 'Race 2', etc. After a while the date increments. enter image description here.

I'm trying to create a dataframe that looks like this:

enter image description here

Each race has different numbers of runners. Is there a way to do this in pandas ? Thanks

Upvotes: 0

Views: 126

Answers (1)

BC Smith
BC Smith

Reputation: 757

I assumed output would be another DataFrame. 

import pandas as pd
import numpy as np
from nltk import flatten
import copy

df = pd.DataFrame({'EventName': ['sydney', 'sydney', 'sydney', 'sydney', 'sydney', 'sydney'],
                   'Date': ['2019-01.01', '2019-01.01', '2019-01.01', '2019-01.01', '2019-01.01', '2019-01.01'],
                   'Race_Number': ['Race1', 'Race1', 'Race1', 'Race2', 'Race2', 'Race3'],
                   'Number': [4, 7, 2, 9, 5, 10]
                   })
print(df)

dic={}
for rows in df.itertuples():
    if rows.Race_Number in dic:
        dic[rows.Race_Number] = flatten([dic[rows.Race_Number], rows.Number])
    else:
        dic[rows.Race_Number] = rows.Number

copy_dic = copy.deepcopy(dic)
seq = np.arange(0,len(dic.keys()))
for key, n_key in zip(copy_dic, seq):
    dic[n_key] = dic.pop(key)

df = pd.DataFrame([dic])

print(df)

Upvotes: 1

Related Questions