Pandas - Extract unique column combinations and count them in another table

Question

TASK 1:

I have table like this:

+----------+------------+----------+------------+----------+------------+-------+
| a_name_0 | id_qname_0 | a_name_1 | id_qname_1 | a_name_2 | id_qname_2 | count |
+----------+------------+----------+------------+----------+------------+-------+
| country  | 1          | NAN      | NAN        | NAN      | NAN        | 100   |
+----------+------------+----------+------------+----------+------------+-------+
| region   | 2          | city     | 8          | NAN      | NAN        | 20    |
+----------+------------+----------+------------+----------+------------+-------+
| region   | 2          | city     | 9          | NAN      | NAN        | 80    |
+----------+------------+----------+------------+----------+------------+-------+
| region   | 3          | age      | 4          | sex      | 6          | 40    |
+----------+------------+----------+------------+----------+------------+-------+
| region   | 3          | age      | 5          | sex      | 7          | 60    |
+----------+------------+----------+------------+----------+------------+-------+

I need to turn each row in series, drop NANs and convert series in a dictionaries which will be variable in size, for example, first 2 dicts will look like this:

{'a_name_0':'country','id_qname_0':1}
{'a_name_0':'region','id_qname_0':2, 'a_name_1':'city','id_qname_1':8}
{'a_name_0':'region','id_qname_0':2, 'a_name_1':'city','id_qname_1':9}

Each dictionary after that should be stored in a list.

TASK 2.

Using table below I have to count appearance of columns from dict from previous step:

+----------+------------+----------+------------+----------+
| id       | country    | city     | age        | sex      | 
+----------+------------+----------+------------+----------+
| 1        | 1          | NAN      | NAN        | NAN      | 
+----------+------------+----------+------------+----------+
| 2        | 1          | 8        | NAN        | NAN      | 
+----------+------------+----------+------------+----------+

If there is some faster mapping solution please advise since what I'm about to do is probably going to be quite messy. This answer doesn't help me since I need iterator for extracting parameters as well as counting their appearance.

Answer 1

You can remove count column and convert all rows to list of dicts by DataFrame.to_dict with orient='r' (records) and then filter out dicts with missing values in dictionary comprehension:

L = [{k:v for k, v in x.items() if pd.notna(v)} for x in df.drop('count', 1).to_dict('r')]
print (L)
[{'a_name_0': 'country', 'id_qname_0': 1},
 {'a_name_0': 'region', 'id_qname_0': 2, 'a_name_1': 'city', 'id_qname_1': 8.0}, 
 {'a_name_0': 'region', 'id_qname_0': 2, 'a_name_1': 'city', 'id_qname_1': 9.0}, 
 {'a_name_0': 'region', 'id_qname_0': 3, 'a_name_1': 'age', 
 'id_qname_1': 4.0, 'a_name_2': 'sex', 'id_qname_2': 6.0},
 {'a_name_0': 'region', 'id_qname_0': 3, 'a_name_1': 'age',
 'id_qname_1': 5.0, 'a_name_2': 'sex', 'id_qname_2': 7.0}]

Not 100% sure for second DataFrame:

L1 = [dict(zip(list(x.values())[::2], list(x.values())[1::2])) for x in L]
df = pd.DataFrame(L1)
print (df)
   country  region  city  age  sex
0      1.0     NaN   NaN  NaN  NaN
1      NaN     2.0   8.0  NaN  NaN
2      NaN     2.0   9.0  NaN  NaN
3      NaN     3.0   NaN  4.0  6.0
4      NaN     3.0   NaN  5.0  7.0