Rows selection based on Column Values

pandas select rows by value
pandas select rows by multiple conditions
pandas select row by index
pandas select columns by condition
select rows of dataframe by column value
select rows with unique column value pandas
pandas find value in any column
pandas choose row based on value

I have a problem where for a single value there can be maximum of two rows. I have to select only one row based on the column value.Below example can show my data and what I am trying to achieve.

Table Item with the following information
Item   Dist_Building     Country_Building
I123   B123               B245   
I980   B980               B345
I780   B780               B445
Table item_info with the following columns
Item_number Building_Nbr Building_Area  Value
I123         B123        District        10
I123         B245        Country         20
I980         B980        District        50
I780         B445        Country         20

Select the items from the Item table and check for the corresponding Building information present in Item_info table. If for a certain item, values are present at District and Country level then select the District level VALUE else select the Country Level VALUE(i.e. row with district area will take the priority over the Country )

Item_number Value
I123         10
I980         50
I780         20

are the Dist_Building, Country_Building, and Building_Nbr important here? It looks like theyre not, in which case if you create a new table from the item_info table like so...

select 
  item_number
  , building_nbr
  , sum(case when building_area = 'District' then value end) as District_Val
  , sum(case when building_area = 'Country' then value end) as Country_Val
  , coalesce(District_Val, Country_Val) as Value 
into new_item_info
from item_info
group by 
  1,2 

you should be able to just left join it to the item table. (im not that familiar with Oracle SQL, so you may have to break the above code in to two steps)

Select rows from a Pandas Dataframe based on column values , A step-by-step Python code example that shows how to select rows from a Pandas DataFrame based on a column's values. Provided by Data Interview� Select DataFrame Rows Based on multiple conditions on columns Select rows in above DataFrame for which ‘Sale’ column contains Values greater than 30 & less than 33 i.e. filterinfDataframe = dfObj[ (dfObj['Sale'] > 30) & (dfObj['Sale'] < 33) ] It will return following DataFrame object in which Sales column contains value between 31 to 32,

One way to do this is with a join to either building, and then keep the one with the 'higher' area value (which works here as 'District' sorts after 'Country'):

select i.item,
  max(ii.value) keep (dense_rank last order by ii.building_area) as value 
from item i
join item_info ii on ii.item_number = i.item
and (
  (ii.building_nbr = i.dist_building and ii.building_area = 'District')
  or (ii.building_nbr = i.country_building and ii.building_area = 'Country')
)
group by i.item;

ITEM      VALUE
---- ----------
I123         10
I780         20
I980         50

You could make that more explicit with a case expression in the order-by clause if you don't want to rely on that sort order.

You could also do two outer joins and use coalesce to pick the one you want:

select i.item,
  coalesce(iid.value, iic.value) as value
from item i
left join item_info iid on iid.item_number = i.item
and iid.building_nbr = i.dist_building
and iid.building_area = 'District'
left join item_info iic on iic.item_number = i.item
and iic.building_nbr = i.country_building
and iic.building_area = 'Country';

ITEM      VALUE
---- ----------
I123         10
I780         20
I980         50

db<>fiddle

Selecting rows in pandas DataFrame based on conditions , Selecting rows based on particular column value using '>', '=', '=', '<=', '!=' operator . Code #1 : Selecting all the rows from the given dataframe in� Selecting rows and columns simultaneously You have to pass parameters for both row and column inside the.iloc and loc indexers to select rows and columns simultaneously. The rows and column values may be scalar values, lists, slice objects or boolean. Select all the rows, and 4th, 5th and 7th column:

You can achieve your goal by simply using sub-queries and the NVL function:

SELECT Item
     , NVL((SELECT VALUE FROM item_info ii 
             WHERE ii.item_number = i.item
               AND ii.Building_Nbr = i.Dist_Building)
          ,(SELECT VALUE FROM item_info ii 
             WHERE ii.item_number = i.item
               AND ii.Building_Nbr = i.Country_Building)) VALUE
  FROM Item i

How To Filter Pandas Dataframe By Values of Column?, Essentially, we would like to select rows based on one value or multiple values present in a column. Here are SIX examples of using Pandas dataframe to filter rows or select rows based values of a column(s). Let us first load gapminder data as a dataframe into pandas. This data frame has over 6000 rows and 6 columns. Let’s see how to Select rows based on some conditions in Pandas DataFrame. Selecting rows based on particular column value using '>', '=', '=', '<=', '!=' operator. Code #1 : Selecting all the rows from the given dataframe in which ‘Percentage’ is greater than 80 using basic method.

Using iloc, loc, & ix to select rows and columns in , The iloc, loc and ix indexers for Python Pandas select rows and columns from DataFrames. the .loc method directly selects based on index values of any rows. Often, you may want to subset a pandas dataframe based on one or more values of a specific column. Essentially, we would like to select rows based on one value or multiple values present in a column. Here are SIX examples of using Pandas dataframe to filter rows or select rows based values of a column (s).

How to select rows and columns in Pandas using [ ], .loc, iloc, .at and , The list values can be a string or a Python object. You can also use the filter method to select columns based on the column names or index labels. filter_method� My requirement is to select distinct email addresses, and then to select rows containing those addresses. Edit: I cannot use the "Group By" keyword either, because for this I will also have to Group By with Id (which is the PK) and doing this will return two rows with the same EmailAddress values but with different Ids.

How do I select a subset of a DataFrame? — pandas 1.1.0 , The selection returned a DataFrame with 891 rows and 2 columns. Remember, a You can assign new values to a selection based on loc / iloc . To user guide. Filter rows based on a list selection with Advanced Filter function. To filter rows based on a selection, you can apply the Advanced Filter function. 1. Select the column list you want to filter, and click Data > Advanced in the Sort & Filter group. See screenshot: 2.

Comments
  • Oracle doesn't let you use a column alias in the same level of query as it was defined, so your coalesce will get an 'invalid identifier'. You would need to use a subquery. (I think that's a SQL restriction so true of all databases, but not 100% sure). You can't use column position numbers in the group by clause either; and into isn't right here. This is quite a long way from being usable for an Oracle-tagged question.
  • Hi how are you suggesting to use a CASE statement in order by clause?
  • @sqlpractice - I was referring to the order-by clause within the keep; I didn't mean an overall order-by for the whole statement. So something like max(ii.value) keep (dense_rank last order by case ii.building_area when 'Country' then 1 when 'District' then 2 end) as value. That is more explicit and flexible, but possibly overkill in this query.