Agfdhyk

I have a dataframe called data it's have 2 columns as follows:

color  fruitN

red    apple

yellow orange

blue   banana

green  avocado

Also, I have 3 different files of one columns; each contain list of fruits as follows:

file1

akee

apricot

avocado



file2

avocado

bilberry

banana

blackberry



file3

blackberry

coconut

cranberry

I need to create another dataframe called type that have # of rows equal the same as data= 4, and # of columns equal same as the number of files = 3

I need to check each fruit in column fruitN of the dataframe data if it's exist in first file, put 1 in first row first column and continue checking in file2 and file3. So, the output dataframe type should be like that:

c1 c2 c3

0  0  0

0  0  0

0  1  0

1  1  0

Then, concatenated with data to be like this:

color  fruitN c1 c2 c3

red    apple  0  0  0

yellow orange 0  0  0

blue   banana 0  1  0

green  avocado 1 1  0

I'm beginner with python, so any help would be appreciated.

Using isin

l=[f1,f2,f3]

for x,y in enumerate(l):

    df['c'+str(x+1)]=df.fruitN.isin(y.iloc[:,0].tolist()).astype(int)



df

Out[144]: 

    color   fruitN  c1  c2  c3

0     red    apple   0   0   0

1  yellow   orange   0   0   0

2    blue   banana   0   1   0

3   green  avocado   1   1   0

I think you can use this:

data.loc[:, 'c1'] = np.where(data['fruitN'].isin(file1.values(), 1, 0)

That should create c1, repeat to create the other two columns. At the end all the information you want will be in dataframe data.

You might need to import numpy

import numpy as pd

At first create the data-frames like:

import pandas as pd

data = pd.DataFrame(data=[["red", "apple"], ["yellow", "orange"], ["blue", "banana"], ["green", "avocado"]],

                    columns=["color", "fruitN"])

data = data.set_index("fruitN")

file_1 = ["akee", "apricot", "avocado"]

file_2 = ["avocado", "bilberry", "banana", "blackberry"]

file_3 = ["blackberry", "coconut", "cranberry"]

file_1_df = pd.DataFrame(data=[1] * len(file_1), index=file_1, columns=["type_1"])

file_2_df = pd.DataFrame(data=[1] * len(file_2), index=file_2, columns=["type_2"])

file_3_df = pd.DataFrame(data=[1] * len(file_3), index=file_3, columns=["type_3"])

then concatenate them with corresponding axis and set the sort to false:

data_concat = pd.concat([data, file_1_df, file_2_df, file_3_df], axis=1, sort=False).fillna(0)

then pick the right indices and reformat result data as you like, I did it to get exactly what you mentioned that you need:

res = data_concat.loc[["apple", "orange", "banana", "avocado"]]

res.reset_index(level=0, inplace=True)

res.columns = ["fruitN", "color", "type_1", "type_2", "type_3"]

res = res.ix[:, ["color", "fruitN", "type_1", "type_2", "type_3"]]



print(res)

it gives:

    color   fruitN  type_1  type_2  type_3

0     red    apple     0.0     0.0     0.0

1  yellow   orange     0.0     0.0     0.0

2    blue   banana     0.0     1.0     0.0

3   green  avocado     1.0     1.0     0.0

I hope this may help you.