How To Add And Update A Value In Pandas Df Each Time A New Value Is Found?

Most of the the other questions regarding updating values in pandas df are focused on appending a new column or just updating the cell with a new value (i.e. replacing it). My ques

Solution 1:

Instead of doing that way iteratively, you can more automate and use pandas to perform those operations.

Start by creating the dataframe from id_dict:

df = pd.DataFrame([id_dict]).stack().explode().to_frame('id').droplevel(0).reset_index()\
     .astype({'id': int})

           index       id0      Treponema      1621     Leptospira      1742   Azospirillum      1923  Campylobacter      1954  Campylobacter      1975  Campylobacter      1996  Campylobacter      2017    Pseudomonas      2878           NONE  28293589           NONE  2806529

Read the count/id text file into a data frame:

idDF = pd.read_csv('Sample1_idsummary.txt', sep=',' , names=['count',  'id'])

   count       id011621151742419535197462015102829358

Now outer merge both the dataframes, fill NaN's with 0, then groupby index, and call sum and create the dataframe calling to_frame and passing count as column name, finally transpose the dataframe:

df.merge(idDF, how='outer').fillna(0).groupby('index')['count'].sum().to_frame('Sample1').T

OUTPUT:

index    Azospirillum  Campylobacter  Leptospira  NONE  Pseudomonas  Treponema
Sample1           0.015.015.010.00.01.0