Remove rows from dataframes not present in dataframes in dictionary

user_14235248 0
Viewed 364

I have a hundreds pandas dataframes such as these:

Df1 = 
    MD    A    B    C  
    r1    6    3    9   
    r2    2    1    1  
    r3    5    7    2  
    r4    8    2    0

Df2 = 
    MD    A    B    C  
    r1    1    7    1  
    r2    6    3    0  
    r3    3    1    8
    r4    1    7    4

I also have one dictionary like this (except with a number of keys and values to reflect the number of dataframes):

Dict = {'D1', MD
              r1
              r4
        [2 rows x 1 columns]
        'D2', MD
              r1
              r2
              r3
        [3 rows x 1 columns]}

What I need is a way to go through the dictionary, and for each dataframe and corresponding key (I.e. Df1 and D1, and Df2 and D2), remove the rows not present in the "dictionary-value dataframe".

So for Df1, I need to go to the key D1 and remove the rows that are not in the dataframe for this key. That is, remove r2 and r3 from Df1.

So the result I want would be:

Df1 = 
    MD    A    B    C  
    r1    6    3    9   
    r4    8    2    0

Df2 = 
    MD    A    B    C  
    r1    1    7    1  
    r2    6    3    0  
    r3    3    1    8

Any help is greatly appreciated!

PS. If it is easier to keep the rows instead of removing them, that would also be helpful!

pandas dataframe dictionary python
1 Answers

Here I just print the values, but you could reassign the DataFrame.

>>> df1 = pd.DataFrame.from_dict({"r1": [1,2,3], "r2": [4,5,6], "r3": [7,8,9]}, columns=["A", "B", "C"], orient="index")
>>> df2 = pd.DataFrame.from_dict({"r1": [1,2,3], "r2": [4,5,6], "r3": [7,8,9]}, columns=["A", "B", "C"], orient="index")

>>> dfs = [df1, df2]
>>> dfs

[    A  B  C
 r1  1  2  3
 r2  4  5  6
 r3  7  8  9,
     A  B  C
 r1  1  2  3
 r2  4  5  6
 r3  7  8  9]

lookup = {"d1": ["r1", 'r2'], 'd2': ["r2", "r3"]}

for rows, df in zip(lookup.values(), dfs):
    print(df.loc[rows])

    A  B  C
r1  1  2  3
r2  4  5  6
    A  B  C
r2  4  5  6
r3  7  8  9

EDIT

If you're DataFrames aren't in a dict and are just available as global variables, providing the keys match in your lookup to the variable name, you could do something like this:

>>> df1 = pd.DataFrame.from_dict({"r1": [1,2,3], "r2": [4,5,6], "r3": [7,8,9]}, columns=["A", "B", "C"], orient="index")
>>> df2 = pd.DataFrame.from_dict({"r1": [1,2,3], "r2": [4,5,6], "r3": [7,8,9]}, columns=["A", "B", "C"], orient="index")

>>> lookup = {"df1": ["r1", 'r2'], 'df2': ["r2", "r3"]}

# Use the reference key from lookup, to grab the relevant DataFrame
# then index rows using .loc with the corresponding values from lookup
for key in lookup.keys():
    print(globals()[key].loc[lookup[key]])

Otherwise, I'd recommend just putting them in a datastructure you can iterate over easily, as demonstrated in the original answer.

user_16279680
Related