How to select values out of many in pandas dataframe using conditions?

user_2287486 0
Viewed 76

I have a CSV with multiple values for a single value and I have to filter them out based on several conditions. Below is an example of my data.

df1 = pd.DataFrame(
data=[['Afghanistan','2.7;2.7','27.0;26.7','','22.9;22.8'],
    ['Bahrain','6.3;6.3;6.4','13.0;13.0;13.0','16.8;17.0',''],
    ['Djibouti','3.0;3.0;3.0','2.0','','23.1;24']],
columns=['Country', '2019', '2018', '2017', '2016'])

Following are the conditions to use to filter:

  1. if the values are duplicated, select one.
  2. if the values differ and the difference is less than 0.5, for eg. 26.7 and 27.0, we select 26.7 as we want to preserve the precision and would discard the rounding offs eg. 6.7 and 6.8, preserving both as both give precision. However, this contradicts the 0.5 rule, so taking any is also okay
  3. If the values differ and the difference is more than 0.5, select both eg. 23.1 and 24, select both

Below is my desired output for this example.

desired_op = pd.DataFrame(
data=[['Afghanistan','2.7','26.7','','22.9;22.8'],
    ['Bahrain','6.3;6.4','13.0','16.8',''],
    ['Djibouti','3.0','2.0','','23.1;24']],
columns=['Country', '2019', '2018', '2017', '2016'])

This is a small example of the dataset. To conduct this operation, I have to convert the values to numeric format first, However, the row headings (country) and column headings(year) still have to be a string. I have more than 20 columns, and more than 50 datasets so converting each column's data to numeric is also not feasible. Please Help!

pandas data-cleaning dataframe python
1 Answers

Use from apply method for each col

def f(x):
    a = x.split(';')
    if cond1:
        return ...
    if cond2:
        return ...
    if cond3:
        return ...
df['2019']=df['2019'].apply(f)
...

For your many cols you can do:

for i in df.columns:
    if i != 'country':
        df[i]=df[i].apply(f)

also you can put your dataframes to a list and with a for loop iterate on each df and do above operations

for example you can remove duplicate such as below:

def f(x):
    a=x.split(';')
    if len(a)>len(set(a)):
        return ';'.join(list(set(a)))

it returns a string value

user_192344240
Related