Pythus Pythus - 2 months ago 18
Python Question

Filtering out string in a Panda Dataframe

I have the following formulas that I use to compute data in my Dataframe. The Datframe consists of data downloaded. My Index is made of dates, and the first row contains only strings..

cols = df.columns.values.tolist()
weight =
pd.DataFrame([df[col] / df.sum(axis=1) for col in df], index=cols).T
std = pd.DataFrame([df.std(axis=1) for col in df], index=cols).T


A B C D E
2006-04-27 00:00:00 'dd' 'de' 'ede' 'wew' 'were'
2006-04-28 00:00:00 69.62 69.62 6.518 65.09 69.62
2006-05-01 00:00:00 71.5 71.5 6.522 65.16 71.5
2006-05-02 00:00:00 72.34 72.34 6.669 66.55 72.34
2006-05-03 00:00:00 70.22 70.22 6.662 66.46 70.22
2006-05-04 00:00:00 68.32 68.32 6.758 67.48 68.32
2006-05-05 00:00:00 68 68 6.805 67.99 68
2006-05-08 00:00:00 67.88 67.88 6.768 67.56 67.88


The Issue I am having is that the formulas I use do not seem to ignore the Index and also the first Indexed row where it's only 'strings'. Thus i get the following error for the weight formula:


TypeError: Cannot compare type 'Timestamp' with type 'str'


and I get the following error for the std formula:


ValueError: No axis named 1 for object type

Answer

You could filter the rows so as to compute weight and standard deviation as follows:

df_string = df.iloc[0]                       # Assign First row to DF
df_numeric = df.iloc[1:].astype(float)       # Assign All rows after first row to DF

cols = df_numeric.columns.values.tolist()

Computing:

weight = pd.DataFrame([df_numeric[col] / df_numeric.sum(axis=1) for col in df_numeric],    
                       index=cols).T
weight

Image

std = pd.DataFrame([df_numeric.std(axis=1) for col in df_numeric],index=cols).T
std

Image

To reassign, say std values back to the original DF, you could do:

df_string_std = df_string.to_frame().T.append(std)  
df_string_std

Image


As the OP had difficulty in reproducing the results, here is the complete summary of the DF used:

df.info()
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 8 entries, 2006-04-27 to 2006-05-08
Data columns (total 5 columns):
A    8 non-null object
B    8 non-null object
C    8 non-null object
D    8 non-null object
E    8 non-null object
dtypes: object(5)
memory usage: 384.0+ bytes

df.index
DatetimeIndex(['2006-04-27', '2006-04-28', '2006-05-01', '2006-05-02',
               '2006-05-03', '2006-05-04', '2006-05-05', '2006-05-08'],
               dtype='datetime64[ns]', name='Date', freq=None)

Starting DFused:

df

Image