Get CSV values on a pandas rolling function

user_4381897 0
Viewed 98

I am trying to get a csv output of values for a given window in a rolling method but I am getting an error must be real number, not str.

It appears that the output must be of numeric type. https://github.com/pandas-dev/pandas/issues/23002

df = pd.DataFrame({"a": range(1,10)})
df.head(10)

# Output
    a
0   1
1   2
2   3
3   4
4   5
5   6
6   7
7   8
8   9

Tried:

to_csv = lambda x: x.to_csv(index=False)
# to_csv = lambda x: ",".join([str(d) for d in x])
df["running_csv"] = df.rolling(min_periods=1, window=3).apply(to_csv) # <= Causes Error

# Error:
# TypeError: must be real number, not str

Expected Output

    a   running_csv
0   1   1
1   2   1,2
2   3   1,2,3
3   4   2,3,4
4   5   3,4,5
5   6   4,5,6
6   7   5,6,7
7   8   6,7,8
8   9   7,8,9

Question: Is there any alternative way to get the CSV output like shown above?

rolling-computation pandas python
4 Answers

Something like this?

>>> df['running_csv'] = pd.Series(df.rolling(min_periods=1, window=3)).apply(lambda x:x.a.values)
>>> df
   a running_csv
0  1         [1]
1  2      [1, 2]
2  3   [1, 2, 3]
3  4   [2, 3, 4]
4  5   [3, 4, 5]
5  6   [4, 5, 6]
6  7   [5, 6, 7]
7  8   [6, 7, 8]
8  9   [7, 8, 9]

From here, further processing should be easy enough.

user_158730430

While it would be great to be able to do this using:

df['a'].astype(str).rolling(min_periods=1, window=3).apply(''.join)

as you mentioned, rolling currently does not work with strings

Here is one way:

(pd.DataFrame({i: df['a'].astype(str).shift(i) for i in range(3)[::-1]})
   .fillna('')
   .apply(','.join, axis=1)
   .str.strip(',')
)

output:

0        1
1      1,2
2    1,2,3
3    2,3,4
4    3,4,5
5    4,5,6
6    5,6,7
7    6,7,8
8    7,8,9
user_163434640

Riding on the incomplete/partial solution by @fsimonjetz, we can complete it to generate the CSV values, as follows:

df['running_csv'] = (pd.Series(df.rolling(min_periods=1, window=3))
                                 .apply(lambda x: x['a'].astype(str).values)
                                 .str.join(',')
                    )

or further simplify it and enhance it to all vectorized functions, as follows:

df['running_csv'] = (pd.Series(df['a'].astype(str).rolling(min_periods=1, window=3))
                                                  .str.join(','))

Now, we have turned all (slow) .apply() and lambda functions to only (fast) vectorized functions.

Result:

print(df)

   a running_csv
0  1           1
1  2         1,2
2  3       1,2,3
3  4       2,3,4
4  5       3,4,5
5  6       4,5,6
6  7       5,6,7
7  8       6,7,8
8  9       7,8,9
user_150706970

pd.rolling doesn't work if the output is not a numeric. Multiple issues exist on github. However, it's possible to get the outcome:

to_str_list = lambda x: ','.join(x[x.notna()].astype(int).astype(str).values.tolist())

df['running_csv'] = pd.concat([df['a'].shift(2), df['a'].shift(1), df['a']], axis=1) \
                      .apply(to_str_list), axis=1)

>>> df
   a running_csv
0  1           1
1  2         1,2
2  3       1,2,3
3  4       2,3,4
4  5       3,4,5
5  6       4,5,6
6  7       5,6,7
7  8       6,7,8
8  9       7,8,9
user_152399510
Related