Code to create sample dataframe:
Sample = [{'account': 'Jones LLC', 'Jan': 150, 'Feb': 200, 'Mar': [[.332, .326], [.058, .138]]},
{'account': 'Alpha Co', 'Jan': 200, 'Feb': 210, 'Mar': [[.234, .246], [.234, .395], [.013, .592]]},
{'account': 'Blue Inc', 'Jan': 50, 'Feb': 90, 'Mar': [[.084, .23], [.745, .923], [.925, .843]]}]
df = pd.DataFrame(Sample)
Sample Dataframe visualized:
df:
account Jan Feb Mar
Jones LLC | 150 | 200 | [.332, .326], [.058, .138]
Alpha Co | 200 | 210 | [[.234, .246], [.234, .395], [.013, .592]]
Blue Inc | 50 | 90 | [[.084, .23], [.745, .923], [.925, .843]]
I looking for a formula to truncate the 'Mar' column so that any row with shape larger than (2,x) is truncated, resulting in the following df
df:
account Jan Feb Mar
Jones LLC | 150 | 200 | [.332, .326], [.058, .138]
Alpha Co | 200 | 210 | [[.234, .246], [.234, .395]
Blue Inc | 50 | 90 | [[.084, .23], [.745, .923]
Operations on a cell level can easily be done using .apply function, combined with lambda operator:
df["Mar"] = df["Mar"].apply(lambda x: x[:2])
str accessor is designed for string operations but for iterables like lists, you can use it for slicing too:
df['Mar'] = df['Mar'].str[:2]
df
Out:
Feb Jan Mar account
0 200 150 [[0.332, 0.326], [0.058, 0.138]] Jones LLC
1 210 200 [[0.234, 0.246], [0.234, 0.395]] Alpha Co
2 90 50 [[0.084, 0.23], [0.745, 0.923]] Blue Inc
Pandas doesn't play particularly nicely with lists in series, so it might work better to pull this out before working on it:
df['Mar'] = [row[:2] for row in df['Mar'].tolist()]
%timeit results for this and the excellent answers by ayhan and Marjan:
3 rows:
%timeit df['Mar'].str[:2]
10000 loops, best of 3: 154 µs per loop
%timeit df['Mar'].apply(lambda x: x[:2])
10000 loops, best of 3: 133 µs per loop
%timeit [row[:2] for row in df['Mar'].tolist()]
The slowest run took 5.51 times longer than the fastest. This could mean that an intermediate result is being cached.
100000 loops, best of 3: 12 µs per loop
>>>5.51*12
66.12
3,000,000 rows:
%timeit df['Mar'].str[:2]
1 loop, best of 3: 1.23 s per loop
%timeit df['Mar'].apply(lambda x: x[:2])
1 loop, best of 3: 1.25 s per loop
%timeit [row[:2] for row in df['Mar'].tolist()]
1 loop, best of 3: 940 ms per loop
If you're willing to split the lists pairs into two columns, you can get a method that scales a bit better to larger dataframes than the above with
>>>pd.DataFrame(df['Mar'].tolist()).iloc[:, :2]]
0 1
0 [0.332, 0.326] [0.058, 0.138]
1 [0.234, 0.246] [0.234, 0.395]
2 [0.084, 0.23] [0.745, 0.923]
On 3,000,000 rows:
%timeit pd.DataFrame(df['Mar'].tolist()).iloc[:, :2]
1 loop, best of 3: 276 ms per loop
