Pandas : create a list based on index of a Dataframe

Question

I am trying to obtain a list from a Dataframe based on a common value of the index.

In the example below I am trying to obtain the lists for 'type' and 'xx' based on 'date'.

Here is the Dataframe:

import pandas as pd
import numpy as np

idx = [np.array(['Jan', 'Jan', 'Feb', 'Mar', 'Mar', 'Mar']),np.array(['A1', 'A2', 'A2', 'A1', 'A3', 'A4'])]
data = [{'xx': 1}, {'xx': 5}, {'xx': 3}, {'xx': 2}, {'xx': 7}, {'xx': 3}]
df = pd.DataFrame(data, index=idx, columns=['xx'])
df.index.names=['date','type']
df.reset_index(inplace=True)
df=df.set_index(['date'])

Which looks like this:

      type  xx
date        
 Jan    A1   1
 Jan    A2   5
 Feb    A2   3
 Mar    A1   2
 Mar    A3   7
 Mar    A4   3

What I am trying to do is to create these two lists:

#list_type
[['A1', 'A2'], ['A2'], ['A1', 'A3', 'A4']]

#list_xx
[['1', '5'], ['3'], ['2', '7', '3']]

As you can see, the elements of the lists are constructed based on a common date.

I would really value an efficient way of doing this in Python.

jezrael · Accepted Answer · 2020-02-16 12:57:28Z

4

Use GroupBy.agg with list and then convert DataFrame to dictionary of lists by DataFrame.to_dict:

d = df.groupby(level=0, sort=False).agg(list).to_dict('l')
print (d)
{'type': [['A1', 'A2'], ['A2'], ['A1', 'A3', 'A4']], 'xx': [[1, 5], [3], [2, 7, 3]]}

print (d['type'])
[['A1', 'A2'], ['A2'], ['A1', 'A3', 'A4']]

print (d['xx'])
[[1, 5], [3], [2, 7, 3]]

answered Feb 16, 2020 at 12:57

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas : create a list based on index of a Dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related