ValueError: Must have equal len keys and value when setting with an iterable when inserting a list

user_13999522 0
Viewed 868

Here's the scenario I met. I have a json like dictionary and intend to count the number of answers from another dataframe with the same column name (Question number I would say)

aa = {
    "Q1": {
        "Number": 100,
        "Question": "Why?",
        "Answer": ["A", "B", "C"],
    },
    "Q2": {
        "Number": 200,
        "Question": "What?",
        "Answer": ["foo", "boo", "koo"],
    },
    "Q3": {"Number": 300, "Question": "How?", "Answer": ["a", "b", "c"]},
}

df = pd.DataFrame.from_dict(aa, orient='index')

print(df)

    Number  Question    Answer
Q1  100 Why?    [A, B, C]
Q2  200 What?   [foo, boo, koo]
Q3  300 How?    [a, b, c]

And here's the dataframe I need to look upon:

table = pd.DataFrame(
    {
        "Q1": ["A", "B", "", "", "A", "A", "C"],
        "Q2": ["foo", "", "", "foo", "foo", "koo", ""],
        "Q3": ["a", "", "c", "", "c", "c", "b"],
        "Q3_1": ["1", "", "k", "3", "c", "6", "e"],
    }
)

print(table)

    Q1  Q2  Q3  Q3_1
0   A   foo a   1
1   B           
2           c   k
3       foo     3
4   A   foo c   c
5   A   koo c   6
6   C       b   e

This is how I tried to render it:

for col in table.columns:
    for i in df.index:
        if i == col:
            temp = table[i].value_counts().to_dict()
            df.loc[i,'Answer Counts'] = list(temp.values())

However, it ended up with a ValueError pointing to the last line of code:

ValueError: Must have equal len keys and value when setting with an iterable

I want to count the number of answers showing up in the Answer key in aa with the same question number and return a list in another column since it seemed not doable to insert a dictionary in the dataframe:

    Number  Question    Answer  Answer Count
Q1  100 Why?    [A, B, C]   [3, 1, 1]
Q2  200 What?   [foo, boo, koo] [3, 0, 1]
Q3  300 How?    [a, b, c]   [1, 1, 3]

It might not be a good idea to put it in another column. I'm open to any suggestions on how to render this.

pandas dataframe python
1 Answers

You could try this:

# New column
df["Answer Count"] = pd.NA

# Iterate on each grade in df and count values in table
for question_number, row in df.iterrows():
    if question_number in table.columns:
        answer_count = []
        for answer in row["Answer"]:
            answer_count.append(list(table[question_number].values).count(answer))
        df.at[question_number, "Answer Count"] = answer_count

print(df)
# Outputs
    Number Question           Answer Answer Count
Q1     100     Why?        [A, B, C]    [3, 1, 1]
Q2     200    What?  [foo, boo, koo]    [3, 0, 1]
Q3     300     How?        [a, b, c]    [1, 1, 3]
user_112460560
Related