Search code examples
pythonpandassplitstrsplit

Split a column of a dataframe into two separate columns


I'd like to split a column of a dataframe into two separate columns. Here is how my dataframe looks like (only the first 3 rows):

enter image description here

I'd like to split the column referenced_tweets into two columns: type and id in a way that for example, for the first row, the value of the type column would be replied_to and the value of id would be 1253050942716551168.

Here is what I've tried:

df[['type', 'id']] = df['referenced_tweets'].str.split(',', n=1, expand=True)

but I get the error:

ValueError: Columns must be the same length as key

(I think I get this error because the type in the referenced_tweets column is NOT always replied_to (e.g., it can be retweeted, and therefore, the lengths would be different)


Solution

  • Why not get the values from the dict and add it two new columns?

    def unpack_column(df_series, key):
        """ Function that unpacks the key value of your column and skips NaN values """
        return [None if pd.isna(value) else value[0][key] for value in df_series]
        
        
    df['type'] = unpack_column(df['referenced_tweets'], 'type')
    df['id'] = unpack_column(df['referenced_tweets'], 'id')
    

    or in a one-liner:

    df[['type', 'id']] = df['referenced_tweets'].apply(lambda x: (x[0]['type'], x[0]['id']))