Pandas: How to convert list to data frame-CodePudding

This is code that I have

import pandas as pd

data = [[1,"credit"],[1,"cash"],[1,"credit"],[2,"credit"],[2,"credit"],[2,"credit"],[3,"credit"],[3,"credit"],[3,"credit"]]
df = pd.DataFrame(data, columns=['account_id','type'])

final={}
sym = df.groupby('account_id')
for symbol,groups in sym:
    final[symbol] = groups["type"].value_counts().values[0]

print(final)

when I use

df= pd.DataFrame (final, columns = ['account_id', 'type'])

It shows only | account_id | type |

CodePudding user response：

Before using groupby, you can do a filtering process to filter out rows of not credit.

result = df[df['type'] == 'credit'].groupby('account_id').value_counts()

account_id  type  
1           credit    2
2           credit    3
3           credit    3
dtype: int64

You can use reset_index if you need to access the value.

result.reset_index()

   account_id    type  0
0           1  credit  2
1           2  credit  3
2           3  credit  3

CodePudding user response：

if you want count 'credit type' by account id, use this code

data = [[1, "credit"], [1, "cash"], [1, "credit"], [2, "credit"], [
    2, "credit"], [2, "credit"], [3, "credit"], [3, "credit"], [3, "credit"]]
df = pd.DataFrame(data, columns=['account_id', 'type'])

df.groupby('account_id')['type'].agg(lambda x: (x == 'credit').sum())

df[df['type'] == 'credit'].groupby('account_id')['type'].count()

'credit' can replace df['type'].value_counts().index[0]

CodePudding user response：

Maybe this can help:

df = pd.DataFrame(pd.Series(final), columns = ['type'])
df.index.rename('account_id', inplace=True)

The Output:

account_id  type
1           2
2           3
3           3