How to create a new data frame based on conditions from another data frame

Question

Just getting into Python, so hopefully I'm not asking a stupid question here...

So I have a pandas dataframe named "df_complete' with let's say 100 rows, and containing columns named: "type", "writer", "status", 'col a', 'col c'. I want to create/update a new dataframe named "temp_df" and create it based on conditions using "df_complete" values.

temp_df = pandas.DataFrame()

if ((df_complete['type'] == 'NDD') & (df_complete['writer'] == 'Mary') & (df_complete['status'] != '7')):
    temp_df['col A'] = df_complete['col a']
    temp_df['col B'] = 'good'
    temp_df['col C'] = df_complete['col c']

However, when I do this, I got the following error message:

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

I read this thread and changed my "and" to "&": Truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()

I also read this thread here to put everything in parenthesis: comparing dtyped [float64] array with a scalar of type [bool] in Pandas DataFrame

But the error is still present. What is causing this? and how can I fix it?

** follow up question ** Also, how can I obtain the index values of those rows that met the condition?

The problem is you are putting your big boolean expression, which results in a boolean-valued Series, in an `if` condition, which, as the error says, has a truth value that is ambiguous. Can you say in words exactly what that `if` block is suppose to accomplish? — juanpa.arrivillaga, Nov 06 '16 at 23:04

score 9 · Accepted Answer · edited Sep 27 '21 at 05:20

9

I think you need boolean indexing with loc for selecting only columns col a and col c:

temp_df = df_complete.loc[(df_complete['type'] == 'NDD') & 
                         (df_complete['writer'] == 'Mary') & 
                         (df_complete['status'] != '7'), ['col a','col c']]
#rename columns
temp_df = temp_df.rename(columns={'col a':'col A','col c':'col C'})
#add new column 
temp_df['col B'] = 'good'
#reorder columns
temp_df = temp_df[['col A','col B','col C']]

Sample:

df_complete = pd.DataFrame({'type':  ['NDD','NDD','NT'],
                            'writer':['Mary','Mary','John'],
                            'status':['4','5','6'],
                            'col a': [1,3,5],
                            'col b': [5,3,6],
                            'col c': [7,4,3]}, index=[3,4,5])

print (df_complete)
   col a  col b  col c status type writer
3      1      5      7      4  NDD   Mary
4      3      3      4      5  NDD   Mary
5      5      6      3      6   NT   John

temp_df = df_complete.loc[(df_complete['type'] == 'NDD') & 
                         (df_complete['writer'] == 'Mary') & 
                         (df_complete['status'] != '7'), ['col a','col c']]

print (temp_df)  
   col a  col c
3      1      7
4      3      4

temp_df = temp_df.rename(columns={'col a':'col A','col c':'col C'})
#add new column 
temp_df['col B'] = 'good'
#reorder columns
temp_df = temp_df[['col A','col B','col C']]
print (temp_df)  
   col A col B  col C
3      1  good      7
4      3  good      4

edited Sep 27 '21 at 05:20

zr0gravity7

2,917
1
12
33

answered Nov 06 '16 at 23:01

jezrael

822,522
95
1,334
1,252

what is `df` here? `df_complete`, perhaps? – juanpa.arrivillaga Nov 06 '16 at 23:03
Hi @jezrael thank you SO much for the detailed explanation! Can I ask a follow up question as to how to obtain the index values of those rows that met the condition? – alwaysaskingquestions Nov 06 '16 at 23:26
I think it is not necessary, because if use `boolean indexing` it select data from original dataframe with original indexes. – jezrael Nov 06 '16 at 23:29
I edit sample with custom indexes and with returning 2 rows - there indexes from original dataframe. then if need select them use `temp_df.index` – jezrael Nov 06 '16 at 23:33
@jezrael Thank you so much! Your answer is truly helpful – alwaysaskingquestions Nov 06 '16 at 23:57
5

ix is depreciated for those look at this years later – May 17 '18 at 21:22
`ix` is deprecated, use [`loc`](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.loc.html) instead. – zr0gravity7 Sep 26 '21 at 19:22
May I ask how you'd do if you've just one column to check, but against a list of possible values? How do you loop through the list instead of writing explicitly all the `or`? Thanks! – Davide Barranca Oct 17 '21 at 16:53
@DavideBarranca - You can use [`isin`](https://stackoverflow.com/questions/19960077/how-to-filter-pandas-dataframe-using-in-and-not-in-like-in-sql) – jezrael Oct 18 '21 at 05:02

score 3 · Answer 2 · edited Feb 19 '21 at 12:05

3

In the current version of Pandas, the .ix has deprecated; instead, use .loc.

temp_df = df_complete.loc[]

edited Feb 19 '21 at 12:05

apaderno

28,547
16
75
90

answered Feb 19 '21 at 01:42

Mickey

31
1

How to create a new data frame based on conditions from another data frame

2 Answers2

Linked