GroupBy results to dictionary of lists

Question

I have an excel sheet that looks like so:

Column1 Column2 Column3
0       23      1
1       5       2
1       2       3
1       19      5
2       56      1
2       22      2
3       2       4
3       14      5
4       59      1
5       44      1
5       1       2
5       87      3

And I'm looking to extract that data, group it by column 1, and add it to a dictionary so it appears like this:

{0: [1],
1: [2,3,5],
2: [1,2],
3: [4,5],
4: [1],
5: [1,2,3]}

This is my code so far

excel = pandas.read_excel(r"e:\test_data.xlsx", sheetname='mySheet', parse_cols'A,C')
myTable = excel.groupby("Column1").groups
print myTable

However, my output looks like this:

{0: [0L], 1: [1L, 2L, 3L], 2: [4L, 5L], 3: [6L, 7L], 4: [8L], 5: [9L, 10L, 11L]}

Thanks!

for easy test, I add sample dataframe code. data = { 'col1': [0,1,1,1,2,2,3,3,4,5,5,5], 'col2': np.random.randint(0, 55, 12), 'col3': [1,2,3,5,1,2,4,5,1,1,2,3] } and df = pd.DataFrame(data) — hyun woo Cho, Oct 21 '22 at 00:13

Zero · Accepted Answer · 2018-08-31T15:41:40.523

97

You could groupby on Column1 and then take Column3 to apply(list) and call to_dict?

In [81]: df.groupby('Column1')['Column3'].apply(list).to_dict()
Out[81]: {0: [1], 1: [2, 3, 5], 2: [1, 2], 3: [4, 5], 4: [1], 5: [1, 2, 3]}

Or, do

In [433]: {k: list(v) for k, v in df.groupby('Column1')['Column3']}
Out[433]: {0: [1], 1: [2, 3, 5], 2: [1, 2], 3: [4, 5], 4: [1], 5: [1, 2, 3]}

edited Aug 31 '18 at 15:41

answered Apr 26 '15 at 10:02

Zero

74,117
18
147
154

22

Bit mean to accept this answer when @EdChum's is identical and was posted 3 minutes earlier. – LondonRob Jun 05 '15 at 19:07
What is the optimal way to do this for multiple features ie: Column1 is the consistent key but inplace of Column3, it can have Column4, Column5 and then merge so that Column1 still remains as a key to the dict. – Sade Mar 04 '21 at 09:05
2

wow man, your name should be Hero not Zero – Andrea Russett Mar 12 '21 at 20:25
2

what if I want to have `column2` and `column3`? stored on a dictionary, but also preserving its colnames? – Álvaro A. Gutiérrez-Vargas Feb 10 '23 at 12:40

score 46 · Answer 2 · edited May 23 '17 at 12:32

According to the docs, the GroupBy.groups:

is a dict whose keys are the computed unique groups and corresponding values being the axis labels belonging to each group.

If you want the values themselves, you can groupby 'Column1' and then call apply and pass the list method to apply to each group.

You can then convert it to a dict as desired:

In [5]:

dict(df.groupby('Column1')['Column3'].apply(list))
Out[5]:
{0: [1], 1: [2, 3, 5], 2: [1, 2], 3: [4, 5], 4: [1], 5: [1, 2, 3]}

(Note: have a look at this SO question for why the numbers are followed by L)

GroupBy results to dictionary of lists

2 Answers2

Linked

Related