converting pandas dataframe to contain a dictionary or list of lists

Question

      state      Year  Month  count
0       alabama  2017.0   10.0     31
1       alabama  2017.0   11.0     30
2       alabama  2017.0   12.0     31
3       alabama  2018.0    1.0     31
4       alabama  2018.0    2.0     28
5       alabama  2018.0    3.0     31
6       alabama  2018.0    4.0     30
7       alabama  2018.0    5.0     31
8       alabama  2018.0    6.0     30
9       alabama  2018.0    7.0     14
10     arkansas  2017.0   10.0     31
11     arkansas  2017.0   11.0     30
12     arkansas  2017.0   12.0     31

Can I convert dataframe above to:

                                                            Month
state                                                        
alabama         {2017:10.0, 2017:11.0, 2017:12.0, 2018:1.0, 2018:2.0, 2018:3.0, 2018:4.0, 2018:5.0, 2018:6.0, 2018:7.0}
arkansas        {2017:10.0, 2017:11.0, 2017:12.0}

related to converting pandas dataframe to contain a list

based on @Vaishali's comment below, since dictionary cannot contain duplicate keys, this should be ok too:

                                                            Month
state                                                        
alabama         [[2017,10.0], [2017,11.0], [2017,12.0], [2018,1.0], [2018,2.0], [2018,3.0], [2018,4.0], [2018,5.0], [2018,6.0], 2[018,7.0]]
arkansas        [[2017,10.0], [2017,11.0], [2017,12.0]]

Did you look at the NumPy level answer from the linked duplicate on your other question? With a bit of effort, adapting it for your dicts (with proper keys) should not be hard. Have you tried anything? — miradulo, Dec 04 '17 at 20:20
@user308827, I'd remove it from the question then. May be misleading for others too — MattR, Dec 04 '17 at 20:21
@MattR Not really? There are plenty of Pandas operations where you ignore certain columns (and using a subset of columns is part of the specification). — miradulo, Dec 04 '17 at 20:22
You can at best get alabama {2017.0: 12.0, 2018.0: 7.0} and arkansas {2017.0: 12.0} — Vaishali, Dec 04 '17 at 20:23
@user308827, I might be splitting hairs here. that's why I used "may" but when I think [MVCE](https://stackoverflow.com/help/mcve) it's a luxury to have only what you need. — MattR, Dec 04 '17 at 20:24
As @Vaishali has already said it contradicts the nature of dictionary - you can't have duplicated keys in a dictionary — MaxU - stand with Ukraine, Dec 04 '17 at 20:26
thanks @Vaishali, you have a valid point. I have modified question to get list of lists instead of dictionary — user308827, Dec 04 '17 at 20:27
@MattR, I will point this out in the question. I do not want to remove `count` just in case proposed soln does not work with `count` included — user308827, Dec 04 '17 at 20:28

score 5 · Accepted Answer · answered Dec 04 '17 at 20:28

5

Try

df.groupby('state').apply(lambda x: list(zip(x['Year'], x['Month'])))


state
alabama     [(2017.0, 10.0), (2017.0, 11.0), (2017.0, 12.0...
arkansas     [(2017.0, 10.0), (2017.0, 11.0), (2017.0, 12.0)]

answered Dec 04 '17 at 20:28

Vaishali

37,545
5
58
86

score 2 · Answer 2 · answered Dec 04 '17 at 20:28

In [73]: (df.groupby('state')['Year','Month']
            .apply(lambda x: x.values.tolist())
            .to_frame('Month')
            .reset_index())
Out[73]:
      state                                              Month
0   alabama  [[2017.0, 10.0], [2017.0, 11.0], [2017.0, 12.0...
1  arkansas   [[2017.0, 10.0], [2017.0, 11.0], [2017.0, 12.0]]

score 1 · Answer 3 · answered Dec 04 '17 at 20:27

I guess this will work.

d={}
for index, row in df.iterrows():
  if(d.get(row['state'],0)==0):
    d[row['state']=[].append(str(row['year'])+" : "+ str(row['month']))
  else:
    d[row['state']] = d[row['state']].append(str(row['year'])+" : "+ str(row['month']))

This will have it like

arkansas        ["2017 : 10.0", "2017 : 11.0", "2017 : 12.0"]

score 1 · Answer 4 · answered Dec 06 '17 at 00:13

1

Or also

df.groupby('state').apply(lambda x:x[['Year','Month']].values)

state
alabama     [[2017.0, 10.0], [2017.0, 11.0], [2017.0, 12.0...
arkansas     [[2017.0, 10.0], [2017.0, 11.0], [2017.0, 12.0]]

answered Dec 06 '17 at 00:13

DJK

8,924
4
24
40

converting pandas dataframe to contain a dictionary or list of lists

4 Answers4

Linked