Converting collections.Counters of combinations frequency from dataframe multi-index into string

user_15296904 0
Viewed 68

Would like to ask for some advise on how to do this properly. I'm new to python.

Initially I wanted to find out the counters/frequency of the combinations of multi-index. I tried a few ways, such as loop, itertuples, iterrows, etc and I realize the fastest and least overhead is to use collections.Counter

However, it returns a list of tuples of the multi-index index combinations as the counter dict keys. The keys of tuples makes it hard for thereafter processing.

Thus I am figuring out how to make them into string with separators to make the thereafter processing easier to manage.

For example this multi-index below:

# testing
def testing():
    testing_df = pd.read_csv("data/testing.csv", float_precision="high")
    testing_df = testing_df.set_index(["class", "table", "seat"]).sort_index()
    print("\n1: \n" + str(testing_df.to_string()))

    print("\n2 test: \n" + str(testing_df.index))
    occurrences = collections.Counter(testing_df.index)

    print("\n3: \n" + str(occurrences))

output:

1: 
                    random_no
class   table seat           
Emerald 1     0         55.00
Ruby    0     0         33.67
              0         24.01
              1         87.00
Topaz   0     0         67.00

2 test: 
MultiIndex([('Emerald', 1, 0),
            (   'Ruby', 0, 0),
            (   'Ruby', 0, 0),
            (   'Ruby', 0, 1),
            (  'Topaz', 0, 0)],
           names=['class', 'table', 'seat'])

3: 
Counter({('Ruby', 0, 0): 2, ('Emerald', 1, 0): 1, ('Ruby', 0, 1): 1, ('Topaz', 0, 0): 1})

As we can see from 3), it returns the combinations in tuples of different data types as the dict keys, and makes it hard for processing.

I tried to separate it or making it string so processing it can be easier.

Tried below with errors:

x = "|".join(testing_df.index)
print(x)

 x = "|".join(testing_df.index)
TypeError: sequence item 0: expected str instance, tuple found

and below with errors

x = "|".join(testing_df.index[0])
print(x)

 x = "|".join(testing_df.index[0])
 TypeError: sequence item 1: expected str instance, numpy.int64 found

Basically, its either:

  1. I make the combinations into strings before calculating collections.Counter or
  2. after making it into collections.Counter, where all the numerous keys are tuples and convert those keys into strings

Can I ask how do I do this properly?

Thank you very much!

multi-index pandas dictionary collections python
1 Answers

I can offer a solution for 2., convert key tuples into strings:

from collections import Counter
# recreate your problem
occurrences = Counter([('Ruby', 0, 0),
    ('Ruby', 0, 0),
    ('Emerald', 1, 0),
    ('Ruby', 0, 1),
    ('Topaz', 0, 0)])

# convert tuple keys to string keys
new_occurrences = {'|'.join(str(index) for index in key) : value for key,value in occurrences.items()}
print(new_occurrences)

{'Ruby|0|0': 2, 'Emerald|1|0': 1, 'Ruby|0|1': 1, 'Topaz|0|0': 1}

Counter is a subclass of dict, therefore you can use fancy things like dict-comprehensions and .items() to loop over keys and values at the same time.

Depending on you how you intend to further process your data, it might be more useful to convert the result of your counter to a pandas DataFrame. Simply because pandas offers more and easier functionality for processing.

Here's how:

import pandas as pd
df = pd.DataFrame({'class': [k[0] for k in occurrences.keys()],
    'table': [k[1] for k in occurrences.keys()],
    'seat': [k[2] for k in occurrences.keys()],
    'counts': [v for _,v in occurrences.items()]})
df.head()

     class  table  seat  counts
0     Ruby      0     0       2
1  Emerald      1     0       1
2     Ruby      0     1       1
3    Topaz      0     0       1
user_112208840
Related