CODEWITHSUNDEEP
pythonpandasdataframerows
uniXVanXcel
uniXVanXcel

Reputation: 806

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

Upvotes: 1

Views: 426

Answers (1)

Nickil Maveli
Nickil Maveli

Reputation: 29719

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

Upvotes: 2

Related Questions