Add a column from several files in the cycle



  • I've got a few files of which I'm counting the cycle of files, pole name and count. The name in each file consists of a part of the column name in another file read in df. In all files, names vary. I need to add a count column from every file to df.

    For example, I have two such tables:

    idnamecount
    a48/a2
    c48/c5
    idnamecount
    a50/a5
    b50/b6
    d50/d6

    This table should be added from the two previous counts to the original table df

    namecount
    48/a2
    48/b
    48/c5
    50/a5
    50/b6
    50/c
    50/d6

    Code

    for i in range(len(file)) :
        obj = pd.read_csv(file[i])
        obj['count'] = obj.groupby('id')['id'].transform('count')
        for j in range(obj.shape[0]):
            obj['name'][j] = str(file[i])+'/'+str(obj['id'][j])
        obj.to_csv('frame'+str(i)+'.csv')
        df = df.merge(obj[['name','count']], on = 'link', how = 'outer' )
    

    But my code gives me this table:

    namecount_xcount_y
    48/a2
    48/b
    48/c5
    50/a5
    50/b6
    50/c
    50/d6

    How can you rewrite it so it can be added to one column?



  • What prevents the introduction of a new index and simple content?

    df1 = df1.set_index("name")
    df2 = df2.set_index("name")
    res = pd.concat([df1, df2])
    

    res:

         id  count
    name          
    48/a  a      2
    48/c  c      5
    50/a  a      5
    50/b  b      6
    50/d  d      6
    

    Necessary columns can be removed and, if there is a wish, reset_index(s)

    UPDATE

    If the number of columns may vary, but the columns you need all have the same name and meaning in the column. name It's unique, so we can try this:

    res1 = pd.merge(res1[['name', 'count']], df2[['name', 'count']], how="outer")
    

    res1:

       name  count
    0  48/a      2
    1  48/c      5
    2  50/a      5
    3  50/b      6
    4  50/d      6
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2