convert list of strings in a column into separated columns Python

Question

I have a column named (events) comes from csv file, I have loaded csv into dataframe. This column contains the events of each soccer match: here is an example: sample of data

I need each key to be a column and the rows will be its value to be like:


event_team  event_time  event_type ....
home         34          Yellow card
away         14          Goal
....

this is a sample file Sample from data how can I do it please ?

Does this answer your question? [Convert Python dict into a dataframe](https://stackoverflow.com/questions/18837262/convert-python-dict-into-a-dataframe) — Aagam Sheth, Aug 02 '21 at 05:24
Step 1: read a turtorial. Step 2: try it. Step 3: if you still can't figure it out read [ask] and [mre] — Julien, Aug 02 '21 at 05:24

Farhood ET · Accepted Answer · 2021-08-02T06:37:44.223

-1

Pandas support reading straight from a list of dicts like this.

list1 = [dict1, dict2, dict3]
df = pd.DataFrame(list1)

Using that you can later select a column using:

column = df["column_name"]

If you want a non pandas way you can do this:

list1 = [dict1, dict2, dict3]

columns = {}

# Initializing the keywords
for d in list1:
    for k in d:
        if k not in columns:
            columns[k] = []

for d in list1:
    for k in columns:
        if k in d:
           columns[k].append(d[k])
        else:
           # because you want all columns to have the same length
           columns[k].append(None) 

print(columns)

EDIT: This script unpacks the "events_list" column to a new dataframe with the given blueprint described by the OP.

import pandas as pd
import ast


df = pd.read_csv("Sampleofuefa.csv")



l = []
for d in df["events_list"]:
    # the values in the columns are strings, you have to interpret them
    # since ast.literal_eval returns a list of dicts, we extend the following
    # list with that list of dict: l = l1 + l2
   l.extend(ast.literal_eval(d))
   


event_df = pd.DataFrame(l)

edited Aug 02 '21 at 06:37

answered Aug 02 '21 at 05:19

Farhood ET

1,432
15
32

This list of dictionaries are values in a column in a dataframe cam from csv file – Ahmed Emad Aug 02 '21 at 05:35
Ah I don't get it. You have a list of dictionaries, am I right? And then you want to change this to a dataframe? – Farhood ET Aug 02 '21 at 05:37
It was my fault to not explain well, yes exactly, I need each key to be a column and values to be the rows. – Ahmed Emad Aug 02 '21 at 05:40
ah. So if you want it that way use the first option and convert your list of dicts to a pandas dataframe. – Farhood ET Aug 02 '21 at 05:41
will I replace this values [dict1, dict2, dict3] with my column ? to be list1 = df['events_list'] – Ahmed Emad Aug 02 '21 at 05:44
Ah I just found out that you have a csv like that. Have you been able to read it into a list of dicts? Or you've not done anything at all? If that's so, could you upload a very small sample for me to work things out for you? – Farhood ET Aug 02 '21 at 05:46
I have loaded the csv file into a dataframe, and this column exists in this df, I will upload a sample. – Ahmed Emad Aug 02 '21 at 06:04
I have edited the post and added a link to sample file, kindly check the post – Ahmed Emad Aug 02 '21 at 06:10
@AhmedEmad check the edit please. I hope this fixes the issue. – Farhood ET Aug 02 '21 at 06:38
@AhmedEmad did the issue got fixed? Never recieved an update from you. – Farhood ET Aug 02 '21 at 16:28
Dear sir, sorry for lating, thank you so much, the solution is right, appreciate it a lot :) – Ahmed Emad Aug 02 '21 at 19:16
@AhmedEmad no problem! great news that it worked for you. If it's the acceptable solution please mark it as the accepted one so if another person had the same situation knows which solution to see. – Farhood ET Aug 03 '21 at 05:18

convert list of strings in a column into separated columns Python

1 Answers1