Python dataframe in one column strings delimit by comma, and in another column if pass or fail

Question

How I can count if a country that is in more rows , has failed or passed, enter image description here

Like is

ID unique   Countries                    Test
1           Spain, Netherlands               Fail
2           Italy                             Pass
3         France, Netherlands                Pass
4         Belgium, France, Bulgaria         Fail
5         Belgium, United Kingdom            Pass
6        Netherlands, France                 Pass
7        France, Netherlands, Belgiu        Pass

and the result should be like this enter image description here

             Pass   Fail
Spain           0   1
Italy           1   0
France          3   1
Netherlands     3   1
Belgium         2   1
United Kingdom  1   0

Because Netherlands is in 4 rows , and has 3 passed and one failed.

jezrael · Accepted Answer · 2021-06-17T07:37:28.617

0

Use Series.str.split with DataFrame.explode and last call crosstab:

df1 = df.assign(Countries = df.Countries.str.split(', ')).explode('Countries')
df2 = pd.crosstab(df1['Countries'],df1['Test'])

edited Jun 17 '21 at 07:37

answered Jun 17 '21 at 06:59

jezrael

822,522
95
1,334
1,252

does `explode()` only work with `lists`? I tried directly on the original `Countries` column and it didn't work. – Karthik S Jun 17 '21 at 07:51
1

@KarthikS - than need only `df1 = df.explode('Countries')` – jezrael Jun 17 '21 at 07:54
I tried your answer, and i get typeError: Dataframe is not callable, i m using an excel file , i tried to do something like this : https://stackoverflow.com/questions/12680754/split-explode-pandas-dataframe-string-entry-to-separate-rows , and then counting if the value of a country is passed or failed – George Dascalu Jun 17 '21 at 09:09
@GeorgeDascalu - Can you edit question with your code, especially what failed? – jezrael Jun 17 '21 at 09:15
@jezrael Thank you, it worked , the mistake that i made was to put pd as dataframe. – George Dascalu Jun 17 '21 at 13:07
@jezrael Done. Thanks again. – George Dascalu Jun 18 '21 at 06:49

Python dataframe in one column strings delimit by comma, and in another column if pass or fail

1 Answers1