Convert dataframe column to 1 or 0 for true/false values and assign to dataframe
@chappers solution (in the comments) works as.integer(as.logical(data.frame$column.name))
How can I map True/False to 1/0 in a Pandas DataFrame?
A succinct way to convert a single column of boolean values to a column of integers 1 or 0:
df["somecolumn"] = df["somecolumn"].astype(int)
Converting 'no' and 'yes' into 0 and 1 in pandas dataframe
You can also just use replace
:
df.edjefe.replace(to_replace=['no', 'yes'], value=[0, 1])
How to map True and False to 'Yes' and 'No' in a pandas data frame for columns of dtype bool only?
Use the dtypes attribute to check if the column is boolean and filter based on that:
df = pd.DataFrame({'A': [0, 1], 'B': ['x', 'y'],
'C': [True, False], 'D': [False, True]})
df
Out:
A B C D
0 0 x True False
1 1 y False True
bool_cols = df.columns[df.dtypes == 'bool']
df[bool_cols] = df[bool_cols].replace({True: 'Yes', False: 'No'})
df
Out:
A B C D
0 0 x Yes No
1 1 y No Yes
I think the fastest way would be to use map in a loop though:
for col in df.columns[df.dtypes == 'bool']:
df[col] = df[col].map({True: 'Yes', False: 'No'})
Replacing few values in a pandas dataframe column with another value
The easiest way is to use the replace
method on the column. The arguments are a list of the things you want to replace (here ['ABC', 'AB']
) and what you want to replace them with (the string 'A'
in this case):
>>> df['BrandName'].replace(['ABC', 'AB'], 'A')
0 A
1 B
2 A
3 D
4 A
This creates a new Series of values so you need to assign this new column to the correct column name:
df['BrandName'] = df['BrandName'].replace(['ABC', 'AB'], 'A')
Converting true/false to 0/1 boolean in a mixed dataframe
Let's modify your lambda to use an isinstance
check:
df.applymap(lambda x: int(x) if isinstance(x, bool) else x)
Only values of type bool
will be converted to int
, everything else remains the same.
As a better solution, if the column types are scalar (and not "mixed" as I originally assumed given your question), you can instead use
u = df.select_dtypes(bool)
df[u.columns] = u.astype(int)
How to unnest (explode) a column in a pandas DataFrame, into multiple rows
I know object
dtype columns makes the data hard to convert with pandas functions. When I receive data like this, the first thing that came to mind was to "flatten" or unnest the columns.
I am using pandas and Python functions for this type of question. If you are worried about the speed of the above solutions, check out user3483203's answer, since it's using numpy and most of the time numpy is faster. I recommend Cython or numba if speed matters.
Method 0 [pandas >= 0.25]
Starting from pandas 0.25, if you only need to explode one column, you can use the pandas.DataFrame.explode
function:
df.explode('B')
A B
0 1 1
1 1 2
0 2 1
1 2 2
Given a dataframe with an empty list
or a NaN
in the column. An empty list will not cause an issue, but a NaN
will need to be filled with a list
df = pd.DataFrame({'A': [1, 2, 3, 4],'B': [[1, 2], [1, 2], [], np.nan]})
df.B = df.B.fillna({i: [] for i in df.index}) # replace NaN with []
df.explode('B')
A B
0 1 1
0 1 2
1 2 1
1 2 2
2 3 NaN
3 4 NaN
Method 1apply + pd.Series
(easy to understand but in terms of performance not recommended . )
df.set_index('A').B.apply(pd.Series).stack().reset_index(level=0).rename(columns={0:'B'})
Out[463]:
A B
0 1 1
1 1 2
0 2 1
1 2 2
Method 2
Using repeat
with DataFrame
constructor , re-create your dataframe (good at performance, not good at multiple columns )
df=pd.DataFrame({'A':df.A.repeat(df.B.str.len()),'B':np.concatenate(df.B.values)})
df
Out[465]:
A B
0 1 1
0 1 2
1 2 1
1 2 2
Method 2.1
for example besides A we have A.1 .....A.n. If we still use the method(Method 2) above it is hard for us to re-create the columns one by one .
Solution : join
or merge
with the index
after 'unnest' the single columns
s=pd.DataFrame({'B':np.concatenate(df.B.values)},index=df.index.repeat(df.B.str.len()))
s.join(df.drop('B',1),how='left')
Out[477]:
B A
0 1 1
0 2 1
1 1 2
1 2 2
If you need the column order exactly the same as before, add reindex
at the end.
s.join(df.drop('B',1),how='left').reindex(columns=df.columns)
Method 3
recreate the list
pd.DataFrame([[x] + [z] for x, y in df.values for z in y],columns=df.columns)
Out[488]:
A B
0 1 1
1 1 2
2 2 1
3 2 2
If more than two columns, use
s=pd.DataFrame([[x] + [z] for x, y in zip(df.index,df.B) for z in y])
s.merge(df,left_on=0,right_index=True)
Out[491]:
0 1 A B
0 0 1 1 [1, 2]
1 0 2 1 [1, 2]
2 1 1 2 [1, 2]
3 1 2 2 [1, 2]
Method 4
using reindex
or loc
df.reindex(df.index.repeat(df.B.str.len())).assign(B=np.concatenate(df.B.values))
Out[554]:
A B
0 1 1
0 1 2
1 2 1
1 2 2
#df.loc[df.index.repeat(df.B.str.len())].assign(B=np.concatenate(df.B.values))
Method 5
when the list only contains unique values:
df=pd.DataFrame({'A':[1,2],'B':[[1,2],[3,4]]})
from collections import ChainMap
d = dict(ChainMap(*map(dict.fromkeys, df['B'], df['A'])))
pd.DataFrame(list(d.items()),columns=df.columns[::-1])
Out[574]:
B A
0 1 1
1 2 1
2 3 2
3 4 2
Method 6
using numpy
for high performance:
newvalues=np.dstack((np.repeat(df.A.values,list(map(len,df.B.values))),np.concatenate(df.B.values)))
pd.DataFrame(data=newvalues[0],columns=df.columns)
A B
0 1 1
1 1 2
2 2 1
3 2 2
Method 7
using base function itertools
cycle
and chain
: Pure python solution just for fun
from itertools import cycle,chain
l=df.values.tolist()
l1=[list(zip([x[0]], cycle(x[1])) if len([x[0]]) > len(x[1]) else list(zip(cycle([x[0]]), x[1]))) for x in l]
pd.DataFrame(list(chain.from_iterable(l1)),columns=df.columns)
A B
0 1 1
1 1 2
2 2 1
3 2 2
Generalizing to multiple columns
df=pd.DataFrame({'A':[1,2],'B':[[1,2],[3,4]],'C':[[1,2],[3,4]]})
df
Out[592]:
A B C
0 1 [1, 2] [1, 2]
1 2 [3, 4] [3, 4]
Self-def function:
def unnesting(df, explode):
idx = df.index.repeat(df[explode[0]].str.len())
df1 = pd.concat([
pd.DataFrame({x: np.concatenate(df[x].values)}) for x in explode], axis=1)
df1.index = idx
return df1.join(df.drop(explode, 1), how='left')
unnesting(df,['B','C'])
Out[609]:
B C A
0 1 1 1
0 2 2 1
1 3 3 2
1 4 4 2
Column-wise Unnesting
All above method is talking about the vertical unnesting and explode , If you do need expend the list horizontal, Check with pd.DataFrame
constructor
df.join(pd.DataFrame(df.B.tolist(),index=df.index).add_prefix('B_'))
Out[33]:
A B C B_0 B_1
0 1 [1, 2] [1, 2] 1 2
1 2 [3, 4] [3, 4] 3 4
Updated function
def unnesting(df, explode, axis):
if axis==1:
idx = df.index.repeat(df[explode[0]].str.len())
df1 = pd.concat([
pd.DataFrame({x: np.concatenate(df[x].values)}) for x in explode], axis=1)
df1.index = idx
return df1.join(df.drop(explode, 1), how='left')
else :
df1 = pd.concat([
pd.DataFrame(df[x].tolist(), index=df.index).add_prefix(x) for x in explode], axis=1)
return df1.join(df.drop(explode, 1), how='left')
Test Output
unnesting(df, ['B','C'], axis=0)
Out[36]:
B0 B1 C0 C1 A
0 1 2 1 2 1
1 3 4 3 4 2
Update 2021-02-17 with original explode function
def unnesting(df, explode, axis):
if axis==1:
df1 = pd.concat([df[x].explode() for x in explode], axis=1)
return df1.join(df.drop(explode, 1), how='left')
else :
df1 = pd.concat([
pd.DataFrame(df[x].tolist(), index=df.index).add_prefix(x) for x in explode], axis=1)
return df1.join(df.drop(explode, 1), how='left')
replace() method not working on Pandas DataFrame
You need to assign back
df = df.replace('white', np.nan)
or pass param inplace=True
:
In [50]:
d = {'color' : pd.Series(['white', 'blue', 'orange']),
'second_color': pd.Series(['white', 'black', 'blue']),
'value' : pd.Series([1., 2., 3.])}
df = pd.DataFrame(d)
df.replace('white', np.nan, inplace=True)
df
Out[50]:
color second_color value
0 NaN NaN 1.0
1 blue black 2.0
2 orange blue 3.0
Most pandas ops return a copy and most have param inplace
which is usually defaulted to False
Related Topics
Convert Multiple Columns of Numeric Data to Dates in R
Create Counter Within Consecutive Runs of Values
Pass a String as Variable Name in Dplyr::Filter
Split Delimited Strings in a Column and Insert as New Rows
How to Get a Contingency Table
Convert Data.Frame Columns from Factors to Characters
How to Escape Backslashes in R String
Create a Sequential Number (Counter) For Rows Within Each Group of a Dataframe
Adding Some Space Between the X-Axis and the Bars, in Ggplot
Installing Rgl on Ubuntu and Mac: X11 Not Found
Order Discrete X Scale by Frequency/Value
How to Use a Variable to Specify Column Name in Ggplot
Error in ≪My Code≫: Object of Type 'Closure' Is Not Subsettable
How to Combine Multiple Conditions to Subset a Data-Frame Using "Or"
Why Does Data.Table Update Names(Dt) by Reference, Even If I Assign to Another Variable