How to create a table from an existing dataframe?

niko_bio · May 11, 2022, 8:29am

Hi

I want to create a table that has three columns: "Family", "Species " and "BIN". I want to use data from a dataframe. There should only be 6 rows; one that represent each family. The species and BIN rows should be counts and unique. I want it to look something like this:
Family Species BIN
FamilyA 15 20
FamilyB 14 18
FamilyC 13 19

etc.

Is there any easy way to do this?

Here's my data. Thank you!!!:

data.frame(
stringsAsFactors = FALSE,
BIN = c("BOLD:AAA2224","BOLD:AAA2226",
"BOLD:AAA5337","BOLD:AAA6082","BOLD:AAB7950",
"BOLD:AAC1308","BOLD:AAC3024","BOLD:AAD4857","BOLD:AAD6030",
"BOLD:AAD7189","BOLD:AAD9073","BOLD:AAE2153",
"BOLD:AAE2156","BOLD:AAE6788","BOLD:AAE7041","BOLD:AAE7250",
"BOLD:AAE7537","BOLD:AAF0350","BOLD:AAF7629",
"BOLD:AAF7788","BOLD:AAH5021","BOLD:AAI0236","BOLD:AAI5276",
"BOLD:AAI5283","BOLD:AAI6157","BOLD:AAI6158","BOLD:AAI8379",
"BOLD:AAI9570","BOLD:AAI9595","BOLD:AAI9669",
"BOLD:AAJ1582","BOLD:AAJ2868","BOLD:AAJ8310","BOLD:AAJ8738",
"BOLD:AAJ9556","BOLD:AAJ9557","BOLD:AAJ9688",
"BOLD:AAK0507","BOLD:AAK1950","BOLD:AAK5305","BOLD:AAK7843",
"BOLD:AAK9221","BOLD:AAK9513","BOLD:AAL5461",
"BOLD:AAL8427","BOLD:AAN1184","BOLD:AAN3545","BOLD:AAN9736",
"BOLD:AAW0492","BOLD:AAX1260","BOLD:AAX2471",
"BOLD:AAX2577","BOLD:AAX2752","BOLD:AAY2201","BOLD:AAY6965",
"BOLD:AAZ4523","BOLD:ABX9996","BOLD:ABY8818","BOLD:ABZ0345",
"BOLD:ABZ6145","BOLD:ABZ6191","BOLD:ACA1475",
"BOLD:ACA1612","BOLD:ACA1665","BOLD:ACA3156","BOLD:ACA4478",
"BOLD:ACB5732","BOLD:ACD4058","BOLD:ACD6834",
"BOLD:ACD7373","BOLD:ACD8413","BOLD:ACE8136","BOLD:ACE8137",
"BOLD:ACF5598","BOLD:ACH3444","BOLD:ACQ2590",
"BOLD:ACS5043","BOLD:ACV7130","BOLD:ACX3791","BOLD:ADC7557",
"BOLD:ADF1053","BOLD:ADJ8787","BOLD:ADK2061","BOLD:ADK2143",
"BOLD:ADL0250","BOLD:ADL0521","BOLD:ADL0536",
"BOLD:ADL0772","BOLD:ADL0800","BOLD:ADL0827","BOLD:ADL0937",
"BOLD:ADL0978","BOLD:ADL1054","BOLD:ADL1155",
"BOLD:ADL1194","BOLD:ADL1331","BOLD:ADL1334","BOLD:ADL1514",
"BOLD:ADL1598","BOLD:ADL1630","BOLD:ADL1899",
"BOLD:ADL1909","BOLD:ADL1973","BOLD:ADL2123","BOLD:ADL2159",
"BOLD:ADL2274","BOLD:ADL2497","BOLD:ADR4931",
"BOLD:ADR6352"),
family = c("Pieridae","Pieridae",
"Nymphalidae","Pieridae","Nymphalidae","Nymphalidae",
"Papilionidae","Nymphalidae","Papilionidae","Papilionidae",
"Nymphalidae","Nymphalidae","Nymphalidae",
"Papilionidae","Pieridae","Papilionidae","Nymphalidae",
"Nymphalidae","Pieridae","Papilionidae","Nymphalidae",
"Nymphalidae","Papilionidae","Papilionidae","Papilionidae",
"Papilionidae","Nymphalidae","Pieridae","Nymphalidae",
"Nymphalidae","Nymphalidae","Nymphalidae","Nymphalidae",
"Papilionidae","Nymphalidae","Nymphalidae",
"Papilionidae","Pieridae","Nymphalidae","Papilionidae",
"Nymphalidae","Nymphalidae","Nymphalidae","Nymphalidae",
"Nymphalidae","Hesperiidae","Nymphalidae","Nymphalidae",
"Papilionidae","Nymphalidae","Nymphalidae","Papilionidae",
"Papilionidae","Papilionidae","Nymphalidae",
"Nymphalidae","Nymphalidae","Papilionidae","Nymphalidae",
"Nymphalidae","Nymphalidae","Nymphalidae","Pieridae",
"Pieridae","Nymphalidae","Nymphalidae","Pieridae",
"Nymphalidae","Papilionidae","Nymphalidae","Papilionidae",
"Papilionidae","Papilionidae","Papilionidae",
"Nymphalidae","Nymphalidae","Riodinidae","Hesperiidae",
"Hesperiidae","Nymphalidae","Nymphalidae","Riodinidae",
"Nymphalidae","Nymphalidae","Papilionidae","Nymphalidae",
"Nymphalidae","Nymphalidae","Hesperiidae","Nymphalidae",
"Nymphalidae","Papilionidae","Nymphalidae",
"Nymphalidae","Nymphalidae","Nymphalidae","Nymphalidae",
"Nymphalidae","Nymphalidae","Papilionidae","Lycaenidae",
"Nymphalidae","Papilionidae","Nymphalidae","Papilionidae",
"Papilionidae","Papilionidae","Nymphalidae",
"Nymphalidae"),
genus = c("Pieris","Pieris","Vanessa",
"Eurema","Hypolimnas","Tirumala","Papilio","Kaniska",
"Papilio","Graphium","Polygonia","Junonia","Junonia",
"Papilio","Hebomoia","Graphium","Dichorragia",
"Vanessa","Pieris","Papilio","Neptis","Symbrenthia",
"Papilio","Papilio","Papilio","Papilio","Hestina",
"Delias","Danaus","Cyrestis","Argyreus","Acraea",
"Childrena","Byasa","Athyma","Athyma","Pachliopta","Talbotia",
"Callerebia","Chilasa","Neope","Lethe","Elymnias",
"Euploea","Parthenos","Choaspes","Lethe","Lethe",
"Graphium","Parasarpa","Parantica","Papilio","Papilio",
"Graphium","Neptis","Parantica","Sumalia","Troides",
"Euploea","Melanitis","Junonia","Athyma","Prioneris",
"Delias","Hestina","Euploea","Pieris","Vindula",
"Lamproptera","Polyura","Graphium","Papilio","Papilio",
"Papilio","Calinaga","Cethosia","Zemeros","Choaspes",
"Parnara","Stichophthalma","Neope","Dodona","Neptis",
"Danaus","Graphium","Stibochiona","Parantica",
"Pseudergolis","Burara","Neorina","Lethe","Graphium",
"Euthalia","Euthalia","Euthalia","Neope","Enispe","Lethe",
"Mycalesis","Papilio","Heliophorus","Athyma",
"Graphium","Euthalia","Chilasa","Atrophaneura","Byasa",
"Parasarpa","Childrena"),
species = c("Pieris rapae","Pieris rapae",
"Vanessa cardui","Eurema hecabe","Hypolimnas bolina",
"Tirumala septentrionis","Papilio bianor",
"Kaniska canace","Papilio demoleus","Graphium agamemnon",
"Polygonia c-aureum","Junonia almana","Junonia atlites",
"Papilio memnon","Hebomoia glaucippe","Graphium sarpedon",
"Dichorragia nesimachus","Vanessa indica","Pieris canidia",
"Papilio paris","Neptis hylas","Symbrenthia lilaea",
"Papilio helenus","Papilio nephelus","Papilio protenor",
"Papilio polytes","Hestina assimilis",
"Delias sububila","Danaus genutia","Cyrestis thyodamas",
"Argyreus hyperbius","Acraea issoria","Childrena childreni",
"Byasa polyeuctes","Athyma cama","Athyma asura",
"Pachliopta aristolochiae","Talbotia naganum","Callerebia polyphemus",
"Chilasa epycides","Neope pulaha","Lethe confusa",
"Elymnias malelas","Euploea mulciber","Parthenos sylvia",
"Choaspes xanthopogon","Lethe verma","Lethe dura",
"Graphium cloanthus","Parasarpa dudu","Parantica melaneus",
"Papilio arcturus","Papilio doddsi","Graphium doson",
"Neptis hylas","Parantica aglea","Sumalia daraxa",
"Troides aeacus","Euploea eunice","Melanitis leda",
"Junonia orithya","Athyma selenophora","Prioneris thestylis",
"Delias pasithoe","Hestina nama","Euploea eunice",
"Pieris canidia","Vindula erota","Lamproptera curius",
"Polyura dolon","Graphium chironides","Papilio paris",
"Papilio karna","Papilio polyctor","Calinaga sudassana",
"Cethosia biblis","Zemeros flegyas",
"Choaspes benjaminii","Parnara ganga","Stichophthalma louisa",
"Neope pulaha","Dodona adonira","Neptis hylas","Danaus genutia",
"Graphium leechi","Stibochiona nicea","Parantica sita",
"Pseudergolis wedah","Burara vasutana","Neorina patria",
"Lethe bhairava","Graphium agetes",
"Euthalia monastyrskiyi","Euthalia iva","Euthalia narayana",
"Neope armandii","Enispe cycnus","Lethe sinorix",
"Mycalesis francisca","Chilasa agestor","Heliophorus brahma",
"Athyma orientalis","Graphium sarpedon","Euthalia lubentina",
"Chilasa epycides","Atrophaneura aidoneus",
"Byasa crassipes","Parasarpa dudu","Childrena childreni")
)

