Coordinates of an element in a csv file

From raju

Objective

The idea here is to find the column name and row numbers of a given set of values in a csv file.

For example, if we have a csv file

    $ cat -n tests/data/marks.csv
         1     Name,  Age,     City,  Marks
         2     jack,   34,   Sydney,    155
         3     Riti,   31,    Delhi,    177
         4     Aadi,   16,   Mumbai,     81
         5    Mohit,   31,    Delhi,    167
         6    Veena,   81,    Delhi,    144
         7  Shaunak,   35,   Mumbai,    135
         8    Shaun,   35,  Colombo,    111
    

and given a set of values [31, 81, 'Veena'] we want to output

    row, col, value
    6,  Name, Veena
    3,   Age,    31
    5,   Age,    31
    6,   Age,    81
    4, Marks,    81
    

Solution

    $ col_row.py tests/data/marks.csv 31 81 Veena
    row,column,value
    6,Name,Veena
    3,Age,31
    5,Age,31
    6,Age,81
    4,Marks,81
    

where col_row.py is a python script I wrote. It can be found in https://github.com/KamarajuKusumanchi/rutils/blob/master/python3/col_row.py.

code snippet

/* Only showing the main stuff here. See the actual script for the complete implementation */

    def get_col_rows(df, values):
        rownum = 'rownum0'    # this column should not exist in the dataframe
        masked = df[df.isin(values)]
        masked[rownum] = np.arange(2, len(masked)+2)    # row number in the csv file
        result = masked.melt(id_vars=rownum, var_name='column').dropna()\
            .rename(columns={rownum:'row'}).reset_index(drop=True)
        return result
    

miscellaneous

Ref:-

search tags | get row column of a cell in python, find element position in a csv file, pandas isin melt row col, melt ignore nan, print row column of all non None values, "print index, column of all true values", find the column and row an element belongs to, which column and rows an element belongs to