Merge rows with same index in a DataFrame

Question

I have a DataFrame with species and their occurence. An excerpt follows:

+-------------+------------+-------------------------------------------+--------------+-----------------+-------------+-----------+------------+----------+
| Family      | Genus      | Species                                   | Species code | Nearctic Mexico | Mesoamerica | Caribbean | Los Llanos | Amazonia |
+-------------+------------+-------------------------------------------+--------------+-----------------+-------------+-----------+------------+----------+
| Acanthaceae | Aphelandra | Aphelandra castaneifolia Britton ex Rusby | Aphecast     |                 |             |           |            |          |
+-------------+------------+-------------------------------------------+--------------+-----------------+-------------+-----------+------------+----------+
| Acanthaceae | Aphelandra | Aphelandra pilosa Leonard                 | Aphpilo      |                 | +           |           | +          | +        |
+-------------+------------+-------------------------------------------+--------------+-----------------+-------------+-----------+------------+----------+
| Acanthaceae | Aphelandra | Aphelandra pulcherrima (Jacq.) Kunth      | Aphepulc     |                 | +           | +         | +          | +        |
+-------------+------------+-------------------------------------------+--------------+-----------------+-------------+-----------+------------+----------+
| Acanthaceae | Aphelandra | Aphelandra sinclairiana Nees              | Aphesinc     |                 | +           |           |            |          |
+-------------+------------+-------------------------------------------+--------------+-----------------+-------------+-----------+------------+----------+

I need to analyze genera only, but preserve their occurences as follows. I'm sorry if that's a real rookie question, but I'm having trouble putting it in words what I intend to do. This is the expected result:

+-------------+------------+-----------------+-------------+-----------+------------+----------+
| Family      | Genus      | Nearctic Mexico | Mesoamerica | Caribbean | Los Llanos | Amazonia |
+-------------+------------+-----------------+-------------+-----------+------------+----------+
| Acanthaceae | Aphelandra |                 | +           | +         | +          | +        |
+-------------+------------+-----------------+-------------+-----------+------------+----------+

I have found similar problems, but dealing with numeric data and I couldn't manage to adapt the solution.

Do you want the string equivalent of a "max" of the location columns? So if there is a plus anywhere for that Genus then give it a plus? — noah
– noah, Commented Jan 28, 2021 at 7:08

jezrael · Accepted Answer · 2021-01-28 07:13:46Z

1

Use GroupBy.first for first non misisng values for 5th+ columns:

#if necessary replace empty space to missing values
df = df.replace('',np.nan)

cols = df.columns[4:].tolist()
df = df.groupby(['Family','Genus'], as_index=False)[cols].first()
print (df)
        Family       Genus  Nearctic Mexico Mesoamerica Caribbean Los Llanos  \
0  Acanthaceae  Aphelandra              NaN           +         +          +   

  Amazonia  
0        +

answered Jan 28, 2021 at 7:13

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merge rows with same index in a DataFrame

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related