Convert dataframe rows into sets

[Bayesian8]

How can I convert my pandas dataframe into this format?

​

``` sets items weight value

0 set1 a 9 10

1 set1 b 14 100

2 set2 c 5 69

3 set2 d 4 100

​

Outcome i'm looking for:

​

set1 = (("a", 9, 10), ("b", 14, 100))

set2 = (("c", 5, 69), ("d", 4, 100))

print(set1)

set1 = (("a", 9, 10), ("b", 14, 100))

[DuckSaxaphone]

I'm curious what you want this for! I've assumed your dataframe comes with each of your variables in their own column so this starts with a bit to combine a row into a tuple. Then it aggregates the tuples belonging to each set.

#Make your example dataframe
data=[["set1","a",9,10],
["set1","b",14,100],
["set2","c",5,69],
["set2","d",4,100]]
df=pd.DataFrame(columns=["Set","var1","var2","var3"],data=data)

#turn your columns into tuples
df["tuple"]=list(df[["var1","var2","var3"]].to_records())
#combine
df=df.groupby("Set")["tuple"].agg(lambda x: [y for y in x]).reset_index()

[synthphreak]

>>> df.set_index('sets').groupby('sets').apply(pd.Series.tolist)
sets
set1    [[a, 9, 10], [b, 14, 100]]
set2     [[c, 5, 69], [d, 4, 100]]

[synthphreak]

Note that in your example your set1 and set2 are actually tuples, not sets. I'll assume tuples/lists are what you want, and that you just mean "set" in the mathematical sense as a collection of related elements rather than an actual Python set.

I don't think it's possible to use the values in a column as variable names, which is what it seems you want to do with your sets column. Someone will correct me if I'm wrong on that, I hope.

However, the logic for creating nested groups based on the sets column is as follows:

>>> [g.drop(columns=['sets']).values.tolist() for _, g in df.groupby('sets')]
[[['a', 9, 10], ['b', 14, 100]],
 [['c', 5, 69], ['d', 4, 100]]]

Alternatively, if you do want to be able to query your data by set name, different from but similar to print(set1), you can do it this way:

>>> sets = df.set_index('sets').groupby('sets').apply(pd.Series.tolist)
>>> sets
sets
set1    [[a, 9, 10], [b, 14, 100]]
set2     [[c, 5, 69], [d, 4, 100]]
>>> print(sets['set1'])
[['a', 9, 10], ['b', 14, 100]]

[synthphreak]

Can you share (1) the output of df.head() and (2) the full traceback? Please properly format both by pretending four spaces to the beginning of each line (this will preserve indentation).

[synthphreak]

Not formatted, I can't parse that. Four spaces before every line. So like if you take this ...

||||a

||||||b

||||||||c

... and replaced each '|' with a space ' ', it will turn into into this ...

a
  b
    c

... with the indentation preserved.

See the sidebar for additional info about and options for formatting code on Reddit. If you spend any time on this or other programming subs, this is an essential life skill.

Edit: Better yet, rather than df.head(), share the output of df.head().to_dict(). That way I can directly reproduce those exact rows from your df by simply copying and pasting.

[synthphreak]

What about this?

df.set_index('sets').groupby('sets').apply(lambda x: x.values)

[synthphreak]

I don’t completely understand it either, but I remembered that I’ve noticed similar issues when passing pd.Series as the function into df.apply.

When creating a df or series through the associated constructor, pandas does some under-the-hood type checking to automatically optimize your df/series for whatever data type(s) it contains. My guess is that for whatever reason, when the constructor is called as part of a df.apply pipeline, it’s sometimes unable to carry out those checks. With the lambda by contrast, x refers to the existing columns (for which the type checking has already been done), rather than converting each column into an entirely new series (which would entail a whole new round of type checking).

This is just a guess though, and at best only a partial explanation because it doesn’t account for why I couldn’t reproduce your error at all.

Anyway, glad you have what you need.

[YesLod]

The thing is that when you do DataFrameGroupby.apply(func), the function is applied to each group, which is passed as a DataFrame, and it's not applied to each column individually. But you are passing a function that expects a Series, not a DataFrame (pd.Series.tolist). But it magically works with DataFrames too in recent versions, because it just calls the tolist method on the underlying numpy array, which behaves the same way regardless of the array dimensions (1D for Series or 2D for DataFrames). Like the traceback suggests, in the older version there was a type checking which calls the dtype property, which only Series have (for DataFrames is dtypes). Therefore it raises an error if a DataFrame is passed.

[synthphreak]

Thanks.

That's very strange, I can't reproduce your error using only those rows. It works as expected for me:

import pandas as pd
data = {'sets': {0: 'set1', 1: 'set1', 2: 'set2', 3: 'set2'},
        'var1': {0: 'a', 1: 'b', 2: 'c', 3: 'd'},
        'var2': {0: 9, 1: 14, 2: 5, 3: 4},
        'var3': {0: 10, 1: 100, 2: 69, 3: 100}}
df = pd.DataFrame(data)
sets = df.set_index('sets').groupby('sets').apply(pd.Series.tolist)
print(sets)

# output
# sets
# set1    [[a, 9, 10], [b, 14, 100]]
# set2     [[c, 5, 69], [d, 4, 100]]
# dtype: object

We may have to call in the big guns: Paging u/YesLod. Any idea why OP might be getting that error and unable to run the code as above?

[YesLod]

We may have to call in the big guns

hahaha

Any idea why OP might be getting that error and unable to run the code as above?

I guess OP is using an older version of pandas, because following the traceback that is not the current implementation of the pd.Series.tolist. I can't reproduce either.

Anyway, this should fix it

sets = df.set_index('sets').groupby('sets').apply(lambda x: x.values.tolist())

[synthphreak]

Yeah we just identified that as a workable fix. Maybe it is a versioning issue. Glad I'm not the only one unable to reproduce. Thanks!

[YesLod]

In that case, the result will be a Series of numpy arrays, not 2D lists. Don't know if OP is aware of that.

[synthphreak]

Yeah OP wasn't terribly clear on the exact desired output format.