Pandas Merging Dataframe Columns

Question

My input dataframes are:

df1

Grp       A       B       C
Men       10      15      14
Women     4       6       5
Baby      3       5       15

df2

Grp       Upper_A    Lower_A       Upper_B    Lower_B       Upper_C   Lower_C
Men       10         1             9          2             15        2
Women     6          4             10         4             10        3
Baby      5          3             15         7             6         3

Desired output is;

Grp      Features    Values   Upper_values  Lower_values    Evaluation
Men      A           10       10            1               True
Men      B           15       9             2               False
Men      C           14       15            2               True
Women    A           4        6             4               True
Women    B           6        10            4               True
Women    C           5        10            6               True
Baby     A           3        5             3               True
Baby     B           5        15            7               False
Baby     C           15       6             3               False

Could you please help me about this? PS: Evaluation columns is assigned according to whether it is between upper and lower values or not.

Could you show us what you have tried so far?

Nils
– Nils

2019-10-31 14:48:50 +00:00
Commented Oct 31, 2019 at 14:48 — Nils
– Nils, Commented Oct 31, 2019 at 14:48
Where does the Evaluation column come from?

Brian
– Brian

2019-10-31 14:51:44 +00:00
Commented Oct 31, 2019 at 14:51 — Brian
– Brian, Commented Oct 31, 2019 at 14:51

jezrael · Accepted Answer · 2019-10-31 20:21:30Z

Solution is create MultiIndex back by split with columns and reshape by DataFrame.stack:

df2.columns = df2.columns.str.split('_', expand=True)

df2 = df2.stack().rename_axis(('Grp','Features'))

Then processing df1 same way and create one column DataFrame by Series.to_frame with DataFrame.join second df2, last use Series.between for new column:

df1 = df1.rename_axis('Features', axis=1).stack().to_frame('Values')

df = df1.join(df2).reset_index()
df['Evaluation'] = df['Values'].between(df['Lower'], df['Upper'])
print (df)
     Grp Features  Values  Lower  Upper  Evaluation
0    Men        A      10      1     10        True
1    Men        B      15      2      9       False
2    Men        C      14      2     15        True
3  Women        A       4      4      6        True
4  Women        B       6      4     10        True
5  Women        C       5      3     10        True
6   Baby        A       3      3      5        True
7   Baby        B       5      7     15       False
8   Baby        C      15      3      6       False

I got this for df2: AttributeError: Can only use .str accessor with Index, not MultiIndex
when i tried this only for df2; df2 = df2.stack(0).rename_axis(('Grp','Features')) I got this error: ValueError: Length of names must match number of levels in MultiIndex.
yeah, "Grp" is already my index before that. so for this reason i got an error.

Dani Mesejo · Accepted Answer · 2019-10-31 15:13:23Z

You could use melt + crosstab + merge:

m1 = df1.melt(id_vars=['Grp'])
m2 = df2.melt(id_vars='Grp')

m2[['group', 'variable']] = m2.variable.str.split('_', expand=True)

cross = pd.crosstab(index=[m2['Grp'], m2.variable], columns=m2.group,
                     values=m2['value'], aggfunc='sum').reset_index()

result = m1.merge(cross, on=['Grp', 'variable'])
result['evaluation'] = (result['value'] <= result.Upper) & (result['value'] >= result.Lower)

print(result)

Output

     Grp variable  value  Lower  Upper  evaluation
0    Men        A     10      1     10        True
1  Women        A      4      4      6        True
2   Baby        A      3      3      5        True
3    Men        B     15      2      9       False
4  Women        B      6      4     10        True
5   Baby        B      5      7     15       False
6    Men        C     14      2     15        True
7  Women        C      5      3     10        True
8   Baby        C     15      3      6       False

Collectives™ on Stack Overflow

Pandas Merging Dataframe Columns

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related