xvalda · May 11, 2022, 11:14am

Hi @niko_bio ,

Is it what you're looking for?

DF <- data.frame(
  stringsAsFactors = FALSE,
  BIN = c("BOLD:AAA2224","BOLD:AAA2226",
          "BOLD:AAA5337","BOLD:AAA6082","BOLD:AAB7950",
          "BOLD:AAC1308","BOLD:AAC3024","BOLD:AAD4857","BOLD:AAD6030",
          "BOLD:AAD7189","BOLD:AAD9073","BOLD:AAE2153",
          "BOLD:AAE2156","BOLD:AAE6788","BOLD:AAE7041","BOLD:AAE7250",
          "BOLD:AAE7537","BOLD:AAF0350","BOLD:AAF7629",
          "BOLD:AAF7788","BOLD:AAH5021","BOLD:AAI0236","BOLD:AAI5276",
          "BOLD:AAI5283","BOLD:AAI6157","BOLD:AAI6158","BOLD:AAI8379",
          "BOLD:AAI9570","BOLD:AAI9595","BOLD:AAI9669",
          "BOLD:AAJ1582","BOLD:AAJ2868","BOLD:AAJ8310","BOLD:AAJ8738",
          "BOLD:AAJ9556","BOLD:AAJ9557","BOLD:AAJ9688",
          "BOLD:AAK0507","BOLD:AAK1950","BOLD:AAK5305","BOLD:AAK7843",
          "BOLD:AAK9221","BOLD:AAK9513","BOLD:AAL5461",
          "BOLD:AAL8427","BOLD:AAN1184","BOLD:AAN3545","BOLD:AAN9736",
          "BOLD:AAW0492","BOLD:AAX1260","BOLD:AAX2471",
          "BOLD:AAX2577","BOLD:AAX2752","BOLD:AAY2201","BOLD:AAY6965",
          "BOLD:AAZ4523","BOLD:ABX9996","BOLD:ABY8818","BOLD:ABZ0345",
          "BOLD:ABZ6145","BOLD:ABZ6191","BOLD:ACA1475",
          "BOLD:ACA1612","BOLD:ACA1665","BOLD:ACA3156","BOLD:ACA4478",
          "BOLD:ACB5732","BOLD:ACD4058","BOLD:ACD6834",
          "BOLD:ACD7373","BOLD:ACD8413","BOLD:ACE8136","BOLD:ACE8137",
          "BOLD:ACF5598","BOLD:ACH3444","BOLD:ACQ2590",
          "BOLD:ACS5043","BOLD:ACV7130","BOLD:ACX3791","BOLD:ADC7557",
          "BOLD:ADF1053","BOLD:ADJ8787","BOLD:ADK2061","BOLD:ADK2143",
          "BOLD:ADL0250","BOLD:ADL0521","BOLD:ADL0536",
          "BOLD:ADL0772","BOLD:ADL0800","BOLD:ADL0827","BOLD:ADL0937",
          "BOLD:ADL0978","BOLD:ADL1054","BOLD:ADL1155",
          "BOLD:ADL1194","BOLD:ADL1331","BOLD:ADL1334","BOLD:ADL1514",
          "BOLD:ADL1598","BOLD:ADL1630","BOLD:ADL1899",
          "BOLD:ADL1909","BOLD:ADL1973","BOLD:ADL2123","BOLD:ADL2159",
          "BOLD:ADL2274","BOLD:ADL2497","BOLD:ADR4931",
          "BOLD:ADR6352"),
  family = c("Pieridae","Pieridae",
             "Nymphalidae","Pieridae","Nymphalidae","Nymphalidae",
             "Papilionidae","Nymphalidae","Papilionidae","Papilionidae",
             "Nymphalidae","Nymphalidae","Nymphalidae",
             "Papilionidae","Pieridae","Papilionidae","Nymphalidae",
             "Nymphalidae","Pieridae","Papilionidae","Nymphalidae",
             "Nymphalidae","Papilionidae","Papilionidae","Papilionidae",
             "Papilionidae","Nymphalidae","Pieridae","Nymphalidae",
             "Nymphalidae","Nymphalidae","Nymphalidae","Nymphalidae",
             "Papilionidae","Nymphalidae","Nymphalidae",
             "Papilionidae","Pieridae","Nymphalidae","Papilionidae",
             "Nymphalidae","Nymphalidae","Nymphalidae","Nymphalidae",
             "Nymphalidae","Hesperiidae","Nymphalidae","Nymphalidae",
             "Papilionidae","Nymphalidae","Nymphalidae","Papilionidae",
             "Papilionidae","Papilionidae","Nymphalidae",
             "Nymphalidae","Nymphalidae","Papilionidae","Nymphalidae",
             "Nymphalidae","Nymphalidae","Nymphalidae","Pieridae",
             "Pieridae","Nymphalidae","Nymphalidae","Pieridae",
             "Nymphalidae","Papilionidae","Nymphalidae","Papilionidae",
             "Papilionidae","Papilionidae","Papilionidae",
             "Nymphalidae","Nymphalidae","Riodinidae","Hesperiidae",
             "Hesperiidae","Nymphalidae","Nymphalidae","Riodinidae",
             "Nymphalidae","Nymphalidae","Papilionidae","Nymphalidae",
             "Nymphalidae","Nymphalidae","Hesperiidae","Nymphalidae",
             "Nymphalidae","Papilionidae","Nymphalidae",
             "Nymphalidae","Nymphalidae","Nymphalidae","Nymphalidae",
             "Nymphalidae","Nymphalidae","Papilionidae","Lycaenidae",
             "Nymphalidae","Papilionidae","Nymphalidae","Papilionidae",
             "Papilionidae","Papilionidae","Nymphalidae",
             "Nymphalidae"),
  genus = c("Pieris","Pieris","Vanessa",
            "Eurema","Hypolimnas","Tirumala","Papilio","Kaniska",
            "Papilio","Graphium","Polygonia","Junonia","Junonia",
            "Papilio","Hebomoia","Graphium","Dichorragia",
            "Vanessa","Pieris","Papilio","Neptis","Symbrenthia",
            "Papilio","Papilio","Papilio","Papilio","Hestina",
            "Delias","Danaus","Cyrestis","Argyreus","Acraea",
            "Childrena","Byasa","Athyma","Athyma","Pachliopta","Talbotia",
            "Callerebia","Chilasa","Neope","Lethe","Elymnias",
            "Euploea","Parthenos","Choaspes","Lethe","Lethe",
            "Graphium","Parasarpa","Parantica","Papilio","Papilio",
            "Graphium","Neptis","Parantica","Sumalia","Troides",
            "Euploea","Melanitis","Junonia","Athyma","Prioneris",
            "Delias","Hestina","Euploea","Pieris","Vindula",
            "Lamproptera","Polyura","Graphium","Papilio","Papilio",
            "Papilio","Calinaga","Cethosia","Zemeros","Choaspes",
            "Parnara","Stichophthalma","Neope","Dodona","Neptis",
            "Danaus","Graphium","Stibochiona","Parantica",
            "Pseudergolis","Burara","Neorina","Lethe","Graphium",
            "Euthalia","Euthalia","Euthalia","Neope","Enispe","Lethe",
            "Mycalesis","Papilio","Heliophorus","Athyma",
            "Graphium","Euthalia","Chilasa","Atrophaneura","Byasa",
            "Parasarpa","Childrena"),
  species = c("Pieris rapae","Pieris rapae",
              "Vanessa cardui","Eurema hecabe","Hypolimnas bolina",
              "Tirumala septentrionis","Papilio bianor",
              "Kaniska canace","Papilio demoleus","Graphium agamemnon",
              "Polygonia c-aureum","Junonia almana","Junonia atlites",
              "Papilio memnon","Hebomoia glaucippe","Graphium sarpedon",
              "Dichorragia nesimachus","Vanessa indica","Pieris canidia",
              "Papilio paris","Neptis hylas","Symbrenthia lilaea",
              "Papilio helenus","Papilio nephelus","Papilio protenor",
              "Papilio polytes","Hestina assimilis",
              "Delias sububila","Danaus genutia","Cyrestis thyodamas",
              "Argyreus hyperbius","Acraea issoria","Childrena childreni",
              "Byasa polyeuctes","Athyma cama","Athyma asura",
              "Pachliopta aristolochiae","Talbotia naganum","Callerebia polyphemus",
              "Chilasa epycides","Neope pulaha","Lethe confusa",
              "Elymnias malelas","Euploea mulciber","Parthenos sylvia",
              "Choaspes xanthopogon","Lethe verma","Lethe dura",
              "Graphium cloanthus","Parasarpa dudu","Parantica melaneus",
              "Papilio arcturus","Papilio doddsi","Graphium doson",
              "Neptis hylas","Parantica aglea","Sumalia daraxa",
              "Troides aeacus","Euploea eunice","Melanitis leda",
              "Junonia orithya","Athyma selenophora","Prioneris thestylis",
              "Delias pasithoe","Hestina nama","Euploea eunice",
              "Pieris canidia","Vindula erota","Lamproptera curius",
              "Polyura dolon","Graphium chironides","Papilio paris",
              "Papilio karna","Papilio polyctor","Calinaga sudassana",
              "Cethosia biblis","Zemeros flegyas",
              "Choaspes benjaminii","Parnara ganga","Stichophthalma louisa",
              "Neope pulaha","Dodona adonira","Neptis hylas","Danaus genutia",
              "Graphium leechi","Stibochiona nicea","Parantica sita",
              "Pseudergolis wedah","Burara vasutana","Neorina patria",
              "Lethe bhairava","Graphium agetes",
              "Euthalia monastyrskiyi","Euthalia iva","Euthalia narayana",
              "Neope armandii","Enispe cycnus","Lethe sinorix",
              "Mycalesis francisca","Chilasa agestor","Heliophorus brahma",
              "Athyma orientalis","Graphium sarpedon","Euthalia lubentina",
              "Chilasa epycides","Atrophaneura aidoneus",
              "Byasa crassipes","Parasarpa dudu","Childrena childreni")
)

library(tidyverse)
DF %>% group_by(family) %>% 
  summarise(species = n_distinct(species), 
            BIN = n_distinct(BIN))
#> # A tibble: 6 × 3
#>   family       species   BIN
#>   <chr>          <int> <int>
#> 1 Hesperiidae        4     4
#> 2 Lycaenidae         1     1
#> 3 Nymphalidae       55    62
#> 4 Papilionidae      27    30
#> 5 Pieridae           8    10
#> 6 Riodinidae         2     2

niko_bio · May 13, 2022, 5:34am

Yes, exactly that. Thank you so much.

system · May 20, 2022, 5:34am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.