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       id
0      Treponema      162
1     Leptospira      174
2   Azospirillum      192
3  Campylobacter      195
4  Campylobacter      197
5  Campylobacter      199
6  Campylobacter      201
7    Pseudomonas      287
8           NONE  2829358
9           NONE  2806529

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

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

   count       id
0      1      162
1     15      174
2      4      195
3      5      197
4      6      201
5     10  2829358

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.0           15.0        15.0  10.0          0.0        1.0