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:-
- https://pandas.pydata.org/docs/reference/api/pandas.melt.html
- https://stackoverflow.com/questions/47684961/melt-uneven-data-in-columns-and-ignore-nans-using-pandas
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