python, count unique list values of a list inside a data frame

user_13941428 0
Viewed 714

I have a dataframe which contains two columns of user feedback.

The first column is from a multi-choice answer of the survey. In each row of the column is a list of the answers they selected.

the next column is a category of age range. so one row will contain a list of the users colour preferences and an age range.

e.g.

what colours do you like?         age
['yellow','orange','green']       18-25
['yellow']                        18-25
['blue','green','red','orange']   26-30
['blue','red']                    26-30

I'm looking to get individual counts for each colour in the list and then split by age range desired output:

age    colour   count
18-25  yellow     2
18-25  orange     1
18-25  green      1
26-30  blue       2
26-30  green      1
26-30  red        2
26-30  orange     1

thanks in advance!

pandas count list python split
2 Answers

Set the index of dataframe as age, then use Series.explode on column what colours do you like?' then use groupby on level=0 and aggregate the series using value_counts:

df1 = (
    df.set_index('age')['what colours do you like?'].explode()
    .rename('color').groupby(level=0).value_counts().reset_index(name='count')
)

Result:

print(df1)
     age   color  count
0  18-25  yellow      2
1  18-25   green      1
2  18-25  orange      1
3  26-30    blue      2
4  26-30     red      2
5  26-30   green      1
6  26-30  orange      1
user_128331660

Another option:

df1 = df.apply(lambda x: [str(x[0][i]) + " " + str(x[1]) for i in range(len(x[0]))], axis = 1).agg("sum")
df1 = pd.DataFrame(np.unique(df1, return_counts = True), index = ["A","count"]).T
df1[["colour","age"]] = df1.A.str.split(expand = True)
df1 = df1.drop("A")

It should output:

df1

   count   age   color  
0      2 18-25  yellow  
1      1 18-25   green  
2      1 18-25  orange  
3      2 26-30    blue  
4      2 26-30     red  
5      1 26-30   green  
6      1 26-30  orange
user_68134170
Related