Pandas GroupBy using multiple columns and multiple indexes and convert to dictionary

user_11454091 0
Viewed 368

I have a csv file as such (I removed the commas and all):

ID Team Col3 Col4 Value
3   t1   c2   x1   0.124
6   t2   c11  x10  1.342
7   t3   c5   x2   0.654
4   t3   c5   x2   123.3
5   t2   c11  x10  75.2
9   t2   c11  x10  34.97 
1   t1   c2   x1   0.23

I'm looking to group by Team, Col3, and Col4 and turn it into a dictionary whose values are ID and Value. So it looks like this

{
  (t1, c2, x1): [[3, 0.124],[1, 0.23]],
  (t2, c11, x10): [[6, 1.342], [5, 75.2], [9, 34.97]],
  (t3, c5, x2): [[7, 0.654], [4, 123.3]]
}

My code right now is

df = pd.read_csv('file.csv')
gk = dict(df.groupby(['team', 'Col3', 'Col4']).apply(list))

This code returns the keys how I want them (in tuple form), but the values of each key are simply just the column names... like such

(t1, c2, x1): ['ID', 'team', 'Col3', 'Col4', 'Value']

How can I make it as such that the values of the dictionary are only the 'ID' and 'Value' columns?

pandas-groupby pandas python
1 Answers

If integers in ID should be converted to floats use custom lambda function for convert both columns selected after groupby to numpy array, then to lists and last convert to dictioanry:

gk = (df.groupby(['Team', 'Col3', 'Col4'])[['ID','Value']]
        .apply(lambda x: x.to_numpy().tolist())
        .to_dict())
print (gk)
{('t1', 'c2', 'x1'): [[3.0, 0.124], [1.0, 0.23]], ('t2', 'c11', 'x10'): [[6.0, 1.3419999999999999], [5.0, 75.2], [9.0, 34.97]], ('t3', 'c5', 'x2'): [[7.0, 0.654], [4.0, 123.3]]}

Or use zip of both columns in custom function, then types are not changed:

gk = (df.groupby(['Team', 'Col3', 'Col4'])
        .apply(lambda x: list(zip(x['ID'], x['Value'])))
        .to_dict())
print (gk)
{('t1', 'c2', 'x1'): [(3, 0.124), (1, 0.23)], ('t2', 'c11', 'x10'): [(6, 1.3419999999999999), (5, 75.2), (9, 34.97)], ('t3', 'c5', 'x2'): [(7, 0.654), (4, 123.3)]}
user_29010020
Related