CODEWITHSUNDEEP
pythonpandasdictionarydataframe
F.Lira
F.Lira

Reputation: 653

Merge dictionaries to dataframe get_dummies

In a dictionary with information about a string in a text file, where keys are the strings and values are the names of the files. 

Dict1 = {'str1A':'file1', 'str1B':'file1', 'str1C':'file1', 'str1D':'file1', 'str2A':'file2', 'str2B':'file2', 'str2C':'file2', 'str2D':'file2', 'str2D':'file2', 'str3A':'file3',

'str3B':'file3','str3C':'file3', 'str3D':'file3', 'str3D':'file3' , 'str4A':'file4', 'str4B':'file4', 'str4C':'file4', 'str4D':'file4', 'str4E':'file4'}

Another dictionary contains information about the best match for the strings from the text.

Dict2 = {'str1A':'jump', 'str1B':'fly', 'str1C':'swim', 'str2A':'jump', 'str2B':'fly', 'str2C':'swim', 'str2D':'run', 'str3A':'jump', 'str3B':'fly', 'str3C':'swim', 'str3D':'run'}

The third dictionary contains information about the percentage of occurrence of the string in the text.

Dict3 = {'str1A':'90', 'str1B':'60', 'str1C':'30', 'str2A':'70', 'str2B':'30', 'str2C':'60', 'str2D':'40', 'str3A':'10', 'str3B':'90', 'str3C':'70', 'str3D':'90'}

Now my aims are to use the information of these different dictionaries to generate a dataframe like this:

       jump     fly     swim    run
file1   90      60      30      NA
file2   70      30      60      40
file3   10      90      70      90

To this, I started the script but I am stuck:

col_file = ['str', 'file']
df_origin = pd.DataFrame(Dict1.items(), columns=col_file)
#print df_origin

col_bmatch = ['str', 'text']
df_bmatch =  pd.DataFrame(Dict2.items(), columns=col_bmatch)
#print df_bmatch

col_percent = ['str', 'percent']
df_percent = pd.DataFrame(Dict3.items(), columns=col_percent)
#print df_percent

This block was removed from script: 

df_origin['text'] = df_origin['str'].map(df_bmatch.set_index('str')['text'])

df_origin['percent'] = df_origin['str'].map(df_percent.set_index('str')['percent'])

And substituted to: 

data = {}
for k, col in Dict1.items():
    if k in Dict1 and k not in Dict3:
        data.setdefault(k, {})[col] = "NA"
    elif k in Dict1 and k in Dict3:
        data.setdefault(k, {})[col] = Dict3[k]

    df = pd.DataFrame(data)

print(df)

But the final result was not very exact:

      str1A str1B str1C str1D str2A str2B str2C str2D str3A str3B  \
file1     90     60     30     NO    NaN    NaN    NaN    NaN    NaN    NaN   
file2    NaN    NaN    NaN    NaN     70     30     60     40    NaN    NaN   
file3    NaN    NaN    NaN    NaN    NaN    NaN    NaN    NaN     10     90   
file4    NaN    NaN    NaN    NaN    NaN    NaN    NaN    NaN    NaN    NaN   

      str3C str3D str4A str4B stre4C str4D str4E  
file1    NaN    NaN    NaN    NaN    NaN    NaN    NaN  
file2    NaN    NaN    NaN    NaN    NaN    NaN    NaN  
file3     70     90    NaN    NaN    NaN    NaN    NaN  
file4    NaN    NaN     NO     NO     NO     NO     NO

But the expected table is:

         jump   fly    swim   run   sit
file1    90     60     30     NA    NA
file2    70     30     60     40    NA
file3    10     90     70     90    NA
file4    NA     NA     NA     NA    NA

Where the string in file4 where not detected.

Blosk removed

print df_origin

#          str   file  text percent
#    0   str2B  file2   fly      30
#    1   str2C  file2  swim      60
#    2   str3C  file3  swim      70
#    3   str3B  file3   fly      90
#    4   str3D  file3   run      90
#    5   str2D  file2   run      40
#    6   str3A  file3  jump      10
#    7   str1D  file1   NaN     NaN
#    8   str1C  file1  swim      30
#    9   str1B  file1   fly      60
#    10  str1A  file1  jump      90
#    11  str2A  file2  jump      70

