2

Having a dataframe consisting of a person and the order...

person     order                                 elements
Alice      [drink, snack, salad, fish, dessert]  5          
Tom        [drink, snack]                        2          
John       [drink, snack, soup, chicken]         4          
Mila       [drink, snack, soup]                  3

I want to known what customers had as a main meal. Thus, I want to add another column [main_meal] so that would be my df.

person     order                               elements   main_meal
Alice      [drink, snack, salad, fish, dessert]  5          fish
Tom        [drink, snack]                        2          none
John       [drink, snack, soup, chicken]         4          chicken
Mila       [drink, snack, soup]                  3          none

The rule is that if a customer ordered 4 or more meals, it means the 4th element is always the main dish, so I want to extract the 4th element from the list on the order column. If it contains less than 4 elements, then assign 'main_meal' to none. My code:

df['main_meal'] = ''
if df['elements'] >= 4:
     df['main_meal'] = df.order[3]
else:
     df['main_meal'] = 'none'

It doesn't work:

 ValueError                                Traceback (most recent call last)
 <ipython-input-100-39b7809cc669> in <module>()
     1 df['main_meal'] = ''
     2 df.head(5)
 ----> 3 if df['elements'] >= 4:
       4     df['main_meal'] = df.order[3]
       5 else:

 ~\Anaconda\lib\site-packages\pandas\core\generic.py in __nonzero__(self)
 1571         raise ValueError("The truth value of a {0} is ambiguous. "
 1572                          "Use a.empty, a.bool(), a.item(), a.any() or 
 a.all()."
 -> 1573                          .format(self.__class__.__name__))
 1574 
 1575     __bool__ = __nonzero__

 ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

What is wrong with my code?

Tadas Melnikas
  • 87
  • 2
  • 3
  • 12
  • `df['elements'] >= 4` is an array. Some elements may be true, some may be false. You have to explain the `if` statement how do you want to handle this case. – DYZ Aug 29 '18 at 14:31
  • 1
    @DYZ I think he meant `if len(df['elements']) >= 4`, but he didn't vectorize the operation, he's thinking in one row at a time. – vhcandido Aug 29 '18 at 14:33
  • This question has dozens of dupes. Here's [one](https://stackoverflow.com/questions/30944577/check-if-string-is-in-a-pandas-dataframe). – DYZ Aug 29 '18 at 14:33
  • `np.where(len(df.order.str.len()>4),df.order.str[3],'None')` – BENY Aug 29 '18 at 14:36

3 Answers3

4

Use str method to slice

In [324]: df['order'].str[3]
Out[324]:
0       fish
1        NaN
2    chicken
3        NaN
Name: order, dtype: object

In [328]: df['main_meal'] = df['order'].str[3].fillna('none')

In [329]: df
Out[329]:
  person                                 order  elements main_meal
0  Alice  [drink, snack, salad, fish, dessert]         5      fish
1    Tom                        [drink, snack]         2      none
2   John         [drink, snack, soup, chicken]         4   chicken
3   Mila                  [drink, snack, soup]         3      none
Zero
  • 74,117
  • 18
  • 147
  • 154
1

For small dataframes, you can use the str accessor as per @Zero's solution. For larger dataframes, you may wish to use the NumPy representation to create a series:

# Benchmarking on Python 3.6, Pandas 0.19.2

df = pd.concat([df]*100000)

%timeit pd.DataFrame(df['order'].values.tolist())[3]  # 125 ms per loop
%timeit df['order'].str[3]                            # 185 ms per loop

# check results align
x = pd.DataFrame(df['order'].values.tolist())[3].fillna('None').values
y = df['order'].str[3].fillna('None').values
assert (x == y).all()
jpp
  • 159,742
  • 34
  • 281
  • 339
0

You can also use the apply and lambda functions.

df['main_meal'] = df['order'].apply(lambda r: r[3] if len(r) >= 4 else 'none')

It's slower than @jpp's answer for large datasets but faster (and more verbose) than both @jpp's and @Zero's answers for smaller ones (note that I added .fillna() so they return the same result):

%timeit df['order'].apply(lambda r: r[3] if len(r) >= 4 else 'none')  # 242 µs
%timeit pd.DataFrame(df['order'].values.tolist())[3].fillna('none')  # 1.17 ms
%timeit df['order'].str[3].fillna('none')  # 487 µs

# Large dataset
df = pd.concat([df]*100000)

%timeit df['order'].apply(lambda r: r[3] if len(r) >= 4 else 'none')  # 118ms
%timeit pd.DataFrame(df['order'].values.tolist())[3].fillna('none')  # 51.8ms
%timeit df['order'].str[3].fillna('none')  # 309ms

And if you check their values they'll match.

x = df['order'].apply(lambda r: r[3] if len(r) >= 4 else 'none')
y = pd.DataFrame(df['order'].values.tolist())[3].fillna('none')
z = df['order'].str[3].fillna('none')

(x.values == y.values).all()  # True
(x.values == z.values).all()  # True

Python 3.6.6 | pandas 0.23.4

vhcandido
  • 344
  • 2
  • 5