193
Pandas isna() vs isnull().
I'm assuming you are referring to pandas.DataFrame.isna() vs pandas.DataFrame.isnull(). Not to confuse with pandas.isnull(), which in contrast to the two above isn't a method of the DataFrame class.
These two DataFrame methods do exactly the same thing! Even their docs are identical. You can even confirm this in pandas' code.
...
32
If you want to check equals values on a certain column let's say Name you can merge both Dataframes to a new one:
mergedStuff = pd.merge(df1, df2, on=['Name'], how='inner')
mergedStuff.head()
I think this is more efficient and faster then whereif you have a big data set
28
In case this can help anyone else. Here is a solution that is more computationally efficient.
TL;DR version
If each row already has a unique index, then do this:
>>> df.loc[df.groupby('A')['C'].idxmin()]
If you've already indexed by 'A', then convert 'A' back into a column first.
>>> df2 = df.reset_index()
>>> df2.loc[df2....
19
df1.where(df1.values==df2.values).notna()
True entries show common elements. This also reveals the position of the common elements, unlike the solution with merge.
18
pivot_table was made for this:
df.pivot_table(index='Date',columns='Groups',aggfunc=sum)
results in
data
Groups one two
Date
2017-1-1 3.0 NaN
2017-1-2 3.0 4.0
2017-1-3 NaN 5.0
Personally I find this approach much easier to understand, and certainly more pythonic than a convoluted groupby operation. Then if you want the format ...
16
Feeding your column names into the y values argument as a list works for me like so:
total_year[-15:].plot(x='year', y=['action', 'comedy'], figsize=(10,5), grid=True)
Using something like the answer at this link is better and gives you way more control over the labels and whatnot:
adding lines with plt.plot()
13
You may use
df2 = df.groupby(['address']).sum()
or
df2 = df.groupby(['address']).agg('sum')
If there are columns other than balances that you want to peak only the first or max value, or do mean instead of sum, you can go as follows:
d = {'address': ["A", "A", "B"], 'balances': [30, 40, 50], 'sessions': [2, 3, 4]}
df = pd.DataFrame(data=d)
df2 = df....
12
Comparing values in two different columns
Using set, get unique values in each column. The intersection of these two sets will provide the unique values in both the columns.
Example:
df1 = pd.DataFrame({'c1': [1, 4, 7], 'c2': [2, 5, 1], 'c3': [3, 1, 1]})
df2 = pd.DataFrame({'c4': [1, 4, 7], 'c2': [3, 5, 2], 'c3': [3, 7, 5]})
set(df1['c2']).intersection(set(...
12
You can double check the exact number of common and different positions between two df by using isin and value_counts()
Like that:
df['your_column_name'].isin(df2['your_column_name']).value_counts()
Result:
True = common
False = different
12
an example to write in same sheet:
import pandas as pd
data1 = """
class precision recall
<18 0.0125 12
18-24 0.0250 16
25-34 0.00350 4
"""
data2 = """
class precision recall
<18 0 0
18-24 0.25 6
25-34 0.35 5
"""
#create 2 df for ...
11
You can do this. But I doubt the efficiency.
>> import pandas as pd
>> df = pd.DataFrame({'a':[1,1,3,3],'b':[4,5,6,3], 'c':[1,2,3,5]})
>> df
a b c
0 1 4 1
1 1 5 2
2 3 6 3
3 3 3 5
>> df[df['c'].isin(df.groupby('a').min()['c'].values)]
a b c
0 1 4 1
2 3 6 3
11
One option is to map rare values to 'other'. This is commonly done in e.g. natural language processing - the intuition being that very rare labels don't carry much statistical power.
I have also seen people map 1-hot categorical values to lower-dimensional vectors, where each 1-hot vector is re-represented as a draw from a multivariate Gaussian. See e.g. ...
11
The best way would be to use drop_duplicates(). If you have a larger DataFrame and only want those two columns checked, set subset equal to the combined columns you want checked.
df = df.drop_duplicates()
or
df = df.drop_duplicates(subset=['userid', 'itemid'])
11
You can use this:
df.columns = ['Goods_1', 'Durable goods','Services','Exports', 'Goods_2', 'Services', 'Imports', 'Goods_3', 'Services']
or if you have too many columns:
cols = []
count = 1
for column in df.columns:
if column == 'Goods':
cols.append(f'Goods_{count}')
count+=1
continue
cols.append(column)
df.columns = cols
11
The function dataframe.columns.difference() gives you complement of the values that you provide as argument. It can be used to create a new dataframe from an existing dataframe with exclusion of some columns. Let us look through an example:
In [2]: import pandas as pd
In [3]: import numpy as np
In [4]: df = pd.DataFrame(np.random.randn(5, 4), columns=list(...
10
Consider the following approach:
def f(col, threshold=3):
mask = col.groupby((col != col.shift()).cumsum()).transform('count').lt(threshold)
mask &= col.eq(0)
col.update(col.loc[mask].replace(0,1))
return col
In [79]: df.apply(f, threshold=3)
Out[79]:
col1 col2
names
A 1 0
B 1 0
C 1 0
D ...
9
Pandas black magic:
df = df.groupby(['Date', 'Groups']).sum().sum(
level=['Date', 'Groups']).unstack('Groups').fillna(0).reset_index()
# Fix the column names
df.columns = ['Date', 'one', 'two']
Resulting df:
Date one two
0 2017-1-1 3.0 0.0
1 2017-1-2 3.0 4.0
2 2017-1-3 0.0 5.0
9
You can try this:
cond = df1['Email'].isin(df2['Email'])
df1.drop(df1[cond].index, inplace = True)
>>df1
First Last Email
2 Joe Max email3@email.com
3 Will Bill email4@email.com
9
Break col1 into sub-groups of consecutive strings. Extract first and last entry per sub-group.
Something like this:
df = pd.DataFrame({'col1':['A>G','C>T','C>T','G>T','C>T', 'A>G','A>G','A>G'],'col2':['TCT','ACA','TCA','TCA','GCT', 'ACT','CTG','ATG'], 'start':[1000,2000,3000,4000,5000,6000,10000,20000]})
df['subgroup'] = (df['col1']...
8
cols = list(df.columns)
cols = [cols[-1]] + cols[:-1]
df = df[cols]
8
You can convert df2 to a dictionary and use that to replace the values in df1
cat_1 = [10, 11, 12]
cat_2 = [25, 22, 30]
cat_3 = [12, 14, 15]
df1 = pd.DataFrame({'cat1':cat_1, 'cat2':cat_2, 'cat3':cat_3})
all_cats = [10, 11, 12, 25, 22, 30, 15]
cat_codes = ['A', 'B', 'C', 'D', 'E', 'F', 'G']
df2 = pd.DataFrame({'all_cats':all_cats, 'cat_codes':cat_codes})
...
6
As Emre already mentioned, you may use the groupby function. After that, you should apply reset_index to move the MultiIndex to the columns:
import pandas as pd
df = pd.DataFrame( [ ['Hospital 1', 'District 19', 5],
['Hospital 1', 'District 19', 10],
['Hospital 1', 'District 19', 6],
['Hospital ...
6
I found the issue, I need to return a pd.Series()
return pd.Series([
out_cost_price_dist,
out_cost_price_stock,
out_cost_price_expected,
out_cost_price_country,
out_cost_price_real_dist,
out_cost_price_real_stock,
out_cost_price_real_expected,
out_cost_price_real_country
])
6
Based on the documentation 0.22 and 0.24.1, the flavor does not exist in the argument list of the to_sql method. You're probably running the 0.24.1 version which does not need flavor argument.
5
First check your data. You can't get value 12.780517 for index 196341. This value will be for index 196641.
Now, to get column 'b' in your result, use pd.merge. Don't make column 'a' as index. Leave it as it is.
>>> df = pd.DataFrame({'a':[196512, 196512, 196512, 196512, 196795, 196795, 196795, 196795, 196341, 196341, 196641, 196641, 196346, ...
5
This sort of effect can be achieved with
pandas.DataFrame.resample() combined with Resampler.aggregate() like:
Code:
df.resample("1Min").agg({'A': sum, 'B': np.mean})
Test code:
df = pd.read_fwf(StringIO(u"""
A B
2017-01-01T00:01:01 0 100
2017-01-01T00:01:10 1 200
2017-01-01T00:01:16 2 300
...
5
You should use pandas.DataFrame.shift() to find the pattern you need.
Code:
def fill_zero_not_3(series):
zeros = (True, True, True)
runs = [tuple(x == 0 for x in r)
for r in zip(*(series.shift(i)
for i in (-2, -1, 0, 1, 2)))]
need_fill = [(r[0:3] != zeros and r[1:4] != zeros and r[2:5] != zeros)
...
5
I found a package called pandasql, which is based on sqldf for R. It seems quite a bit slower than doing the transformations with the pandas package, but it gets the job done. Just put the SQL query into a string like this:
query_string = """
select * from df
"""
Then use the string in the pandasql.sqldf package, as follows:
new_dataframe = pandasql....
5
You simply need to do:
df['NEWcolumn'] = df['COLUMN_to_Check'].str.contains(pattern)
df['NEWcolumn'] = df['NEWcolumn'].map({True: 'Yes', False: 'No'})
5
df3 = pd.merge(df1,df2,left_on=['cat'+str(i)], right_on = ['cat_codes'], how = 'left')
I would iterate this for cat1,cat2 and cat3. This does not replace the existing column values but appends new columns.
Only top voted, non community-wiki answers of a minimum length are eligible
