How to assign multiple values to a key in a dictionary, multiple times using pandas

Question

I have read an excel file with pandas. I want to loop over 4 values in 4 consecutive rows and assign those 4 values as a list to a certain key in a dictionary. When the first 4 values are assigned to the first key, i want to assign the next 4 values to the second key and so on.

s    t    values_I_need
AT   1          123
AT   2          21 
AT   3          1
AT   4          34
BT   1          34
BT   2          34
BT   3          213
BT   4          12
CE   1          23
CE   2          45
CE   3          234
CE   4          23
#and so on...

The Output I want to see is a dictionary y = { AT : [123,21,1,34], BT : [34,34,213,12], CE : [23,45,234,23]}

I tried the following but it only returns empty lists assigned to the keys, and the dictionary doesnt even contain all the keys from the excelsheet.

df = pd.read_excel("Inputdaten.xlsx", sheetname="dummy")
y = {}
lst = []
t=0
z=4
for row in df.itertuples():
    for i in df.iloc[t:z,2]:
        lst.append(i)
        if len(lst) ==4:
            t = t+4
            z = z+4
            y[row.s]= lst
            lst[:] = []
            break

What am I missing? Or is there a smarter way to code it without for-loops?

Thanks for any help in advance :)

there are only 4 rows per `s` ? could you try `df.groupby('s')['values_I_need'].agg(list).to_dict()` — anky, Aug 21 '19 at 16:22
Possible dup https://stackoverflow.com/questions/29876184/groupby-results-to-dictionary-of-lists — Mark Wang, Aug 21 '19 at 16:24

score 1 · Accepted Answer · answered Aug 21 '19 at 16:23

1

You could simply do the following:

result = df.groupby("s")["values_I_need"].apply(list).to_dict()

Output:

{'AT': [123, 21, 1, 34], 'BT': [34, 34, 213, 12], 'CE': [23, 45, 234, 23]}

answered Aug 21 '19 at 16:23

PMende

5,171
2
19
26

How to assign multiple values to a key in a dictionary, multiple times using pandas

1 Answers1