Here relies the problem

print pd.get_dummies(df_origin.set_index('file')['text']).max(level=0).max(level=0, axis=1)

But the only result that I get is this:

       fly  jump  run  swim
file                       
file2    1     1    1     1
file3    1     1    1     1
file1    1     1    0     1

As I can understand, pd.getdummies groups the field 'file' from my df_origin and uses 'text' to check their presence.

How can I redirect the command to plot the columns 'percent' in my df_origin dataframe?

Upvotes: 1

Views: 103

Answers (2)

javidcf
javidcf

Reputation: 59731

Try this:

import pandas as pd

Dict1 = {'str1A':'file1', 'str1B':'file1', 'str1C':'file1', 'str1D':'file1', 'str2A':'file2', 'str2B':'file2', 'str2C':'file2', 'str2D':'file2', 'str2D':'file2', 'str3A':'file3', 'str3B':'file3','str3C':'file3', 'str3D':'file3', 'str3D':'file3' , 'str4A':'file4', 'str4B':'file4', 'str4C':'file4', 'str4D':'file4', 'str4E':'file4'}
Dict2 = {'str1A':'jump', 'str1B':'fly', 'str1C':'swim', 'str2A':'jump', 'str2B':'fly', 'str2C':'swim', 'str2D':'run', 'str3A':'jump', 'str3B':'fly', 'str3C':'swim', 'str3D':'run'}
Dict3 = {'str1A':'90', 'str1B':'60', 'str1C':'30', 'str2A':'70', 'str2B':'30', 'str2C':'60', 'str2D':'40', 'str3A':'10', 'str3B':'90', 'str3C':'70', 'str3D':'90'}

data = {}
for k, col in Dict2.items():
    if k not in Dict1 or k not in Dict3:
        continue
    data.setdefault(col, {})[Dict1[k]] = Dict3[k]
df = pd.DataFrame(data, index=sorted(set(Dict1.values())), columns=sorted(set(Dict2.values())))

print(df)

Output:

       fly jump  run swim
file1   60   90  NaN   30
file2   30   70   40   60
file3   90   10   90   70
file4  NaN  NaN  NaN  NaN

Upvotes: 2

Tai
Tai

Reputation: 7994

Using pivot.

Dict1 = {'str1A':'file1', 'str1B':'file1', 'str1C':'file1', 'str1D':'file1', 'str2A':'file2', 'str2B':'file2', 'str2C':'file2', 'str2D':'file2', 'str2D':'file2', 'str3A':'file3', 'str3B':'file3','str3C':'file3', 'str3D':'file3', 'str3D':'file3'}
Dict2 = {'str1A':'jump', 'str1B':'fly', 'str1C':'swim', 'str2A':'jump', 'str2B':'fly', 'str2C':'swim', 'str2D':'run', 'str3A':'jump', 'str3B':'fly', 'str3C':'swim', 'str3D':'run'}
Dict3 = {'str1A':'90', 'str1B':'60', 'str1C':'30', 'str2A':'70', 'str2B':'30', 'str2C':'60', 'str2D':'40', 'str3A':'10', 'str3B':'90', 'str3C':'70', 'str3D':'90'}

col_file = ['str', 'file']
df_origin = pd.DataFrame.from_dict(Dict1, orient="index")
df_bmatch = pd.DataFrame.from_dict(Dict2, orient="index")
df_percent = pd.DataFrame.from_dict(Dict3, orient="index")

df_temp = pd.concat([df_origin, df_bmatch, df_percent], axis=1)
df_temp.columns = ["col1", "col2", "col3"]

        col1    col2    col3
str1A   file1   jump    90
str1B   file1   fly     60
str1C   file1   swim    30
str1D   file1   NaN     NaN
str2A   file2   jump    70
str2B   file2   fly     30
str2C   file2   swim    60
str2D   file2   run     40
str3A   file3   jump    10
str3B   file3   fly     90
str3C   file3   swim    70
str3D   file3   run     90

df_temp.pivot(values="col3", columns="col2", index="col1").drop([np.nan], axis=1)

col2   fly  jump  run   swim
col1                    
file1  60   90    None  30
file2  30   70    40    60
file3  90   10    90    70

Upvotes: 2

Related Questions