hoelzi hoelzi - 1 month ago 6
Python Question

Pandas dataframe, each cell into list - more pythonic way?

I have a pandas dataframe with columns and rows like this:

a b c d
a 40 15 25 35

b 10 25 35 45

c 20 35 45 55

d 40 45 55 65


For all numbers > 30 I need an output like this:

a, a, 40
a, d, 40
b, c, 35
b, d, 45


and so on.

Currently I am running a loop like this:

for i in df.columns:
for j in df.index:
if df[i][j] > 30:
a.append(i+","+j+","+str(df[i][j])")


This works, but is very slow. Is there a more pythonic way to do this?

Thanks!

Answer

You can use stack with boolean indexing:

df = df.stack().reset_index()
df.columns = ['a','b','c']

print (df[df.c > 30])
    a  b   c
0   a  a  40
3   a  d  35
6   b  c  35
7   b  d  45
9   c  b  35
10  c  c  45
11  c  d  55
12  d  a  40
13  d  b  45
14  d  c  55
15  d  d  65

Similar solution:

s = df.stack()
df = s[s > 30].reset_index()
df.columns = ['a','b','c']

print (df)
    a  b   c
0   a  a  40
1   a  d  35
2   b  c  35
3   b  d  45
4   c  b  35
5   c  c  45
6   c  d  55
7   d  a  40
8   d  b  45
9   d  c  55
10  d  d  65

Another solution:

df1 = df[df > 30].stack().reset_index()
df1.columns = ['a','b','c']
df1.c = df1.c.astype(int)
print (df1)
    a  b   c
0   a  a  40
1   a  d  35
2   b  c  35
3   b  d  45
4   c  b  35
5   c  c  45
6   c  d  55
7   d  a  40
8   d  b  45
9   d  c  55
10  d  d  65

Last you can apply join:

df['d'] = df.astype(str).apply(', '.join, axis=1)
print (df)
    a  b   c         d
0   a  a  40  a, a, 40
1   a  d  35  a, d, 35
2   b  c  35  b, c, 35
3   b  d  45  b, d, 45
4   c  b  35  c, b, 35
5   c  c  45  c, c, 45
6   c  d  55  c, d, 55
7   d  a  40  d, a, 40
8   d  b  45  d, b, 45
9   d  c  55  d, c, 55
10  d  d  65  d, d, 65

print (df.d.tolist())
['a, a, 40', 'a, d, 35', 'b, c, 35', 'b, d, 45', 'c, b, 35', 'c, c, 45', 
'c, d, 55', 'd, a, 40', 'd, b, 45', 'd, c, 55', 'd, d, 65']